How To Find And Highlight Data Range Differences In Excel

Using only keyboard shortcuts, this tutorial shows how you can find differences in your data range from your row or column headers in Excel.

You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.

This tip came from a recent post on the ablebits.com website. They have some pretty comprehensive Excel tips and tutorials there, so you may want to check their blog out.

Here we have a data range of 80 names, plus headers for both rows and columns:

82715-1

Our goal is to use the Go To Special option in Excel to identify where the column headers match the data in each column, and where the row headers match the data in each row, and do this with only keyboard commands, no mouse clicks.

First, we’re going to check on the columns. I want to eliminate the row between the headers and the data, so I will move my active cell to G2, then use the keyboard shortcut Ctrl + “-” (Control key plus the minus sign). This will bring up the “Delete” dialog box. I’ll move down to “Entire row” and hit Enter:

82715-2

This brings my column headers and data together. Next I’ll move my active cell to G1 and then use the keyboard commands Shift + Ctrl + Right Arrow + Down Arrow to select my whole data range. Then I’ll hit the F5 function key to bring up the Go To dialog box:

82715-3

Notice the “Special” command button in the lower left corner, and the fact that the “S” is underlined. I’ll next use the shortcut Alt + S to select the Special command button. This will bring up the Go To Special dialog box. Here I’ll use the keyboard shortcut Alt + M to select the “Column differences” option:

82715-4

Now when I hit Enter, Excel will highlight all the differences between the headers and the column data! Don’t worry about the fact that cell J2 with “Denise” is also not highlighted. This is just because Excel has chosen this as the active cell:

82715-5

Next, I’ll use the key tips command of Alt H H. This will select the Fill Color option. I’ll use my cursor movement keys to select the color I want to use to fill the “differences” cells, and the result is a data range clearly showing the matching and non-matching cells from our headers:

82715-6

The process for checking the rows is the same, except using the keyboard command Alt + W for the “Row differences” in the Go To Special dialog box.

Take a few minutes to watch the video and see if you came up with the same result!

What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , , , , ,

No comments yet.

Leave a Reply