How to Use Excel Formulas to Compare Two Columns and Find the Difference

Excel is a powerful tool for working with data, and one of the common tasks is to compare two columns and find out how they have changed. For example, you may want to compare sales figures of two different months, or test scores of two different groups of students, or prices of two different products, and so on.

There are different ways to compare two columns in Excel, depending on what kind of change you want to measure. In this article, we will focus on how to compare change in terms of greater than or less than, using excel formulas. We will also show you some alternative methods to compare two columns, such as using conditional formatting or pivot tables.

To compare change between two columns in Excel, we need to use a logical function that can evaluate whether one value is greater than or less than another value. The most basic logical function in Excel is the IF function, which has the following syntax:

=IF (logical_test, value_if_true, value_if_false)

The IF function takes three arguments: a logical test, a value to return if the test is true, and a value to return if the test is false. The logical test can be any expression that evaluates to TRUE or FALSE, such as a comparison operator, a logical operator, or a reference to a cell that contains a logical value.

For example, to compare two values in cells A2 and B2, and return “Yes” if A2 is greater than B2, and “No” otherwise, we can use the following formula:

=IF (A2>B2, "Yes", "No")

We can also use other comparison operators, such as < (less than), >= (greater than or equal to), <= (less than or equal to), = (equal to), or <> (not equal to), to compare two values. For example, to compare two values in cells A2 and B2, and return “Increase” if A2 is greater than or equal to B2, and “Decrease” otherwise, we can use the following formula:

=IF (A2>=B2, "Increase", "Decrease")

We can also use the AND function or the OR function to combine multiple logical tests, and return a value based on the result. The AND function returns TRUE if all the logical tests are true, and FALSE otherwise. The OR function returns TRUE if any of the logical tests is true, and FALSE otherwise. For example, to compare two values in cells A2 and B2, and return “High” if A2 is greater than 80 and B2 is less than 20, and “Low” otherwise, we can use the following formula:

=IF (AND (A2>80, B2<20), "High", "Low")

Procedures

To compare change between two columns in Excel, we need to follow these steps:

1. Select a cell where we want to enter the formula, such as C2.
2. Type the equal sign (=) to start the formula.
3. Type the IF function, followed by an opening parenthesis (().
4. Type the logical test that compares the values in the two columns, such as A2>B2, followed by a comma (,).
5. Type the value to return if the logical test is true, such as “Yes”, followed by a comma (,).
6. Type the value to return if the logical test is false, such as “No”, followed by a closing parenthesis ()).
7. Press Enter to complete the formula and return the result in the selected cell.
8. Copy the formula down to the other cells in the same column, by dragging the fill handle (a small square in the bottom-right corner of the selected cell) or using the keyboard shortcut Ctrl+D.

Example

Let’s say we have a table that shows the sales figures of two products, A and B, in two different months, January and February, as shown below:

We want to compare the change in sales between the two months, and see which product has increased or decreased in sales. To do this, we can use the following formula in cell D2:

=IF (C2>B2, "Increase", "Decrease")

This formula compares the values in cells C2 and B2, and returns “Increase” if C2 is greater than B2, and “Decrease” otherwise. We can copy the formula down to the other cells in column D, and get the following results:

We can see that products A and B have increased in sales, while products C and D have decreased in sales, between January and February.

Alternative Methods

Besides using formulas, there are other ways to compare two columns in Excel, such as using conditional formatting or pivot tables.

Conditional Formatting

Conditional formatting is a feature that allows us to apply different formats, such as colors, icons, or data bars, to cells based on certain criteria. For example, we can use conditional formatting to highlight the cells that have increased or decreased in sales, as shown below:

To apply conditional formatting, we need to follow these steps:

1. Select the cells that we want to format, such as C2:C5.
2. Click the Home tab on the ribbon, and click the Conditional Formatting button in the Styles group.
3. Choose the type of format that we want to apply, such as Color Scales, Icon Sets, or Data Bars.
4. Choose the specific format that we want to apply, such as Green – Yellow – Red Color Scale, 3 Arrows (Colored) Icon Set, or Solid Fill Blue Data Bar.
5. Adjust the format settings if needed, such as changing the color, icon, or value range, by clicking the Manage Rules option in the Conditional Formatting menu, and editing the rule in the Conditional Formatting Rules Manager dialog box.

Pivot Tables

Pivot tables are a tool that allows us to summarize and analyze data in a table format. For example, we can use pivot tables to calculate the total sales, the percentage change, or the rank of each product, as shown below:

To create a pivot table, we need to follow these steps:

1. Select the cells that contain the data, such as A1:C5.
2. Click the Insert tab on the ribbon, and click the PivotTable button in the Tables group.
3. Choose where to place the pivot table, such as a new worksheet or an existing worksheet, in the Create PivotTable dialog box, and click OK.
4. Drag and drop the fields that we want to use in the pivot table, such as Product, January, and February, to the appropriate areas, such as Rows, Values, or Columns, in the PivotTable Fields pane.
5. Change the calculation method, the number format, or the layout of the pivot table, by clicking the Field Settings option in the Analyze or Options tab on the ribbon, and modifying the options in the Value Field Settings dialog box.