Excel change the color of a cell conditionally. Fill cells depending on the value in Microsoft Excel. How to change the color of a row based on the text value of one of the cells

When working with tables, the values displayed in it are of primary importance. But its design is also an important component. Some users consider this to be a secondary 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 an especially 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 especially relevant given opportunity 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 in this huge amount of information, since it can be said to be already structured.

You can try to color the elements of the sheet by hand, but again, if the table is large, it will take a significant amount of time. In addition, in such an array of data human factor can play a 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 in large quantities. In this case, manually changing the color becomes generally 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 value boundaries at which cells will be painted in a particular color. The staining will be done automatically. If the value of the cell, due to the change, goes beyond the border, then the repainting of this element of the sheet will automatically occur.

Let's see how this method works for specific example... We have a table of the company's 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 exceeds 500000 rubles.

Select the column that contains information on the company's income. Then we move to the tab "Home"... Click on the button "Conditional Formatting", which is located on the tape in the toolbox "Styles"... In the list that opens, select the item "Rule management ...".

The window for managing the rules of conditional formatting is launched. In field "Show formatting rules for" should be set to "Current snippet"... By default, this is what 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 ...".

The window for creating a formatting rule 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 "Smaller"... In the third field, specify the value; sheet elements containing a value less than which will be painted with a certain color. In our case, this value will be 400000 ... After that, click on the button "Format…".

The cell format window opens. Moving to the tab "Fill"... Select the fill color with which we want to highlight cells containing a value less than 400000 ... After that, click on the button "OK" at the bottom of the window.

We return to the window for creating a formatting rule and there, too, click on the button "OK".

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

And again we find ourselves in the window for creating a rule. Moving to the 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 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 be 500000 ... After that, click on the button "Format…".

In the formatting window, move to the tab again "Fill", but this time we already choose a different color, after which we press the button "OK".

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

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

In the rule creation window, go back to the section "Format only cells that contain"... In the first field, we leave the option "Cell value"... In the second field, set the switch to the police "More"... In the third field, drive in the number 500000 ... Then, as in the previous cases, click on the button "Format…".

In the window "Cell format" again move to the tab "Fill"... This time, choose a color that is different from the two previous cases. Click on the button "OK".

In the window for creating rules, repeat clicking on the button "OK".

Opens Rule manager... As you can see, all three rules have been created, so click on the button "OK".

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

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

Alternatively, you can use conditional formatting in a slightly different way to color the sheet elements with color.

Method 2: using the Find and Select tool

If the table contains static data that you do not plan to change over time, then you can use the tool to change the color of the cells by their contents called Find and highlight... The specified tool will allow you to find the specified values and change the color in these cells to the desired user. But keep in mind that when you change 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 actual one, you will have to repeat the procedure again. Therefore, this method is not optimal for tables with dynamic content.

Let's see how this works for a specific example, for which we will take all the same table of the company's income.

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

The window starts Find and Replace in the tab "Find"... First of all, let's find the values up to 400000 rubles. Since we do not have a single cell containing 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 specify this range directly, as in the case of applying conditional formatting, in this way it is forbidden.
But there is an opportunity to do a little differently, which will give us the same result. You can set the following pattern in the search bar "3 ?????"... A question mark means any character. Thus, the program will search for all six-digit numbers that start with a digit. "3"... That is, the search results will contain values in the range 300000 – 400000 , which is what we need. If there were fewer numbers in the table 300000 or less 200000 , then for each range of one hundred thousand, the search would have to be performed separately.

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

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

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

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

Now we need to color the elements in which the values are located in the range from 400000 before 500000 rubles. This range includes numbers that match the pattern "4??????"... We drive it into the search field and click on the button Find All, having previously selected the column we need.

Similarly to the previous time in the search results, we select the entire result obtained by pressing a combination of hot keys CTRL + A... After that, move on to the fill color selection icon. Click on it and click on the icon of the desired shade, which will color the elements of the sheet, where the values are in the range from 400000 before 500000 .

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

Now it remains for us to select the last interval of values - more 500000 ... Here we were also lucky, since all numbers are more than 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 exceeding 600000 , then we would have to additionally search for the expression "6 ?????" etc.

Again, highlight the search results using the combination Ctrl + A... Next, using the button on the ribbon, select new color to fill an interval greater than 500000 by the same analogy as we did earlier.

As you can see, after this action, all the elements of the column will be painted 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.

But if we replace the number with another one that goes beyond the boundaries that are set for a particular color, then the color will not change, as it was in the previous way... 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 they contain: using conditional formatting and using the tool Find and Replace... The first method is more progressive, since it allows you to more clearly define the conditions by which the elements of the sheet will be highlighted. In addition, with conditional formatting, the color of an element automatically changes if the content in it changes, which the second method cannot do. However, filling cells depending on the value by using the tool Find and Replace can also be used, but only in static tables.

To accomplish this task, we will use the conditional formatting capabilities.
Let's take a table containing a list of orders, due dates, current status, and cost. Let's try to make its cells colorize themselves, depending on their content.

Excel 2010 manual

TURN ON SUBTITLES!

How to do it in Excel 2007

TURN ON SUBTITLES!
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 you to compare the current values with the average. We are interested in values above the average. Pressing the "Format" button, set the color of the cells.

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

Select cells with order statuses and create a new rule. This time, we will use the second option, which allows you to check the contents of the cell. Select "Text", "contains" and enter the word "Done". Let's set the color green, confirm, and the work performed here turned green.

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

OK, and we got a fun colored spreadsheet that allows us to visually track the progress of orders.

Have you noticed that statuses are set by choosing from a drop-down list of values? We described how to make such lists in the instructions.

How to do it in Excel 2003

TURN ON SUBTITLES!
"Conditional Formatting" in the "Format" menu. It will take a little more here self made... This is how the settings for our first task will look like - paint over the cells with values greater than 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 specify the desired range with the mouse.
But the principle is the same.
Conquer Excel and see you soon!

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

In a previous article, we discussed how to change the color of a cell based on its value. This time we will talk about how in Excel 2010 and 2013 to highlight the entire row depending on the value of one cell, as well as 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 a table of company orders like this:

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

As you can see, change the color of an entire row in Excel based on numerical value one of the cells is not difficult at all. 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 Priority Conditional Formatting Rules

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

In order for both rules we created to work at the same time, you need to prioritize them.

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 lines 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 filling of such cells should be orange;
If the order is delivered (value Delivered), then the filling of such cells should be green;
If the delivery date of the order is in the past (value Past due), then the filling of such cells should be red.

And, of course, the fill color of the cells 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 via NS days (value Due in X Days). We can 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 is aimed at the exact value.

V this case 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 to apply a formula to an entire line; condition " >0 ”Means that the formatting rule will be applied if the specified text (in our case it is“ Due in ”) is found.

Prompt: If the formula uses the condition “ >0 “, Then the line will be highlighted in color in each case when 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 in translation means - Urgent, deliver within 6 hours), and this line will also be colored.

In order to highlight in color those lines in which the content of the key cell begins with the specified text or characters, the formula must be written as follows:

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

You need to be very careful when using such a formula to check if there are no spaces in the cells of the key column of data. 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

In fact, 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 so that only cells containing the order number (column Order number) based on the value of another cell in this row (we use the values from the column Delivery).

How to set multiple conditions for changing the color of a row

If you need to highlight lines 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 that will be completed 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 select orders with the quantity of goods at least 5, but not more than 10 (value in the column Qty.), 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")

Prompt: Now that you have learned how to color cells in different colors, depending on the values they contain, you may want to know 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 the article devoted to the question How to calculate the quantity, amount in Excel and set up a filter for cells of a certain color.

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

In order to correctly place accents in a document made in MS Excel, you can increase the size of the cells, choose a different font or size of letters, or you can fill in 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 specified condition, depending on the value inscribed in it, and how to work with the created rules.

Simple block fill

It is not difficult to paint over one or several blocks in Excel. First, select them and on the Home tab, click on the arrow next to the paint bucket to expand the list. Choose a color from there, and if nothing works, click "More Colors."

By the way, in this way you can fill in a whole line, just first click on its number to select it. You can read a separate article about cell selection 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, like this. Click on it and from the colors, or select a new one, or click on the option "No".

Depending on the entered data

Now let's look at how to make the 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

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

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

This window opens. At the top, select the type - "Format only cells that contain", further we will also mark 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 the text.

The difference is that by placing 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 the options, click on the "Other" button.

The rule is created and we save it by clicking the "OK" button.

As a result, all the blocks containing the specified text are colored 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 the column, create a rule, and specify its type. Then we prescribe - "Value" "more" "15". You can either enter the last number manually, or specify the address of the cell where the data will be taken from. Determine 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 "Rule Management".

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

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

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

The difference between imported and sold goods would be clearly visible here. Select two columns and click "Cell selection rules" – Duplicate Values... Choose a suitable color. After that, if two cells next to each other are colored, it means that the fruits that were delivered have all sold.

Let's use the last column as an example to show how to make the cell change color depending on the specified value. Select them and go to "Rule 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 as a whole, or for other sheets.

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

I will create one more 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 on 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 be revised as well. If the value becomes more or less than the specified one, 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 "Rule Management" there will be a complete list. Using the buttons at the top, you can add, change or delete them.

Choosing tools on the "MAIN" tab in the "Styles" section from the "Conditional formatting" drop-down menu, we have access to a whole group "Rules for selecting the first and last values". However, it is often necessary to compare and highlight cells in Excel, but none of the ready-made solutions matches our conditions. For example, in the construction of a condition, we want to use more criteria or perform more complex calculations. You can always select the last option "Other rules", it is also the option "Create rule". Conditional formatting allows you to use a formula to create complex criteria for comparing and filtering values. We are not limiting ourselves by creating our own custom rules for conditional formatting using various formulas.

How to compare columns in Excel and highlight their cells with color?

For clarity, let's look at a specific example of creating conditional formatting with formulas. For example, let's take a simple table of store profitability reports for the past and the current year. Our rule is to force Excel to highlight in color, provided that the sums of the stores of the current year have a negative profit (loss) and they are greater than last year:

To create a new custom rule, do the following:

Note! In this formula, we only use relative cell references - this is important. After all, we need the formula to analyze all the cells of the selected range.

How to color highlight a cell in Excel by condition?

Now we will highlight in orange those amounts of stores that are less this year than last year and with a negative profit. Let's create a second rule for the same range D2: D12:

We see that we got a not quite expected result, since the created new rule always has higher priority compared to the old rules of conditional formatting in Excel. It is necessary to lower the priority for the new rule. To analyze this feature visually and adjust accordingly, you need to select the tool: HOME "-" Styles "-" Conditional formatting "-" Manage rules ".

Select the new orange rule in the Conditional Formatting Rules Manager window that appears and click the Down button (CTRL + Down Arrow), as shown in the figure:

As you can see, the sequence of rules is very important if there are many of them assigned to the same range of cells:

At first glance, it may seem that several rules can format the same cell at the same time. In principle, this is so, but under a certain condition that all rules will use different types of formatting. For example, rule 1 - changes the font, 2 - changes the fill, 3 - adds a border, 4 - pattern, etc. But if, after the execution of any rule, when its condition is met, the following rule for this cell was checked, then in the dispatcher window, tick the box in the "Stop if true" column:

And finally, let's add a third rule to color the cells of the sums of the stores, where the profit is positive and more than last year. Enter a new formula:

These cells will be assigned a green color and click OK everywhere.

Note. Any reference for the current sheet can be used in the formula. In Excel 2010, you can reference other sheets as well. And in Excel 2007, other sheets can only be referenced through range names. We recommend that all versions of Excel refer to other sheets by name, as this avoids many pitfalls when creating custom rules for conditional formatting.

Categories

Popular