With the command "go" you can quickly find and select all cells that contain data of a certain type, such as formulas. In addition, to find only those cells that match certain conditions(e.g. the last cell in a sheet containing data or formatting, use command Go).

Follow the steps below.

Team	To highlight
Notes
Constants
Formulas	Note: With the checkboxes below formulas formula type is determined.
empty	Empty cells.
current area	the current scope, such as the entire list.
current array	the entire array if the active cell is contained in the array.
Objects	Graphic objects, including charts and buttons, on the sheet and in text fields.
Differences between strings	All cells that are different from the active cell in the selected row. There is always one active cell in the selected area - it is a range, row or column. By pressing the ENTER or TAB key, you can change the location of the active cell, which by default is the first cell in the row. If more than one row is selected, the comparison is performed for each individual row of the selection, and the cell used in the comparison for each additional row is in the same column as the active cell.
Differences between columns	All cells that are different from the active cell in the selected column. A selection always has an active cell, whether it's a range, row, or column. By pressing the ENTER or TAB key, you can change the location of the active cell, which by default is the first cell in a column. If you select more than one column, the comparison is performed for each individual column of the selection. The cell used in the comparison for each additional column is in the same row as the active cell.
Influencing	Cells referenced by the formula in the active cell. In chapter dependent items, do one of the following: Click only straight lines to find only the cells referenced by the formula. Click all levels to find all cells directly or indirectly referenced by cells in the selection.
Dependent	Cells with formulas that refer to active cell. Do one of the following: Click only straight lines to find only cells with formulas that directly refer to the active cell. Click all levels to find all cells that directly or indirectly refer to the active cell.
last cell	The last cell on a worksheet that contains data or formatting.
Only visible cells	Only cells visible in a range that intersects with hidden rows and columns.
Conditional formats	Only cells that have conditional formatting applied. In chapter Data validation do one of the following: Click the button all to find all cells that have conditional formats applied. To search for cells with the same conditional formatting highlighted in the current cell, click one of them.
Data validation	Only cells that contain data validation rules are applied. Do one of the following:

Find out how to Excel sheets quickly change the color of an entire row depending on the value of one cell. See tricks and examples of formulas for numeric and text values.

In one of the previous articles, we discussed how to change the color of a cell depending on its value. This time we will talk about how to highlight the entire row in Excel 2010 and 2013 depending on the value of one cell, and also reveal a few tricks and show examples of formulas for working with numeric and text values.

How to change the color of a row based on the numeric value of one of the cells

Suppose we have the following table of company orders:

We want to color the rows with different colors depending on the quantity ordered (the value in the column Qty.) to highlight the most important orders. The Excel tool will help us to cope with this task - " Conditional Formatting».

As you can see, changing the color of an entire row in Excel based on the numeric value of one of the cells is not at all difficult. Next, we will look at a few more examples of formulas and a couple of tricks for solving more complex problems.

How to create multiple conditional formatting rules with a given priority

In the table from the previous example, it would probably be more convenient to use different fill colors to highlight the rows containing in the column Qty. different meanings. For example, create another conditional formatting rule for strings containing the value 10 or more, and highlight them in pink. For this we need a formula:

In order for both rules we created to work simultaneously, you need to arrange them in the right priority.

How to change the color of a row based on the text value of one of the cells

To simplify the control of order fulfillment, we can highlight in our table with different colors the rows of orders with different delivery status, information about which is contained in the column Delivery:

• If the delivery date of the order is in the future (value Due in X Days), then the fill of such cells should be orange;
• If the order has been delivered (value Delivered), then the fill of such cells should be green;
• If the delivery date of the order is in the past (value Past Due), then the fill of such cells should be red.

And, of course, the cell fill color should change if the order status changes.

With formula for values Delivered and Past Due everything is clear, it will be similar to the formula from our first example:

=$E2="Delivered"
=$E2="Past Due"

The task sounds more difficult for orders that must be delivered through X days (value Due in X Days). We see that the delivery time for various orders is 1, 3, 5 or more days, which means that the above formula does not apply here, as it targets the exact value.

In this case, it is convenient to use the function SEARCH(SEARCH) and to find a partial match, write the following formula:

SEARCH("Due in";$E2)>0
=SEARCH("Due in",$E2)>0

In this formula E2- this is the address of the cell, based on the value of which we will apply the conditional formatting rule; dollar sign $ needed in order to apply the formula to the whole line; condition " >0 ” means that the formatting rule will be applied if the specified text (in our case, “Due in”) is found.

Clue: If the formula uses the condition “ >0 “, then the line will be highlighted in color every time the specified text is found in the key cell, regardless of where it is located in the cell. In the example table in the figure below, the column Delivery(column F) may contain the text “Urgent, Due in 6 Hours” (which means Urgent, deliver within 6 hours), and this line will also be colored.

In order to highlight with color those lines in which the contents of the key cell begin with the specified text or symbols, the formula must be written in the following form:

SEARCH("Due in";$E2)=1
=SEARCH("Due in",$E2)=1

You need to be very careful when using such a formula and check if the cells of the key column of data start with a space. Otherwise, you can rack your brains for a long time, trying to understand why the formula does not work.

So, following the same steps as in , we created three formatting rules, and our table began to look like this:

How to change the color of a cell based on the value of another cell

Actually, this is a special case. Instead of the whole table, select the column or range in which you want to change the color of the cells, and use the formulas described above.

For example, we can set up three of our rules in such a way that only cells containing an order number are highlighted in color (column order number) based on the value of another cell in this row (using the values ​​from the column Delivery).

How to set multiple conditions to change the color of a row

If you need to highlight rows with the same color when one of several different values ​​appears, then instead of creating several formatting rules, you can use the functions And(and) OR(OR) and thus combine several conditions in one rule.

For example, we can mark orders expected within 1 and 3 days in pink, and those due within 5 and 7 days in yellow. The formulas will look like this:

OR($F2="Due in 1 Days";$F2="Due in 3 Days")
=OR($F2="Due in 1 Days",$F2="Due in 3 Days")

OR($F2="Due in 5 Days";$F2="Due in 7 Days")
=OR($F2="Due in 5 Days",$F2="Due in 7 Days")

In order to highlight orders with a quantity of at least 5, but not more than 10 (the value in the column Qty.), we write the formula with the function And(AND):

AND($D2>=5;$D2<=10)
=AND($D2>=5,$D2<=10)

Of course, in your formulas you can use not necessarily two, but as many conditions as required. For example:

OR($F2="Due in 1 Days";$F2="Due in 3 Days";$F2="Due in 5 Days")
=OR($F2="Due in 1 Days",$F2="Due in 3 Days",$F2="Due in 5 Days")

Clue: Now that you have learned how to color cells in different colors, depending on the values ​​they contain, you may want to find out how many cells are highlighted in a particular color, and calculate the sum of the values ​​in these cells. I want to please you, this action can also be done automatically, and we will show the solution to this problem in an article on the question How to calculate the amount, amount and set up a filter for cells of a certain color in Excel.

We have shown just a few of the possible ways to make a table look like a zebra stripe, the color of which depends on the values ​​​​in the cells and can change along with changing these values. If you are looking for something different for your data, let us know and together we will definitely come up with something.

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:

To accomplish this task, we will use the possibilities of conditional formatting.
Let's take a table containing a list of orders, their deadlines, current status and cost. Let's try to make its cells color themselves, depending on their contents.

Instructions for Excel 2010

TURN ON SUBTITLE!

How to do it in Excel 2007

TURN ON SUBTITLE!
Select the cells with order prices and, by clicking on the arrow next to the "Conditional Formatting" button, select "Create Rule".

Let's choose the fourth item, which allows us to compare the current values ​​with the average. We are interested in values ​​above the average. By clicking the "Format" button, set the color of the cells.


We confirm our choice, and the cells with the price above the average turned blue, drawing our attention to expensive orders.


Select cells with order statuses and create a new rule. This time we use the second option, which allows you to check the contents of the cell. Select "Text", "contains" and enter the word "Completed". We set the green color, confirm it, and the completed work turns green.


Well, let's make one more rule that paints overdue orders in red. We select the dates for the execution of orders. When creating the rule, we again select the second item, but this time we set the "Cell value", "less than", and in the next field we enter a function that returns today's date.


“OK”, and we got a cheerfully decorated table that allows you to visually track the progress of orders.


Have you noticed that the statuses are set by choosing from a drop-down list of values? How to make such lists, we told in the instructions.

How to do it in Excel 2003

TURN ON SUBTITLE!
"Conditional Formatting" in the "Format" menu. This will require a bit more manual work. This is what the settings for our first task will look like - to fill in the cells with values ​​above the average.


You will have to manually enter the function "=AVERAGE()", put the cursor between the brackets, click on the button next to it and use the mouse to select the desired range.
But the principle of action is the same.
Conquer Excel and see you soon!

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.