Highlighting lines with color through one. How to make Excel cell color change based on value

In order to correctly place accents in a document made in MS Excel, you can increase the size of the cells, select a different font or letter size, or you can paint over the squares that you definitely need to pay attention to.

Now we will figure out how to highlight cells with color in Excel, or change the color of those that are already painted over. How to make a cell change color according to a given condition, depending on the value entered into it, and how to work with the created rules.

Simple block fill

Coloring one or more blocks in Excel is not difficult. First, select them and on the Home tab, click on the arrow next to the paint bucket to expand the list. Choose a suitable color from there, and if nothing suits, click "Other colors".

By the way, in this way you can fill in the whole line, just click on its number first to select it. You can read a separate article about selecting cells in Excel.

If you are working with a table in which something is already painted over, then you can change the color of the blocks, or even remove it altogether. Click on it and from the colors, or select a new one, or click on the option "None".

Depending on the entered data

Now let's look at how to make a cell change color based on a given condition. Conditional formatting is used for this, about which there is a separate article on the site.

Text

Let's take the following table as an example. Let's make it so that the red corresponds to the apple, yellow to the pear, and orange to the orange.

We select the data with which we will work, in my case, these are the names of fruits. Then we press "Conditional Formatting", which we will continue to use in the future. From the list, click on "Create a Rule".

This window opens. Select the type at the top "Format only cells that contain", further we will also celebrate it. Below we indicate the conditions: we have a text that contains certain words. In the last field, either click on the button and specify a cell, or enter text.

The difference is that by putting a reference to a cell (=$B$4 ), the condition will change depending on what is typed in it. For example, instead of an apple in B4, I will indicate a currant, the rule will change accordingly, and blocks with the same text will be painted over. And if you enter an apple in the field, then this particular word will be searched for, and it will not depend on anything.

Here, select a fill color and click OK. To view all options, click on the "Other" button.

The rule has been created and save it by pressing the OK button.

As a result, all blocks that contained the specified text were painted red.

Rules are also created for other fruits, only a different filling option is selected.

Numeric

Now let's deal with numerical data. Let's place the numbers in column D on a certain background according to the condition that we set.

Select a column, create a rule, specify its type. Next, we write - “Value” “greater than” “15”. You can either enter the last number manually, or specify the address of the cell where the data will be taken from. We decide on the fill, click "OK".

Blocks where numbers greater than the selected one are entered are painted over.

Let's specify more rules for the selected cells - select "Rules Management".

Here, choose everything, as I described above, you only need to change the color and set the condition "less or equal".

When everything is ready, click "Apply" and "OK".

Everything works, values ​​equal to and below 15 are painted over in pale blue.

Here the difference between imported and sold goods would also be clearly visible. Select two columns and click "Cell Selection Rules" – "Duplicate Values". Choose the right color. After that, if two cells next to each other are colored, it means that the fruits that were brought in have all been sold.

Let's use the example of the last column to show how to make the cell change color depending on the specified value. Select them and go to "Rules Management".

We create a new one for the current fragment, if necessary, in this drop-down list, you can select either for the entire sheet or for other sheets.

Select the desired items in the window that opens. I will fill with dark green all values ​​that are greater than 90. Since I specified the address in the last field (=$F$15 ), when the number 90 in the cell changes, for example, to 110, the rule will also change. Save the changes by clicking on the OK button.

I will create another rule, but in it I will highlight in light green everything that is less than or equal to 90. Do not forget to save everything by clicking on the buttons at the bottom right.

Here's what happened. From the last column, you can visually quickly determine the profit from the sale of which product was greater.

For example, if the price changes, the rules will also be revised. If the value becomes greater or less than the specified value, then the color of the cell will automatically change.

I think you noticed that the rule is created not only for text and numbers, but also for dates, and depending on the filling and the presence of errors in the cells. Specify a condition, choose a color and save.

To see what you added, select the range and in the window "Rules Management" there will be a complete list. Using the buttons at the top, you can add, change or delete them.

When working with tables, the values ​​displayed in it are of primary importance. But an important component is also its design. Some users consider this a minor factor and do not pay much attention to it. But in vain, because a beautifully designed table is an important condition for its better perception and understanding by users. Data visualization plays a particularly important role in this. For example, you can use visualization tools to color table cells based on their content. Let's find out how this can be done in Excel.

Of course, it's always nice to have a well-designed table in which cells are colored in different colors depending on the content. But this feature is especially relevant for large tables containing a significant amount of data. In this case, filling the cells with color will make it much easier for users to navigate through this huge amount of information, since it can be said to be already structured.

You can try to colorize the elements of the sheet manually, but again, if the table is large, then this will take a significant amount of time. In addition, in such an array of data, the human factor can play its role and mistakes will be made. Not to mention the fact that the table can be dynamic and the data in it changes periodically, and massively. In this case, manually changing the color generally becomes unrealistic.

But there is a way out. For cells that contain dynamic (changing) values, conditional formatting is applied, and for statistical data, you can use the tool "Find and Replace".

Method 1: Conditional Formatting

Using conditional formatting, you can set certain boundaries of values ​​at which cells will be painted in one color or another. Coloring will be carried out automatically. If the value of the cell, as a result of the change, goes beyond the border, then this element of the sheet will be automatically repainted.

Let's see how this method works on a specific example. We have a table of enterprise income, in which the data is broken down by month. We need to highlight in different colors those elements in which the amount of income is less than 400000 rubles, from 400000 before 500000 rubles and more 500000 rubles.

1. We select the column in which the information on the income of the enterprise is located. Then we move to the tab "Home". Clicking on the button "Conditional Formatting", which is located on the ribbon in the toolbox "Styles". In the list that opens, select the item "Rules Management...".



2. The Conditional Formatting Rules window opens. In field "Show formatting rules for" must be set to "Current Fragment". By default, it should be indicated there, but just in case, check and, in case of discrepancy, change the settings according to the above recommendations. After that, click on the button "Create a rule...".



3. The Create Formatting Rule window opens. In the list of rule types, select the position . In the rule description block in the first field, the switch must be in the position "Values". In the second field, set the switch to the position "Less". In the third field, specify the value, the elements of the sheet containing a value less than which will be painted in a certain color. In our case, this value will be 400000 . After that click on the button "Format…".



4. The Format Cells window opens. Moving to tab "Pouring". Select the fill color that we want to highlight cells containing a value less than 400000 . After that click on the button OK at the bottom of the window.



5. We return to the window for creating a formatting rule and there we also click on the button OK.



6. After this action, we will be redirected to Conditional Formatting Rules Manager. As you can see, one rule has already been added, but we have to add two more. So click the button again. "Create a rule...".



7. And again we get to the rule creation window. Moving to section "Format only cells that contain". In the first field of this section, leave the parameter "Cell Value", and in the second set the switch to the position "Between". In the third field, you need to specify the initial value of the range in which the sheet elements will be formatted. In our case, this number 400000 . In the fourth, we indicate the final value of this range. It will amount 500000 . After that, click on the button "Format…".



8. In the format window, move to the tab again "Pouring", but this time we already choose a different color, after which we click on the button OK.



9. After returning to the rule creation window, also click on the button OK.



10. As we see, in Rule Manager We have already created two rules. Thus, it remains to create a third. Clicking on the button "Create a Rule".



11. In the rule creation window, move again to the section "Format only cells that contain". In the first field leave the option "Cell Value". In the second field, set the switch to the police "More". Enter a number in the third field 500000 . Then, as in previous cases, click on the button "Format…".



12. In the window "Cell Format" back to the tab "Pouring". This time we choose a color that is different from the previous two cases. Performing a button click OK.



13. In the rule creation window, click the button again. OK.



14. opens Rule Manager. As you can see, all three rules have been created, so click on the button OK.



15. Now the table elements are colored according to the specified conditions and borders in the conditional formatting settings.



16. If we change the content in one of the cells, while going beyond the boundaries of one of the specified rules, then this sheet element will automatically change color.

You can also use conditional formatting in a slightly different way to color sheet elements.

Method 2: Using the Find and Select tool

If the table contains static data that is not planned to be changed over time, then you can use the tool to change the color of the cells according to their contents called "Find and select". The specified tool will allow you to find the given values ​​​​and change the color in these cells to the desired user. But it should be noted that when changing the content in the sheet elements, the color will not automatically change, but will remain the same. In order to change the color to the current one, you will have to repeat the procedure again. Therefore, this method is not optimal for tables with dynamic content.

Let's see how it works on a specific example, for which we will take the same enterprise income table.

1. Select the column with the data that should be formatted with a color. Then go to the tab "Home" and click on the button "Find and select", which is located on the ribbon in the toolbox "Editing". In the list that opens, click on the item "Find".



2. window starts "Find and Replace" tab "Find". First of all, let's find the values ​​up to 400000 rubles. Since we do not have a single cell that contains a value less than 300000 rubles, then, in fact, we need to select all elements that contain numbers in the range from 300000 before 400000 . Unfortunately, you cannot directly specify this range, as in the case of applying conditional formatting, in this method.

   But there is an opportunity to do something a little differently, which will give us the same result. You can enter the following pattern in the search bar "3?????". The question mark means any character. So the program will look for all six digit numbers that start with a digit "3". That is, the search output will include values ​​in the range 300000 – 400000 , which is what we need. If the table had numbers less than 300000 or less 200000 , then for each range of a hundred thousand, the search would have to be done separately.

   We enter the expression "3?????" in field "Find" and click on the button "Find all».



3. After that, the results of the search results are opened at the bottom of the window. Left click on any of them. Then we type the key combination Ctrl+A. After that, all the results of the search results are selected, and at the same time, the elements in the column to which these results refer are selected.



4. After the elements in the column are selected, do not rush to close the window "Find and Replace". Being in the tab "Home" to which we moved earlier, go to the ribbon to the tool block "Font". Click on the triangle to the right of the button "Fill color". A selection of different fill colors opens. We select the color that we want to apply to sheet elements containing values ​​less than 400000 rubles.



5. As you can see, all cells in the column that contain values ​​less than 400000 rubles are highlighted in the selected color.



6. Now we need to color the elements that contain values ​​ranging from 400000 before 500000 rubles. This range includes numbers that match the pattern "4??????". Type it into the search box and click the button "Find All", having previously selected the column we need.



7. Similarly with the previous time in the search results, we select the entire result by pressing a combination of hot keys CTRL+A. After that, move to the fill color selection icon. We click on it and click on the icon of the shade we need, which will color the elements of the sheet, where the values ​​\u200b\u200bare in the range from 400000 before 500000 .



8. As you can see, after this action, all elements of the table with data in the interval from 400000 on 500000 highlighted in the selected color.



9. Now it remains for us to select the last interval of values ​​- more than 500000 . Here we are also lucky, since all the numbers are more 500000 are in the range from 500000 before 600000 . Therefore, in the search field, enter the expression "5?????" and click on the button "Find All". If there were values ​​greater than 600000 , then we would have to additionally search for the expression "6?????" etc.



10. Again, highlight the search results using a combination Ctrl+A. Next, using the button on the ribbon, select a new color to fill the interval exceeding 500000 in the same way as we did before.



11. As you can see, after this action, all elements of the column will be painted over, according to the numerical value that is placed in them. Now you can close the search window by clicking the standard close button in the upper right corner of the window, since our task can be considered solved.



12. But if we replace the number with another one that goes beyond the limits set for a particular color, then the color will not change, as it was in the previous method. This indicates that this option will work reliably only in those tables in which the data does not change.

As you can see, there are two ways to color cells depending on the numeric values ​​that are in them: using conditional formatting and using the tool "Find and Replace". The first method is more progressive, as it allows you to more clearly set the conditions by which the elements of the sheet will be highlighted. In addition, with conditional formatting, the color of the element automatically changes if the content in it changes, which the second method cannot do. However, filling cells depending on the value by applying the tool "Find and Replace" too it is quite possible to use, but only in static tables.

With the command "go" you can quickly find and highlight all cells that contain data of a particular type, such as formulas. Alternatively, to find only those cells that meet certain conditions (for example, the last cell in a worksheet that contains data or formatting, use command Go).

Follow the steps below.

Need to highlight duplicate values ​​in a column? Need to select the first 5 maximum cells? Is it necessary to make a thermal scale for clarity (the color changes depending on the increase / decrease in the value of the cells)? In Excel, highlighting cells by conditions can be done very quickly and easily. The special function "Conditional Formatting" is responsible for highlighting cells with color. Highly recommend! Read more on:

I described the main features at the beginning of the article, but in fact there are a lot of them. More about the most useful

To get started, on the taskbar in the main menu, find the Styles section and click the Conditional Formatting button.

When clicked, a menu will open with different options for this editing. As you can see, there are really a lot of opportunities here.

Now more about the most useful:

Excel highlighting cells by conditions. Simple terms

To do this, go to the Cell Selection Rules item. If, for example, you need to select all cells greater than 100, click the More button. In the window:

by default, the conditions are proposed to be highlighted in red, but you can set the desired cell formatting by clicking in the right box and selecting the required option.

Highlighting duplicate values, incl. across multiple columns

To highlight all duplicate values, select the appropriate menu item Duplicate Values.

What to do if you need to find repetitions in two or more columns, for example, when the full name is in different columns? Make another column and combine the values ​​with the formula = , i.e. in a separate cell you will have written IvanovIvanIvanych. By such a column, you can already easily highlight duplicate values. It is important to understand that if the word order differs, then Excel will consider such lines to be non-repeating (for example, Ivan Ivanych Ivanov).

Color highlighting of the first/last values. Again conditional formatting

To do this, go to the Rules for selecting the first and last cells and select the desired item. In addition to the fact that you can highlight the first / last values ​​​​(including percentages), you can use the ability to highlight data above and below the average (I use it even more often). Very handy for viewing results that deviate from the norm or average!

Construction of thermal diagram and histogram

A cool feature for data visualization is the thermal/temperature chart. The bottom line is that, depending on the value of the value in the column or row, the cell is highlighted with a certain shade of color, the more, the redder, for example. Tables are perceived much better by eye, and making decisions becomes easier. Indeed, one of the best analyzers is often our eye, respectively, the brain, and not a machine!

The cell histogram (in blue in the image below) is also a very useful feature for detecting changes in values ​​and comparing them.

Highlight cells containing specific text

Very often you need to find cells that contain a certain set of characters, you can of course use the function = , but it's easier and faster to apply conditional formatting, go - Rules for selecting cells - Text contains

Very useful when working with text. An example when you have the full names of employees in the column, but you need to select all the colleagues of the Ivanovs. Select the cells, go to the desired item and select the containing text Ivanov, after which we filter the table by color

Excel highlighting. Filter by color

In addition to the above options, you can filter the selected cells by color using a regular filter. To my surprise, very few people know about this - apparently echoes of the 2003 version - this feature was not there.

Checking Formatting Conditions

To check which conditional formatting you have set, go to Home - Conditional Formatting - Manage Rules. Here you can edit the already set conditions, the range of application, and also select the priority of the specified formatting (who is higher is more important, you can change it with the arrow buttons).

Invalid conditional formatting range

Important! Conditional formatting, when used incorrectly, is often the cause of strong . There is a doubling of formatting, for example, if you copy cells with highlighting many times. Then you will have many conditions with color. I myself saw more than 3 thousand conditions - the file slowed down ugly. Also, the file may slow down when the range is set as in the picture above, it is better to specify A: A - for the entire range.

Read more about Excel brakes and their causes. This article has helped more than one hundred people;)

Hope it was helpful, sorry!

Conditional formatting in Excel allows you to select not only a cell, but the entire row at once.In order to select the entire row at once in a large table, you need to set our table formatting conditions in the table.What is conditional formatting, read the article " Conditional Formatting in Excel.
We have a table with visitor data. We need to select visitors who have completed a course of study, treatment, work, etc. The table is as follows. In separate cells we create a small auxiliary table. For example, let's make a label in cells G2, G3 and G4.
If we write in the “Status” column - “Completed” or “Stage 1”, “Stage 2”, the entire line will be colored in color.
Instead of an auxiliary table, you can make a “drop-down list” in the auxiliary cell.
Now we set conditional formatting to table cells.
We select the entire table with data and additionally at the bottom of the table one more empty line. We will copy an empty row of the table if we need to add rows in the table. Formatting conditions will be copied immediately.
So, we have selected the range table A2:E7.
On the Home tab, click the Conditional Formatting button and select the Create Rules option.
Click on the line "Compare table columns to determine formatted cells."In the "Format" line, write the formula. =$E2=$G$2
note– reference to cell E2 is mixed.
Press the button with "0". Here we select and set the fill color of the line, the font color of this line. We have chosen the green cell fill color.Click on all three dialog boxes "OK". Everything.
Now we write in the table in the "Status" column - "Completed" and our line turns green, which we set in the conditional formatting rules.
Attention! In the cells of the "Status" column, write the words in the same way as they are written in the auxiliary table. For example, we have the word “Completed” written with a capital letter. If we write the word “completed” in a column cell with a small letter, then the conditional formatting will not work. Therefore, it is better to set a drop-down list in the "Status" column.How to install a dropdown list, see the article " Dropdown list in Excel. It turned out like this.