How to Create Progress Bars in Excel (Step-by-Step)


This step-by-step tutorial explains how to create the following progress bars in Excel:

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

5 Replies to “How to Create Progress Bars in Excel (Step-by-Step)”

  1. 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!

  2. thank you. Could you do a follow up article on how to change the colour to red if the percentage exceeds 100?

    1. 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%.

  3. thank you. Could you do a follow up article on how to change the colour to red if the percentage exceeds 100?

Leave a Reply

Your email address will not be published. Required fields are marked *