Bottom Line: Learn how to quickly check if there are values, formulas, or text in a row or column that are different than the other entries.
Skill Level: Beginner
Download the Excel File
If you'd like to download the same workbook that I use in the video, you can get it here:
Select Cells With Differences.xlsx (18.6 KB)
Selecting Cells with Differences
This is a great tip for anyone who is auditing or reviewing a spreadsheet and wants to quickly find any abnormalities in the formulas or values that are being used for calculations.
For example, in the spreadsheet that I use in the video, we want to quickly look for any cells where the sales commission is calculated differently from the norm.
You can easily select cells with differences or variations in formulas or values.
To do that, we start by highlighting the entries in a particular column that we want to check. Then we simply use the keyboard shortcut Ctrl + Shift + \ (backslash).
Note: The active cell in your highlighted column will be the basis for comparison. Using the keyboard shortcut will select all the abnormalities based on the formula in that active cell.
Flag or Highlight the Abnormal Cells
Once all of the cells with differences (exceptions or irregularities) are selected, we can then flag them for review.
With the abnormal cells selected, you can easily change the fill color for those cells, then filter by color to see only those cells. This is especially useful if your data set has a lot of rows.
Check out my free 3-part video series on filters to learn more about filtering by color.
You can also add a cell comment/note to flag the differences. Check out the video above for instructions on these techniques.
How to View the Formula Text
Now, looking at the image above, you can't tell that the formulas for those four cells are any different, so let me show you the table with the formulas revealed.
The shortcut to show all of the formulas on a spreadsheet is Ctrl + ~ (tilde). Press it again to toggle back to view the values. You can also press the Show Formulas button on the Formulas tab of the ribbon.
The FORMULATEXT Function
Microsoft introduced the FORMULATEXT function in Excel 2013. The function returns the text of the formula to the cell.
This makes it easy to see both the result of the formulas and the text of the formulas in an adjacent column/row.
You can wrap the function in IFERROR or IFNA to return a different result for cells that do not contain formulas.
Using the Go To Special Window
If you're more of a mouse user, you can also select cells with differences in the Go To Special window.1. From the Home Tab on the Ribbon, choose the Find & Select button. This brings up a drop-down menu.
2. Click on Go To Special…
3. This will open up the Go To Special window, where you can select Column differences.
4. When you hit OK, only the cells that have values different from the original active cell will be selected.
It Works for Rows Too
All of the above instructions work for differences in rows instead of columns. The only change is that the keyboard shortcut is Ctrl + \.
Obviously, when using the Go To Special window, you are going to choose the Row differences option rather than the Column differences.
And as with the columns, you can find differences in values (whether numbers or text), and not just differences in formulas.
If you like this keyboard shortcut, check out some of my other favorites in this post: 5 Keyboard Shortcuts for Rows and Columns in Excel.
I hope this tip was helpful, especially if you need to review or audit a spreadsheet for any potential errors in the way it is calculating results. Do you have other tips for auditing workbooks? If so, let us know in the comments below.