How to Create a Progress Bar in Excel (3 Easy Methods)

While working in Microsoft Excel, we might need to visualize data in a chart to make it easy to understand. Often users use progress bar charts to make the dataset look more attractive. In this article, I am sharing with you how to create a progress bar in Excel.’

How to Create a Progress Bar in Excel: 3 Easy Methods

In the following article, I have shared 3 easy methods to create a progress bar in Excel.

Suppose we have a dataset of a company’s year-wise Forecasted Sales and Actual Sales. Now we will create a progress bar graphing both the Forecasted Sales and Actual Sales.

1. Insert Bar Chart to Create a Progress Bar

A progress bar chart is mostly used to make a company’s financial report. You can plot various values in the same chart. Select your data and make a diagram from the “Insert” option. Excel will create the bar chart automatically. Simple isn’t it? In this method, I am explaining creating a progress bar by inserting a bar chart in an Excel sheet.

Step 1:

• Select data from your data table with the heading that you want to plot in the progress bar chart.
• Here I have selected cells (C4:E11).
• While the data is selected go to the “Charts” list from the “Insert” option.

• Choose a “Clustered Bar” from the “2-D Bar”.

• As you can see a chart will be created plotting all the sales year-wise.

Step 2:

• Now we will edit the chart.
• To edit the chart select bars from the diagram and right-click the mouse button to appear options.
• From the options choose “Format Data Series”.

• Go to “Fill” options and click “Solid Fill”.
• Choose a color from the “Color” row and also select a border color from the “Border” options.

• Remove unnecessary data and here we have our progress bar created successfully.

2. Use Conditional Formatting to Create a Progress Bar

Conditional formatting is mostly used to change formats in various cells. But there is more to it. You can create a progress bar inside in excel cells using the conditional formatting.  In this method, I will show you how to create a progress bar using conditional formatting.

Step 1:

• Firstly, let’s calculate the achievement percentage by dividing the actual sales by the forecasted sales.

• Now selecting the percentage values click “Conditional Formatting” from the ribbon.

• Go to “More Rules” from the “Data Bars”.

• A new window will pop up named “New Formatting Rule”.
• In “Select a Rule Type” click “Format all cells based on their values”.
• Change the type to “Number” in both the “Minimum” and “Maximum” sections.
• After that, type the numeric value “0” in the “minimum” part and type “1” in the “maximum” part.
• Now choose a color according to your choice and press OK to continue.

• As you can see here we have created our progress bar in Excel.

3. Run a VBA Code to Create a Progress Bar

You can also run a VBA code to create a progress bar in Excel.

Steps:

• Select cells (F5:F11) to apply the code over those chosen cells.
• Press Alt+F11 to open the “Microsoft Visual Basic for Applications”.

• Create a new module from the insert option.

• In the module apply the following code-

Sub Progress_Bar()
Range("F5:F11").Select
Selection.FormatConditions.AddDatabar
Selection.FormatConditions(Selection.FormatConditions.Count).ShowValue = True
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1)
.MinPoint.Modify newtype:=xlConditionValueNumber, newvalue:=0
.MaxPoint.Modify newtype:=xlConditionValueNumber, newvalue:=1
End With
With Selection.FormatConditions(1).BarColor
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0
End With
Selection.FormatConditions(1).BarFillType = xlDataBarFillSolid
Selection.FormatConditions(1).Direction = xlContext
Selection.FormatConditions(1).NegativeBarFormat.ColorType = xlDataBarColor
Selection.FormatConditions(1).BarBorder.Type = xlDataBarBorderNone
Selection.FormatConditions(1).AxisPosition = xlDataBarAxisAutomatic
With Selection.FormatConditions(1).AxisColor
.Color = 0
.TintAndShade = 0
End With
With Selection.FormatConditions(1).NegativeBarFormat.Color
.Color = 255
.TintAndShade = 0
End With
End Sub

• Press “Run”.

• There we have our precious result creation of a progress bar in an Excel workbook.

Things to Remember

• In method 2, you can use different types of bar charts from the “New Formatting Rule” window. Just open the drop-down list of “Format Style” to make different types of formats inside a cell.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

In this article, I have tried to cover all the simple methods to create a progress bar in Excel. Take a tour of the practice workbook and download the file to practice by yourself. Hope you find it useful. Please inform us in the comment section about your experience.

Stay tuned and keep learning.

Related Articles

• How to Create Progress Bar Based on Another Cell in Excel
• How to Show Percentage Progress Bar in Excel

<< Go Back to Progress Bar in Excel | Data Visualisation in Excel | Learn Excel