## How to highlight differences in price lists

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

Compare pricelists in excel 2007

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 existing in only one list in excel

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 in excel

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

How to use Excel’s MATCH function

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

Fetch a value in a data set based on coordinates.

**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

Quickly compare two tables in excel 2007

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

You can click on headers to sort table for easy finding.

### 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>

**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