What is the best way to track payroll total by month/employee/taxes/YTD

Hello – as a 45 day old newbie I have learned so much from this forum – many thanks to all the contributors.

I have been working on a project for 8 weeks now and decided to finally ask for help. Attached is a workbook (excel 2010) in which I utilize as a backdoor to do payroll via inventory in my accounting software.

I need the most logical, efficient and simple way to track payroll values by employee/item/monthly and cumulative YTD amounts. I may be making this more complicated than it needs to be, but in 45 days I went from being familiar with simple sum values to now realizing that the possibilities in excel are endless, but I have a long way to go in understanding if I should use tables, names, concatenate, name lists, etc…

I would like to be able to enter the information once in the sheet tab “PR details by PDF’s” in columns B through F for each respective Pay period (Pp). I would like the Totals for each row/column to update the “cumulative” sheet accordingly.

I would like to utilize the names in sheet “Option 1” –to create a name list for the entire year. My thought was to use those columns/rows to auto-create defined names from selection (create from selection) for each Pay period month - is this possible? Or, am I misunderstanding the use for the defined names (manager)?

Is it necessary/advantageous to create a name list with the corresponding name and values in order for the “cumulative” sheet to be updated correctly?

Is it possible to group names in the name manager by Pay period (i.e.: month) to make the process a little bit easier?

Is sheet tab “option 3” an easier way to create a name list (if creating a name list is what I need to do)?


Sheet name information:

PR details by pdf: data is entered for each pay period (Pp) manually and totals are calculated with simple sum formulas.

Cumulative: Summary by month and cumulative values for each employee by Pp in addition to taxes in similar form.

If there is an easier method in tracking monthly and cumulative amounts, any assistance or feedback would be greatly appreciated.


***After submitting post I checked the attachment and despite 3 attempts, when I try to open the attachment via the site, each instance reports it as a corrupted file; any suggestions?

Morning D794913 ........

Firstly allow me to congratulate you on having a go and trying to learn for yourself. People who try, deserve respect points and are appreciated

Now for the negatives
1. Your thread question is more about payroll than Excel which makes the question too broad for this forum because there are multiple ways of dealing with a payroll database/spreadsheet which are all adequate.
2. There are two many questions... Bear in mind that this is an Excel forum... Each thread should be related to one specific Excel based question so that future searches are more effective... The moderators tend to nag about these things but it's all in a good cause..

And Some general tips for spreadsheet design.
1. In general principles design your spreadsheet along the lines of a car design i.e.
a. The engine is under the bonnet and invisible to the driver
b. The drivers control interface (drivers seat) consists of a clean and simple interface broken down into
a.b.1. Control inputs (steering wheel/accelerator etc)
1.b.2. Feedback instruments and displays... (Speedo, Engine temp gauge, Oil pressure warning light etc)
2. The database (all the input data) should be stored on a single worksheet database arranged in a single contiguous table... This raw data is "under the bonnet" usually ....... Excel tools are optimised for data laid out in this way... This means the raw data table does not look good for presentation and so is usually kept hidden (and protected) from the driver.

It takes a while to develop all the techniques to design an Excel database based on the above principles but if you work towards those you will make future development easier and you can build up in stages.

Hi D

About the question and the forum ... I think the aim of this forum is to provide a database of Excel related questions and answers... The broad based questions are just as relevant but don't support (the prime objective).... Not my call mate - the moderators will have us both on a Courts Martial LOL...

2. Named ranges are very (very) powerful and are one technique well worth getting familiar with ... There are often alternative ways to achieve the same result but they do help to maintain simplicity as your spreadsheet grows in complexity ... This generally allows you to build more complex designs.

3.Try attaching the workbook as an Excel 97-2003 (.xls) ... they seem to give less corruption problems... - It's related to security encryption but that's about as much as I know.

As for language ......... Trying to explain Excel problems in "plain" English is extremely difficult - I don't think anyone has (or will) ever really master that

Originally Posted by D794913

when I tried to attach the workbook, I repeatedly received a "corrupted file" message when I tried to open it (after loaded and posting complete) - I made three attempts and changed the ext. name each time...

Just a suggestion: Could you trying zipping the workbook and uploading the zip instead?

Originally Posted by D794913

Absolutely - attached. Thank you!

Hello D794913

I have to say I admire your courage at tackling something like this.

I once created a rough and ready payroll workbook system some time ago and it took a long while to get it anywhere near what was required .

Anyway I have taken a copy of the most recent version, removed most of the functionality (that you are not requiring) and tried to adapt the columns more towards your required inputs. I am uploading it for your interest (see attached).

* Please note that it was not designed for a USA payroll so you if you decide to use it "as is" (which I wouldn't recommend), you would probably need to change it extensively.

* What you could do, however; is browse through it and feel free to copy any ideas on the design, layout etc (ignore the colour format though - All but the 5th tab were intended to be only seen by me so I didn't spend much time on trying to make them look nice )

* Some quick notes:

1. The 1st 2 tabs are "set up" pages for entering employer related data, tax year dates & pay period descriptions. A lot of these fields relate to other tabs that have been removed. I think the Employer Name, Pay Period descriptions and possibly the tax year start/end dates are all that you will need to complete.

2. The 3rd tab is a "set up" page for entering in employee related information. Again almost all of these apply to tabs that are not in the uploaded version (thus can be ignored).

3. The 4th tab is the user input page. Starting from cell B2, you enter data in columns B to N. (Ignore and don't touch column A, columns O - X). Note that the pay period & the employee name are always entered from dropdown boxes. This prevents the summary reports from missing entries due to typos in the input.

4. The 5th tab is what I understand you are after. This is a report tab which produces two pages when printed. "Wages by Employee" and "Wages by all employees year to date". You can change the employee to display by using the dropdown box in cell B2.


Sorry I know this is very rough. If you PM me with any questions on it, I will try to get back to you within a few days.

This is a different approach that you might be interested in.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.