Price Volume Mix Analysis in Excel, Step by Step

A price volume mix analysis in Excel answers the question every sales review asks: why did revenue change? It splits the change into a price effect, a volume effect and a mix effect, so you can say "we grew 7,300, mostly from selling more units and a richer product mix, while price added a little". This guide builds the analysis step by step on a three-product example, with formulas you can copy and a check that proves the numbers add up.

What price, volume and mix mean#

Each effect isolates one reason revenue moved between an old period (last year, or budget) and a new one (this year, or actual).

Mix is the one people find hardest. Imagine you sell exactly as many units as last year at exactly the same prices, but more of them are your expensive product. Revenue goes up, and neither price nor volume explains it. That gain is the mix effect.

Step 1: Build the summary table#

You need four numbers per product: units and price for each period. All figures here are an example, not real company data. Put them in A1:E4:

LY is last year and TY is this year. If your data is a list of transactions, build this table with SUMIFS. For example, with the transactions in an Excel table named Sales, units for Basic last year are:

=SUMIFS(Sales[Units],Sales[Product],A2,Sales[Year],2025)

Then price is revenue divided by units, the average realised price. The SUMIFS function guide and the SUMIF lesson cover the totals.

Revenue was 53,000 last year (1,000 × 20 + 600 × 35 + 200 × 60) and 60,300 this year: =SUMPRODUCT(C2:C4,E2:E4). That's an increase of 7,300, or 13.8% with the percent change formula. Now explain it.

Step 2: Calculate the price effect#

The price effect is the price change for each product multiplied by this year's units:

=SUMPRODUCT(E2:E4-D2:D4,C2:C4)

The result is 1,050. Using this year's units means the price effect reflects what you actually sold. Basic adds 1,100 (1 more × 1,100 units), Standard adds 550, and Premium loses 600 (2 less × 300 units). SUMPRODUCT multiplies each pair and adds them in one go; see the SUMPRODUCT guide and the SUMPRODUCT reference for how it works.

Step 3: Calculate the volume effect#

The volume effect values the change in total units at last year's average price, as if the product blend hadn't changed. Last year's average price is total revenue ÷ total units, 53,000 ÷ 1,800 = 29.44.

=(SUM(C2:C4)-SUM(B2:B4))*SUMPRODUCT(B2:B4,D2:D4)/SUM(B2:B4)

Total units rose by 150 (from 1,800 to 1,950), so the result is 150 × 29.44 = 4,416.67.

Why the average price? Valuing the extra 150 units at last year's average assumes they were sold in last year's blend of products. Any gain or loss from a different blend is left for the mix effect, so nothing is counted twice.

Step 4: Calculate the mix effect#

The mix effect compares this year's units at last year's prices with what the same total units would have earned at last year's average price:

=SUMPRODUCT(C2:C4,D2:D4)-SUM(C2:C4)*SUMPRODUCT(B2:B4,D2:D4)/SUM(B2:B4)

That is 59,250 − 1,950 × 29.44 = 1,833.33. It's positive because Premium, the most expensive product, grew from 11.1% of units to 15.4%, while Standard fell from 33.3% to 28.2%.

Step 5: Check that the effects add up#

The three effects must add up to the actual change in revenue:

7,300 is exactly 60,300 − 53,000. With the three effects in H2:H4, put this check in a cell: =ROUND(SUM(H2:H4)-(SUMPRODUCT(C2:C4,E2:E4)-SUMPRODUCT(B2:B4,D2:D4)),2). It should show 0. If it doesn't, a range is probably off by one row.

To see where the effects come from, split them by product. With totals in row 5 (=SUM(B2:B4) and so on), these row formulas fill down and add up to the same totals:

• Price: =(E2-D2)*C2
• Volume: =($C$5-$B$5)*B2/$B$5*D2
• Mix: =(C2-$C$5*B2/$B$5)*D2

The $ signs lock the total cells so they stay fixed as you fill down.

Present it as a bridge, and know the limits#

Show the result as a bridge from old revenue to new: 53,000, then + price, + volume, + mix, ending at 60,300. In Excel 2016 and later, select the labels and amounts and choose Insert > Waterfall chart. The same layout works for a budget vs actual report (use budget as the old period) or a line in a profit and loss statement.

Keep these points in mind:

• There is more than one method. Some analysts value the price effect at last year's units, or show a separate "price-volume" term. Each method splits the 7,300 a little differently. None is wrong, but use one consistently and say which in a footnote.
• New and discontinued products have no price in one period. Show them as their own bar and run the analysis on products sold in both periods.
• Price is an average. Discounts and returns change the realised price, so build price from revenue ÷ units, not from a list price.

For the wider reporting picture, see how to build a sales report in Excel.

Practise it#

Try the exercise on this page to calculate the price effect with one SUMPRODUCT. Then practise percentages and % change in a live spreadsheet and practise conditional totals in the SUMIF lesson, where every formula you type is checked instantly.