Color cells by condition. How to set the fill color of a cell. Excel highlighting cells by conditions. Simple terms

IN excel There are useful features that many do not know about. One of them is the conditional formatting feature, which automatically applies a certain format, such as red coloring cells that meet a certain condition set by the user.

Here we will tell you how set conditions in excel to then change the color of the cells to match it automatically.

Conditions for shading Excel cells in Windows

Coloring cells on conditions is very simple. Select the cells, columns or rows you want to change the color of. Then on the tab home go to menu Styles and choose Conditional Formatting > Conditions for selecting a cell. Choose the condition that suits you. Enter a reference value, open the drop-down menu and select Custom Format. Click on the tab fill and choose a cell color. Finally click OK.

Conditions for shading Excel cells on Mac

On the Mac, the operation of selecting cells is flower on conditions - exactly the same. The only difference is the wider range of options for setting conditions.

Image: © Mika Baumeister – Unsplash.com

A person is so arranged that he perceives colors better than bare numbers - it is not for nothing that the most important sections of diagrams or table rows that are worth paying attention to are usually highlighted in a contrasting color. However, marking “good” and “bad” table cells in Excel manually is not the most fun activity. I wish MS Excel could do it on its own, and even automatically change these very colors, if the situation changes ...

But he is already perfectly able to do this - you and I can only help him a little!

Let's solve such an applied problem: in our table "fruits" the weight of one or another item is indicated in kilograms. To make it easier to navigate what we lack and what, on the contrary, is in excess, we will color all values ​​less than 20 in red, and everything above 50 in green. In this case, everything that remains in this range will not be marked with color at all. And to complicate the task, let's go even further and make the color assignment dynamic - when the value in the corresponding cell changes, its color will also change.

First, select the data range, that is, the contents of the second column of the MS Excel table, and then go to the " home", where in the "Styles" group we activate the tool " Conditional Formatting", and in the drop-down list, select " Create Rule«.

In the Create Formatting Rule window that appears, select the Rule type: “Format only cells that contain”, and in the constructor below, set the parameters: “Cell value”, “Less than” and manually enter our “edge”: the number 20.

Click the "Format" button below, go to the "Fill" tab and select the red color. We press "OK".

Note: of course, you can mark cells not with their background color, but, for example, with font size or color - for this, you need to use the corresponding tabs in the same window.

Look at the table - we obviously have very few apples and tangerines left, it's time to make a new purchase!

Great, the data is already highlighted in color!

Now, by analogy, let's create another rule - only this time with the parameters "Cell Value", "Greater", 20. Set the fill color to green. Ready.

It seemed to me that this was not enough - black text on a red and green background is not readable, so I decided to decorate our rules a little and change the text color to white. To do this, open the Conditional Formatting tool, but choose not "Create Rule" but " Rule Management", below.

In the window that appears, select both rules in turn, and click the " Change", after which, in the already familiar window " Format» go to the tab « Font” and change the text color to white. Here's what I ended up with:

Now I changed not only the background of the table cells, but also the font color

Let's try to change the "bad" values ​​to "good" ones? Once and done - the color automatically changed as soon as the corresponding cells had values ​​that fall under the action of one of the rules.

We change the values ​​in our excel table ... everything works!

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!

Not all firms buy special programs to do business. Many people use MS Excel, because this one is well suited for large information bases. Practice has shown that it rarely goes beyond filling tables. The table is growing, there is more information and there is a need to quickly select only the one you need. In such a situation, the question arises of how to highlight a cell with color in Excel under a certain condition, apply color gradients to rows depending on the type or name of the supplier, make working with information fast and convenient? Read more below.

Where is conditional formatting

How to change the color of a cell in Excel depending on the value is very simple and fast. To highlight cells with color, a special function "Conditional Formatting" is provided, 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 come up with and customize the necessary algorithm. Let's look at formatting methods in detail.


Cell selection rules


With this set of tools, the following selections are made:

  • find in the table numerical values ​​\u200b\u200bthat are greater than the set value;
  • find values ​​that are less than the set value;
  • find numbers within a given interval;
  • determine the values ​​equal to the conditional number;
  • mark in the selected text fields only those that are necessary;
  • mark columns and numbers for the required date;
  • find duplicate text or number values;
  • come up with the rules that the user needs.

See how the selected text is searched: in the first field, a condition is specified, and in the second, they indicate how to highlight the result. Please note that you can choose the color of the background and text from those offered in the list. If you want to apply other shades, you can do this by going to the "Custom Format". All "Rules for selecting cells" are implemented in a similar way.


The “Other Rules” are very creatively implemented: in six scenarios, come up with those that are most convenient for work, for example, a gradient:


Set color combinations for the minimum, average and maximum values ​​- you get a gradient coloring of the values ​​​​at the output. It is comfortable to use the gradient while 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 will be able to:

  • highlight the first or last N-th number of cells with color;
  • apply formatting to a given percentage of cells;
  • select cells containing a value above or below the average in the array;
  • in the "Other rules" tab, set the necessary functionality.

Histograms

If filling a cell with color does not suit you, 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 requirements of the user.


Color scales

This tool quickly generates a gradient fill of indicators by choice from larger to smaller or vice versa. When working with it, the necessary percentages or text values ​​are set. Pre-made gradient samples are provided, but the custom approach is again implemented in "Other Rules".


Icon sets

If you are a lover of emoticons and emojis, perceive pictures better than colors, the developers provide icon sets in the corresponding tool. There are few pictures, but enough for a full-fledged work. Images are stylized as traffic lights, exclamation marks, checkmarks, crosses to mark deletion - an easy and intuitive approach.


Create, delete and manage rules

The "Create rule" function completely duplicates the "Other rules" listed above, creates a selection initially at the request of the user.

Using the "Delete rule" tab, the created scripts are deleted from the entire sheet, from the selected range of values, from the table.

The “Rules Management” tool is of interest - a kind of history of creating and changing the formatting carried out. Change collections, make rules inactive, return back, alternate the order of application. It is very convenient for working with a large amount of information.


Selecting cells by dates

To figure out how to make the color of a cell in excel based on the value of the set date, consider an example with purchase dates from suppliers in January 2019. To apply this selection, cells with established format"Date of". To do this, before entering information, select the required column, click right click mouse 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 desired 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 item "Date";
  • on the right side of the formatting, open the drop-down box with the rules;
  • select the appropriate rule (for example, the dates for the previous month are 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 the date, you can select ten values: yesterday/today/tomorrow, last/current/next week, last/current/next month, last 7 days.


Highlight a column by condition

To analyze the activities of a company using a table, let's take an example of how to change the color of a cell in excel, depending on the condition specified by the employee. As an example, let's take a table of orders for January 2019 for ten counterparties.

We need to mark in blue those suppliers from whom we bought goods for an amount greater than 100,000 rubles. To make such a selection, we use the following algorithm of actions:

  • select a 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, they analyze information, select the necessary components, check the terms and conditions of interaction between the supplier and the client. The user himself comes up with the combinations he needs.

An important role is played by color design, 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, then the informativeness of the information will be perceived almost intuitively. Screenshots from such tables will clearly look in reports and presentations.

Need to highlight duplicate values ​​in a column? 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. In Excel, the special function "Conditional Formatting" is responsible for highlighting cells with color. Highly recommend! Read more below:

To get started, on the task ribbon in the main menu, find the Styles section and click on 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 (see the picture at the beginning of the article). Next, the formatting window will appear again. Customize as you like.

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. y in a separate cell you will have written IvanovIvanIvanych, then in this column you can select duplicate values. It is important to understand that if the word order differs, Excel will consider such lines to be non-repeating.

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. Tables are perceived much better by eye, and making decisions becomes easier. After all, one of the best analyzers is our eye and, accordingly, the brain, and not a machine!

The histogram in the cell (figure below) is also extremely useful feature, to detect changes in values ​​and compare 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 it to conditional formatting, go to Cell selection rules - Text contains (see picture 2).

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 menu 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

In order to check which conditional formatting you have already set, go to Main - Conditional Formatting - Manage Rules, here you can edit the already set conditions for highlighting cells with color, as well as select the priority of the specified formatting (who is higher, that is more important, you can change it with the buttons - arrows)

Invalid conditional formatting range

Important! Conditional formatting, when used incorrectly, is often the cause of Excel slowdowns. This happens if you copy cells with color highlighting many times. Then you can have multiple conditions with color. I myself saw more than 3 thousand conditions - the file was shamelessly slowed down. It can also slow down when a range is set as in the picture above, it's 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;)

I'm glad that I learned about cell coloring by conditions in Excel.

Share our article on your social networks: