This article will illustrate how to show the percentage change in an Excel graph. Using an Excel graph can present you the relation between the data in an eye-catching way. Showing partial numbers as percentages is easy to understand while analyzing data. In the following dataset, we have a company’s Profit during the period March to September.
Download Practice Workbook
2 Ways to Show Percentage Change in Excel Graph
1. Using Column Chart to Show Percentage Change in Excel
For the dataset we have, we are going to scrutinize the percentage change in monthly profit by a Column Chart. Let’s make some simple discussion below.
- First, make a new column for the profits of the following months and type the following formula in cell D5.
- Then press ENTER and you will see the profit of April month appear in cell D5.
- Use the Fill Handle to AutoFill the lower cells.
- Now make some columns for Variance in profits, Positive Variance, Negative Variance, and Percentage Change.
- Type the following formula in cell E5, hit the ENTER button, and use Fill Handle to AutoFill lower cells as you did in the Next Profit column. (To see the AutoFill procedure, go through the First Formula)
- Then again, type this formula in cell F5, hit the ENTER button and use Fill Handle to AutoFill lower cells like you did in the Next Profit column. (To see AutoFill procedure, go through the First Formula)
The IF Function stores the increase of profits in column F.
- After that, write down the formula given below in G5, hit the ENTER button, and use Fill Handle to AutoFill lower cells like you did in the Next Profit column. (To see the AutoFill procedure, go through the First Formula)
This time, the IF Function stores the decrease in profits in Column G.
- Finally, type the following formula in cell H5, hit the ENTER button, and use Fill Handle to AutoFill lower cells as you did in the Next Profit column. (To see the AutoFill procedure, go through the First Formula)
Now we are going to show this information in a chart. Especially our main focus is on Percentage Change. Let’s see how we make a chart for this.
- Select the range B4:D11 (Month, Profit, and Next Profit columns) and then go to Insert >> Clustered Column Chart
A chart will appear.
- Double-click on the Next Profit column (Orange colored bars) in the graph. Then click on the plus icon and uncheck the Legend and Gridlines option (This is optional, I’m doing this to get a better view of the chart).
- Select Error Bars >> More Options… .
- You will see the Format Error Bars Window. Make sure that the Direction is set to Both and the End Style is Cap. After that from the Error Amount options, select Custom and click on Specify Value.
- A dialog box will appear. Select the range F5:F11 and G5:G11 for the Positive Error Value and Negative Error Value sections respectively.
- Click OK.
- Now right-click on any of the Profit Column Bars (Blue Colored Bars) and select Format Data Series…
- Make Series Overlap and Gap Width to 0% in Format Data Series Window. Also, make sure that Plot Series remains on Primary Axis.
- To change the Fill Color of Next Profit Bars to no Fill, right-click on any of the Orange Bars after selecting the Chart. Then click on the drop down list of the Fill and choose No Fill.
- You will see the Next Profit Bars become neat and clean. Now click on the plus symbol of the chart and select Data Labels >> More Options…
- Then the Format Data Labels will appear. Uncheck Value and check Value From Cells
- You will see a dialog box. Select the range H5:H10 (Percentage Column) for the Data Label Range and click OK.
After that, you will see the increase and decrease in monthly profits in percentage in the chart.
This is a very nice and effective way to show the percentage change in Excel Graph.
- How to Calculate Average Percentage Change in Excel (3 Simple Ways)
- Calculate Percentage Between Multiple Numbers in Excel
- How Do You Calculate Percentage Increase or Decrease in Excel
2. Showing Percentage Change by Using Line Chart in Excel
You can also represent percentage change by using a line diagram. To do this, follow the description below.
- Select the range B4:C11 and then go to Insert >> Line With Markers
- You will see the Line Chart then. But you won’t see the percentage change in that graph.
- So, to see the percentage change, please go through the steps of Section 1 of this article to generate the Line Graph. The process of showing percentage change is completely similar to the first section. Only this time, we don’t need the Error Bars, so we uncheck Error Bars. After repeating those steps, you will finally get a Line Diagram of the percentage change.
So, this is another simple way to show the percentage change in an Excel graph.
Here I present to you the dataset that we used to show the percentage change in an Excel graph so that you can practice on your own.
My goal was to give you some basic tips on how to show the percentage change in an Excel graph. This is very important when you want to understand the business condition of your organization and for that to predict what kind of action needs to be taken. If you have any questions or feedback, please feel free to share them in the comment box. This will help me enrich my upcoming articles.
- How to Make a Percentage Bar Graph in Excel (5 Methods)
- Convert Percentage to Basis Points in Excel (Basis Point Calculation)
- How to Add Percentages to Numbers in Excel (4 Easy Ways)
- Convert Number to Percentage in Excel (3 Quick Ways)
- How to Calculate Percentage Change with Negative Numbers in Excel