When you are working on reports, analyses and presentations, a common thing during this process is to make a progress chart in Excel. In easy words, a progress chart is a graphical representation to monitor targets and progress. It helps visualize the whole objective in a more significant way. In this article, we will illustrate 2 simple methods on how to make a progress chart in Excel.
How to Make Progress Chart in Excel: 2 Simple Methods
There are 2 methods to make a progress chart in excel- the excel charts feature and conditional formatting. To discuss both, we have prepared a dataset. It has the information of 5 students’ obtained marks out of total marks in the exam with progress in percentage.
Let’s look at the methods below and make progress charts out of this.
1. Insert Excel Charts Feature to Make Progress Chart
The process to make a progress chart by inserting the excel charts feature is really quick and simple. You can make both a bar chart and doughnut chart with this method. Let’s see how to do this.
1.1 Bar Chart
In this section, we will make a progress bar chart with the chart function in excel. Follow the steps below:
- In the beginning, select cell range B4:D9.
- Secondly, go to the Insert tab and select Clustered Bar from the Column Chart options in the Charts section.
- After this, you will get your progress chart initially like this:
- Now we will do some customization to make the progress chart presentable.
- First, select the Obtained Marks bar in the chart.
- Then, right-click and select Format Data Series from the Context Menu.
- After that, make the Series Overlap option 100% in the Format Data Series panel.
- Then, change the Fill type and Color.
- Along with that, change the Border type as Solid line and Color as well.
- Following, select the Horizontal Axis and change the Bounds range. Here, the Minimum is 4 and the Maximum is 10.
- Lastly, choose Inside End from the Data Label options in the Chart Elements section.
- Finally, we have made a progress chart using the bar chart.
1.2 Doughnut Chart
Using the charts feature, we will now create a progress chart using the doughnut chart option. Follow the steps below:
- First, select the dataset of columns B and E by pressing the Ctrl button.
- After that, go to the Insert tab and select Doughnut among the Pie Chart options in the Charts section.
- That’s it, we have got our progress chart.
- After some customization following the steps above, the progress chart looks like this:
Read More: Progress Circle Chart in Excel as Never Seen Before
2. Create Progress Chart with Conditional Formatting in Excel
Another useful method to make a progress chart in Excel is conditional formatting. In general words, conditional formatting is a feature in Excel to highlight or emphasize some cells or values based on certain conditions that are mostly different from others. Let’s see how it works in the case of a data bar and doughnut chart.
2.1 Data Bar
Follow the steps below to make a bar chart to show progress analysis:
- First, select the value cells where you want to insert the bar.
- In the second step, select the Conditional Formatting in the Styles section from the Home tab.
- Therefore, go to Data Bars and select More Rules from its drop-down section.
- After this, the New Formatting Rule window appears.
- In this window, format the Type and Value of the cells.
- Along with this, you can change the Bar Appearance in this window and press OK.
- Finally, we have our data bar shown as the progress chart in Excel.
2.2 Doughnut Chart
The process to make a progress doughnut chart with conditional formatting is a bit complex. So follow the steps carefully:
- To begin the process, we need to add a new Conditions column to our existing dataset.
- Here, in cells F7 and F8, we provided the minimum and maximum range respectively according to which the conditions will perform.
- Now, enter any progress value in the cell G5 like this:
=E6
- After this, insert the IF formula based on the conditions of cells F7 and F8 in cell G7.
=IF(AND(G5>F7,G5<=F8),G5," ")
Here, the IF formula is used to calculate if the actual value is greater or less than the minimum or maximum values. On the other hand, the AND function helps to evaluate the value inside the selected dataset to justify whether it is true or false based on the condition.
- Lastly, insert the MAX formula in cell G9Â to calculate the remainder from the actual and conditional values.
=MAX(1,G5)-G5
- Now, select the cell range G7:G9.
- Then, go to the Insert tab and select Doughnut from the Pie Chart options in the Charts section.
- Finally, we can see the progress chart.
- After some customization, it looks like this:
Download Workbook
Download the sample file and practice.
Things to Remember
- The progress chart does not show any change over time.
- In the case of conditional formatting for the doughnut chart, you can only see one cell value result at a time.
- The progress chart displays values on one metric.
- The progress chart does not show any blank cell information.
Conclusion
In this article, we have gone through 2 simple methods of how to make a progress chart in Excel. Let us know your feedback in the comment section.
