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?
New products or models
Conditional formatting formula:
Cells are formatted yellow.
Find lower prices
Conditional formatting formula:
Cells are formatted red.
Find higher prices
Conditional formatting formula:
Cells are formatted green.
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”
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