Being able to calculate and present percentage changes over time in Excel is an incredibly useful skill for data analysis. Whether you are analyzing financial data, sales figures, website metrics, or any other time-series data, understanding growth rates and trends is key.
In this comprehensive guide, I will walk you through everything you need to know to calculate and clearly present percentage changes in Excel, step-by-step.
Table of Contents
Calculating Percentage Changes
The basic formula for calculating percentage change between two values in Excel is:
=(New Value - Old Value) / Old ValueTo show the result as a percentage, simply apply the percentage number format by clicking the % button in the Number section of the Home tab.
Here is a simple example, calculating the percentage change in website visitors between January and February:
Excel percentage change example
As you can see, website visitors increased by 12.5% between January and February.
You can use this same formula to calculate changes between any two time periods, like month-over-month, year-over-year, day-over-day etc. Simply input the old and new values into the formula.
Presenting Percentage Changes Visually
While the raw percentage change figures are useful for analysis, presenting the changes visually in charts makes it much easier to identify trends and communicate findings.
Here are some of the best ways to present percentage changes visually in Excel:
Line Chart
A simple line chart with percentage change on the vertical axis and time on the horizontal axis allows you to see the trends clearly:
Excel line chart percentage change
Column Chart
A column chart presents the same data in a slightly different visual format:
Excel column chart percentage change
Combo Chart
You can also combine columns and a line to emphasize both the individual changes and overall trend:
Excel combo chart percentage change
Conditional Formatting
Applying conditional formatting to the percentage change values in Excel allows you to quickly visualize increases and decreases:
Excel conditional formatting percentage change
In this example, increases are formatted green and decreases are formatted red. This draws the eye to the key changes.
Sparklines
Sparklines are a great way to visualize trends and percentage changes over time, even when space is limited:
Excel sparklines percentage change
Here the sparklines highlight the overall increasing website visitor trend and monthly growth rates.
Formatting Percentages Appropriately
When presenting percentage change figures, proper number formatting is important for clarity and professionalism.
Here are some key formatting tips:
- Always show at least one decimal place (e.g. 12.5% not 13%)
- Be consistent with decimal places, don’t switch between figures with 1 and 2 decimal places
- Show thousands separators for large figures (e.g. 1,250%)
- Consider showing no more than 2 decimal places to avoid false precision
Make sure to apply these percentage formats consistently throughout your Excel dashboards and reports.
Rolling Percentage Changes
When analyzing trends over longer time periods, it can be useful to calculate rolling percentage changes – like year-over-year or month-over-month changes.
The formulas are the same, you simply apply them to calculate changes between a fixed point in time and the comparable period from the previous year.
For example, here are month-over-month percentage changes:
Excel rolling percentage changes
And the same data presented visually:
Excel rolling percentage change chart
This allows you to clearly see monthly website visitor trends compared to the same period in the previous year.
Percentage Change Summary Statistics
When analyzing longer time series, it can also be useful to summarize the distribution of percentage changes in key statistics like the mean, median, minimum, maximum and quartiles.
For example:
Excel percentage change summary stats
This allows you to describe the typical monthly change as well as the spread and extremes, communicating insights like:
“On average, monthly website visitor growth has been 5.2% year-over-year, with most months (50% quartile) seeing changes of 2-9% and extreme declines or growth peaking at -16% and 34% respectively.”
Presenting Insights with Dashboards
Pulling together formatted metrics, charts and summary statistics into interactive Excel dashboards can help turn your percentage change data into clear insights.
For example, this dashboard combines visualizations, sparklines and conditional formatting to allow for rich analysis:
Excel percentage change dashboard
With key headline figures, trends, distributions and historical comparisons all visible, it is easy to analyze performance and make data-backed decisions.
Final Thoughts
Calculating and clearly presenting percentage metric changes over time is a vital skill for any analyst or data professional. I hope this guide gives you a comprehensive overview of simple but effective techniques to master growth rate analysis in Excel.
The key takeaways are:
- Use the basic percentage change formula to calculate differences
- Visualize changes clearly with charts and conditional formatting
- Format percentages appropriately for clarity
- Calculate rolling month-over-month or year-over-year changes
- Summarize distributions with quartiles and extremes
- Bring key metrics, visuals and insights together into dashboards
If you apply these best practices, your Excel reports and models will communicate trends, growth rates and performance much more effectively to support better decision making.
