Create progress bar chart in Excel
A progress bar chart is a type of bar chart which can help you to monitor progress towards a target, it is a simple and visually chart, this article, I will talk about how to create progress chart in Excel.
- Create progress bar chart based on percentage values
- Create progress bar chart based on actual and target values
- Create horizontal or vertical progress bar in cells
- Create progress bar chart based on percentage or actual and target values with a handy feature
- Download Progress Bar Chart sample file
Create progress bar chart based on percentage values
Supposing, you have the following progress completion of the tasks, to create a progress chart based on this completion percentage, please do as this:
1. First, please calculate the remaining percentage of each task, enter this formula: =1-B2, and then drag the fill handle down to the cells, see screenshot:
2. Then, select the data range in column A, column B, column C, and then, click Insert > Insert Column or Bar Chart > Stacked Bar, see screenshot:
3. And then, a stacked bar chart has been inserted, you can delete other elements that you don’t need, such as chart title, grind line, legend, see screenshots:
 |
 |
 |
4. Then, right click the X axis, and choose Format Axis from the context menu, see screenshot:
5. In the opened Format Axis pane, under the Axis Options tab, change the number in Maximum to 1.0, see screenshot:
6. Then, select the performance data bar in the chart, and then click Format, in the Shape Styles group, select one theme style as you need, here, I will select Intense Effect -blue, this will make the bar look more 3-d, see screenshot:
7. And you will get the chart as below screenshot shown:
8. And then, you should format the remaining data bar, right click the remaining data bar, and choose Format Data Series from the context menu, in the opened Format Data Series pane, under the Fill & Line tab, from the Fill section, select Solid fill, and choose one similar color with the performance bar color, and then adjust the transparency to your need, see screenshot:
9. Next, you should add data labels for the chart, right click the performance data bar, and choose Add Data Labels > Add Data Labels, see screenshot:
10. And the data label has been added to the chart, then you can format the data labels as you need, see screenshots:
 |
|
 |
Create progress bar chart based on actual and target values
If you have two columns data which contain the target and actual values, to create progress bar chart based on the data, please do with the following steps:
1. Please calculate the percentage of the target and actual values, enter this formula: =C2/B2 into a cell, and then drag the fill handle down to the cells, after getting the result, format the data as percentage, see screenshot:
2. Select the whole data, and then click Insert > Insert Column or Bar Chart > Clustered Bar, see screenshot:
3. And a bar chart has been created as below screenshot shown:
4. Then, right click the target data bar, and choose Format Data Series from the context menu, see screenshot:
5. In the Format Data Series pane, under the Fill & Line tab, select No fill from the Fill section, and select Solid line from the Border section, and specify a border color to your need, see screenshot:
6. Then, click to select the chart, and click Chart Elements, in the expanded Chart Elements dialog box, check Data Labels > Inside Base, and all data labels have been added into the chart, see screenshot:
7. Now, you should delete other data labels only keep the percentages, see screenshot:
8. And then, right click any bar in the chart, and choose Format Data Series, in the Format Data Series pane, under the Series Options tab, enter 100% into the Series Overlap box, see screenshot:
9. Then, right click X axis in the chart, and choose Format Axis, and in the Format Axis pane, under the Axis Options tab, change the number in Maximum to 1000.0 which is the target number of your data, see screenshot:
10. At last, remove other unneeded chart elements as you need, and you will get the progress chart as below screenshot shown:
Create horizontal or vertical progress bar in cells
In Excel, you can also create the progress bars in cells with Conditional Formatting, please do as this:
Create horizontal progress bars with Conditional Formatting
1. Select the cells where you want to insert the progress bars, and then click Home > Conditional Formatting > Data Bars > More Rules, see screenshot:
2. In the New Formatting Rule dialog box, please do the following operations:
- In Type section, choose Number in Minimum and Maximum drop down list;
- Set the min and max values in Minimum and Maximum box based on your data;
- Finally, choose Solid Fill option under the Fill drop down, and then select a color you need.
3. Then click OK button, progress bars have been inserted into the cells as below screenshot shown:
Create vertical progress bars with Sparklines
If you need to insert some vertical progress bars in cells, the following steps may help you:
1. Select the cells where you want to insert the progress bars, and then click Insert > Column (in the Sparklines group), see screenshot:
2. Then, in the popped out Create Sparklines dialog box, select the data range and location range to your need, see screenshot:
3. Click OK button to close the dialog box, and you will get the below bars, see screenshot:
4. Keep the bar cells selecting, click Design tab in the ribbon, and then click Axis > Custom Value under the Vertical Axis Minimum Value Options section, see screenshot:
5. In the Sparkline Vertical Axis Setting dialog, just click OK button to close the dialog box, see screenshot:
6. Go on clicking Axis > Custom Value under the Vertical Axis Maximum Value Options section from the Design tab, see screenshot:
7. In the Sparkline Vertical Axis Setting dialog, enter 1.0 and click OK. See screenshot:
8. And the vertical progress bars have been created as below screenshot shown:
Create progress bar chart based on percentage or actual and target values with a handy feature
Kutools for Excel provides dozens of special types of charts that Excel does not have, such as Bullet Chart, Target and Actual Chart, Difference Arrow Chart and so on. With its handy tool- Progress Bar Chart, you can create a progess bar chart based on percenatge values or actual and target values as you need in Excel.Click to download Kutools for Excel for free trial!
Download Progress Bar Chart sample file
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
