Highlighting row differences in Excel

Published by | Monday, August 20th, 2012

Curt Frye is the author of over a dozen lynda.com courses and more than 20 books on Microsoft Excel, including Microsoft Excel 2010 Step by Step for Microsoft Press. He is also a popular speaker, presenting his Improspectives® keynote addresses and workshops for corporate clients.

 

Recognizing when your numbers don’t add up is key to successful operations management, which is why organizations of all types and sizes use the Excel spreadsheet program to manage their operations and inventory. A real-world example of this might be conducting a monthly inventory analysis that compares the number of products in your system with the units counted in your warehouse.

In Excel 2007 and 2010, you can quickly check for differences between these two inventory numbers. First, you select the numbers in your worksheet. 

Excel spreadsheet with two rows selected

Next, go to the Home tab on the ribbon, click the Find & Select button, and then click Go To Special.

Selecting row differences in the Go To Special dialog box in Excel

Your Excel data is laid out in two columns, so you want to look for differences between the two cells within each row (A2 compared to B2, A3 compared to B3, and so on). To do this, select the Row differences radio button in the Go To Special dialogue box and click OK.

When you click OK, Excel examines the selected cell range for differences between cells in the same row and highlights cells in the right-hand column that are different from their mates in the left-hand column.

 Excel spreadsheet highlighting the rows where cells contain different values

In this case, cells B4 and B7 contain values that differ from their mates in cells A4 and A7.

If your data were arranged in rows, you could highlight cells with different values by selecting the data cells in the worksheet and clicking the Column differences radio button in the Go To Special dialog box.

The Go To Special dialog box is often overlooked by even advanced Excel users, but it’s worth exploring all its useful options.

 

Interested in more?
• All Business courses on lynda.com
• All Excel courses on lynda.com
• All courses by Curt Frye on lynda.com

Suggested courses to watch next:
• Up and Running with Office Web Apps
• Excel 2010 Essential Training
Excel 2010 Power Shortcuts

 

Share this:Share on Facebook3Tweet about this on Twitter7Share on Google+2Pin on Pinterest0Share on LinkedIn9

lynda.com - start learning today

Tags:


8 Responses to “Highlighting row differences in Excel”

  1. Ronald Black says:

    Curt,

    For Excel Expert how would I select and manipulate similar cells and objects using Go To Special for blanks, comments, objects, conditional formats, formulas, and visible cells only?

  2. Deepa says:

    Good one

  3. Terri says:

    FYI. In Excel 2010 “Find & Select” is in the Editing group on the Home tab (not on the File tab).

  4. Paul says:

    This is one of those great little tools that is easily overlooked. I run a Microsoft training organisation in the UK and we don’t include this on any of our courses. I think I shall add it into our Intermediate course, thanks for the post Curt!

  5. emy says:

    I would like to know how to use row difference in inventory. like for example I have baseline inventory stock level of ballpen 40 pcs . when everytime customer buy ballpen stocklevel will be minus from 40 to 39 pcs until it will become 0 . thank you

    • @emy – it sounds like what you’re looking for is more of a formula that you can use in your spreadsheet. Highlight Row Differences is going to find where values in two rows do not match. It’s not a formula. I would suggest setting up a column that has your current inventory and in an adjacent column, enter your daily sales. In the column that has your inventory, you’ll want to write a formula that looks at your current inventory and subtracts the sales for that day and returns the result in cell below your current inventory.

      For example, let’s say you keep track of the daily sales in column A and the inventory in column B.

      In cell A1, I label the column “sales” and in cell B1, I label the column “inventory”. For Day 1 (which would be row 2 in this example), I’ll put the number 0 in cell A2 for my sales. In cell B2, I put 40 for the number of items in my inventory.

      In cell A3, I’ll enter “3″ for the number of sales that day as an example.

      In cell B3, I’ll write the formula “=B2-A3″ but I won’t include the quotes. Copy that formula down as far as you need in Column B.

Leave a Reply