How to highlight differences in price lists
Today I am going to show you how to quickly compare two tables using Conditional Formatting (CF). I am going to compare two price lists from the year 2010 and year 2011.
I have two types of data set layouts I want to share a solution for, basic dataset layout shown above and a two-index layout.
What's on this page
To make things more authentic in my examples, products in price list 2011 are not sorted in the same way as 2010. New products are also introduced.
It is quite common that price lists are huge and a total mess. Excel is the perfect tool for finding the differences between datasets.
A requirement for these conditional formatting formulas to work, is that column and row headers have identical spelling.
The same letter capitalization is not required.
Basic dataset layout
I have two worksheets, in this example, named 2010 and 2011. They both contain a category, product, and a price column.
Color | Description |
Yellow | A new item in the list. |
Green | Price increase. |
Red | Price decrease. |
There are three different CF formulas applied to cell range D3:D14, each coloring a cell based on a condition.
Two or more CF formulas can't color cell at the same time, the logical expressions I built can't all be true at the same time.
Red Conditional Formatting formula
The red CF formula compares the value in column D based on category value and product value with the corresponding product, category and price in worksheet 2010. If the value in column D is smaller the cell is highlighted red.
The COUNTIFS function returns an array that indicates the position of the corresponding price. The following explanation is for cell D12, see picture above.
COUNTIFS($B12, 2010'!$B$3:$B$12, $C12, 2010'!$C$3:$C$12) returns {0; 0; 0; 0; 0; 0; 1; 0; 0; 0}.
The MATCH function returns the position of a given value in the array.
MATCH(1,COUNTIFS($B12, 2010'!$B$3:$B$12, $C12, 2010'!$C$3:$C$12), 0)
becomes
MATCH(1,{0; 0; 0; 0; 0; 0; 1; 0; 0; 0}, 0) and returns 7. Now we know where the value we are looking for is.
$D12<INDEX(2010'!$D$3:$D$12, MATCH(1,COUNTIFS($B12, 2010'!$B$3:$B$12, $C12, 2010'!$C$3:$C$12), 0))
becomes
$D12<INDEX(2010'!$D$3:$D$12, 7)
becomes
$441.27<$450 and returns TRUE.
Cell D12 is highlighted red.
Green Conditional Formatting formula
The green CF formula is similar to the red CF formula except that the cell is highlighted green if the value in column D is larger than the corresponding value in worksheet 2010.
The only difference between the red and green CF formulas is the larger than and smaller than sign.
Yellow Conditional Formatting formula
The yellow CF formula checks if the category and product is not found in worksheet 2010, if TRUE the cell is highlighted yellow.
Two index table
The questions I am going to answer in this article 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?
Here are the two tables together on the same worksheet.
As you can see "product E" is new for 2011 (highlighted yellow), "product A" type 4 has a lower price than the previous year 2010 (highlighted red), etc.
The colors make it very easy to spot differences.
New values compared to last year
The following CF formula highlights entire row yellow if it finds a new product name.
Conditional formatting formula:
It compares the product and type columns between the tables and if a value is not found the CF formula highlights the entire row yellow.
Find lower prices
Conditional formatting formula:
Cells are formatted red if the price is lower than the price in the other table.
Find higher prices
Conditional formatting formula:
Cells are formatted green if the price is higher than the price in the other table.
How to apply conditional formatting formula
Make sure you adjust cell references to your excel sheet.
- Select cells C11:G15
- Press with left mouse button on "Home" tab
- Press with left mouse button on "Conditional Formatting" button
- Press with left mouse button on "New Rule.."
- Press with left mouse button on "Use a formula to determine which cells to format"
- Copy and paste conditional formatting formula in "Format values where this formula is TRUE" window.
- Press with left mouse button on "Format.." button
- Press with left mouse button on "Fill" tab
- Select a color for highlighted cells.
- Press with left mouse button on "Ok"
- Press with left mouse button on "Ok"
- Press with left mouse button on "Ok"
Explaining find lower prices conditional formatting formula in cell C11
You can follow along, copy the CF formula and paste it in a cell.
Go to tab "Formula" on the ribbon and then press with left mouse button on "Evaluate Formula" button.
Press with mouse on the "Evaluate" button to move to the next step in the calculations.
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
The MATCH function 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 price list
=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. Type 1 is found in position 1 in cell range C3:G3.
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. The picture above shows how the formula finds the value at the intersection of a given row and column number.
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 green.
Get Excel *.xlsx file
Cf compare category
A conditional formatting formula highlights values in column B that also exist in column D. =COUNTIF($D$3:$D$7,B3) The same thing happens […]
In this blog post I will demonstrate a conditional formatting formula that will highlight common records in two lists. The […]
The image above demonstrates a conditional formatting formula that highlights records that only exist in one table. There are two […]
This article demonstrates a conditional formatting formula that will highlight the differences between two columns. The image above shows two […]
The picture above shows two lists. How do I highlight values in List 1 that are not in List 2? […]
Compare category
This article demonstrates ways to extract shared values in different cell ranges, two and three cell ranges. The Excel 365 […]
Array formula in B15: =INDEX($B$3:$B$12, MATCH(0, COUNTIF($B$14:B14, $B$3:$B$12)+IF(((COUNTIF($D$3:$D$11, $B$3:$B$12)>0)+(COUNTIF($F$3:$F$12, $B$3:$B$12)>0))=2, 0, 1), 0)) Copy cell B15 and paste it to […]
This article shows how to compare two nonadjacent cell ranges and extract values that exist only in one of the […]
How would you figure out an unique list where the sum of in one column doesn't match the other column? […]
The image above demonstrates an array formula in cell B11 that extracts values that only exist in List 1 (B3:B7) […]
I will in this blog post demonstrate a formula that extracts common records (shared records) from two data sets in […]
This article explains how to extract values that exist in three different columns, they must occur in each of the […]
Overview Updating a list using copy/paste is a boring task. This blog article describes how to update values in a price […]
This article demonstrates a formula that extracts values that exist only in one column out of two columns. There are […]
Question: How do i remove common values between two lists? Answer: The solution in this article, removes common values and […]
Question: i have two sets of data - one has an identifier column and one result column. A2 data1 B2 […]
The image above shows an array formula in cell B12 that extracts values shared by cell range B2:D4 (One) and […]
The formulas above extracts values that exists only in one or the other cell range, if you are looking for […]
In this example we are going to use two lists with identical columns, shown in the image above. It is […]
This article describes an array formula that compares values from two different columns in two worksheets twice and returns a […]
Conditional formatting category
The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]
This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]
Question: How do I highlight dates that meet criteria using conditional formatting? Table of contents Highlight values in a column […]
This post demonstrates how to highlight records with the closest value to a criterion, you can also choose to highlight […]
The image above shows rows highlighted based on value in column C being the largest or smallest in that particular […]
This article shows you how to easily identify duplicate rows or records in a list. What's on this webpage Conditional […]
The image above demonstrates a conditional formatting formula applied to an Excel Table containing random data. The Excel Table has […]
Here is how to highlight every other row using conditional formatting. Conditional formatting formula: =ISEVEN(ROW())*OR($B3:$D3<>"") Alternative CF formula: =EVEN(ROW())=ROW() This […]
Question: I have a list that I keep adding rows to. How do i create a border that expands as […]
The image above shows you how to highlight rows with multiple criteria using OR logic. The criteria are found in […]
In this article, I will demonstrate how to search a table using conditional formatting. The criteria highlight matching column and […]
The image above demonstrates a conditional formatting formula that highlights duplicate items based on date. The first instance is not highlighted, […]
A conditional formatting formula highlights values in column B that also exist in column D. =COUNTIF($D$3:$D$7,B3) The same thing happens […]
In this post I am going to try to explain formula basics in conditional formatting. It is really good if […]
The image above shows conditional formatting highlighting unique distinct values, duplicates are not highlighted. Conditional Formatting Formula: =COUNTIF($B$3:B3, B3)=1 The […]
This article demonstrates how to apply different cell formatting to a cell range based on a Drop Down list, column […]
In this blog post I will demonstrate a conditional formatting formula that will highlight common records in two lists. The […]
The image above demonstrates a conditional formatting formula that highlights records that only exist in one table. There are two […]
Functions in this article
More than 600 Excel formulas
Conditional Formatting categories
Excel categories
4 Responses to “How to highlight differences in price lists”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
thanks
Thank u so much
I want to compare price of a material for previous month and current month and if the difference exceeds a certain limit want to highlight the same.
Hello Oscar. Excellent articles above. Have subscribed to your blog.
Am compiling my monthly shopping list and need help with the following.
A. Need to highlight (colour) lowest and highest prices (arranged in rows) out of 5 shops (arranged in columns)
B. Need to subtotal the average price for each item and it becomes my budget for that item.
C. Need to subtotal the lowest price as amount to add as the price to pay.
D. Need to total all budget item prices as expected budget for the given current month.
E. Need to total all lowest item prices as total expected price to pay for the given current month.
F. To be able to add a new column as new current month and current month becomes last month.
G. To compare the differences between last month vs this month.