This example will show you how to format your payroll history pivot table to highlight hourly employees that have greater than 30 hours per week. Remember – you can’t hurt anything by working with this and you can always use the UNDO button if you make a mistake!

Refresh you Payroll History Spreadsheet. Click on the Payroll History Pivot Table tab at the bottom of your sheet.

First, click on the Hourly Employees by Location as this will give you a good starting point for creating the report.

Remove the Site Loc from the table by clicking on the header for “Site Loc” and then dragging to the top portion of the table.

Remove the Job ID from the table by clicking on the header for “Job ID” and then dragging to the top portion of the table.

Your table should look similar to this now…

Remove all of the data fields from the table by clicking on 

each of the headers and dragging up and to the right until you

see the red X

Your table should look similar to this now…

Right click anywhere in the pivot table and select “Show Field List

Scroll through the list of fields in the Pivot Table Field List and locate the Hours for weeks 1 through 8. Drag each field into the blank data area (it may say to drop value fields here…)

After dropping all 8 weeks into the table it should look similar to this…

Drag the Pay Period Begin Date onto the table after Full Name (watch for the grey T to appear after the Full Name before letting the mouse go)

Your table should now look similar to this.

To help locate people who have weeks with more than a certain number of hours, do the following:

Highlight the column headers for the columns containing the hours.

For office 2007 and above, click on the Conditional Formatting icon on the Home ribbon. 

Select “Highlight Cell Rules” and then “Greater Than”

Select the number to use – for example we used 28 so that people with more than 28 hours in a week will be highlighted. Click on OK