All about conditional formatting based on another cell

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 Excel sheets

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 number in another cell

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.

Working with text

Conditional formatting also allows you to work with text values. Let’s take a look at an example. Imagine that you need to format a certain column or several columns when another cell in the same row contains a certain phrase or word. In this case, you can use either the SEARCH formula, which is case insensitive, or the FIND formula, which is case sensitive. These are great for partial matches.

For example, you need to find the word “license” to find out how many licenses of different types (unlimited, 3-year, 1-year, etc.) have been ordered by your customers. To format the selected cells or rows if a corresponding cell in column C contains the word “license”, use the following formula:

=SEARCH("license", $C2)>0

For this, select the required cells, find Conditional formatting on the Home tab, choose Formula, enter the formula above in the corresponding field, select one of the presets and click OK. Please note that this formula will find all such cells, and it doesn’t matter where the specified text is located in a cell.

Now you can see that all the cells that contain the word “license” are highlighted, so you can easily count the total number of various licenses to be purchased.

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.