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.


Download Practice Workbook

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


3 Easy Methods to Create a Progress Bar in Excel

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.

Create a Progress Bar in Excel


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 excel sheet.

Step 1:

  • Select data from your data table with the heading which 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.

Insert Bar Chart to Create a Progress Bar in Excel

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

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

Insert Bar Chart to Create a Progress Bar in Excel


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

Insert Bar Chart to Create a Progress Bar in Excel

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

Insert Bar Chart to Create a Progress Bar in Excel

Read More: How to Make Progress Chart in Excel (2 Simple Methods)


Similar Readings


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 a cell using the conditional formatting of excel. 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.

Use Conditional Formatting to Create a Progress Bar in Excel

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

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

Use Conditional Formatting to Create a Progress Bar in Excel

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

Use Conditional Formatting to Create a Progress Bar in Excel

Read More: How to Create Progress Bar Based on Another Cell in Excel (2 Easy Ways)


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

VBA Code to Create a Progress Bar in Excel

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

VBA Code to Create a Progress Bar in Excel

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

VBA Code to Create a Progress Bar in Excel


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.

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. We, the Exceldemy team, are always responsive to your queries. Stay tuned and keep learning.


Related Articles

Wasim Akram

Wasim Akram

Hi! my name is Wasim Akram. I am a graduate in mechanical engineering from Ahsanullah University of Science & Technology. I am passionate about learning new things and writing articles. I write article about Microsoft Excel and it's my favourite thing to do.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo