Excel conditional formatting data bars based on another cell

1. 03-08-2019, 04:23 PM #1

   Table of Contents Show

   • What is conditional formatting?
   • About conditional formatting based on another cell
   • Conditional formatting based on another cell in Excel sheets
   • Step 1. Select the data cells
   • Step 2. Set the new formatting rule
   • How to delete a conditional formatting rule in Excel sheet

   im trying to use the data in the second column to show progress on close out of the issues in the first column; im having trouble 1) pointing to the number in the other cell to use as the data value and 2)each data bars' maximum could be unique will be different based on the reported amount, is there a way to do this using formulas?

   Capture.PNG

2. 03-08-2019, 06:43 PM #2

   If I'm understanding you correctly you want data bars to graphically show the progress of issue closure. Correct? I chose to add a 3rd column that shows % complete for each row then added the formula =B2/C2 to C2 and copied down. To apply the data bars, first select the cells of interest, in this case C2:C4 then Conditional formatting > Data bars > More Rules. You should see something like the following:

   databars2.png

   I changed Minimum Type to Number and a Value of 0 then Maximum Type to Number and a Value of 1. Those are the only changes I made from the defaults. You should now have something like:

   databars1.png

   Is this along the lines of what you were looking for?

3. 03-08-2019, 11:00 PM #3

   Crafty solution, thanks! Yea I’m thinking this is the way, I was trying to show the data bar in the same cell that has the total issues but that seems superfluous after seeing your solution, cheers!

   

   Originally Posted by GeoffW283

   

   If I'm understanding you correctly you want data bars to graphically show the progress of issue closure. Correct? I chose to add a 3rd column that shows % complete for each row then added the formula =B2/C2 to C2 and copied down. To apply the data bars, first select the cells of interest, in this case C2:C4 then Conditional formatting > Data bars > More Rules. You should see something like the following:

   Attachment 614734

   I changed Minimum Type to Number and a Value of 0 then Maximum Type to Number and a Value of 1. Those are the only changes I made from the defaults. You should now have something like:

   Attachment 614733

   Is this along the lines of what you were looking for?

4. 03-09-2019, 12:49 AM #4

   Glad to help - thanks for the feedback.

   If you’re all set then can you please mark the thread as solved. To do this select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks!

5. 09-09-2019, 06:07 PM #5

   GeoffW283; how can we do the same but without the need to introduce Column C. Suppose Column A says Issues Closed, B says Total issues Logged, and I want B to show % complete i.e. A/B bar to display in column B itself?

I came up with one way that might do the trick. In one of your parameter descriptions, replace the description with the value you want. 10, for example, or even a formula to the other cell, =C10. Now select the cell and go to number format --> More number formats --> custom. In the Type: box, put [=10]"Weight" and click OK. Now the value of the cell is 10, but it displays Weight. Repeat for each parameter cell. You'll need a different format for each cell. Finally, select all the parameter cells, go to Conditional Formatting and select the Data Bar option you want for that range and click OK. You should see the Data Bar underneath the description in the same cell.

Let me know how it works.

The custom formatting has a LOT of options, more than I can keep track of. Do a Google search on Excel custom formatting to find whole websites devoted to it. You pretty much just have to ignore most of it until you need something special, then look just for that. In this case, the [=10] part refers to a format that is applied only if the equation is true, in this case only if the cell value = 10. Anything in quotes is presented as part of the format. So this is kind of a roundabout way for the cell to equal 1 value, but say something else. The parameter name that Excel added is another option that we don't need in this case, but Excel wants to remind us of.

I'm glad it helped.

Do you want to highlight some data in your Excel spreadsheets or differentiate some cells from others depending on the information shown in each one of them? Then conditional formatting is what you definitely need. In this article, you will know what Excel conditional formatting based on another cell is and how to apply it correctly to achieve your goals.

What is conditional formatting?

Conditional formatting is one of the most widely used tools in spreadsheets because it allows users to apply a certain format to the cells according to the conditions they meet. This way you can easily see if the value of a cell meets a series of predetermined requirements beforehand, as the chosen pattern will automatically be applied to these cells.

With this feature, you can identify trends or incorrect data at a glance. This tool also provides an opportunity to highlight certain cells, which makes it easier to work with large amounts of data.

Here is what you can do using this feature:

• Display values. All data in a range of cells can be highlighted according to the correlation between them. In this scenario, which is frequently used in Excel, the appearance of the cells is based on the size of the values. If they don’t correspond to the established range, they will be highlighted in colour.
• Filter values. You can create a list of unique values by hiding the duplicate ones that can be deleted later if necessary.
• Check data. Conditional formatting is ideal if you want to check that the data in your spreadsheet is correct. It allows you to establish certain conditions so that only the cells you want to find are highlighted.

This tool is widely used in finance, sales, banking, research, accounting and other fields of work.

Watch this short video guide on how you can process numerical and textual data in ONLYOFFICE Docs:

Here you can see how to highlight cells with colours, add icons and data bars:

About conditional formatting based on another cell

In the case of a huge database, it might be necessary to format some cells based on other cells or values. For this purpose, you can use conditional formatting to check the value in a cell and apply its formatting to other cells, based on the value of that cell. For example, if the values in a column are greater or less than the required parameter, all data cells in the same row will be marked with a certain colour.

This is what is called conditional formatting based on another cell. Using this feature, you can establish different rules for your Excel sheets.

Now let’s find out how to correctly apply Excel conditional formatting based on another cell using some examples from real life.

Conditional formatting based on another cell in Excel sheets

Imagine running a computer shop that sells hardware and software. You have a lot of orders from your customers and want to find out how many big orders you have to complete. A big order is worth $1,000 or more.

For this purpose, you need spreadsheet editing software that is compatible with this feature. Let’s see how to apply conditional formatting based on another cell in Excel sheets using ONLYOFFICE Docs as an example.

The table below shows data about the customers and what they order. Column H shows the sum of each order. All the cells are highlighted if their values are greater or equal to the amount of a big order shown in cell J2.

To create such a basic rule, follow these simple steps in ONLYOFFICE Docs.

Step 1. Select the data cells

To create a rule, you need to select the data cells in the required range (H2:H23 in our example). Then you need to open Conditional formatting by clicking the corresponding icon on the Home tab. Then choose Value is and Greater than or equal to. Depending on your needs, you can choose other parameters from the list.

Alternatively, you can select the data cells in the required range and access Conditional formatting via the context menu.

Step 2. Set the new formatting rule

Step 1 allows you to open the New Formatting Rule window. Here you can choose the required highlighting criteria and parameters.

Click Select data to choose the value in the comparison cell. You can either enter =$J$2 in the input box or chose J2 manually with the mouse. Click OK.

After that, you can choose one of the ready-to-use presets or create a unique pattern by adjusting the font, text colour and border settings. The Preview option lets you see what your formatting pattern will look like. When everything is ready, click OK.

That is it. Now all the orders worth $1,000 or more are highlighted, and you can easily count them.

How to delete a conditional formatting rule in Excel sheet

With ONLYOFFICE Docs, you can create several rules and edit them at any moment. For example, if you don’t need conditional formatting anymore and want to delete the existing rule, follow these steps:

• Open Conditional formatting on the Home tab or use the context menu;
• Choose Manage rules at the bottom of the list;
• Click Show formatting rules for and select This worksheet;
• Delete the required rule by clicking the corresponding button;
• Click OK.

Alternatively, you can clear all conditional formatting from your Excel workbook using the Clear Rules option. To access it, click Conditional formatting on the Home tab and choose what you need:

• Current selection;
• This worksheet;
• This table;
• This pivot.

ONLYOFFICE Docs comes with a powerful spreadsheet editor that allows you to apply various formatting styles (colour, font, borders), highlight and sort data according to the required criteria as well as create your own formatting rules for different occasions. Try ONLYOFFICE Docs now and make use of Excel conditional formatting based on another cell now:

ON-PREMISES IN THE CLOUD

Conclusion

As you can see, conditional formatting based on another cell in Excel sheets is not as hard to master as it might sound. All the basic principles are quite simple, and a bit of practice will help you specify your own conditions and apply them to certain ranges with ease.