Comparing Months of Information in the CCI Spreadsheets(Example uses Sartox Deductions Register)

Refresh your spreadsheet so that it includes multiple months of information. We are using the Sartox Deductions Register for this example, but this technique can be used in any pivot table that contains a date or month field

In the Deductions Register, be sure to run the Sartox Deductions Register with options so that it includes multiple months.

After the spreadsheet refreshes, click on the Deductions Pivot Table tab at the bottom of the spreadsheet.

Your spreadsheet pivot table will look similar to this when it is first refreshed.

To compare months, drag the Month field from the top portion of the table and drag it ON TOP OF the Ded Amount heading. (simply click on the Month field in the top part of the table and drag)

Your table should now look similar to this – with the months spread across the top of the table.

In order to compare the details for each person, we will need to drag down the Full Name from the top part of the table. Click on Full Name in the top portion and drag it to the RIGHT of the Deduction Code (look for the grey T to appear just after the deduction code column before releasing the mouse).

Note – if you drag the full name to the incorrect place, simply click on the UNDO button in Excel and try it again!

Your table should now look similar to this, with full names listed and the months side by side.

We want to add a conditional format to highlight the entries in the Ded Amount Column for month 11 that are different that the Ded Amounts for month 10.

You can see that the Ded Amount for month 11 is in column E and the Ded Amount for column 10 is in column C

Highlight all of the entries for the Ded Amount for month 11 (in our case, column E). You can do this by clicking on the first amount and then using the shift key and scrolling down.

To add the conditional formatting in Office 2003, click on Format, Conditional Formatting (see the next entry for Office 2007/2010)

Select “Formula Is”

And then type the formula


(where Column C is the column that we are comparing with and E23 is the first cell of the data that we have highlighted.  This formula works for any comparison of data between 2 columns – you just have to adjust the Column that you are comparing with and adjust the cell of the first entry of your data)

Click on the Format button on the Conditional Formatting box.

Choose the format of the font if the selection differs from the entry you are comparing with. In this case, any entries in column E that don’t match the corresponding entry in column C will be red and bold.

Click on OK

Click on OK again

You should see the entries that are different highlighted in red and bold.