Conditional Formatting – Highlighting differences in information
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 Rave – Salaried” |
|
|
Set the pivot table so that it only has the Full Name and the Net Gross (Sartox) or Total Gross (ISIS). |
|
|
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)
Click OK
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)
|
Related Articles
Conditional Formatting – Highlighting differences in information
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 ...Using Conditional formatting to highlight Pay Group differences in the Payroll Deductions sheet for ISIS
Using Conditional formatting to highlight Pay Group differences in the Payroll Deductions sheet for ISIS Highlight the data in the column that you want to compare. Hint – place you cursor on the bottom border of the cell and hold the shift key and ...Obtaining Counts for the SS300 Report using Payroll History Spreadsheet - Sartox
Obtaining Counts for the SS300 Report using your Payroll History Spreadsheet - Sartox Select the year that you need to obtain information for. Select “List of Object Codes” from the drop down list. List all of the object codes that apply to the ...Obtaining Counts for the SS300 Report using Payroll History Spreadsheet - LINQ
Obtaining Counts for the SS300 Report using your Payroll History Spreadsheet - LINQ Select the year that you need to obtain information for. Select “List of Object Codes” from the drop down list. List all of the object codes that apply to the ...Using the CCI Spreadsheets to determine who has worked more than 30 hours
Using the CCI Spreadsheets to determine who has worked more than 30 hours 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 ...