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

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will learn to create a progress bar based on another cell in Excel. Generally, you can’t generate a progress bar based on another cell in Excel. The progress bar appears on the cell that contains the value. But, today we will demonstrate 2 easy methods. Using these methods, you can easily create progress bars based on another cell. So, without any delay, let’s start the discussion.


How to Create Progress Bar Based on Another Cell in Excel: 2 Easy Ways

To explain the methods, we will use a dataset that contains information about some Tasks and their Completion status. In the first method, we will use Excel Bar Chart and in the second method, we will use Conditional Formatting. We will try to show the progress of each task in the progress bar.


1. Use Excel Bar Chart to Create Progress Bar Based on Another Cell

Microsoft Excel provides us with different kinds of bar charts for various purposes. We can use the 2D Stacked Bar Chart to show the progress bars of different tasks in our Excel sheet. To make the explanation easier, we have removed the Status column and also, named Column D as Remaining %. We will find out the reasons behind these changes in the following steps. So, without further ado, let’s follow the steps below.

Use Excel Bar Chart to Create Progress Bar Based on Another Cell

STEPS:

  • First of all, select Cell D5 and type the formula below:
=1-C5

Use Excel Bar Chart to Create Progress Bar Based on Another Cell

  • Press Enter to see the result.
  • Secondly, drag the Fill Handle down.

Use Excel Bar Chart to Create Progress Bar Based on Another Cell

  • You will see results like the picture below.

Use Excel Bar Chart to Create Progress Bar Based on Another Cell

  • Thirdly, select the range B5:D10 in the dataset.
  • You need to select the Task Name, Completion %, and Remaining %.

Use Excel Bar Chart to Create Progress Bar Based on Another Cell

  • After that, go to the Insert tab and select Bar Chart from the Charts section. A drop-down menu will appear.
  • Select Stacked Bar from the 2-D Bar section.

Use Excel Bar Chart to Create Progress Bar Based on Another Cell

  • As a result, a 2-D Bar Chart will appear on the Excel sheet.

Use Excel Bar Chart to Create Progress Bar Based on Another Cell

  • In the following step, click on the orange part of a bar like the picture below.

Use Excel Bar Chart to Create Progress Bar Based on Another Cell

  • Then, right-click on the orange part. A drop-down menu will appear.
  • Select Format Data Series from there.
  • It will open the Format Data Series settings on the right side of the sheet.

Use Excel Bar Chart to Create Progress Bar Based on Another Cell

  • Now, click on the Fill icon and fill the bar with a Solid White Color.

Use Excel Bar Chart to Create Progress Bar Based on Another Cell

  • After changing the color of the orange part to white, the progress bars will be like the one below.
  • At this moment, click on the plus (+) icon and deselect Chart Title, Gridlines, and Legend.

Use Excel Bar Chart to Create Progress Bar Based on Another Cell

  • As a result, the progress bars will look more understandable.

Use Excel Bar Chart to Create Progress Bar Based on Another Cell

  • To test the progress bars, we have changed the value of Cell C5 to 80%.

Use Excel Bar Chart to Create Progress Bar Based on Another Cell

  • Finally, the progress bar of Task 1 will change like the picture below.

Use Excel Bar Chart to Create Progress Bar Based on Another Cell


2. Make Progress Bar Based on Another Cell with Conditional Formatting

In Excel, we use Conditional Formatting according to your needs easily. Today, we will show how we can use conditional formatting to make a progress bar based on another cell. In this case, we will use our raw dataset. The steps of this method are very easy but they can be repetitive.

Make Progress Bar Based on Another Cell with Conditional Formatting

Let’s observe the steps below to learn more about this method.

STEPS:

  • In the first place, select Cell E5 and type the formula below:
=1-D5

Make Progress Bar Based on Another Cell with Conditional Formatting

Make Progress Bar Based on Another Cell with Conditional Formatting

  • Secondly, select Cell E5 to E10.

Make Progress Bar Based on Another Cell with Conditional Formatting

  • Thirdly, navigate to the Home tab and select Conditional Formatting. A drop-down menu will appear.
  • Select New Rule from there. It will open the New Formatting Rule window.

Make Progress Bar Based on Another Cell with Conditional Formatting

  • After that, select Use a formula to determine which cells to format and type the formula below:
=D5<0.5
  • Then, click on Format to open the Format Cells window.

Make Progress Bar Based on Another Cell with Conditional Formatting

Here, the formula denotes if the value of Cell D5 is less than 50%, then, it will apply the formatting.

  • In the Format Cells window, select Fill and choose a background color according to your choice.
  • We have selected the Red color.
  • Click OK to proceed.

Make Progress Bar Based on Another Cell with Conditional Formatting

  • Again, select Cell E5 to E10.
  • Then, go to the Home tab, select Conditional Formatting, and then, select New Rule.

Make Progress Bar Based on Another Cell with Conditional Formatting

  • This time, select Use a formula to determine which cells to format but type the formula below:
=AND(D5>=0.5,D5<=0.70)
  • Then, click on Format.

Make Progress Bar Based on Another Cell with Conditional Formatting

This formula indicates if the value of Cell D5 is between 50 % to 70 %, then, it will apply the formatting.

  • Also, select Fill and choose Yellow as a background color in this case.
  • Click OK to proceed.

  • Once again, select Cell E5 to E10 and go to the Home tab, select Conditional Formatting.
  • And then, select the New Rule.

  • Now, select Use a formula to determine which cells to format but type the formula below:
=D5>0.70
  • Then, click on Format.

In this case, the formula represents if the value of Cell D5 is greater than 70 %, then, it will apply the formatting.

  • At this moment, select the Green color in the Fill tab.
  • Select OK to proceed.

  • After applying the 3 conditions, the dataset will look like the picture below.

  • One last time, go to the Home tab and select Conditional Formatting.
  • Select New Rule from the drop-down menu.

  • Select Format cells based on their values in the Formatting Rule window.
  • After that, choose Data Bar and check Show Bar Only in the Format cells based on their values section.
  • Then, select Number and type 0 in the Minimum box.
  • Also, select Number and type 1 in the Maximum box.
  • Select Solid Fill and white color in the Bar Appearance settings.
  • Most importantly, select Right-to-Left in the Bar Direction.

  • Finally, click OK to see results like the picture below.

  • Now, if you change the Value of Cell D5 to 85 %, the progress bar will also change according to the conditions.


Download Practice Book

You can download the practice book from here.


Conclusion

In this article, we have discussed 2 easy methods to create a Progress Bar Based on Another Cell in Excel. I hope this article will help you to perform your tasks easily. Moreover, using Method-2 you can create progress bars according to your needs. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.


Related Articles


<< Go Back to Progress Bar in Excel | Data Visualisation in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mursalin Ibne Salehin
Mursalin Ibne Salehin

Mursalin Ibne Salehin holds a BSc in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. Over the past 2 years, he has actively contributed to the ExcelDemy project, where he authored over 150 articles. He has also led a team with content development works. Currently, he is working as a Reviewer in the ExcelDemy Project. He likes using and learning about Microsoft Office, especially Excel. He is interested in data analysis with Excel, machine learning,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo