Conditional Formatting – Highlighting differences in information
In this example, we will show how to use conditional formatting to highlight differences in data. We will use the Payroll History spreadsheet to show salary amounts from month to month and highlight entries that are different from the previous entry.
Use the Payroll History Spreadsheet. Select “Type of Rate – Salaried”
Set the pivot table so that it only has the Full Name and the Net Gross (Sartox) or Total Gross (LINQ).
Highlight the Month field in the top portion of the pivot table. Click and drag the Month ON TOP OF the heading for the Net Gross (or Total Gross).
This should spread your Months across the columns. Use the Undo button in Excel if it did not drop in the correct place.
Highlight the data beneath the months that you want to compare. We didn’t highlight the first column because we are comparing the data to the column to the left.
Next we want to highlight in red any changes from a previous month. We will use Conditional Formatting to do this.
Click on the Home tab, Conditional Formatting, Highlight Cell Rules, More Rules
Select Use a Formula to determine which cells to format.
Use =E35 <> Offset (E35,0,-1)
Where E35 is the upper left most cell of the highlighted range.
This formula says if E35 is not equal to the cell 1 column to the left of E35 then format the cell.
Click on the Format button and select the formatting of the text that meets the formula (in this case a red font, colored background and border)
Note that the cells that are different from the cell to its left are highlighted in red.
The Offset function:
Offset(starting cell, row offset, column offset)