For a sales comparison in Excel, calculate the change as new sales minus old sales, then divide that difference by old sales to get the percentage change. If old sales are in B2 and new sales are in C2, enter =C2-B2 for the amount change and =(C2-B2)/B2 for the percentage change; format the second result as a percentage.
The basic sales comparison formula
A comparison usually answers two different questions: how many dollars or units did sales change by, and how large was that change relative to the starting figure? The first is an absolute change; the second is a percentage change. Keeping both measures makes a report more informative than either one alone.
For example, sales rising from $10,000 to $12,000 are up $2,000, or 20%. A $2,000 increase from $100,000, however, is only 2%. The absolute change is identical, but the percentage gives useful context about the size of the original sales base.
The standard percentage-change calculation is (new value − old value) ÷ old value. In Excel, use ordinary cell references and put the formula in the row for the item or period you are comparing. A positive result means sales increased, a negative result means they decreased, and zero means they did not change.
Set up the worksheet
Arrange the figures so each row represents the same product, region, salesperson, or other category, and each sales column represents a period. Use clear headings and consistent units. For example:
- Column A: Product
- Column B: Previous month sales
- Column C: Current month sales
- Column D: Change in sales
- Column E: Percentage change
Enter the change formula in D2, assuming the previous and current figures are in B2 and C2. The formula is =C2-B2. If B2 contains 10,000 and C2 contains 12,000, the result is 2,000. Copy the formula down to compare the other rows. In Excel for Microsoft 365 on Windows, drag the fill handle—the small square at the bottom-right corner of the selected cell—down the column. Excel adjusts the row references automatically.
For the percentage-change column, with old sales in B2 and new sales in C2, enter =(C2-B2)/B2. If those cells contain 10,000 and 12,000, respectively, the result is 0.2, which represents 20%. Select the result cells and choose Home > Number > Percent Style, or use the percentage button in the Number group. Increase or decrease decimal places if the report needs more or less precision.
Worked example with product sales
Suppose a store wants to compare three products between two months. The data are $10,000 and $12,000 for Product A, $8,000 and $7,600 for Product B, and $5,000 and $6,500 for Product C. Put the earlier month in column B and the later month in column C. For each row, the difference formula in column D is =C2-B2, assuming that row’s earlier and later values are in B2 and C2. For Product A, the result is $2,000; for Product B, it is −$400; for Product C, it is $1,500.
For the percentage column, with the same B2 and C2 layout, use =(C2-B2)/B2. The results are 20% for Product A, −5% for Product B, and 30% for Product C. These figures show both the direction and relative scale of each change. A negative value is not an Excel error: it means the later-period sales are lower than the earlier-period sales.
To compare the combined sales, add a total row beneath the products. With the three earlier-period amounts in B2:B4, enter =SUM(B2:B4) in B5; it returns $23,000. With the later-period amounts in C2:C4, enter =SUM(C2:C4) in C5; it returns $26,100. For the total dollar change, with those totals in B5 and C5, enter =C5-B5; it returns $3,100. For the total percentage change, using the same total cells, enter =(C5-B5)/B5; the result is approximately 13.48%.
Notice that the overall increase is not the simple average of 20%, −5%, and 30%. The products started with different sales amounts, so their changes contribute different amounts to the total. Comparing the summed sales calculates the percentage change in the overall sales base and is generally the appropriate method when reporting a combined total.
Handle a zero or missing baseline
The percentage formula divides by the earlier sales amount. If that baseline is zero, Excel cannot calculate a conventional percentage change because division by zero is undefined. A product that went from zero sales to $500 has a $500 increase, but it does not have a meaningful percentage increase from zero. Avoid presenting an arbitrary percentage in this situation; show the dollar change and label the percentage as not applicable or as a new sale.
For a row with old sales in B2 and new sales in C2, use =IF(B2=0,"N/A",(C2-B2)/B2) to display N/A when the baseline is zero and calculate the percentage otherwise. If B2 is zero and C2 is 500, the result is N/A. If B2 is 10,000 and C2 is 12,000, the result is 0.2, or 20% when formatted as a percentage. The formula treats a blank B2 as zero, so check that missing data are not accidentally being mistaken for zero sales.
It is also helpful to distinguish an actual zero from unavailable information. If a blank means the data have not arrived, filling it with zero can make a comparison misleading. Resolve missing entries or flag them separately before using totals and percentage changes.
Choose the comparison that answers the question
“Sales comparison” can refer to several different measures. Pick the one that fits the question, and label the result so readers do not mistake a dollar difference for a growth rate.
- Amount change: New sales minus old sales. Use it when the question is how many dollars, units, or orders were added or lost.
- Percentage change: The difference divided by the old amount. Use it to compare relative movement across products or periods with different starting sizes.
- Sales ratio: New sales divided by old sales. A result of 1.2 means the new value is 1.2 times the old value, or 120% of it. That is a 20% increase, not a 120% increase.
- Share of total: A product’s sales divided by total sales. This describes its portion of the total, not how much its sales grew.
For a ratio, with old sales in B2 and new sales in C2, use =C2/B2. If sales move from $10,000 to $12,000, the result is 1.2, or 120% when formatted as a percentage. This can be useful when a report explicitly asks how current sales compare as a multiple, but percentage change is usually clearer for describing growth.
For a share calculation, suppose product sales are in B2:B4 and the total is in B5. In C2, use =B2/$B$5 to calculate Product A’s share. If Product A has $10,000 of a $23,000 total, the result is about 43.48%. The dollar signs keep the total reference fixed when you copy the formula down. Share of total and percentage change answer different questions, even though both are often displayed as percentages.
Make the comparison fair
A correct formula cannot repair an unfair comparison. Compare equivalent time periods and use the same definition of sales on both sides. Monthly sales should usually be compared with another month, and a partial month should not be compared directly with a full month without explaining the difference. Consider whether the figures include returns, discounts, taxes, shipping, or cancellations, and apply the same treatment to both periods.
Seasonality can also change what a useful comparison looks like. Comparing December with November may show a real change, but it may not reveal whether the business is performing better than it did at the same time last year. A year-over-year comparison—this month against the corresponding month in the previous year—can provide context, provided the periods and accounting rules match.
Check that the rows refer to the same products or categories and that currencies and units are consistent. A sales amount in dollars cannot be meaningfully added to one in euros without conversion, and revenue should not be compared with units sold as if they were the same measure. If a product was introduced during the later period, mark it as new rather than interpreting its zero prior sales as an ordinary percentage decline or increase.
Common formula and reporting problems
- Reversing the subtraction: The standard change is new minus old. Reversing the order changes the sign and can make an increase look like a decline.
- Using the new value as the denominator: Percentage change is measured against the starting value. Dividing by current sales gives a different statistic.
- Forgetting percentage formatting: A formula result of 0.2 means 20%, not 0.2%. Apply percentage formatting rather than multiplying the result by 100 and then formatting it as a percentage.
- Comparing totals with average rates: The average of individual percentage changes may not equal the percentage change in total sales. Calculate from the total amounts when the question concerns overall sales.
- Including the wrong rows: Check that total formulas include every relevant product and exclude headings, subtotals, and unrelated data.
- Confusing blanks and zeros: A blank can mean missing information, while zero means measured sales were zero. Treat them consistently and document the difference.
If a result looks unexpected, inspect the input cells and the formula bar, then check whether the two periods use the same units and scope. A sudden percentage can be mathematically correct but still misleading if the baseline is unusually small. Showing the dollar change next to the percentage helps readers see that context.
Present results clearly in Excel
Use descriptive headings such as “Change ($)” and “Change (%)” instead of a generic “Comparison.” Apply a consistent number format: currency for revenue, whole numbers for units, and a sensible number of decimal places for percentages. For an executive summary, one decimal place is often readable; retain more precision in the underlying data if needed.
To make positive and negative changes easier to scan, select the percentage-change cells and use Home > Conditional Formatting > Highlight Cells Rules. Choose a rule that fits the report, and verify that the colors remain understandable when printed or viewed by someone with color-vision differences. Do not rely on color alone; retain the signed number and a clear heading.
A chart can complement the table, particularly when comparing trends over several periods. Use a line chart for values tracked over time and a bar or column chart for categories in a single period. Keep the source data and labels clear, and do not use a chart to hide the baseline or the distinction between absolute and percentage change.
Frequently Asked Questions
What is the formula for percentage sales growth in Excel?
Subtract old sales from new sales and divide by old sales. If old sales are in B2 and new sales are in C2, use =(C2-B2)/B2, then format the result as a percentage. A zero baseline needs special handling because percentage change from zero is undefined.
How do I compare two sales figures in Excel?
Use the difference formula to show the amount change and the percentage-change formula to show relative growth or decline. For old sales in B2 and new sales in C2, the formulas are =C2-B2 and =(C2-B2)/B2, respectively.
How do I calculate a sales increase or decrease?
Subtract the earlier figure from the later figure. A positive result is an increase and a negative result is a decrease. To express the movement relative to the earlier figure, divide that difference by the earlier figure.
Why does my sales percentage formula show #DIV/0!?
The formula is dividing by a zero or blank baseline. Check whether the earlier sales value is genuinely zero or missing. If it is zero, report the dollar difference or use a label such as N/A instead of claiming a conventional percentage change.
Should I average individual sales growth percentages?
Not when the question is how much combined sales changed. Add the old-period sales and new-period sales, then calculate the percentage change between those totals. Individual percentages have different starting bases, so their simple average may not represent overall growth.
Related
- Outside Border Shortcut in Excel: Windows and Mac
- Redo in Excel: Restore an Undone Action
- Real Estate Excel Template for Cash Flow: Build One
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel AVERAGEIF Not NA: Exclude Errors & TextTo average a range in Excel while ignoring #N/A errors (and other errors), you cannot use AVERAGEIF alone—it…
- Calculating Hours Worked in Excel: Formulas & TipsTo calculate hours worked in Excel, put a shift’s start time in A2 and end time in B2,…
- Check for a Match in Excel: Formulas, Methods, and TipsTo check whether a value in Excel appears in a list, use =IF(COUNTIF($B$2:$B$20,A2)>0,"Match","No match"). With the value to…
- Excel Row Filter: How to Show Only Matching Data RowsTo filter rows in Excel for Microsoft 365 on Windows, select a cell in your data and choose…
- Excel Formula for Date Minus Date: Days BetweenThe core Excel formula for subtracting one date from another is simply =B2-A2, where B2 holds the later…
- PV of Annuity Formula in Excel: Examples and TimingWith the annual interest rate in A2, the number of annual payments in B2, and each end-of-year payment…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.