Instant payroll totals for quarterly tax reporting

Accurate payroll report summaries are critical for 941 quarterly tax forms. Fortunately, performing these calculations doesn't have to be time-consuming. You can now sort and total data instantly, with criteria you specify, by using the new Excel list feature in Microsoft Office Excel 2003. With an Excel list, you can:

  • Calculate custom totals for the criteria you specify, for example a specific employee or a specific time period, more quickly than you could in earlier versions of Excel.
  • Instantly display only the details you want to show in your payroll report. You can print targeted versions of the same report for different audiences, with just a few clicks.

Customize payroll report results with an Excel list

Building lists is, of course, a common spreadsheet activity, and something you probably do on a routine basis. But with the new List command, you can turn an ordinary list into an Excel list, which gives you new options for working with data. This makes financial calculations quicker than ever.

This article describes how to use the Sample Payroll Register to summarize the quarterly gross pay and total federal and state income tax withheld for all employees for 941 quarterly tax reporting. You'll see how the List command makes these calculations effortless.

From ordinary list to Excel list

First, make the payroll register into an Excel list. Be sure that any list you make into an Excel list has header labels above each column, like this example.

  1. Click any cell within the data, point to List on the Data menu, and then click Create List.
  2. In the Create List dialog box, confirm that the My list has headers box is selected, and then click OK.

A blue border now shows that your payroll data has been converted to an Excel list.

Payroll register becomes an Excel list

So, what's different?

Click inside the list. You'll notice that it has changed for the better:

  • AutoFilter arrows in the header rows are added automatically.
  • An insert row is added at the bottom of the list, in case you want to add additional data.
  • The List toolbar appears, with new options for working with data. If you don't see it, point to Toolbars on the View menu, and then click List.

Target specific tax reporting criteria with AutoFilter

The payroll register contains data from two quarters, but you only want to summarize data from the first quarter. Using AutoFilter, you can create a custom filter to see only payroll data from the first quarter of the year.

  1. Click the arrow in cell A1, and then click Custom.
  2. In the Custom AutoFilter dialog box, under Show rows where: Date, select is less than.
  3. In the box to the right, type 4/1/2004 and then click OK.

Custom AutoFilter dialog box

The list is filtered to show only those dates within the first quarterly pay period.

Quarterly gross pay for all employees

The next step is to total the quarterly gross pay and total federal and state income tax withheld for all employees.

  1. On the List toolbar, click Toggle Total Row Button image. This adds a new row with the word "Total" and the sum. The only column that is initially summarized is the Net Pay column, but you can select the total row in any other column, and total (or perform other functions on) just that column.
  2. In the Total Tax Withheld column, click the last cell within the list (in the Total row). Click the arrow that appears, and select Sum from the drop-down list.

Select Sum from the menu

  1. Repeat this action for each column you want to total.

There you have it: the quarterly gross pay and the total tax and other withholding for just the first quarter listed in this report.

Quarterly gross pay for just one employee

In some states, for example Oregon, you need to calculate these totals for each employee. Again, the List command makes it easy.

In this example, you will calculate the quarterly gross pay for just one employee from the payroll report: Masato Kawai.

You have already applied a custom filter, the date, to this report, to display and subtotal payroll information for one quarter only. Now you'll apply another filter to the Name column to get data not just restricted to the first quarter, but also specific to Masato Kawai.

  1. Click the AutoSort arrow in the Name column, and then click Kawai, Masato. Now you only see payroll information for Masato Kawai, for the first quarter.
  2. Click the last cell in the Gross Pay column, and then click Sum.

Select Sum in the Total row of the Gross Pay column

Now, thanks to the List command, you have the quick answer you need: first quarter gross pay, for only Masato Kawai.

Reset a list to display all fields

When you are finished filtering data and have all the results you need, click the AutoSort arrow in any column you've sorted (the Date and Name columns, in this example), and then click (All) to restore the report to display all fields.

Many reports in one

Excel lists can also help you print custom reports. Use the AutoFilter arrows in the header rows of an Excel list to apply filters in the exact combination you need to report on, and print the results. Your print report will only show specific time periods, employees, or other restrictions you choose, while leaving the full range of data intact in the master payroll register.

Excel lists make quarterly tax reporting a snap

As this example demonstrates, the List command puts quick AutoFilter tools at your fingertips without extra work, giving you instant, custom payroll totals for tax reporting. And this is only one of the list management features new to Excel 2003. Be sure to check the See Also section of this article to learn about publishing lists to a team site, and other ways to enhance common finance tasks. Working with lists has never been this easy!

Applies to:
Excel 2003