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:

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

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 […]

Comments(7) Filed in category: Compare, Excel

Find lower prices

Conditional formatting formula:

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

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 […]

Comments(0) Filed in category: Compare, Excel, Records

Find higher prices

Conditional formatting formula:

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

Cells are formatted green.

Recommended post

Compare two lists of data: Filter common row records in excel

This blog post describes how to extract common rows (records) from two lists in excel 2007. Sheet: List 1 Sheet: […]

Comments(7) Filed in category: Compare, Excel, Records

How to apply conditional formatting formula

Make sure you adjust cell references to your excel sheet.

  1. Select cells C11:G15
  2. Click "Home" tab
  3. Click "Conditional Formatting" button
  4. Click "New Rule.."
  5. Click "Use a formula to determine which cells to format"
  6. Copy and paste conditional formatting formula in "Format values where this formula is TRUE" window.
  7. Click "Format.." button
  8. Click "Fill" tab
  9. Select a color for highlighted cells.
  10. Click "Ok"
  11. Click "Ok"
  12. 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 […]

Comments(2) Filed in category: Compare, Excel, Records

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

MATCH function

Identify the position of a value in an array.

Comments(12) Filed in category: Excel

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

INDEX function explained

Fetch a value in a data set based on coordinates.

Comments(14) Filed in category: Cross reference table, Excel, INDEX function

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 […]

Comments(0) Filed in category: Compare, Excel, Records