To show percentage change on an Excel column chart, calculate the change between periods, then either use those percentages as the charted values or display them as labels on columns showing the original values. For a period-over-period calculation, with January in B2 and February in B3, enter =(B3-B2)/B2 in C3 and format C3 as a percentage; the example returns 25% when B2 is 120 and B3 is 150.
What percentage change means in a chart
Percentage change describes how much a value has risen or fallen relative to its starting value. The calculation is (new value minus old value) divided by old value. A positive result indicates an increase, a negative result indicates a decrease, and zero indicates no change. Because it uses the starting value as its reference, percentage change can make movements easier to compare when the original amounts differ in scale.
For example, an increase from 120 to 150 is 25%, while an increase from 1,000 to 1,030 is 3%. A chart of the underlying amounts emphasizes that the second increase is larger in absolute terms; a chart of percentage changes emphasizes that the first increase is larger relative to its starting point. Neither view is universally better: choose the measure that answers the reader’s question.
There are two common ways to combine percentage change with a column chart:
- Chart the percentage changes themselves. This is useful when the rate of growth or decline is the main subject.
- Keep columns for the original values and add percentage changes as data labels or as a separate line. This preserves the amounts while adding context about movement.
Set up the data and calculate the change
Put periods in column A and the values you want to compare in column B, with one period per row. For example, A2:A5 might contain January through April, while B2:B5 contains 120, 150, 135, and 162. Use a separate column for the change so that the original values remain intact. The first period has no prior period in this table, so it has no period-over-period change.
With January’s value in B2 and February’s value in B3, enter =(B3-B2)/B2 in C3. It returns 25% because (150 − 120) ÷ 120 = 0.25. Copy the formula down through C5. With the example values, March’s change is -10% because (135 − 150) ÷ 150 = -0.10, and April’s is 20% because (162 − 135) ÷ 135 = 0.20. Format C3:C5 as percentages using Home > Number > Percent Style. Adjust decimal places with Increase Decimal or Decrease Decimal if needed.
The formula compares each row with the one immediately above it. If your data is arranged newest-first, or periods are missing or out of order, sort or reorganize it before copying the formula; otherwise, it will compare the wrong observations. For year-over-year change, compare each value with the corresponding period a year earlier instead of the previous row.
Choose what the columns should represent
If the question is how growth rates vary across months, select the period names and percentage-change values, then make a column chart from those percentages. In this example, use A3:A5 and C3:C5, because January has no preceding month from which to calculate a change. Choose Insert > Insert Column or Bar Chart > Clustered Column. The horizontal axis shows the periods, and each column’s height represents its change rate.
This design makes gains and losses easy to compare around zero, especially when the vertical axis includes both positive and negative values. It does not show the original sales or other measurements, however. If readers need both the amount and the rate, use the original values for the columns and show the change as an additional label or series instead.
Consider the audience and the message before choosing. A manager comparing monthly growth may prefer a percentage-only chart. A report tracking actual revenue may need columns for revenue, with change labels to explain month-to-month movement. Avoid treating the two measures as interchangeable: a 20% rise is not the same thing as an increase of 20 units.
Create a column chart of percentage changes
In Excel for Microsoft 365 on Windows, select the period cells and their corresponding percentage-change cells. If the ranges are not adjacent, select one range and then hold Ctrl while selecting the other. Choose Insert > Insert Column or Bar Chart > Clustered Column. Check the resulting axis labels and series name; if Excel assigns an unhelpful heading, edit the header cell in the worksheet and the chart will generally use that heading.
Give the chart a descriptive title, such as Monthly percentage change. To format the vertical axis, right-click it and choose Format Axis. In the Format Axis pane, use the Number section to choose Percentage and set an appropriate number of decimal places. If the data includes losses, retain a visible zero baseline so that readers can distinguish negative from positive changes. Avoid setting axis bounds so tightly that ordinary movements look dramatic.
On Mac, the chart commands are also available from the Insert tab, although ribbon layout and the format pane may look different. Excel for the web offers chart creation and basic formatting, but some detailed formatting controls can differ from the desktop application. If an option described here is not visible in the web interface, open the workbook in the desktop version of Excel.
Keep original-value columns and add percentage labels
To show the actual amounts as columns while adding the change figures above them, first create a standard column chart from the period names and original values. Select A2:B5, then choose Insert > Insert Column or Bar Chart > Clustered Column. This chart’s columns continue to represent the amounts, not the percentages.
Click a column once to select the data series, then add data labels from Chart Design > Add Chart Element > Data Labels, choosing a position such as Outside End. Right-click a label and choose Format Data Labels. In the pane, select Value From Cells and select the percentage-change cells, such as C2:C5, then clear the Value option if you want to show only the percentages. The first label may be blank because the first period has no earlier comparison. If the labels appear on the wrong columns, verify that the selected cell range has one entry for each plotted category and is in the same order.
This approach is often clearer than adding a second vertical scale: the column height remains tied to the actual value, while the label supplies the rate. It is especially useful when the amounts are meaningful on their own and the percentage is supporting information. A chart can become crowded if it has many periods, so consider showing fewer labels or splitting a dense chart into smaller time ranges.
When a secondary-axis line is useful
A combo chart can show the original amounts as columns and percentage changes as a line. Select the chart, then choose Chart Design > Change Chart Type. In the Combo category, assign the value series to Clustered Column and the percentage series to Line; select Secondary Axis for the percentage series. The precise dialog arrangement can vary slightly by Excel build, but the combo-chart choices are part of the desktop Excel chart tools.
A secondary axis is useful when the two series use very different units or scales. Format the right-hand axis as a percentage and label both axes so readers know which measure each one represents. The line then makes the direction of change visible alongside the amount columns.
Use this design with care. A secondary axis can make two unrelated scales appear visually aligned, and changing its bounds can change the apparent steepness of the line. Explain the axes clearly, keep the percentage scale sensible, and do not imply that a line value and a column height are directly comparable. If the chart is for a brief report and the rate is only supplementary, cell-based data labels may communicate the same information with less visual complexity.
Format the chart for accurate reading
Percentage formatting affects how values are displayed, not the underlying calculation. Excel stores 25% as 0.25, so a percentage axis or label should use percentage number formatting. If a chart label shows 0.25 rather than 25%, change the number format; do not multiply the formula by 100 and then also apply percentage formatting, which would display 2,500%.
Use a clear title and, when needed, axis titles that identify the measure and unit. For example, use Revenue ($) for the amount axis and Change (%) for the rate axis. Keep the number of decimal places consistent across labels. Whole percentages are readable for many business summaries, while smaller movements may need one decimal place. Use contrasting colors for positive and negative changes only when the distinction remains clear in grayscale or for readers with color-vision differences; signs and axis position should carry the meaning too.
Do not rely on color alone to identify a loss. A negative bar extending below the zero line, a minus sign in a label, and an explanatory legend or title make the message more robust. Remove unnecessary chart effects and gridlines if they distract from comparisons, but retain useful scale marks so values can be estimated.
Common problems and how to fix them
- #DIV/0!: The previous value is zero, so the percentage-change formula would divide by zero. Percentage change from a zero baseline is undefined by the standard calculation. Decide whether to show a blank, a note such as New, or a separate absolute change; do not present an arbitrary percentage.
- Unexpectedly large percentages: A small starting value can produce a large rate from a modest absolute increase. Show the original amounts as well when the baseline matters.
- Percentages look 100 times too large: Check whether the formula was multiplied by 100 and the cells were also formatted as percentages. Usually, leave the formula as a decimal ratio and apply Percentage formatting once.
- Labels do not match the columns: Check that the selected Value From Cells range contains the same number of rows and the same period order as the chart categories.
- First period appears as zero: The first period has no earlier comparison. Leave its change cell blank rather than implying that it had no change.
- Chart hides negative changes: Check whether the vertical axis minimum has been manually set above zero. Reset it or choose bounds that include the negative values.
Frequently Asked Questions
How do I calculate percentage change in Excel? With the old value in B2 and the new value in B3, enter =(B3-B2)/B2 in a result cell and format it as Percentage. A change from 120 to 150 returns 25%.
Can I show percentage change above columns showing sales? Yes. Create the column chart from the sales values, add data labels, then use Format Data Labels > Value From Cells to select the percentage-change cells. Clear Value if you want the labels to show percentages instead of sales amounts.
Should percentage change be a column or a line? Use columns when comparing distinct period-by-period rates, especially positive and negative changes around zero. Use a line when the rate is a secondary series across a continuous timeline, typically alongside amount columns on a clearly labeled secondary axis.
How do I show a decrease? The formula returns a negative percentage when the new value is below the old value. Format the result as Percentage and make sure the chart’s axis includes values below zero; the negative column will extend beneath the baseline.
Why can’t I calculate percentage change from zero? The standard formula divides by the old value, and division by zero is undefined. Describe the new value as a new or nonzero result, or report its absolute increase separately rather than claiming a finite percentage change.
Related
- Excel 3-Color Scale: How It Works and When to Use It
- Excel Conditional Formatting Based on Color: How It Works
- Conditional Formatting in Excel: Compare Dates
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- How to Make a Graph in Excel the Easy WayTo make a graph in Excel, put your labels and numbers in a simple table, select the data,…
- Excel Pivot Chart: How to Remove a SeriesTo remove a series from an Excel PivotChart, select the chart, open PivotChart Analyze > Field List, and…
- Add a Histogram in Excel: Create, Format, and Read ItTo add a histogram in Excel for Microsoft 365 on Windows, select one column of numeric data, then…
- Excel Pareto Chart Template: Build One Step by StepYes. In Excel for Microsoft 365 on Windows, a reusable Pareto chart template can be made from a…
- How to Make a Graph in Google Sheets on a ComputerTo make a graph in Google Sheets on a computer, arrange your information in labeled rows or columns,…
- Does Excel Online Have Sparklines? Setup and LimitsYes. Excel for the web—often called Excel Online—can create and format sparklines, the small charts that fit inside…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.