When I decided to hire a couple of part-timers to help me with my business, my goal was to save time by having them take over some routine jobs for me. But in order to make it worth my while, I had to ensure that the payroll tasks involved wouldn’t cancel out any benefits I derived from their employment.
For two part-time workers, I figured that the simplest way to keep records of their time and paychecks was to use the database feature of Excel 2000. It was easy to set up the worksheet to record their hours and pay each week. After that, all I needed was a straightforward way to sort and total each employee's pay at the end of every month. I found my answer in Excel’s Data-Subtotals menu. Using Data-Subtotals, I could quickly sort, subtotal, and get a monthly summary. This article uses a similar example to help you train your end users to employ the Subtotals menu to quickly "add up" their Excel data.
Set up the database
Figure A shows an example of a portion of the database used to enter the employees’ hours and calculate their weekly pay. (Note that all numbers are fictitious and used for demo purposes only.)
Using the database shown in Figure A to enter the weekly payroll data worked fine, but it could not, in its present form, be used to calculate the monthly or quarterly data. While I could have set up the necessary database functions to sort and total each employee's pay, there was simply not enough time. Instead, I turned to the Data-Subtotals menu and found it could perform all the calculations I needed without having to enter a single function.
Sort the data
To calculate each employee’s earnings for the month, I first had to group or sort the records by employee.
Like all operations performed on an Excel database, Sort requires that at least one cell within the database be selected. In this example, I selected A1, but any cell in the database could be selected. Then I clicked on Data in the menu bar and selected Sort from the submenu (see Figure B).
Clicking on Sort displayed the Sort dialog box.
As shown in Figure C, under Sort By, I chose the Employee field and then clicked the OK button. The results, sorted by employee, are shown in Figure D.
Sum the sorted data
With the employees’ records sorted and Cell A1 still selected, I was ready to use Subtotals to obtain their monthly totals.
First, I clicked on Data in the menu bar, and then I selected Subtotals from the submenu, as shown in Figure E.
Clicking on the Subtotals menu displayed the Subtotal dialog box, as shown in Figure F.
To direct Excel to separately add each employee’s weekly gross pay for the month of April, I selected:
- Employee as the field in the At Each Change In box to group the records by employee.
- Sum as the function to use in the Use Function box.
- Gross Pay in the Add Subtotal To list box, making sure that no other check boxes other than Gross Pay were selected. Excel has a habit of trying to anticipate your wishes.
After clicking the OK button, I got my results (see Figure G).
To make it even easier to see the results, I clicked on the outline level 2 symbol on the far left of the screen. This displayed only the summarized data I needed to write the checks, as shown in Figure H.
|If you don’t see the outline symbols when you use Subtotals, click Options on the tools menu, then click the View tab, and then select the Outline Symbols check box.|
Return to data entry
When I complete my payroll for the month, I return the worksheet to the original format I used for data entry. To do that, I bring up the Subtotal dialog box again by clicking Data and then Subtotals, as shown in Figure I.
Next, I click on the Remove All button to return to the data entry sheet, as shown in Figure J.
During May, I can use this same worksheet to add the weekly payroll records for each employee.
At the end of May, I want to summarize the records not only by employee but also by month. To do that, I make use of nesting subtotals. I follow the same procedure as above, with one exception: After running Subtotals with Employee selected in the At Each Change In box, I run Subtotals again, only with the Month field selected in the At Each Change In box. The results after this second run are shown in Figure K.
The results show each employee’s total earnings for the months of April and May, as well as a grand total for both employees for the two months. On June 30, 2001, when I perform this operation again, the grand total will come in handy in preparing my quarterly tax returns.
Using Subtotals has helped me perform my payroll tasks quickly and effectively. But Sum is not the only function Subtotals can help you with. Subtotals also lets you use the Count, Average, Max, Min, Product, CountNums, StdDev, StdDevp, and Var functions to quickly perform calculations on your data.
Rate this article
What do you think of Mary Ann’s lesson on Excel Data-Subtotals function? Is this type of information valuable to you and your end users? Post a comment or write to Mary Ann Richardson and share your opinions.