Highlight cells with a value greater than 500. Excel - conditionally paint cells. How to highlight a column with color in Excel by condition

Hello dear readers. Have you ever worked with huge data in a table? You know, it will be much more convenient to work with them if you know how to select several Excel cells in different colors under a certain condition. Would you like to know how this is done? In this tutorial, we will make the cell color change depending on the Excel value, and also color all the cells using search.

Fill color changes with value

For example, we will practice making a cell change color in this table under a certain condition. Yes, none, but all with a value in the range from 60 to 90. For this we will use the "Conditional Formatting" function.

First, select the data range that we will format.

Next, find the button "Conditional Formatting" on the "Home" tab and select "Create Rule" from the list.

We have opened the "Create formatting rules" window. In this window, select the type of rule: "Format only cells that contain".

Next, go to the section "Change the description of the rule", where you need to specify the conditions according to which the filling will be performed. In this section, you can set a variety of conditions under which it will change.

In our case, it is necessary to put the following: "cell values" and "between". We also designate the range that, given a value from 60 to 90, a fill will be applied. Look at the screenshot how I did it.

Of course, when working with your spreadsheet, you may need to fill in completely different conditions, which you will indicate, well, but now we are just practicing.

If you have filled in, then do not rush to click on the "OK" button. First, you need to click on the "Format" button, as in the screenshot, and go to the fill settings.

Ok, as you can see, you have opened the "Format Cell" window. Here you need to go to the "Fill" tab, where you select the desired one, and click on "OK" in this window and in the previous one. I chose a green fill.

Look at your result. I think you succeeded. I definitely did it. Take a look at the screenshot:

Let's paint the cell in a certain color if it is equal to something

Let's go back to our table in its original form. And now we will change the color where the number 40 is contained to red, and with the number 50 to yellow. Of course, for this case, you can use the first method, but we want to know more Excel features.

This time we will use the Find and Replace function.

Select the part of the table in which we will make changes. If this is the whole sheet, then there is no point in highlighting.

Now is the time to open the search box. On the "Home" tab in the "Editing" section, click on the "Find and Select" button.

You can also use hotkeys: CTRL + F

In the "Find" field, we indicate what we are looking for. In this case, write "40" and then click the "Find All" button.

Now that the search results have been shown below, select one of them and press CTRL + A to select them all at once. And then click on "Close" to hide the "Find and Replace" window.

When we have selected all containing the number 40, on the "Home" tab in the "Font" section, select the color of the cell. We have it red. And, as you can see on your screen, and on my screenshot, they turned red.

Now the same steps need to be performed to color the ones where the number 50 is indicated. I think now you understand how to do this.

Did you succeed? And look what happened to me.

That's all. Thanks friends. Subscribe, comment, join the group, share on social networks and be always aware of new articles. Also, do not forget to study other articles on this site.

To accomplish this task, we will use the conditional formatting capabilities.
Let's take a table containing a list of orders, their 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 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 done here turned green.

Well, let's make one more rule, coloring overdue orders in red. We highlight the dates of the orders. When creating a 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. This will take a little more manual work. 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!

Function = FLOWERS (CELL) returns the fill color code of the selected cell. It has one required argument:

CELL- reference to the cell for which you want to apply the function.

Below is an example that demonstrates how the function works.

Pay attention to the fact that the function is not automatically recalculated. This is because changing the fill color of an Excel cell does not recalculate formulas. To recalculate the formula, you must use the keyboard shortcut Ctrl + Alt + F9

Usage example

Since the filling of cells greatly simplifies the perception of data, almost all users like to use it. However, there is also a big disadvantage - in standard Excel functionality it is not possible to perform operations based on the fill color. You cannot sum cells of a certain color, count their number, find the maximum, and so on.

Using the function FLOWERS it all becomes doable. For example, "drag" a given formula with a fill color in the adjacent column and calculate based on the numeric code of the cell.

VBA code

Public Function FILLER (CELL As Range) As Double FILLER = CELL.Interior.Color End Function

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 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 differently 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 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, the 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 this element of the sheet will automatically be repainted.

Let's see how this method works with a 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 "Less"... 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 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 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 be 500000 ... After that, click on the button "Format…".

In the formatting window, move to the tab again "Fill", but this time we 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 we 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 we 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 this sheet element will automatically change its 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 a 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 ribbon 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, you cannot directly specify this range, as in the case of applying conditional formatting, in this method.
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 selected and at the same time the elements in the column to which these results refer are selected.

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 with 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 a new color to fill the interval exceeding 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 specific 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 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.

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 size of letters, 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 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 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 blocks containing the specified text are colored red.

Rules are also created for other fruits, only a different fill 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 write - "Value" "more" "15". The last number can either be entered manually, or you can 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 or equal".

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 brought in have sold everything.

Let's use the last column as an example to show how to make a 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 the 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 in the lower 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.