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 2013 and year 2014.

To make things more interesting, price list 2014 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?

 compare-pricelists

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.

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.

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.

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”

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.

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.

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)

Functions:

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

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

ISERROR(value)
Checks whether a value is an error and returns TRUE or FALSE


Courtesy: get digital help

Leave a Reply

Your email address will not be published. Required fields are marked *

eleven + seven =

This site uses Akismet to reduce spam. Learn how your comment data is processed.