This post describes how to compare two price lists in excel 2007. I am going to create three conditional formatting formulas and highlight:

  • new products yellow
  • prices higher than previous year green
  • prices lower than previous year red

Sheet1 - Year 2011

Sheet2 - Year 2010

Create named ranges

You can´t reference cells outside current sheet when constructing a conditional formatting formula unless you use named ranges.

category2010 - B3:B12
product2010 - C3:C12
price2010 - D3:D12

  1. Select sheet2
  2. Select B3:B12
  3. Type category2010 in name box
  4. Repeat step 1-3 with remaining names and cell ranges

Highlight new products yellow

Conditional formatting formula:

=COUNTIFS(category2010, $B3, product2010, $C3)=0

Highlight higher prices green

Conditional formatting formula:

=$D3>INDEX(price2010, MATCH(1, COUNTIFS($B3, category2010, $C3, product2010), 0))

Highlight lower prices red

Conditional formatting formula:

=$D3<INDEX(price2010, MATCH(1, COUNTIFS($B3, category2010, $C3, product2010), 0))

How to apply conditional formatting formula

  1. Select sheet1
  2. Select cell range B3:D14
  3. Click "Home" tab
  4. Click "Conditional Formatting" button
  5. Click "New Rule.."
  6. Click "Use a formula to determine which cells to format"
  7. Copy and paste conditional formatting formula in "Format values where this formula is TRUE" window.
  8. Click "Format.." button
  9. Click "Fill" tab
  10. Select a color for highlighted cells.
  11. Click "Ok"
  12. Click "Ok"
  13. Click "Ok"

Download excel file

Compare pricelists_new.xlsx
(Excel 2007 Workbook *.xlsx)

Recommended blog posts
How to quickly compare pricelists in excel
 

Functions in this article:

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

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

COUNTIFS(criteria_range1,criteria1, criteria_range2, criteria2...)
Counts the number of cells specified by a given set of conditions or criteria