This step-by-step tutorial explains how to create the following progress bars in Excel:
Step 1: Enter the Data
First, let’s enter some data that shows the progress percentage for 10 different tasks:
Step 2: Add the Progress Bars
Next, highlight the cell range B2:B11 that contains the progress percentages, then click the Conditional Formatting icon on the Home tab, then click Data Bars, then click More Rules:
A new window appears that allows you to format the data bars.
For Minimum, change the Type to Number and the Value to 0.
For Maximum, change the Type to Number and the Value to 1.
Then choose any color you’d like for the bars. We’ll choose light green:
Once you click OK, a progress bar will appear in each cell in column B:
Step 3: Format the Progress Bars
Lastly, we can stretch out the column width and row width in our Excel spreadsheet so that the progress bars become larger and easier to read.
We can also add a border around the cells and align the text for the percentages to be on the left side of the cells:
Note that if you update any of the percentages, the length of the progress bar will automatically change.
For example, suppose we change the last progress percentage to 22%:
Notice that the progress bar automatically shortened to reflect this new percentage.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Create a Gantt Chart in Excel
How to Create a Timeline in Excel
How to Create a Double Doughnut Chart in Excel
How to Create a Statistical Process Control Chart in Excel
for example if value less than 30% and the bar color change to red so how i will do that?
What an article, I was looking for a tutorial and this page was my solution. Thank you for your time, it is very well done explained, I even learned how to use different bars. Amazing!
thank you. Could you do a follow up article on how to change the colour to red if the percentage exceeds 100?
Hi SadieMi…You are very welcome! To create progress bars in Excel and change the color to red if the percentage exceeds 100%, you can use Conditional Formatting with Data Bars. Here’s a step-by-step guide:
### Steps to Create and Customize Progress Bars:
1. **Select the Range:**
– Select the range of cells where you want to display the progress bars. For example, if your percentages are in cells B2:B10, select this range.
2. **Apply Data Bars:**
– Go to the `Home` tab on the Ribbon.
– Click on `Conditional Formatting`.
– Choose `Data Bars` and select a style you like.
3. **Edit the Data Bar Rule:**
– With the range still selected, go back to `Conditional Formatting`.
– Select `Manage Rules`.
– In the `Conditional Formatting Rules Manager` dialog, select the rule for the data bars and click `Edit Rule`.
4. **Customize the Data Bar:**
– In the `Edit Formatting Rule` dialog, you can set the minimum and maximum values. Set the minimum to `0` and the maximum to `100%`.
– Check the option `Show Bar Only` if you don’t want to display the actual percentage values in the cells.
5. **Add a New Rule for Color Change:**
– In the `Conditional Formatting Rules Manager`, click `New Rule`.
– Select `Use a formula to determine which cells to format`.
– Enter the formula to identify percentages greater than 100%. For example, if your percentages are in column B starting from row 2:
“`excel
=B2>1
“`
6. **Set the Format:**
– Click `Format` and go to the `Fill` tab.
– Choose `Red` as the fill color.
– Click `OK` to close the `Format Cells` dialog.
– Click `OK` again to close the `New Formatting Rule` dialog.
7. **Apply and Confirm:**
– Make sure your new rule is listed above the Data Bar rule in the `Conditional Formatting Rules Manager`.
– Click `OK` to apply the changes.
### Example:
Here’s an example of how you would apply these steps to a range of cells:
1. **Data Range:** Assume your percentage values are in cells B2:B10.
2. **Apply Data Bars:**
– Select cells B2:B10.
– Go to `Home` > `Conditional Formatting` > `Data Bars` > `Solid Fill`.
3. **Edit Data Bar Rule:**
– Go to `Home` > `Conditional Formatting` > `Manage Rules`.
– Select the rule for the data bars and click `Edit Rule`.
– Set the minimum to `0` and the maximum to `100%`.
4. **Add Conditional Formatting Rule:**
– While still in the `Conditional Formatting Rules Manager`, click `New Rule`.
– Choose `Use a formula to determine which cells to format`.
– Enter the formula `=B2>1`.
– Click `Format`, go to the `Fill` tab, and choose `Red`.
– Click `OK` twice to apply the new rule.
5. **Adjust Rule Order:**
– Ensure the new rule for red fill is above the Data Bar rule.
By following these steps, you will have progress bars that turn red if the percentage exceeds 100%.
thank you. Could you do a follow up article on how to change the colour to red if the percentage exceeds 100?
Okay, simple explanation here, I appreciate it. However, now when starting a new chart, if everyone is only 25% complete, the entire bar is filled reflecting like its 100% complete. Without creating and hiding line (checking all 4 boxes) how can I reflect the progress bar only at 25% if everyone is only at 25%?
Great question! It sounds like you’re trying to create a **progress bar in Excel** that visually shows **how far along someone is**, like 25%, **without making it look like they’re done (100%)**. You’re also trying to avoid the workaround that involves **creating and hiding background lines**.
Here’s a **simple and clean method using a stacked bar chart** that shows a true visual of progress — no hiding series or messing with chart options:
—
### ✅ **Step-by-Step: Create a Real Progress Bar (e.g., 25% filled)**
#### **1. Prepare Your Data**
In your worksheet, organize the data like this:
| Name | Progress (%) | Remaining (%) |
|———–|————–|—————-|
| Alice | 25% | =1-B2 |
| Bob | 25% | =1-B3 |
| Charlie | 25% | =1-B4 |
This gives you two columns: one for **actual progress** and one for the **remaining part**.
—
#### **2. Insert a Stacked Bar Chart**
– Select the data **from columns B and C** (Progress and Remaining).
– Go to **Insert → Bar Chart → Stacked Bar**.
—
#### **3. Format the Chart to Look Like a Progress Bar**
– You’ll see a bar that is split into two segments: Progress (colored) and Remaining (usually a different color).
– **Click on the “Remaining” part of the bar**, and format it to a **light gray** or **no fill** to make it look like a background.
– **Click on the “Progress” bar**, and give it a **solid fill color** (like green or blue) to represent the percentage complete.
—
#### ✅ Optional:
– Remove the legend.
– Remove the chart title if not needed.
– Set data labels if you want to show actual percentages.
—
### 🎉 Result:
You’ll now have a **true progress bar** where each bar only fills up to **25%**, visually accurate, **no need to hide anything**, and it updates dynamically when progress changes!