How to highlight row in excel. How to highlight anything in Excel: the most complete guide! Differences between strings

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 chose green color cell filling.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 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.

By the same principle, we also set conditional formatting rules by changing the cell address of column G in the formula.Multiple conditional formatting rules can be set in the same cells.

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 through - Cell selection rules - 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!

Share our article on your social networks: Example file

If the value in a cell satisfies a user-defined condition, then with the help you can highlight this cell (for example, change its background). In this article, let's go further - we will select the entire row of the table containing this cell.

Let in the range A6:C16 there is a table with a list of works, deadlines and status of their completion (see example file).

Task1 - text values

It is necessary to highlight the line containing the work of a certain status with a color. For example, if the work has not been started, then the line will be highlighted in red, if the work has not yet been completed, then in gray, and if completed, then in green. We will select lines using rules.

Solution1

Let's create a small table with the statuses of jobs in the range E6:E9 .

Select a range of cells A7:C17 , containing a list of works, and install through the menu Home/ Fill color the fill background is red (we assume that all works are initially in the status Not started).

Make sure the range of cells is selected A7:C17 (A7 must be ). Call the menu command Conditional Formatting / Create Rule / Use Formula to Determine Cells to Format .

• in field " Format values ​​for which the following formula is true» you need to enter =$C7=$E$8 (in the cell E8 value is found In work). Pay attention to the use of ;
• press the button Format ;
• select tab fill ;
• choose gray color ;
• Press OK.

ATTENTION: Once again I pay attention to the formula =$C7=$E$8 . Usually users enter =$C$7=$E$8 , i.e. enter an extra dollar symbol.

You need to do similar actions to select works in the status Completed. The formula in this case will look like =$C7=$E$9 , and set the fill color to green.

As a result, our table will take the following form.

To quickly extend Conditional Formatting rules to a new row in a table, select the new row cells ( A17:C17 ) and press . The rules will be copied to a string 17 tables.

Task 2 - Dates

Assume that you keep a log of visits by employees scientific conferences(cm. example sheet date file).

Unfortunately, the Date Visited column is not sorted and you need to highlight the date of the first and last visit of each employee. For example, employee Kozlov went to the conference for the first time on 07/24/2009, and the last time on 07/18/2015.

First, let's create a formula for conditional formatting in columns B and E. If the formula returns TRUE, then the corresponding row will be highlighted, if FALSE, then not.

In column D created = MAX(($A7=$A$7:$A$16)*$B$7:$B$16)=$B7 A that defines the maximum date for a particular employee.

Note: If you need to determine the maximum date regardless of the employee, then the formula will be much simpler = $B7=MAX($B$7:$B$16) and the array formula is not needed.

Now let's select all the table cells without a header and create a rule. Let's copy the formula into the rule (it doesn't need to be entered as an array formula!).

Now suppose that the date column has been sorted and it is required to select rows whose visit dates fall within a certain range.

To do this, use the formula =AND($B23>$E$22;$B23

For cells E22 and E23 with cutoff dates (highlighted in yellow) used $E$22 and $E$23. Because the reference to them should not change in the UV rules for all table cells.

For cell B22 mixed addressing $B23 is used, i.e. the reference to column B should not change (for this, it precedes B with a $ sign), but the reference to the row should change depending on the row of the table (otherwise all date values ​​will be compared with the date from B23 ).

So the UV rule for example for a cell is A27 will look =AND($B27>$E$22;$B27 , i.e. A27 will be highlighted because in this line date from B27 falls within the specified range (for cells from column A, the selection will still be made depending on the contents of column B from the same row - this is the "magic" of mixed addressing $B23).

And for the cell B31 the UV rule would look like =AND($B31>$E$22;$B31 , i.e. B31 will not be selected, because in this line the date from B31 does not fall within the specified range.

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 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 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 colored 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, enter initial value 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". This tool will help you find setpoints and change the color in these cells to what the user wants. 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, to directly specify this range, as in the case of applying conditional formatting, in this method it is forbidden.

   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 "four??????". 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.