Not all companies buy special software for business management. Many people use MS Excel, because it is well suited for large information databases. Experience has shown that it rarely goes beyond filling out tables. The table grows, more information becomes available, and the need arises to quickly select only what you need. In such a situation, the question arises: how to highlight a cell in Excel under a certain condition, apply color gradients to rows depending on the type or name of the supplier, and make working with information quick and convenient? Read more below.
Where is conditional formatting located?
How to change the color of a cell in Excel depending on the value - it’s very simple and fast. To highlight cells with color, there is a special “Conditional Formatting” function located on the “Home” tab:
Conditional formatting includes a standard set of provided rules and tools. But most importantly, the developer gave the user the opportunity to invent and configure the necessary algorithm himself. Let's look at formatting methods in detail.
Cell selection rules
Using this set of tools, the following selections are made:
- find numerical values in the table that are greater than the established value;
- find values that are less than the set value;
- find numbers that are within a given interval;
- determine values equal to a conditional number;
- mark only those that are necessary in the selected text fields;
- mark columns and numbers for the required date;
- find repeated text or number values;
- come up with the rules necessary for the user.
See how the selected text is searched: the first field specifies the condition, and the second indicates how to highlight the result. Please note that you can select the background and text colors from those suggested in the list. If you want to apply other shades, you can do this by going to “Custom Format”. All “Rules for selecting cells” are implemented in a similar way.
“Other rules” are implemented very creatively: in six scenario options, come up with those that are most convenient for work, for example, a gradient:
You set color combinations for minimum, average and maximum values - you get a gradient coloring of the values as a result. It is comfortable to use a gradient when analyzing information.
Rules for selecting the first and last values.
Let's consider the second group of functions “Rules for selecting the first and last values”. In it you can:
- highlight the first or last N number of cells with color;
- apply formatting to a specified percentage of cells;
- select cells containing a value above or below the average in the array;
- in the “Other Rules” tab, set the required functionality.
Histograms
If you are not satisfied with filling a cell with color, use the Histogram tool. The proposed coloring is easier to perceive by eye in a large amount of information, the functional rules are adjusted to the user's requirements.
Color scales
This tool quickly creates a gradient fill of indicators of your choice from large to small or vice versa. When working with it, the necessary percentages or text values are established. Ready-made gradient samples are provided, but a custom approach is again implemented in the “Other Rules”.
Icon Sets
If you are a lover of emoticons and emojis, and perceive pictures better than colors, the developers have provided sets of icons in the corresponding tool. There are few pictures, but enough for a complete work. The images are stylized to resemble traffic lights, exclamation marks, ticks, crosses to mark deletion - a simple and intuitive approach.
Create, delete and manage rules
The “Create Rule” function completely duplicates the “Other Rules” listed above and creates a selection initially at the user’s request.
Using the “Delete Rule” tab, created scripts are deleted from the entire sheet, from the selected range of values, from the table.
The “Manage Rules” tool is of interest - a kind of history of creating and changing formatting. Change the selections, make the rules inactive, return them back, alternate the order of application. This is very convenient for working with large amounts of information.
Selection of cells by dates
To figure out how to change the color of a cell in Excel based on the value of a set date, consider an example with dates of purchases from suppliers in January 2019. To apply this selection, you need cells with the “Date” format set. To do this, before entering information, select the required column, right-click and in the “Format Cells” menu, find the “Number” tab. Set the number format to "Date" and select its type as you wish.
To select the required dates, we use the following sequence of actions:
- select columns with dates (in our case for January);
- find the “Conditional Formatting” tool;
- in the “Rules for selecting cells”, select the “Date” item;
- on the right side of the formatting, open a drop-down window with rules;
- select the appropriate rule (for example, dates for the previous month were selected);
- in the left field set the ready-made color selection “Yellow fill and dark yellow text”
- The selection is colored, click “OK”.
By formatting cells containing a date, you can select values from ten options: yesterday/today/tomorrow, last/current/next week, last/current/next month, last 7 days.
Coloring a column by condition
To analyze the company’s activities using a table, let’s look at an example of how to change the color of a cell in Excel depending on the condition set by the employee. As an example, let’s take a table of orders for January 2019 for ten contractors.
We need to mark in blue those suppliers from whom we purchased goods worth more than 100,000 rubles. To make such a selection, we will use the following algorithm of actions:
- highlight the column with January purchases;
- Click the “Conditional Formatting” tool;
- go to “Cell selection rules”;
- item “More...”;
- on the right side of the formatting, set the amount to 100,000 rubles;
- in the left field, go to the “Custom Format” tab and select the blue color;
- the required selection is colored blue, click “OK”.
The Conditional Formatting tool is used to solve everyday business problems. With its help, information is analyzed, the necessary components are selected, and the terms and conditions of interaction between the supplier and the client are checked. The user himself comes up with the combinations he needs.
Color design plays an important role, because it is difficult to navigate in a white table with a large amount of data. If you come up with a sequence of colors and signs, the information content will be perceived almost intuitively. Screenshots from such tables will be clearly visible in reports and presentations.
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 the cells are colored different 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, one might say, will already be structured.
You can try to color the sheet elements by hand, but again, if the table is large, this will take a significant amount of time. In addition, in such a data array, the human factor can play a role and errors will be made. Not to mention that the table can be dynamic and the data in it changes periodically, and massively. In this case, manually changing the color 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 value limits at which cells will be colored in one color or another. Coloring will be carried out automatically. If the cell value, due to a change, goes beyond the border, then this sheet element will be automatically repainted.
Let's see how this method works with a specific example. We have a table of enterprise income in which the data is broken down monthly. We need to highlight in different colors those elements in which the amount of income is less 400000 rubles, from 400000 before 500000 rubles and exceeds 500000 rubles
- We highlight the column that contains information on the company’s income. Then move to the tab "Home". Click on the button "Conditional Formatting", which is located on the ribbon in the toolbox "Styles". In the list that opens, select the item "Rule management...".
- The window for managing conditional formatting rules opens. In field "Show formatting rules for" value must be set "Current fragment". By default, this is exactly what should be indicated there, but just in case, check and, in case of discrepancy, change the settings according to the above recommendations. After this, click on the button "Create a rule...".
- The window for creating a formatting rule opens. In the list of rule types, select a position . In the rule description block in the first field, the switch should be in the position "Values". In the second field, set the switch to position "Less". In the third field we indicate the value; sheet elements containing a value less than which will be painted in a certain color. In our case this value will be 400000 . After that, click on the button "Format…".
- The Format Cells window opens. Moving to the tab "Fill". Select the fill color that you 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 click on the button there too "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 need to add two more. So we press the button again "Create a rule...".
- And again we find ourselves in the rule creation window. Moving to the section "Format only cells that contain". In the first field of this section we leave the parameter "Cell value", and in the second we set the switch to position "Between". In the third field you need to specify the initial value of the range in which the sheet elements will be formatted. In our case this number 400000 . In the fourth we indicate the final value of this range. It will amount to 500000 . After this, click on the button "Format…".
- In the formatting window, move to the tab again "Fill", but this time we select a different color, and then click on the button "OK".
- After returning to the rule creation window, also click on the button "OK".
- As we see, in Rules Manager We have already created two rules. Thus, it remains to create the third. Click on the button "Create Rule".
- In the rule creation window, move again 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 police "More". In the third field we enter the number 500000 . Then, as in previous cases, click on the button "Format…".
- In the window "Cell Format" move to the tab again "Fill". This time we choose a color that is different from the two previous cases. Click on the button "OK".
- In the rule creation window, press the button again "OK".
- Opens Rules Manager. As you can see, all three rules have been created, so click on the button "OK".
- Now table elements are colored according to the specified conditions and boundaries in the conditional formatting settings.
- If we change the contents in one of the cells, 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 worksheet elements.
Method 2: Using the Find and Select tool
If the table contains static data that is not planned to change over time, then you can use a tool to change the color of cells based on their contents called "Find and Select". This tool will allow you to find the specified values and change the color in these cells to the one desired by the user. But please note 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 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 this works using a specific example, for which we will take the same table of enterprise income.
- Select the column with the data that should be formatted in color. Then go to the tab "Home" and press 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".
- Window opens "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 using conditional formatting, in this method.
But there is an opportunity to do things a little differently, which will give us the same result. You can specify the following pattern in the search bar "3?????". A question mark means any symbol. Thus, the program will search for all six-digit numbers that begin with the digit "3". That is, the search results will include values in the range 300000 – 400000 , which is what we need. If the table had smaller numbers 300000 or less 200000 , then for each range of one hundred thousand the search would have to be performed separately.
Enter the expression "3?????" in field "Find" and press the button "Find everything».
- After that, search results open at the bottom of the window. Left-click on any of them. Then we type the key combination Ctrl+A. After this, all search results are selected and at the same time the elements in the column to which these results link 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 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
- As you can see, all cells of the column containing values less than 400000 rubles, highlighted in the selected color.
- Now we need to color the elements that contain values in the range from 400000 before 500000 rubles This range includes numbers that match the pattern "4??????". Enter 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 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 are in the range from 400000 before 500000 .
- As we can see, after this action, all table elements with data in the interval s 400000 By 500000 highlighted with the selected color.
- Now we just have to select the last interval of values – more 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 we enter the expression "5?????" and press the button "Find All". If there were values greater than 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 elements of the column will be filled in, 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 problem 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 was the case in the previous method. This indicates that this option will only work reliably 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 they contain: using conditional formatting and using the "Find and Replace". The first method is more progressive, as it allows you to more clearly define the conditions by which sheet elements 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" It 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, choose 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 filled in. How to make a cell change color according to a given condition, depending on the value entered in it, and how to work with the created rules.
Simple block filling
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. Select a suitable color from there, and if nothing suits, click "Other colors".
By the way, you can fill in an entire line this way, just first click on its number to highlight it. You can read a separate article about selecting cells in Excel.
If you are working with a table in which something has already been painted over, then you can change the color of the blocks or 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 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 red corresponds to an apple, yellow to a pear, and orange to an orange.
We select the data we will work with, in my case these are the names of fruits. Then click "Conditional Formatting", which we will continue to use continuously. From the list, click on "Create Rule".
This window opens. Select the type at the top - "Format only cells that contain", we will also celebrate it further. Below we indicate the conditions: we have a text that contains certain words. In the last field, either click on the button and select a cell, or enter text.
The difference is that by placing a link 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 indicate currants, the rule will change accordingly, and blocks with the same text will be filled in. And if you enter apple in the field, then this particular word will be searched, and it will not depend on anything.
Here, select a fill color and click OK. To view all options, click on the “Others” button.
The rule has been created and we save it by clicking 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.
Numerical
Now let's look at the 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 indicate its type. Next we write - “Value” “greater than” “15”. You can either enter the last number manually, or specify the address of the cell from which the data will be taken. We decide on the fill, click “OK”.
Blocks containing numbers greater than the selected one are colored in.
Let's specify more rules for the selected cells - select "Rule Management".
Here, select everything as I described above, you just need to change the color and set the condition "less or equal".
When everything is ready, click "Apply" and "OK".
Everything works, the values are equal and below 15 are painted pale blue.
Here the difference between imported and sold goods would be clearly visible. Select two columns and click "Rules for highlighting cells" – "Duplicate values". Choose a suitable color. After this, if two cells next to each other are colored, it means that the fruits that were delivered were sold.
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 or for other sheets.
Select the required items in the window that opens. I will fill all values that are greater than 90 with dark green. Since I indicated the address in the last field (=$F$15), then if 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’ll create another rule, but in it I’ll highlight in light green everything that is less than or equal to 90. Don’t forget to save everything by clicking on the buttons at the bottom right.
This is 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, the cell color 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 presence of errors in the cells. Specify the 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.
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 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 filled in. How to make a cell change color according to a given condition, depending on the value entered in it, and how to work with the created rules.
Simple block filling
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. Select a suitable color from there, and if nothing suits, click "Other colors".
By the way, you can fill in an entire line this way, just first click on its number to highlight it. You can read a separate article about selecting cells in Excel.
If you are working with a table in which something has already been painted over, then you can change the color of the blocks or 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 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 red corresponds to an apple, yellow to a pear, and orange to an orange.
We select the data we will work with, in my case these are the names of fruits. Then click "Conditional Formatting", which we will continue to use continuously. From the list, click on "Create Rule".
This window opens. Select the type at the top - "Format only cells that contain", we will also celebrate it further. Below we indicate the conditions: we have a text that contains certain words. In the last field, either click on the button and select a cell, or enter text.
The difference is that by placing a link 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 indicate currants, the rule will change accordingly, and blocks with the same text will be filled in. And if you enter apple in the field, then this particular word will be searched, and it will not depend on anything.
Here, select a fill color and click OK. To view all options, click on the “Others” button.
The rule has been created and we save it by clicking 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.
Numerical
Now let's look at the 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 indicate its type. Next we write - “Value” “greater than” “15”. You can either enter the last number manually, or specify the address of the cell from which the data will be taken. We decide on the fill, click “OK”.
Blocks containing numbers greater than the selected one are colored in.
Let's specify more rules for the selected cells - select "Rule Management".
Here, select everything as I described above, you just need to change the color and set the condition "less or equal".
When everything is ready, click "Apply" and "OK".
Everything works, the values are equal and below 15 are painted pale blue.
Here the difference between imported and sold goods would be clearly visible. Select two columns and click "Rules for highlighting cells" – "Duplicate values". Choose a suitable color. After this, if two cells next to each other are colored, it means that the fruits that were delivered were sold.
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 or for other sheets.
Select the required items in the window that opens. I will fill all values that are greater than 90 with dark green. Since I indicated the address in the last field (=$F$15), then if 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’ll create another rule, but in it I’ll highlight in light green everything that is less than or equal to 90. Don’t forget to save everything by clicking on the buttons at the bottom right.
This is 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, the cell color 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 presence of errors in the cells. Specify the 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.
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 highlight several Excel cells with different colors under a certain condition. Would you like to know how it's done? In this tutorial we will make the cell color change depending on the Excel value, and also color all the cells using search.
The fill color changes with the value
As an example, we will practice making a cell change color in a given table under a certain condition. Yes, not one, but all with a value in the range from 60 to 90. To do this, we will use the “Conditional Formatting” function.
First, select the data range that we will format.
Next, on the “Home” tab, find the “Conditional Formatting” button and select “Create Rule” from the list. 
The “Create formatting rules” window has opened. In this window, select the type of rule: “Format only cells that contain.”
Next, go to the “Change the rule description” section, where you need to specify the conditions under 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 indicate a range that if the value is from 60 to 90, a fill will be applied. Look at the screenshot how I did it. 
Of course, when working with your table, you may need to fill in completely different conditions, which you will indicate, but now we are just training.
If you have filled it out, 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.
Okay, as you can see, the “Format Cell” window has opened. Here you need to go to the “Fill” tab, where you select the one you need, 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 succeeded. Take a look at the screenshot: 
Let's color a cell a certain color if it is equal to something
Let's return to our table in its original form. And now we will change the color where the number 40 contains to red, and where the number 50 is contained to yellow. Of course, you can use the first method for this matter, but we want to know more Excel capabilities.
This time we will use the Find and Replace function.
Select the section of the table in which we will make changes. If this is the entire sheet, then there is no point in selecting it.
Now it's time to open the search window. 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, we 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 remove the “Find and Replace” window. 
When we have everything containing the number 40 selected, on the “Home” tab in the “Font” section, select the color of the cell. Ours is red. And, as you see on your screen and in my screenshot, they turned red. 
Now the same steps need to be followed to color those 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 always be aware of new articles. And also, do not forget to study other articles on this site.
