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

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

Method 1 – Insert a Bar Chart to Create a Progress Bar

Steps:

• Select data from your data table with the heading that you want to plot in the progress bar chart. We have selected cells (C4:E11).
• Go to the Charts list from the Insert option.

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

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

• To edit the chart, select bars from the diagram and right-click on them to show options.
• Choose Format Data Series.

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

• Remove unnecessary data and you will get a progress bar.

Method 2 – Use Conditional Formatting to Create a Progress Bar

Steps:

• Calculate the achievement percentage by dividing the actual sales with the forecasted sales.

• Select the column with the percentage values.
• Click on 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, select Format all cells based on their values.
• Change the type to Number in both the Minimum and Maximum sections.
• Type 0 in the minimum part and 1 in the maximum part.
• Choose a color for the progress bar and press OK to continue.

• This creates a small progress bar for every row in Excel.

Method 3 – Use VBA Code to Create a Progress Bar

Steps:

• Create a column and calculate the completion percentages in it (see Method 2).
• Select cells (F5:F11) to apply the code.
• Press Alt + F11 to open the Microsoft Visual Basic for Applications window.

• 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.

• This automatically creates progress bars in new cells.

Things to Remember

• In Method 2, you can use different types of bar charts from the New Formatting Rule window. 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 experiment with progress bars while you are reading this article.

Related Articles

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

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