The progress bar is a pictorial representation that tells about the details of any goal or task. By seeing the progress bar one can judge how much work is completed and how much is left to be covered. By default, there is no such Progress Chart in Excel but it can be made manually using the existing vertical or horizontal bars. Generally, horizontal bars are mostly preferred to make a Progress Chart as it gives a more insightful view.
In this article, we are going to see the step-by-step process of making a Progress Bar in Excel using a suitable example shown below :
Example: Consider your class teacher has assigned you some tasks to complete. He also asked you to send him a report of the progress you made in the tasks every Sunday. There are two options :
- By using a table.
- The pictorial representation is the Progress Bar.
Consider the table shown below :
| Progress Report | ||
|---|---|---|
| Tasks | Completed | Remaining |
| T-1 | 100% | 0% |
| T-2 | 67% | 33% |
| T-3 | 45% | 55% |
| T-4 | 30% | 70% |
| T-5 | 0% | 100% |
Implementation :
Step 1: Insert the data in the cells of Excel. Now for the column “Remaining” no need to enter the data manually. It will become cumbersome if there are more records. Excel provides us with a formula to directly calculate it. You can find the “Remaining” value for the first record T-1 and then drag the cell down and automatically all the columns will be filled with the values. The command is :
= 1 - Cell_Number Cell_Number : The cell number of the "Completed" column whose remaining percentage value is to be calculated The Cell_Number can be manually entered or by just left clicking on the cell.
Step 2: Now select all the data and perform the following operations :
Select -> Insert -> Chart Sets -> 2-D Bar (Horizontal)
2-D Bar Insertion
Step 3: This is the most important step in making a Progress bar. Here, we have to perform a lot of modifications to the above-inserted chart to make it look like a Progress bar.
To perform any modification in the chart, you can simply select the point in the chart to be modified and then right-click and click on “Format”. Another way is by using the “+” button in the top right corner of the chart.
- Formatting the axis: Select the axis and then right-click on it and click on “Format Axis”. Now under the Format axis tab change the Bound and Unit values as shown below :
Format Axis
Axis changed
Now, it can be observed that the order of the tasks is reversed in the chart. To edit it again go to the format axis window and check the “Categories in Reverse Order” as shown below :
You can now format the Y-axis containing the tasks by increasing its font size and changing to “Bold” for a better view as shown below :
Remove all the outlines by going to Shape Outline and select “No Outline”.
- In the Progress bar, we don’t need any title, axes for the percentage, grid lines. We can simply remove them by clicking on the “+” button in the top right corner and uncheck those options. You can simply remove the “Percentage” axis by selecting it and pressing the “Delete” button.
Modified Chart
- Now the final goal is to make the bar more insightful by changing the color and its transparency so that it looks like a Progress bar. In the above chart, the “Blue color” part is the task completed, and the “Orange color” is the task remaining. So, the remaining part has to be made transparent.
To assign different color and style to the individual bar :
- Select the bar “Completed Portion” in blue color.
- Click on “Format” on the top of Excel.
- Now select Theme Styles. Select the one which is having some shadow behind the color.
Assign different colors to all the bars of the “Completed” portion only. Thereafter, we will deal with the “Remaining” portion and will see how to change the transparency.
- Now, select the “Remaining” portion in the bar individually and right-click on it and then select “Format Data Points.”
Now in the Format Data Point window, select Fill as “Solid Fill” and then change the color to the original color assigned to the bar as in the previous step and change the transparency to 60-70% to reduce the glow of the color. Repeat it for all the “Remaining” bar portions.
Progress bar
Now, you can change the value in the initial table and see the Progress Bar will also change as shown below :
- To make the progress bar more appealing we can add data labels to it which tells data about how much work is completed.
Select the bars only the “Completed” portion and then right-click and select “Add Data Labels”. Now Data Labels will be added to it and you can change the font, size, color, and style of the Data Labels as per convenience.
Data Labels
Finally, after all the modifications the Progress Bar will look like this:
Final Progress Bar
This is one of the famous designs for a Progress Bar. You can perform various other modifications to obtain other types of Progress Bars as per your requirements.