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

How Useful is Big-O?

Here is a question I was answering on Reddit recently:

Say you have 2 algorithms and you determine both are O(log n) (or anything else that's the same). Does this mean both algorithms are just as good as one another?

Absolutely not. One algorithm might run 30x faster than …

Read more

Posted Sun 17 July 2022 by mcherm in Programming

Unique IDs

I am building a system to store records about businesses (within the US). As with most systems these days, it will be highly distributed: it runs in three different data centers, each running multiple machines each of which runs numerous threads. One of the things that this distributed system needs …

Read more

Posted Sun 15 September 2019 by mcherm in Programming

Confluence Fix

Just a note for myself for the NEXT time this happens.

If Confluence starts returning an error saying "400 Bad Request - Request Header Or Cookie Too Large" then the cause is that something has gotten borked with the cookie that the browser has for Confluence. The fix is to delete …

Read more

Posted Mon 09 April 2018 by mcherm in Programming

Depending on Someone Else's Code

Depending on Someone Else's Code

We had an interesting problem arise the other day, a problem about code dependency. Our problem was in no way unique, so it seemed worthwhile to write out the problem and our proposed solution.

The problem

The core of the problem is that we need …

Read more

Posted Sun 28 January 2018 by mcherm in Programming