Building the simplest payroll template in Excel. No, really simple.
June 2, 2011 4:57 PM Subscribe
I'm dusting off my rusty Excel skills to try to build an easy-to-use, easily updated very basic payroll spreadsheet for a userbase which is completely unfamiliar with Excel.
What they're doing is manually entering a bunch of names, ID#s and SS #s into an Excel spreadsheet every day and sending it along to higher-ups.
The list of available names, IDs and SS doesn't change all that much. What I would like for them to be able to do is to have a way to easily enter that info - whether it's to type the first few letters of the last name and have it auto-populate, pull it off a drop-down, something.
I have the template and I have a list of the personnel, which is about 25 people. What's throwing me is that the name/ID/SS all need to be in their own columns in the same row, and I'd like that info to be idiot-proof to enter....these are guys who can barely type.
Further, on occasion names will be added to the roster, so there needs to be a dead-simple method to do this as well.
What I was thinking was that I would hide the personnel roster in the appropriate columns further on down the spreadsheet, and when they started to type a last name, all the info would fill itself in. I'm finding this is beyond what I remember.
The big wrinkle: 90% of the users are very unfamiliar with Excel, or with computers in general. This needs to be as easy as possible to use, update and (for me to) teach.
I have extensive experience with Excel in the past, but haven't used it in maybe 7 years or so and am finding myself very, very rusty.
What they're doing is manually entering a bunch of names, ID#s and SS #s into an Excel spreadsheet every day and sending it along to higher-ups.
The list of available names, IDs and SS doesn't change all that much. What I would like for them to be able to do is to have a way to easily enter that info - whether it's to type the first few letters of the last name and have it auto-populate, pull it off a drop-down, something.
I have the template and I have a list of the personnel, which is about 25 people. What's throwing me is that the name/ID/SS all need to be in their own columns in the same row, and I'd like that info to be idiot-proof to enter....these are guys who can barely type.
Further, on occasion names will be added to the roster, so there needs to be a dead-simple method to do this as well.
What I was thinking was that I would hide the personnel roster in the appropriate columns further on down the spreadsheet, and when they started to type a last name, all the info would fill itself in. I'm finding this is beyond what I remember.
The big wrinkle: 90% of the users are very unfamiliar with Excel, or with computers in general. This needs to be as easy as possible to use, update and (for me to) teach.
I have extensive experience with Excel in the past, but haven't used it in maybe 7 years or so and am finding myself very, very rusty.
Storing social security numbers anywhere you don't have to, in a nonencrypted form, is a Bad Idea. If you need to show the security numbers (e.g. you have 20 J. Smiths working) only store and display the last four digits. Hiding data in Excel is rather difficult to do correctly (hidden rows always make me curious, and usually even if they are locked, cell formula access can display the data inside).
Personally, I'd use Access to make a dumb, single entry form which talks to some sort of database on the backend (Access can even talk to an Excel spreadsheet on a file server, if that's what you want).
OpenOffice Base is the open source Access clone in case you are interested.
posted by benzenedream at 6:07 PM on June 2, 2011
Personally, I'd use Access to make a dumb, single entry form which talks to some sort of database on the backend (Access can even talk to an Excel spreadsheet on a file server, if that's what you want).
OpenOffice Base is the open source Access clone in case you are interested.
posted by benzenedream at 6:07 PM on June 2, 2011
Hiding data in Excel is rather difficult to do correctly (hidden rows always make me curious, and usually even if they are locked, cell formula access can display the data inside)
This can't be emphasized enough. It is easy to do badly though. I once got a national contact list from a major organization with a few thousand names with all sensitive data "blacked out" by simply changing all the cell backgrounds to black, same as the text. You never know how someone will misuse your sheet in the future.
posted by meinvt at 6:15 PM on June 2, 2011
This can't be emphasized enough. It is easy to do badly though. I once got a national contact list from a major organization with a few thousand names with all sensitive data "blacked out" by simply changing all the cell backgrounds to black, same as the text. You never know how someone will misuse your sheet in the future.
posted by meinvt at 6:15 PM on June 2, 2011
What they're doing is manually entering a bunch of names, ID#s and SS #s into an Excel spreadsheet every day and sending it along to higher-ups ... 90% of the users are very unfamiliar with Excel, or with computers in general. This needs to be as easy as possible to use, update and (for me to) teach.
I'm a school computer technician. The teachers at my school range from very computer literate to self-avowed Luddites. I have found over the years that the only workable way to deal with the Luddites is to suppress my tendency to hop on one foot and go "but... but... but..." when I see them doing something that ought to be easy in some bizarrely convoluted way that takes twenty times as long as necessary.
The only way you will ever get a Luddite to change is by making the old way impossible. If you give one something that looks even slightly like the old thing, they will revert to using it the old way because that's easier for them. So unless the present idiotic approach is actually causing quantifiable harm to the business, I recommend you leave it alone.
posted by flabdablet at 6:33 PM on June 2, 2011
I'm a school computer technician. The teachers at my school range from very computer literate to self-avowed Luddites. I have found over the years that the only workable way to deal with the Luddites is to suppress my tendency to hop on one foot and go "but... but... but..." when I see them doing something that ought to be easy in some bizarrely convoluted way that takes twenty times as long as necessary.
The only way you will ever get a Luddite to change is by making the old way impossible. If you give one something that looks even slightly like the old thing, they will revert to using it the old way because that's easier for them. So unless the present idiotic approach is actually causing quantifiable harm to the business, I recommend you leave it alone.
posted by flabdablet at 6:33 PM on June 2, 2011
If you're going to change anything at all, just take your sensitive list of SS# away from the computer-illiterate data entry people so that all they have to enter is name and internal ID#. You can build something nice for the higher-ups (who presumably have at least some minimal computing skills) that validates the ID# against the name and auto-fills the SS#.
posted by flabdablet at 6:36 PM on June 2, 2011
posted by flabdablet at 6:36 PM on June 2, 2011
Response by poster: I'm only using the last 4 digits of the SS#. There are only a small handful of people who will ever see this (4 at the most, actually.)
If you're going to change anything at all, just take your sensitive list of SS# away from the computer-illiterate data entry people so that all they have to enter is name and internal ID#.
Unfortunately, it's the computer illiterates who are responsible for the data entry...they're heads of department at a union shop which has never had to use computers before.
posted by nevercalm at 6:48 PM on June 2, 2011
If you're going to change anything at all, just take your sensitive list of SS# away from the computer-illiterate data entry people so that all they have to enter is name and internal ID#.
Unfortunately, it's the computer illiterates who are responsible for the data entry...they're heads of department at a union shop which has never had to use computers before.
posted by nevercalm at 6:48 PM on June 2, 2011
Seconding using Access instead -- very similar for you in terms of setup, and if you're rusty with Excel it shouldn't be a disadvantage to learn some basic Access -- but it will be soooo much nicer for the users. You can easily make a form that will be much easier to read and enter data into than a nasty ol' spreadsheet.
And nthing no SS#s anywhere unless absolutely 100% necessary. I'm in Canada so different laws apply here, but we'd have to get permission from an ethics board or the privacy commissioner to have personal identifying data flying around like that.
posted by lulu68 at 6:51 PM on June 2, 2011
And nthing no SS#s anywhere unless absolutely 100% necessary. I'm in Canada so different laws apply here, but we'd have to get permission from an ethics board or the privacy commissioner to have personal identifying data flying around like that.
posted by lulu68 at 6:51 PM on June 2, 2011
Response by poster: Access would be great, but this workstation doesn't have it, and it took them TWO YEARS to have a shitty 5 year old soon-to-be-junk computer to be put in here in the first place. I can't even imagine what them asking for Access would result in.
I actually had my identity stolen as a teen...I wouldn't wish that on anyone. I can't stress enough that full SS #s aren't going to be used here.
posted by nevercalm at 7:19 PM on June 2, 2011
I actually had my identity stolen as a teen...I wouldn't wish that on anyone. I can't stress enough that full SS #s aren't going to be used here.
posted by nevercalm at 7:19 PM on June 2, 2011
I do know that Excel has a feature where you can lock certain cells, so the user can only manipulate the cells that you want them too. I think you can also put in "tool tips" that instruct them on what to put in each box. You might want to hilight the boxes or something to make it really obvious.
posted by radioamy at 7:43 PM on June 2, 2011
posted by radioamy at 7:43 PM on June 2, 2011
« Older What is the title, author, and complete text of a... | So many bed bugs. How to not have so many bed bugs... Newer »
This thread is closed to new comments.
posted by cranberryskies at 5:22 PM on June 2, 2011