Conditional Formatting with Data Bars Based on Another Cell in Excel

Get FREE Advanced Excel Exercises with Solutions!

In Microsoft Excel, there is a feature named Conditional Formatting that allows you to apply formatting to cells based on certain criteria. We can use Conditional Formatting with data bars based on the value of another cell. In this article, I am going to explain the whole procedure of how to use Conditional Formatting data bars based on another cell in Excel.


How to Use Conditional Formatting with Data Bars Based on Another Cell in Excel: Step-by-Step Procedures

We can divide the whole process of how to use Conditional Formatting data bars based on another cell in Excel into three different steps.

Step 1: Create an Organised Dataset

We often use the Conditional Formatting feature based on a certain criterion. For this, we need an organized dataset where we can apply a certain condition. I have the information on a company’s sales. I want to visualize their performance with data bars based on another cell.

Excel Conditional Formatting Data Bars Based on Another Cell

Steps:

  • Select a cell and apply the following formula to improve the work efficiency of employees.
=D5/C5

Create an Organised Dataset

  • Press ENTER to have the output.

  • To convert it into a percentage, click on Percentage Sign from the ribbon under the Home tab.

Excel Conditional Formatting Data Bars Based on Another Cell

Thus, we will have the value in the percentage form.

  • Afterward, use Fill Handle to AutoFill the rest cells.

Excel Conditional Formatting Data Bars Based on Another Cell


Step 2: Use Formula to Link a Cell with Another One

Linking a cell to another cell is also a part of this process. To accomplish it, follow the following steps.

Steps:

  • Pick a cell (i.e. F5) and input the following formula there.
=E5

Use Formula to Link a Cell with Another One

  • Followingly, press ENTER.

  • AutoFill the remaining cells.

Excel Conditional Formatting Data Bars Based on Another Cell


Step 3: Apply Conditional Formatting with Data Bars

After having a complete dataset, we can now apply a condition and display the results with data bars through the Conditional Formatting feature.

Steps:

  • Select the related cells first (i.e. F5:F15) and go to Home.
  • Next, click on Conditional Formatting from the ribbon and choose Data Bars.
  • After that, pick a color from the Solid Fill group.

Apply Conditional Formatting with Data Bars

  • Now, we will have the data bar with efficiency values.

  • You can also show their performance value with only data bars. For this-
  • Click on Manage Rules… from the Conditional Formatting option.

Excel Conditional Formatting Data Bars Based on Another Cell

  • Then, click on Edit Rule.

  • Followingly, check the Show Bar Only option.
  • Then, press OK.

  • Press OK from the Conditional Formatting Rules Manager wizard.

Excel Conditional Formatting Data Bars Based on Another Cell

  • Thus, we will have Conditional Formatting data bars based on another cell in Excel.


Practice Section

For more expertise, you can practice here.

Practice Section


Download Practice Workbook


Conclusion

At the end of this article, I like to add that I have tried to explain the whole procedure of how to use Conditional Formatting data bars based on another cell in Excel. It will be a matter of great pleasure for me if this article could help any Excel user even a little. For any further queries, comment below. You can visit our site for more articles about using Excel.


Related Articles

<< Go Back to Data Bars | Conditional Formatting | 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.
Naimul Hasan Arif
Naimul Hasan Arif

Naimul Hasan Arif, a BUET graduate in Naval Architecture and Marine Engineering, has been contributing to the ExcelDemy project for nearly two years. Currently serving as an Excel and VBA Content Developer, Arif has written more than 120 articles and has also provided user support through comments His expertise lies in Microsoft Office Suite, VBA and he thrives on learning new aspects of data analysis. Arif's dedication to the ExcelDemy project is reflected in his consistent contributions and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo