Color a cell based on a condition or formula. Filling cells depending on the value in Microsoft Excel How to highlight a row with color in Excel

If a value in a cell meets a user-defined condition, you can use it to highlight that cell (for example, change its background). In this article we will go further - we will select the entire table row containing this cell.

Let in range A6:C16 There is a table with a list of works, deadlines and completion status (see example file).

Task 1 - text values

It is necessary to highlight the line containing the work of a certain status. For example, if the work has not started, then the line will be highlighted in red, if the work has not yet been completed, then in gray, and if completed, then in green. We will select lines using rules.

Solution1

Let's create a small table with job statuses in the range E6:E9 .

Select a range of cells A7:C17 , containing a list of works, and install it through the menu Home/ Fill color the fill background is red (we assume that all works are initially in the status Not started).

Make sure the range of cells is selected A7:C17 (A7 must be ). Call the menu command Conditional formatting / Create a rule / Use a formula to determine which cells to format .

• in field " Format values ​​for which the following formula is true"you need to enter =$C7=$E$8 (in the cell E8 the value is found In progress). Please note the use of ;
• press the button Format ;
• select tab Fill ;
• choose gray color ;
• Click OK.

ATTENTION: Once again I draw attention to the formula =$C7=$E$8 . Typically users enter =$C$7=$E$8 , i.e. enter an extra dollar symbol.

You need to do similar steps to highlight work in the status Completed. The formula in this case will look like =$C7=$E$9 and set the fill color to green.

As a result, our table will take the following form.

To quickly extend Conditional Formatting rules to a new row in a table, select the cells new line (A17:C17 ) and press . The rules will be copied to the line 17 tables.

Task2 - Dates

Let's assume that a log of employee visits is kept scientific conferences(cm. sample sheet file Dates).

Unfortunately, the Date of Visit column is not sorted and you need to highlight the date of the first and last visit of each employee. For example, employee Kozlov went to the conference for the first time on July 24, 2009, and the last time on July 18, 2015.

First, let's create a formula for conditional formatting in columns B and E. If the formula returns TRUE, then the corresponding row will be highlighted, if FALSE, then it will not.

In column D created = MAX(($A7=$A$7:$A$16)*$B$7:$B$16)=$B7, which defines the maximum date for a specific employee.

Note: If you need to determine the maximum date regardless of the employee, then the formula will be significantly simplified = $B7=MAX($B$7:$B$16) and the array formula will not be needed.

Now let's select all the table cells without a header and create a rule. Let's copy the formula into the rule (you don't need to enter it as an array formula!).

Now let's assume that the column with dates has been sorted and you want to select rows whose visit dates fall within a certain range.

To do this, use the formula =AND($B23>$E$22,$B23

For cells E22 And E23 with boundary dates (highlighted in yellow) used $E$22 and $E$23. Because the reference to them should not change in the UV rules for all table cells.

For cell B22 mixed addressing $B23 is used, i.e. the reference to column B should not change (for this there is a $ sign before B), but the reference to the row should change depending on the table row (otherwise all date values ​​will be compared with the date from B23 ).

Thus, the UV rule for example for a cell A27 will look =And($B27>$E$22;$B27 , i.e. A27 will be highlighted because in this line the date from B27 falls within the specified range (for cells from column A, the selection will still be made depending on the contents of column B from the same row - this is the “magic” of mixed addressing $B23).

And for the cell B31 the UV rule would look like =And($B31>$E$22;$B31 , i.e. B31 will not be highlighted, because this line contains the date from B31 does not fall within the specified range.

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

In one of the previous articles, we discussed how to change the color of a cell depending on its value. This time we will talk about how to highlight an entire row in Excel 2010 and 2013 depending on the value of one cell, and also reveal several 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

Let's say we have a table of company orders like this:

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

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

How to create multiple conditional formatting rules with a given priority

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

In order for both rules we created to work simultaneously, we need to place them in the desired priority.

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

To simplify order control, we can highlight order lines in our table with different colors with different delivery statuses, information about which is contained in the column Delivery:

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

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

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

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

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

In this case it is convenient to use the function SEARCH(SEARCH) and to find a partial match write down 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 row; condition " >0 ” means that the formatting rule will be applied if the specified text (in our case, “Due in”) is found.

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

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

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

You need to be very careful when using such a formula and check whether there is any data in the cells of the key column that begins with a space. Otherwise, you can rack your brain for a long time, trying to understand why the formula doesn’t 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 need to change the color of the cells, and use the formulas described above.

For example, we can configure our three rules to highlight only the 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 to change the color of a row

If you want to highlight rows with the same color when one of several different values ​​appears, then instead of creating multiple 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 expected to 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 highlight orders with a quantity of goods of at least 5, but not more than 10 (the 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")

Clue: Now that you've learned how to color cells differently depending on the values ​​they contain, you might want to find out how many cells are highlighted in a particular color and calculate the sum of the values ​​in those cells. I want to please you, this action can also be done automatically, and we will show the solution to this problem in an article devoted to the question How to calculate the quantity, sum 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 changes in these values. If you are looking for something else for your data, let us know, and together we will definitely come up with something.

Hi all. Today’s post I want to devote to ways to select cells in Microsoft Excel. If you already know how to highlight cells, skim through the text; perhaps you are not aware of some of the program’s features and you are not taking advantage of them.

And so, selecting groups of cells is necessary in order to perform some common action with them: or, etc. How can you select cells in Excel? Let's figure it out!

How to select all cells in an Excel worksheet

I will immediately answer the most asked question on the topic of the article. How to select all the cells of a sheet at once? I offer you two methods, whichever you like best, use the one:

Selecting a rectangular range of cells

Let's say you need to select a rectangular group of cells to format them for your data type. I can offer 5 ways of highlighting, and you choose which one is appropriate for you at the moment:

1. Mouse selection. The most common way is to hold the left mouse button inside one of the cells (not on the frame) and stretch the selection in the desired direction along the rows and columns
2. By pressing the key Shift . Place the cursor in one of the corner cells of the future selection. Hold Shift and select the area with the arrow keys. When you're done, release Shift.
3. Press the key F8. The command is similar to the previous one, but the key does not need to be pressed. Place the cursor in one of the corner cells of the array, press F8 to enable selection mode. Use the arrow keys to expand the selection, or click the mouse in the opposite corner of the future selection. When the array is selected, press F8 again to exit this mode.

   Another way is to place the cursor in the upper left cell of the range, hold Shift and click on the lower right cell (see figure)

4. Enter in the "Name" field cell address or range of cells (separated by colon) and press Enter
   
   
5. Run the command(on the keyboard – F5, or Ctrl+G). In the window that opens, in the “Link” field, enter the address of the cell or range of cells separated by a colon. Click OK

Select rows and columns completely

If you need to select an entire column or row, here are a few options:

• Click the mouse on the row number or column name. If you need to select several adjacent lines, hold down the left mouse button on the line number and drag the selection over the lines you want to select. We do the same with columns
• Place the cursor in any cell of the row that you want to select and press combination Shift+Space. To select a column, use the combination Ctrl+Space
• To select non-adjacent rows and columns − pinchCtrl and click by column names and row numbers. Once completed, release Ctrl.

Selecting non-adjacent ranges

If you need to select several cells that are not adjacent, do it using one of the suggested methods:

Selecting on multiple sheets at once

If the worksheet of the same document contains the same tables with different data, we can perform operations on all tabs at the same time. This saves time and reduces the chance of error. For example, we need the headers of each table on several sheets. There is no need to do this on each tab separately - select all the sheets and do everything in one go.

To select the same area on multiple sheets, first select the sheets you want. Activate the first sheet from the list, hold Ctrl and click on the labels of all sheets to select it.

When all the necessary sheets are selected, you can perform operations. Please note that in the name line next to the file name the inscription “ [Group]" This means that Excel is ready to process a group of sheets.

Next, on the active sheet, select the required ranges, make changes, fill out general data and formulas. Everything you do will be applied to all selected sheets. After completing batch processing, do not forget to deselect the sheets. To do this, right-click on any grouped sheet shortcut and select from the context menu Ungroup.

Conditional cell selection

Microsoft Excel can highlight a group of cells based on their contents. Not all users are aware of this feature, although its use can be very useful.

Run the command Home – Editing – Find and Select – Go. In the window that appears, click Select.... The Select Group of Cells dialog box appears with the following selection options:

Selecting cells using the search box

To open the value search window, run Home – Editing – Find and Select – Find(or press the key combination Ctrl+F). Find All appears. A list of cells containing the selected data will appear at the bottom of the window. Select one or more cells (holding Ctrl) in the list so that Excel selects them. To select all found cells, select one of them and press the combination Ctrl+A.

You can use special characters to search:

• "?" - any one symbol
• “*” - any number of any characters

For example, to find all cells that begin with the letter “A”, enter “A*” in the search.

That's all about selecting cells in MS Excel, and I want to devote the next post to . As always, this article will contain many useful things that will definitely come in handy in your work. So read it, you won’t regret it!

By the way, I look forward to your questions regarding this article in the comments!

Conditional formatting in Excel allows you to select not only a cell, but also the entire row at once.To select an entire row in a large table at once, you need to set our table formatting conditions in the table.What is conditional formatting, read the article “ Conditional Formatting in Excel."
We have a table with visitor data. We need to highlight visitors who have completed a course of study, treatment, work, etc. The table is like this. We create a small auxiliary table in separate cells. For example, let's make a table in cells G2, G3 and G4.
If we write in the “Status” column - “Completed” or “1st stage”, “2nd stage”, the entire line will be colored.
Instead of an auxiliary table, you can make a “drop-down list” in an auxiliary cell.
Now we set conditional formatting to the table cells.
Select the entire table with data and additionally at the bottom of the table another empty line. We will copy an empty table row if we need to add rows to the table. The formatting conditions will also be copied immediately.
So, we have selected the range table A2:E7.
On the “Home” tab, click the “Conditional Formatting” button and select the “Create Rules” function.
Click on the line “Compare table columns to determine formatted cells.”In the "Format" line we write the formula. =$E2=$G$2
note– the reference to cell E2 is mixed.
Press the button with “0”. Here we select and set the fill color of the line and the font color of this line. We chose green as the cell fill color.Click “OK” in all three dialog boxes. All.
Now we write in the table in the “Status” column - “Completed” and our line turns green, which we set in the conditional formatting rules.
Attention! In the cells of the “Status” column, write words in the same way as they are written in the auxiliary table. For example, we have the word “Completed” written with a capital letter. If we write the word “completed” with a small letter in a column cell, then the conditional formatting will not work. Therefore, it is better to set a drop-down list in the “Status” column.How to install a drop-down list, see the article " Dropdown list in Excel." It turned out like this.

Need to highlight duplicate values ​​in a column? Do I need to select the first 5 maximum cells? Do you need to make a thermal scale for clarity (the color changes depending on the increase/decrease in the cell value)? In Excel, highlighting cells by color can be done very quickly and easily. The special “Conditional Formatting” function is responsible for highlighting cells with color. Highly recommend! Read on for more details:

I described the main features at the beginning of the article, but in fact there are a lot of them. Read more about the most useful

To begin, 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 ones:

Excel highlighting cells by color according to conditions. Simple conditions

To do this, go to Cell selection rules. If, for example, you need to select all cells greater than 100, click the More button. In the window:

By default, the conditions are suggested to be highlighted in red, but you can set the desired cell formatting by clicking in the right window and selecting the required option.

Highlighting duplicate values, incl. across multiple columns

To select all duplicate values, select the corresponding Duplicate Values ​​menu.

What should you do if you need to find repetitions in two or more columns, for example when your full name is in different columns? Make another column and combine the values ​​with the formula =, i.e. in a separate cell you will have IvanovIvanIvanovich written. Using this column, you can easily identify duplicate values. It is important to understand that if the word order is different, Excel will consider such rows to be non-repeating (for example, IvanIvanovichIvanov).

Highlighting the first/last values ​​in color. Again conditional formatting

To do this, go to the Rules for selecting the first and last cells section and select the desired item. In addition to the fact that you can highlight the first/last values ​​(including by percentage), you can use the ability to highlight data above and below the average (I use it even more often). Very convenient for viewing results that differ from the norm or average!

Building a thermal diagram and histogram

A cool feature for data visualization is the thermal/temperature chart. The bottom line is that, depending on the value in a column or row, the cell is highlighted with a certain shade of color, the more, the redder, for example. Tables are perceived much better by eye, and decision making becomes easier. After all, one of the best analyzers is often our eye, and therefore our brain, and not a machine!

The histogram in the cell (blue in the figure below) is also an extremely useful function for identifying changes in values ​​and comparing them.

Highlight cells containing specific text

Very often you need to find cells that contain a certain set of characters, you can, of course, use the = function, but it’s easier and faster to apply conditional formatting, go through - Rules for selecting cells - Text contains

Very useful when working with text. An example when you have the full names of employees written in a column, but you need to select all the Ivanovs’ colleagues. Select the cells, go to the desired item and select the text containing the text Ivanov, then filter the table by color

Excel color highlighting. Filter by color

In addition to the above options, you can filter selected cells by color using a regular filter. To my surprise, very few people know about this - apparently echoes of the 2003 version - this feature was not there.

Checking formatting conditions

To check what conditional formatting you have set, go to Home - Conditional Formatting - Manage Rules. Here you can edit the already specified conditions, the range of application, and also select the priority of the specified formatting (whoever is higher is more important, you can change it using the arrow buttons).

Invalid conditional formatting range

Important! Conditional formatting, when used incorrectly, is often the cause of strong . Double formatting occurs, for example, if you copy cells with color highlighting many times. Then you will have many conditions with color. I myself saw more than 3 thousand conditions - the file slowed down horribly. Also, the file may slow down when a range is specified as in the picture above; it is better to specify A:A for the entire range.

Read more about Excel brakes and their causes. This article has helped more than one hundred people;)

I hope this was helpful, no goodbyes!