My use of the Go To Special dialog primarily involved the “Visible cells only” or “Blanks” options. Sometimes I might use “Formulas” or “Constants.” But that leaves a host of other options in the dialog that I never thought much about using.
One option that’s worth exploring further is “Row differences.” Consider the data in Figure 1. Cells B4:B17 contain original forecasts from 14 sales reps. Columns C through E contain weekly updates to the forecast. Now suppose you’d like to easily spot the changes from the original forecast. You can use conditional formatting, but it’s one of the trickier conditional formatting formulas to set up. Go To Special’s “Row differences” offers another option.
FINDING GO TO SPECIAL
To start, select cells B4:B17. Then open the Go To Special dialog. In the old days, you could only get to the Go To Special dialog by first opening the Go To dialog. There were a few shortcut keys—either Ctrl+G or F5—to open the Go To dialog, then you’d have to press the Special button in the lower-left corner. Alternately, since the “S” in Special is underlined, you can also press Ctrl+G and then Alt+S to arrive at Go To Special.
Starting with Excel 2007, the Go To Special dialog was promoted to the ribbon in Excel. Near the right side of the Home tab, in the Editing group, open the Find & Select drop-down menu. Go To Special is the fourth item in that menu. Oddly enough, the next six menu items are shortcuts that mostly duplicate choices in the Go To Special dialog: Formulas, Notes, Conditional Formatting, Constants, and Data Validation do exactly what the corresponding choice in Go To Special would do, while Select Objects is similar to, but slightly different than, the Objects option in Go To Special.
USING ROW DIFFERENCES
The top choice in the right-hand column of the Go To Special dialog is “Row differences.” If there were more room in the dialog, a better description would be “Find cells where there is a difference within each row.” Select “Row differences,” and then click OK.
The original selection (B4:B17), which contained 56 cells, will change to include only eight cells. In Figure 2, I’ve changed the font color to red to help show the cells that were selected. These are the cells that are different from the original forecast.
To know why those cells were the ones selected, it’s worth a bit of study to understand how “Row differences” actually works. In row 4, Andy’s original forecast was 4 units. We can see that his forecast changed to 6 in week 3 because the 6 in D4 is different than the 4 in B4. Note that Andy’s forecast from week 3 (D4) to week 4 (E4) didn’t change, but E4 is still marked as a change—that’s because week 4 is different from the original forecast in column B.
In other words, “Row differences” is only selecting values on that row that differ from the first column. For example, look at row 7. Diane’s forecasts in C7, C8, and C9 are all marked because they’re different than her original forecast in cell B4.
The most interesting situation happens with Kelly’s forecasts in row 14. The 3 in C14 is marked as a change because it’s different from the 2 in B14. The next week, the forecast didn’t change from week 2, but the cell is still marked because it’s different from the forecast in week 1. But when the forecast changes from 3 to 2 in week 4, cell E14 isn’t marked. That’s because the forecast changed back to 2, which matches week 1.
Note that Go To Special can also mark changes within a single column. The “Column differences” option would find all cells where the value is different than the first row in the selection.
USING CONDITIONAL FORMATTING
To use conditional formatting to perform the same test, you would start by selecting C4:E17. Select Home, Conditional Formatting, New Rule, Use a formula to determine which cells to format. You would then enter a formula of =C4<>$B4. This formula is tricky because it needs to be written from the point of view of the first cell in the range, which is C4. Since you always want to compare to column B, you need a single dollar sign before the B. (If you wanted to mark when each week’s forecast was different than the previous week’s forecast, you would change the formula to =C4<>B4. By removing the dollar sign, this formula will always look one column to the left of each cell in the range.)
Even Microsoft seems to forget Go To Special is there, because it didn’t update the Notes section to include Comments or Current Array to work with the new Dynamic Arrays. But the options that are there are still useful and worth exploring.
November 2022
