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.
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.
- 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.
- Choose a “Clustered Bar” from the “2-D Bar”.
- As you can see a chart will be created plotting all the sales year-wise.
- 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.
- Progress Circle Chart in Excel as Never Seen Before
- How to Make a Progress Monitoring Chart in Excel (with Easy Steps)
- Excel To Do List with Progress Tracker (4 Suitable Examples)
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.
- Firstly, let’s calculate the achievement percentage by dividing the actual sales by the forecasted sales.
- Now selecting the percentages 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.
- 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.
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.