March 27, 2011, 9:42 a.m.
posted by donnie
Compare Two Excel Ranges
Spotting the differences between two large tables of data can be a very time-consuming task. Fortunately, there are at least two ways in which you can automate what would otherwise be a very tedious manual process.
The two methods you will use are methods we have used in the past when we received an updated copy of a spreadsheet and we needed to identify which cells in the updated copy differed from the ones in the original copy. Both methods save hours of tedious manual checking and, more importantly, eliminate the possibility of mistakes.
For the following examples, we copied the newer data onto the same sheet as the older data beforehand. Figure shows how the data is presented as two ranges. Note that for easier viewing, we boldfaced the cells in Table 2 that are not the same as their counterparts in Table 1.
Method 1: Using True or False
The first method involves entering a simple formula into another range of the same size and shape. The best part about this method is that you can add the formula in one step without having to copy and paste.
To compare the ranges shown in Figure, 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 the following:
If your two sets of data reside on different worksheets, you can use a third worksheet to store the True/False values simply by array-entering the formula. For example, assuming the second table of data is on Sheet2 and starts in cell A9, and the original table of data is on Sheet1 and starts in cell A1, on a third worksheet you can array-enter this formula:
Method 2: Using Conditional Formatting
The second method is often preferred, as it is easier to make any needed changes once the comparison is made. However, with this method, both sets of data must reside on the same worksheet, which should entail only a simple copy and paste.
Click the Format button, shown in Figure, and choose the format with which you want to highlight the differences.
Click OK and all the differences will be formatted according to the format you chose.