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"

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

Recommended blog posts
How to quickly compare pricelists in excel