How to Compare Cells in Excel
In Excel 2013, the IF function serves to compare cells regardless of their position. In many cases, however, you don't need to go through the hassle of writing a formula just to check how cells compare. To search for duplicates, to find cells above an average value or to style cells based on their values, use Conditional Formatting as the fastest way to compare cells.
Select a range of cells to compare internally, open the **Conditional Formatting** menu on the Home tab, point to **Highlight Cells Rules** and choose **Duplicate Values**. This feature works well to track down accidental duplicates or find matching items in a long list, but it doesn't work across multiple sheets.
Leave the first drop-down menu set to **Duplicate**, change the coloring option if you want and press **OK** to highlight duplicate cells.
Delete a duplicate cell to remove the highlighting from its partner.If a duplicate value occurs again later, Excel automatically highlights the cells again. To turn off highlighting permanently, reopen **Conditional Formatting** and choose one of the options from the **Clear Rules** menu.
Use other conditional formatting options to compare cells in other ways. For example, the **Above Average** option listed under **Top/Bottom Rules** highlights cells above the average for the selected range.
Add **Data Bars** or **Color Scales** from the Conditional Formatting menu to help visualize the relative difference between cell values.
Compare Cells Using Formulas
Check whether two specific cells match using the IF function. Start a formula with **=IF(** and enter the two cell locations, separated by an equal sign. Add a comma and enter the text, in quotes, to display if the cells match. Add another comma and then the text, in quotes, for a non-match. For example, the formula could read **=IF(A1=B1,"Match","No Match")** to compare cells A1 and B1. Press **Enter** to finish the formula.
Drag the lower-right corner of the formula's cell to extend it to other cells and compare two columns or rows.
Use the notation **Sheet!Cell** to refer to cells on other spreadsheets while building the formula. For example, **Sheet1!A1** to reference cell A1 on Sheet1. You can also switch to another sheet and click a cell in the middle of typing a formula to insert the cell's location.
Substitute the greater-than or less-than sign for the equal sign to compare cells for conditions other than exact matches. Use **>=** or **<=** to check for greater-than-or-equals or less-than-or-equals conditions, respectively.
Tips & Warnings
- Combine IF with other functions for more advanced comparisons. For example, start with "=IF(PRODUCT(A:A)>PRODUCT(B:B)" to compare the products of all cells in columns A and B.
- You can also use numbers, math operations or other functions as the result of an IF function. For example, "=IF(A1=B1, A1+B1, A1-B1)" adds cells A1 and B1 if they match, or subtracts B1 from A1 if they don't match.