## How to highlight differences in price lists

*Article updated on December 21, 2017*

Today I am going to show you how to quickly compare two tables using **conditional formatting**. We are comparing two price lists from the year 2010 and year 2011.

To make things more interesting, price list 2011 is not sorted. new products are also introduced. It is quite common that pricelists are huge and a total mess. Excel can help us find differences.

A requirement for these conditional formatting formulas to work, is that column and row headers have identical spelling. The same capitalization is not required.

The questions are:

- How do I find
**new products or models**compared to previous year? - How to identify
**lowered prices**compared to previous year? - How to identify
**higher prices**compared to previous year?

### New products or models

**Conditional formatting formula:**

Cells are formatted yellow.

Recommended post

This post describes how to compare two price lists in excel 2007. I am going to create three conditional formatting […]

### Find lower prices

**Conditional formatting formula:**

Cells are formatted red.

Recommended post

Compare two lists of data: Filter records occurring in only one list

In this example we are going to use two lists with identical columns in excel 2007. It is easy to […]

### Find higher prices

**Conditional formatting formula:**

Cells are formatted green.

Recommended post

Compare two tables: Filter common records

I will in this blog post describe how to extract common records from two data sets in Excel. I have […]

### How to apply conditional formatting formula

Make sure you adjust cell references to your excel sheet.

- Select cells C11:G15
- Click "Home" tab
- Click "Conditional Formatting" button
- Click "New Rule.."
- Click "Use a formula to determine which cells to format"
- Copy and paste conditional formatting formula in "Format values where this formula is TRUE" window.
- Click "Format.." button
- Click "Fill" tab
- Select a color for highlighted cells.
- Click "Ok"
- Click "Ok"
- Click "Ok"

Recommended post

Compare two lists of data: Highlight common records

Overview In this blog post you will learn how to: Create a countifs function Create a conditional formatting formula and […]

### Explaining find lower prices conditional formatting formula in cell C11

=INDEX($C$4:$G$7, MATCH($B11, $B$4:$B$7, 0), MATCH(C$10, $C$3:$G$3, 0))>C11

**Step 1 - Find relative position of current row header in previous pricelist**

=INDEX($C$4:$G$7, **MATCH($B11, $B$4:$B$7, 0)**, MATCH(C$10, $C$3:$G$3, 0))>C11

MATCH(lookup_value;lookup_array; [match_type]) returns the relative position of an item in an array that matches a specified value

MATCH($B11, $B$4:$B$7, 0)

becomes

MATCH("Product A", {"Product A";"Product B";"Product C";"Product D"}, 0)

and returns 1.

Recommended post

Identify the position of a value in an array.

**Step 2 - Find relative position of current column header in previous pricelist**

=INDEX($C$4:$G$7, MATCH($B11, $B$4:$B$7, 0), **MATCH(C$10, $C$3:$G$3, 0)**)>C11

MATCH(C$10, $C$3:$G$3, 0)

becomes

MATCH("Model 1", {"Model 1", "Model 2", "Model 3", "Model 4", "Model 5"}, 0)

returns 1.

**Step 3 - Return a value of the cell at the intersection of a particular row and column**

=INDEX($C$4:$G$7, MATCH($B11, $B$4:$B$7, 0), MATCH(C$10, $C$3:$G$3, 0))>C11

becomes

=INDEX($C$4:$G$7, 1, 1)

becomes

=INDEX({27,3, 612,9, 765,6, 872,1, 417,3;266,2, 989,3, 576,7, 768,5, 948,8;213,6, 276, 140,3, 609,5, 6,5;642,8, 159,2, 848,9, 452,2, 574,1}, 1, 1)

returns 27,3.

Recommended post

Gets a value in a specific cell range based on a row and column number.

**Step 4 - Compare returned value to current value**

=INDEX($C$4:$G$7, MATCH($B11, $B$4:$B$7, 0), MATCH(C$10, $C$3:$G$3, 0))>C11

becomes

27,3>C11

becomes

27,3>27,3

returns FALSE. Cell C11 is **not **highlighted red.

**Download excel example file**

compare price lists.xlsx

(Excel 2007 Workbook *.xlsx)

Recommended blog post

This blog post demonstrates how to quickly compare two tables in Excel. Table1 Table2 Create a fourth column and use […]

### 2 Responses to “How to highlight differences in price lists”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

thanks

Thank u so much