Highlighting Differences in Google Sheets

Google Sheets is a fine tool for working with spreadsheets, but it doesn't quite manage to have all of the functionality that Excel has. One example is that Excel contains a built-in tool for finding diffs between two workbooks. However, with a little clever programming we can do almost as well.

Suppose you have a Google Sheets workbook with two tabs or "worksheets" in it that have NEARLY identical data, but not totally identical. You would like to highlight the cells that are different. We can achieve this using conditional formatting.

You'll need to know the name of the worksheets -- which appears in the tab at the bottom. If you need to, you can right-click on the tab and select "Rename" to find the exact name so you can copy-paste it. In these instructions I will assume that the two sheets are named "Alpha" and "Beta".

To highlight cells in sheet Alpha that are different from sheet Beta, navigate to sheet Alpha and select all cells by clicking the place where the row labels and the column labels meet. Then in the "Format" menu, select "Conditional formatting". It will pre-populate the "Apply to range" to cover the entire worksheet.

Under "Format cells if...", select "Custom formula is...". Then in the "Value or formula" field, put the following:

=A1<>INDIRECT(CONCATENATE("Beta!",ADDRESS(ROW(A1),COLUMN(A1))))

(Replace "Beta" with your actual worksheet name.) This will apply a colored highlighting to each cell that is different.

To understand how this works, if you use the name of the top-left corner of the range ("A1" in our formula) in a formula, when it is evaluated it will actually use the current cell in the range. We take the row and column of that, then turn them into an address to get a string like "$C$6" for cell C6 (and so forth). Concatenate that with "name-of-sheet!" and you have the notation for naming the same cell on a different sheet. The INDIRECT() function pulls in the value of that cell which we compare to the value in the cell itself, and highlight any differences.

Posted Sun 18 September 2022 by mcherm in Programming