FREE Excel STUFF
SearchSearch Excel Content
Excel Help. Popular
NEW! Multiple Excel Search & Links
Excel Formulas
Excel Macros
Excel Newsletter
PRODUCTS
Up to $139.00 FREE!
Categories & SearchSearch for software
Excel Templates
Excel Add-ins
Excel Training
More....
OTHER
Excel Development


Compare Two Excel Ranges


NEW! More Books..
Add to Google advanced search! Free Help!

Add Excel Answers & Search To Your Google Toolbar Details

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only $145.00. $59.95 Instant Buy/Download, 30 Day Money Back Guarantee & Free Excel Help for LIFE!

Got any Excel Questions? Free Excel Help

<See Also: Excel Compare Workbooks & Compare Excel Files & Compare Suite & Compare Spreadsheets For Excel>

When you have two large tables of data in an Excel Workbook you may need to compare the tables to try and spot the differences. Below we will look at two very easy ways this can be done which will save you hours of manual work.

For both examples I will use the two ranges shown in the screen shot below. Note that I have bolded the cells in the second table that are not the same as their counterpart in the first table  for easier viewing.

Method 1 - Using True or False

The first method involves entering a simple formula in another identically sized and shaped range. The best part of this method is that we can add the formulas in one step without having to copy and paste.

  1. Using the above ranges to compare select the range E1:G7 Starting from cell E1. This ensures that E1 is the Active cell in the selection.
  2. With this range selected click in the Formula Bar and type in: =A1=A9
  3. Now enter this formula by pushing Ctrl+Enter at the same time

You should now see the range E1:G7 filled with True (the same) and False (not the same).

Method 2 - Using Conditional Formatting

The second method is often the preferred way as it is easier to make any needed changes once the comparison has been done.

  1. Using the above ranges to compare, select the range A1:C7 stating from cell A1. This ensures that A1 is the Active cell in the selection.
  2. With this range selected, go to Format>Conditional Formatting
  3. Select Formula Is and then type the formula =NOT(A1=A9)
  4. Click the Format button and choose the format to mark differences with. I have used an orange background.

Now simply click OK and all your differences will be formatted according to the format you have chosen. When or if you make any changes the cells format will automatically revert back to normal if the cell content is the same as the cell in the other table.

<See Also: Excel Compare Workbooks & Compare Excel Files & Compare Suite & Compare Spreadsheets For Excel>

Excel Dashboard Reports & Excel Dashboard Charts 50% Off

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to special@ozgrid.com 31 days after purchase date.


Instant Download and Money Back Guarantee on Most Software

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Add to Google Search Tips FREE Excel Help

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft