Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

Compare Two Excel Ranges

| | Information Helpful? Why Not Donate.

Got any Excel Questions? Free Excel Help

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.

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.
With this range selected click in the Formula Bar and type in: =A1=A9
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.

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.
With this range selected, go to Format>Conditional Formatting
Select Formula Is and then type the formula =NOT(A1=A9)
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.

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

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 [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

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

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