Dataset Overview
Let’s use the following dataset:
Method 1 – Using a Bar Chart
You can show the percentage progress bar by inserting a Bar Chart in Excel. Follow the steps below to do that.
- Open your Excel workbook and locating the dataset where you want to display the progress bar.
- In cell D5 (or any other cell within the data range), enter the following formula:
=B5/C5
This formula calculates the percentage completion based on the values in cells B5 (completed tasks) and C5 (total tasks). Adjust the cell references according to your dataset.
- Drag the Fill Handle icon (a small square at the bottom-right corner of the cell) down to copy the formula to the cells below (D6, D7, etc.). This will calculate the percentage for each row in your dataset.
- Select any cell within the data range.
- Go to the Insert tab in the Excel ribbon.
- Choose Column or Bar Chart from the Charts group.
- Select the 100% Stacked Bar chart type from the 2-D Bar options.
The 100% Stacked Bar chart will be inserted into your Excel workbook. This chart will visually represent the progress percentage for different tasks or teams.
- The 100% Stacked Bar chart will display the percentage completion for each data point.
- Customize the chart by adjusting labels, colors, and other formatting options to make it visually appealing.
- You can also add a title and axis labels to provide context.
- Your percentage progress bar chart will now show the progress of different tasks or teams.
Remember to adapt these steps to your specific dataset and preferences. With this method, you’ll have an informative progress bar that visually represents your data in Excel!
Method 2 – Using Data Bars
- Highlight the entire range (C5:C10) that contains the progress percentages.
- Go to the Home tab in Excel.
- Click on Conditional Formatting and choose Data Bars.
- Select your desired fill color (either from Gradient Fill or Solid Fill options).
- Make sure the text within the cells is left-aligned.
- This will display the data bars alongside the percentage values.
Method 3 – Using the Excel REPT Function
- Click on cell D5 where you want to display the progress bar.
- Change the font to Playbill from the Home tab.
- Choose top and left alignments for the cell.
- Select a fill color for the cell (you can use the same color as the data bars).
- In cell D5, enter the following formula:
=REPT("|",C5*100)
This formula repeats the “|” character based on the percentage value in cell C5.
- Drag the Fill Handle icon down to copy the formula to other cells (D6, D7, etc.).
- You’ll see a vertical progress bar made of “|” characters, representing the progress percentage.
Things to Remember
- Customize the 100% Stacked Bar chart (from Method 1) as needed.
- You can also apply data bars directly to cells using conditional formatting.
Download Practice Workbook
You can download the practice workbook from here:
Related Articles
- How to Create Progress Bar Based on Another Cell in Excel
- Progress Bar in Excel Cells Using Conditional Formatting
<< Go Back to Data Visualisation in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!