A collection of formulas for conditional formatting. Formatting cells

Youtube

In this tutorial, we'll look at the basics of using conditional formatting in Excel.

With its help, we can highlight table values according to specified criteria, search for duplicates, and also graphically “highlight” important information.

Conditional Formatting Basics in Excel

Using conditional formatting we can:

color the values
change font
set border format

It can be applied to one or several cells, rows and columns. We can configure the format using conditions. Next, we will look at how to do this in practice.

Where is conditional formatting in Excel?

The “Conditional Formatting” button is located on the toolbar, on the “Home” tab:

How to do conditional formatting in Excel?

When applying conditional formatting, the system needs to set two settings:

Which cells do you want to format;
Under what conditions will the format be assigned?

Below, we'll look at how to apply conditional formatting. Let's imagine that we have a table with the dynamics of the dollar exchange rate in rubles over the year. Our task is to highlight in red those data in which the exchange rate decreased in the previous month. So, let's follow these steps:

In the data table, select the range for which we want to apply color highlighting:

Let’s go to the “Home” tab on the toolbar and click on the “Conditional Formatting” item. In the drop-down list you will see several format types to choose from:
- Selection rules
- Rules for selecting the first and last values
- Histograms
- Color scales
- Icon Sets
In our example, we want to highlight data with a negative value. To do this, select the type “Cell selection rules” => “Less”:

The following conditions are also available:

Values greater than or equal to some value;
Highlight text containing certain letters or words;
Highlight duplicates;
Highlight specific dates.

In the pop-up window, in the “Format cells that are LESS than” field, specify the value “0”, since we need to highlight negative values with color. In the drop-down list on the right, select the format that meets the conditions:

To assign a format, you can use pre-configured color palettes, or create your own palette. To do this, click on the item:

In the format pop-up window, specify:
- fill color
- font color
- font
- cell borders

When you complete the settings, click the “OK” button.

Below is an example of a table using conditional formatting based on the parameters we specified. Data with negative values are highlighted in red:

How to create a rule

If the pre-configured conditions are not suitable, you can create your own rules. To configure we will do the following steps:

Let's select the data range. Click on the “Conditional Formatting” item in the toolbar. In the drop-down list, select “New Rule”:

In the pop-up window we need to select the type of rule to apply. In our example, we will use the “Format only cells that contain” type. After this, we will set a condition to select data whose values are greater than “57” but less than “59”:

Click on the “Format” button and set the format, as we did in the example above. Click “OK” button:

Conditional formatting based on the value of another cell

In the examples above, we set the format for the cells based on their own values. In Excel, it is possible to set a format based on values from other cells. For example, in a table with dollar exchange rate data, we can highlight cells according to the rule. If the dollar exchange rate is lower than in the previous month, then the exchange rate value for the current month will be highlighted in color.

To create a condition based on the value of another cell, follow these steps:

Select the first cell to assign a rule. Click on the “Conditional Formatting” item on the toolbar. Let’s select the “Less” condition.
In the pop-up window, indicate the link to the cell with which this cell will be compared. Select the format. Click the “OK” button.

Using the left mouse button again, select the cell to which we assigned the format. Click on “Conditional Formatting”. Select “Manage Rules” from the drop-down menu => click on the “Edit Rule” button:

In the field on the left of the pop-up window, clear the link from the “$” sign. Click the “OK” button and then the “Apply” button.

Now we need to assign the customized format to the remaining cells of the table. To do this, select the cell with the assigned format, then in the upper left corner of the toolbar, click on the “roller” and assign the format to the remaining cells:

In the screenshot below, the data in which the exchange rate became lower compared to the previous period is highlighted in color:

How to apply multiple conditional formatting rules to one cell

It is possible to apply multiple rules to one cell.

For example, in a table with a weather forecast, we want to color the temperature indicators in different colors. Conditions for color highlighting: if the temperature is above 10 degrees - green, if above 20 degrees - yellow, if above 30 degrees - red.

To apply multiple conditions to one cell, follow these steps:

Let's select the range with the data to which we want to apply conditional formatting => click on the “Conditional Formatting” item on the toolbar => select the selection condition “More than...” and indicate the first condition (if more than 10, then green fill). We repeat the same steps for each of the conditions (more than 20 and more than 30). Despite the fact that we applied three rules, the data in the table is colored green:

Looking at the dry numbers in the tables, it is difficult to grasp the overall picture they represent at first glance. However, Microsoft Excel has a graphical visualization tool that can be used to visualize the data contained in the tables. This allows you to absorb information more easily and quickly. This tool is called conditional formatting. Let's figure out how to use conditional formatting in Microsoft Excel.

In order to format a specific area of cells, you need to select this area (most often a column), and being in the “Home” tab, click on the “Conditional Formatting” button, which is located on the ribbon in the “Styles” tool block.

After this, the conditional formatting menu opens. There are three main types of formatting:

Histograms;
Digital scales;
Icons.

In order to perform conditional formatting in the form of a histogram, select the column with the data and click on the corresponding menu item. As you can see, there are several types of histograms with gradient and solid fill to choose from. Choose the one that, in your opinion, best matches the style and content of the table.

As you can see, histograms appear in the selected cells of the column. The larger the numerical value in the cells, the longer the histogram. In addition, in versions of Excel 2010, 2013 and 2016, it is possible to correctly display negative values in the histogram. But the 2007 version does not have this option.

When used instead of a color scale histogram, it is also possible to select different options for this tool. In this case, as a rule, the larger the value located in the cell, the more saturated the color of the scale.

The most interesting and complex tool among this set of formatting features are icons. There are four main groups of icons: directions, shapes, indicators, and scores. Each option the user selects involves using different icons when evaluating the cell's contents. The entire selected area is scanned by Excel and all cell values are divided into parts according to the values specified in them. The largest values are marked with green icons, those in the middle range are marked with yellow icons, and quantities in the smallest third are marked with red icons.

When choosing arrows as icons, in addition to color design, signaling in the form of directions is also used. So, an arrow pointing up is applied to large values, left - to medium values, down - to small values. When using figures, the largest values are marked with a circle, medium ones with a triangle, and small ones with a diamond.

Cell selection rules

By default, a rule is used in which all cells of the selected fragment are designated with a certain color or icon, according to the values located in them. But, using the menu that we already talked about above, you can apply other designation rules.

Click on the menu item “Rules for selecting cells”. As you can see, there are seven basic rules:

More;
Less;
Equals;
Between;
Date of;
Duplicate values.

Let's look at the application of these actions using examples. Select a range of cells and click on the “More…” item.

A window opens in which you need to set values greater than which number will be highlighted. This is done in the “Format cells that are larger” field. By default, the average value of the range is automatically entered here, but you can set any other value, or specify the address of the cell that contains this number. The latter option is suitable for dynamic tables in which the data is constantly changing, or for a cell where a formula is applied. For example, we set the value to 20000.

In the next field, you need to decide how the cells will be highlighted: light red fill and dark red color (default); yellow fill and dark yellow text; red text, etc. In addition, there is a custom format.

When you click on this item, a window opens in which you can edit the selections in almost any way you like, using different font options, fills, and borders.

After we have decided on the values in the selection rules settings window, click on the “OK” button.

As you can see, the cells are selected according to the established rule.

Using the same principle, values are highlighted when applying the Less Than, Between, and Equal rules. Only in the first case, cells less than the value you set are selected; in the second case, the interval of numbers is set, the cells with which will be highlighted; in the third case, a specific number is specified, and only cells containing it will be highlighted.

The "Text Contains" selection rule primarily applies to text format cells. In the rule setup window, you should specify a word, part of a word, or a sequential set of words, when found, the corresponding cells will be highlighted in the way you set.

The Date rule applies to cells that contain date formatted values. At the same time, in the settings you can set the selection of cells by when the event occurred or will occur: today, yesterday, tomorrow, over the last 7 days, etc.

By applying the “Repeating values” rule, you can configure the selection of cells according to the compliance of the data placed in them with one of the criteria: whether it is repeated data or unique.

Rules for selecting the first and last values

In addition, in the conditional formatting menu there is another interesting item - “Rules for selecting the first and last values”. Here you can set the selection of only the largest or smallest values in a range of cells. In this case, you can use selection, both by ordinal values and by percentage. There are the following selection criteria, which are indicated in the corresponding menu items:

First 10 elements;
First 10%;
Last 10 elements;
Last 10%;
Above average;
Below the average.

But, after you clicked on the appropriate item, you can change the rules a little. A window opens in which you select the type of selection, and, if desired, you can set a different selection boundary. For example, having clicked on the “First 10 elements” item, in the window that opens, in the “Format first cells” field we replaced the number 10 with 7. Thus, after clicking on the “OK” button, not the 10 largest values will be highlighted, but only 7.

Creating Rules

Above we talked about the rules that are already installed in Excel, and the user can simply select any of them. But, in addition, if desired, the user can create his own rules.

To do this, you need to click in any subsection of the conditional formatting menu on the “Other rules...” item located at the very bottom of the list.” Or click on the “Create rule...” item, which is located at the bottom of the main conditional formatting menu.

A window opens where you need to select one of six types of rules:

Format all cells based on their values;
Format only cells that contain;
Format only the first and last values;
Format only values that are above or below the average;
Format only unique or duplicate values;
Use a formula to determine which cells to format.

According to the selected type of rules, in the lower part of the window you need to configure the change in the description of the rules by setting values, intervals and other values that we have already discussed below. Only in this case, setting these values will be more flexible. Here you can immediately set, by changing the font, borders and fill, exactly how the selection will look. After all the settings have been completed, you need to click on the “OK” button to save the changes.

Rule management

In Excel, you can apply multiple rules to the same range of cells, but only the last rule entered will be displayed on the screen. In order to regulate the execution of various rules regarding a certain range of cells, you need to select this range, and in the main conditional formatting menu, go to the item manage rules.

A window opens showing all the rules that apply to the selected range of cells. Rules are applied from top to bottom as they are placed in a list. Thus, if the rules contradict each other, then in fact only the most recent of them is displayed on the screen.

To change the rules, there are buttons in the form of up and down arrows. In order for a rule to be displayed on the screen, you need to select it and click on the button in the form of a downward arrow until the rule occupies the very last line in the list.

There is another option. We need to check the box in the column called “Stop if true” next to the rule we need. Thus, going through the rules from top to bottom, the program will stop exactly at the rule next to which this mark appears, and will not go lower, which means that this particular rule will actually be executed.

In the same window there are buttons for creating and changing the selected rule. After clicking on these buttons, the windows for creating and changing rules, which we have already discussed above, are launched.

In order to delete a rule, you need to select it and click on the “Delete Rule” button.

You can also delete rules through the main conditional formatting menu. To do this, click on the “Delete rules” item. A submenu opens where you can choose one of the deletion options: either delete rules only in the selected range of cells, or delete absolutely all rules that are on the open Excel sheet.

As you can see, conditional formatting is a very powerful tool for visualizing data in a table. With its help, you can customize the table in such a way that the general information on it will be absorbed by the user at first glance. In addition, conditional formatting makes the document more aesthetically pleasing.

Conditional formatting is a convenient tool for analyzing data and visually presenting results. Knowing how to use it will save a lot of time and effort. All you have to do is take a quick look at the document and you have received the necessary information.

How to do conditional formatting in Excel

The Conditional Formatting tool is located on the main page in the Styles section.

When you click on the arrow on the right, a menu for formatting conditions opens.

Let's compare the numeric values in an Excel range with a numeric constant. The most commonly used rules are “greater than / less than / equal to / between”. Therefore, they are included in the “Rules for selecting cells” menu.

Let's enter a series of numbers into the range A1:A11:

Let's select a range of values. Open the “Conditional Formatting” menu. Select “Cell selection rules”. Let's set a condition, for example, “greater than”.

Let's enter the number 15 in the left field. In the right field - a way to select values that meet the specified condition: “more than 15”. The result is immediately visible:

Exit the menu by pressing the OK button.

Conditional formatting based on the value of another cell

Let's compare the values of the range A1:A11 with the number in cell B2. Let's enter the number 20 into it.

Select the source range and open the “Conditional Formatting” tool window (abbreviated below as “UV”). For this example, we apply the “less than” condition (“Rules for selecting cells” - “Less than”).

The formatting result is immediately visible on the Excel sheet.

Values in the range A1:A11 that are less than the value in cell B2 are filled with the selected background.

Let's set a formatting condition: compare cell values in different ranges and show the same ones. We will compare column A1:A11 with column B1:B11.

Select the original range (A1:A11). Click “UV” - “Rules for selecting cells” - “Equals”. In the left field is a link to cell B1. The link must be MIXED or RELATIVE!, and not absolute.

The program compared each value in column A with the corresponding value in column B. Identical values are highlighted in color.

Attention! When using relative links, you need to keep track of which cell was active when you called the Conditional Format tool. Since the link in the condition is “attached” to the active cell.

In our example, at the time the tool was called, cell A1 was active. Link $B1. Therefore, Excel compares the value of cell A1 with the value of B1. If we selected the column not from top to bottom, but from bottom to top, then cell A11 would be active. And the program would compare B1 with A11.

Compare:

To ensure that the Conditional Formatting tool performs its task correctly, keep an eye on this point.

You can check the correctness of the specified condition as follows:

Select the first cell in the range with conditional formatting.
Open the tool menu, click "Manage Rules".

In the window that opens, you can see which rule applies to which range.

Conditional formatting - multiple conditions

The initial range is A1:A11. It is necessary to highlight in red the numbers that are greater than 6. In green - greater than 10. Yellow - greater than 20.

1 way. Select the range A1:A11. Apply Conditional Formatting to it. “Rules for highlighting cells” - “More”. In the left field we enter the number 6. In the right field - “red fill”. OK. Again, select the range A1:A11. We set the formatting condition to “greater than 10” and the method to “fill green”. Using the same principle, we fill in yellow numbers greater than 20.
Method 2. In the “Conditional Formatting” tool menu, select “Create Rule”.

Fill in the formatting parameters according to the first condition:

Click OK. We set the second and third formatting conditions in the same way.

Please note that some cell values correspond to two or more conditions at the same time. The processing priority depends on the order in which the rules are listed in the “Manager” - “Rule Management”.

That is, to the number 24, which is simultaneously greater than 6, 10 and 20, the condition “=$A1>20” (first in the list) is applied.

Conditional date formatting in Excel

Select the range with dates.

Let's apply "UV" - "Date" to it.

A list of available conditions (rules) appears in the window that opens:

Select the one you need (for example, for the last 7 days) and click OK.

The cells with the dates of the last week are highlighted in red (the date of writing the article is 02/02/2016).

Conditional formatting in Excel using formulas

If standard rules are not enough, the user can apply a formula. Almost any: the possibilities of this tool are endless. Let's consider a simple option.

There is a column with numbers. It is necessary to highlight cells with even numbers. We use the formula: =REMAT($A1,2)=0.

Select the range with numbers - open the “Conditional Formatting” menu. Select “Create Rule”. Click “Use a formula to determine the cells to format.” Fill in as follows:

To close the window and display the result – OK.

Conditional formatting of a string by cell value

Task: highlight a row containing a cell with a certain value.

Example table:

It is necessary to highlight in red the information on the project that is still in progress (“P”). Green – completed (“Z”).

Select the range with the table values. Click “UV” - “Create Rule”. The rule type is a formula. Let's use the IF function.

The procedure for filling out the conditions for formatting “completed projects”:

Similarly, we set formatting rules for unfinished projects.

In the “Dispatcher” the conditions look like this:

We get the result:

When formatting options are specified for the entire range, the condition will be executed at the same time as the cells are filled. For example, let’s “complete” Dimitrova’s project for January 28 – we’ll put “Z” instead of “R”.

“Coloring” has automatically changed. Using standard Excel tools it would take a long time to achieve such results.

Every manager, accountant, or even the most ordinary user has more than once encountered the excellent capabilities of Excel 2010, which allows you to quickly and conveniently work with tables. This version of the program features advanced conditional formatting capabilities for pivot tables, which improves the entire workflow.

We have visualization functionality that provides the ability to use histograms, various icons and various color scales in cells. Now you can easily create a window in the style of a control console, which makes it easier to find information. Also, now this type of formatting has begun to be successfully applied to pivot tables, or more precisely, to their structure (and not just to the data). The method described below is suitable even for those who are simply going to buy a new washing machine and choose a model.

Here is a simple example of a pivot table that describes sales volumes of a product by region:

Now, based on this data, we will create a graphical report of sales volumes for each time period to make the information easier to perceive. Naturally, you can use a pivot chart, but conditional formatting in Excel 2010 also works well.

The easiest option is to use color scales. To do this, select the “Sales Volume” field, covering all periods. All that remains is to open the “Home” tab, where we click the “Conditional Formatting” button (if you happen to be using the English version, then this function is called “Conditional Formatting”). Hover over “Histograms.”

The available set of gradient and solid fills, in essence, resembles a standard horizontal histogram. And if we now use data filtering for any graph within the scope of the filter, we will notice a dynamic change in the histograms.

Excel 2010 already has a significant number of completely ready-made formatting scripts that significantly save working time. To create the histograms described above, by the way, the program uses an algorithm that independently determines the maximum and minimum values of the range of selected cells and only then formats them based on the processed information.

So here are ready-made scripts that can help in most situations:

First 10 elements;

Last 10 elements;

First 10%;

Last 10%;

Above average;

Less than average.

Removing already used conditional formatting in Excel 2010 occurs according to the following scheme: in the pivot table, go to the “Home” tab, click on “Conditional Formatting”, then “Styles”, and in the drop-down menu use the command “Delete rules” - “Remove rules from” this pivot table" (in the English version - "Clear Rules" and "Clear Rules from this PivotTable").

However, you can easily create your own formatting rules.

This table is a complicated version of the first one, so let’s move straight to the example. Let's track sales and revenue per hour. We'll use Excel 2010's conditional formatting to speed up our search for matches and differences. Select “Sales Volume”. Next, according to the standard procedure, we activate the script (“Home” - “Conditional Formatting”), but select not the ready-made option, but the “Create Rule” (or “New Rule”) function.

It is here that you can determine the cells to which conditional formatting will be applied in Excel 2010, the type of rule used and, in fact, the formatting parameters. First the cells are specified, and there is a fairly simple choice:

Selected Cells;

Included in the “Sales_Amount” Values column, including subtotals and grand totals. This option, by the way, is well suited for analyzing those data that require determining the average, percentage, or other values that are one way or another different levels of the same value;

Included in the “Sales Volume” category only for the “Sales Market” (“All Cells Showing “Sales_Amount” Values for “Market””). This option completely excludes general and subtotals, which is convenient for analyzing some individual values.

Note that the “Sales Volume” and “Sales Market” commands when creating rules change depending on the available worksheet.

According to our example, the most profitable option is “3”, so this option is used:

When choosing a rule (section “Select a Rule” or “Select a Rule Type”), we indicate exactly the one that meets our requirements.

It could be:

- “Format All Cells Based on Their Values”. Used to format cells that match the value range being used. It is best suited for identifying a wide variety of deviations if you have to work with a huge set of data.

- “Format Only Cells That Contain”. Formats cells that meet the appropriate conditions. In this case, the values of formatted cells are not compared with regular ones. Used to compare the overall data set with a previously specified characteristic.

- “Format Only Top or Bottom Ranked Values”.

- “Format Only Values That Are Above or Below the Average”.

- “Use a Formula to Determine Which Cells to Format”. Here, the conditional formatting conditions are based on the formula specified by the user himself. If the cell value (from those substituted into the formula) comes with the value “true”, then formatting is applied to the cell. If set to false, no formatting is applied.

The use of histograms, icon sets, and color scales is only possible when the selected cells are formatted based on the values entered in them. To do this, set the first switch to “Format All Cells Based on Their Values”. You can use a set of icons to indicate problem areas, which also works well for this scenario.

Well, it remains to determine the exact parameters of our formatting. This is where the “Edit the Ruie Description” section comes in handy. To add icons to the problematic cells, we use the Format Style drop-down menu and select Icon Sets.

In the “Icon Style” list, all you have to do is select the “3 characters” value. This is good if the existing table cannot be completely colored. As a result, we should have the following in the window:

With this configuration, Excel will automatically add icons to cells, following the function:

>=67, >=33 and

The simplest options for conditional formatting

After this, the conditional formatting menu opens. There are three main types of formatting:

Histograms;
Digital scales;
Icons.

Cell selection rules

Click on the menu item “Rules for selecting cells”. As you can see, there are seven basic rules:

More;
Less;
Equals;
Between;
Date of;
Duplicate values.

Let's look at the application of these actions using examples. Select a range of cells and click on the “More…” item.

When you click on this item, a window opens in which you can edit the selections in almost any way you like, using different font options, fills, and borders.

After we have decided on the values in the selection rules settings window, click on the “OK” button.

As you can see, the cells are selected according to the established rule.

Rules for selecting the first and last values

First 10 elements;
First 10%;
Last 10 elements;
Last 10%;
Above average;
Below the average.

Creating Rules

Above we talked about the rules that are already installed in Excel, and the user can simply select any of them. But, in addition, if desired, the user can create his own rules.

A window opens where you need to select one of six types of rules:

Format all cells based on their values;
Format only cells that contain;
Format only the first and last values;
Format only values that are above or below the average;
Format only unique or duplicate values;
Use a formula to determine which cells to format.

Rule management

In order to delete a rule, you need to select it and click on the “Delete Rule” button.

We are glad that we were able to help you solve the problem.

Ask your question in the comments, describing the essence of the problem in detail. Our specialists will try to answer as quickly as possible.

Working with VB project (12)
Conditional Formatting (5)
Lists and ranges (5)
Macros (VBA procedures) (63)
Miscellaneous (39)
Excel bugs and glitches (4)

Collection of formulas for conditional formatting

This article contains a list of formulas that can be used in conditional formatting of cells specified using the formula:

Excel 2003 : Format-- formula;
Excel 2007-2010: tab Home-Conditional formatting-Create a rule (New rule)-Use a formula to determine which cells to format (Use a formula to determine which cells to format)

You can read more about conditional formatting in the article: Basic concepts of conditional formatting and how to create it

All conditions are given for the range A1:A20. This means that for the condition to be executed correctly, it is necessary to select the range A1:A20 (there may be more columns), starting from cell A1, and then assign the condition.
If you need to select not from the first row, but, say, from the 4th, then you will need to select the range A4:A20 and in the formula for the condition indicate the first cell of the selected range - A4 - as a criterion.

If you need to format not only a specific cell that satisfies a condition, but the entire row of a table based on a cell in one column, then before setting the rule you need to select the entire table whose rows need to be formatted, and fix the link to the column with the criterion:
= $A1 =MAX($A$1:$A$20)
with the range A1:F20 (the range for applying conditional formatting) selected, the row A7:F7 will be selected if cell A7 contains the maximum number.

You can also apply it not to one specific column, but to the entire range. But in this case, you need to know the principle of shifting references in formulas so that the conditions are applied exactly to the desired cells. For example, if you set the condition for the range B1:D10 as a formula: =B1

NUMERIC VALUES

Selecting cells with numbers:
=ENUMBER(A1)
Selecting cells with numbers, but not counting zeros:
=AND(ISNUMBER(A1), A1 0)
Selecting rows with a value greater than 0:
= A1 >0
Selecting rows with a value in the range from 3 to 10:
=AND(A1 >=3; A1
Selecting cells with the maximum value in the range $A$1:$A$20:
= A1 =MAX($A$1:$A$20)
Selecting cells with the minimum value in the range $A$1:$A$20:
=AND(ISNUMBER(A1), A1 =MIN($A$1:$A$20))
Highlights the cell with the second highest number in the range $A$1:$A$20. Those. from the numbers 1,2,3,4,5,6,7 the number 6 will be selected:
= A1 =LARGEST($A$1:$A$20 ;2)

TEXT VALUES

Select cells with any text:
=ETEXT(A1)
Selecting cells with text Bottom line:
= A1 = "Total"
Select cells containing text Bottom line:
=COUNTIF(A1 ;"*total*")
=NOT(EOSH(SEARCH("total";A1 )))
Select cells that do not contain text Bottom line:
=COUNTIF(A1 ;"*total*")=0
=EOSH(SEARCH("total";A1 ))
Select cells whose text begins with a word Bottom line:
=LEFTSIMB(A1,4)="Total"
Select cells whose text ends with a word Bottom line:
=RIGHT(A1,4)="Total"

DATE TIME

Selecting the current date:
=A1 =TODAY()
Selecting a cell with a date greater than the current one:
=A1 >TODAY()
Selecting a cell with a date that will occur in a week:
=A1 =TODAY()+7
Selecting cells with dates of the current month (any year):
=MONTH(A1 )=MONTH(TODAY())
Selecting cells with dates of the current month of the current year:
=AND(MONTH(A1)=MONTH(TODAY());YEAR(A1)=YEAR(TODAY()))
or
=TEXT(A1 ;"YYYYMM")=TEXT(TODAY();"YYYYMM")
Selecting cells with weekends:
=WEEKDAY(A1,2)>5
Selecting cells with weekdays:
=WEEKDAY(A1,2)
Selecting cells included in the specified period (interval) of dates:
=AND($A1 >DATE(2015,9,1); $A1
OTHER
Highlighting differences in cells by condition:
=A1<>$B1
Selecting a cell if the cell in the next column (B) of the same row is smaller:
=A1 >B1
Highlighting lines in color one at a time:
=REMAT(ROW();2)
Highlight rows with color if column A cell value is in range $F$1:$H$5000 :
=COUNTIF($F$1:$H$5000 ;A1 )
Highlight rows if column A cell value is not in range $F$1:$H$5000 :
=COUNTIF($F$1:$H$5000 ;A1 )=0
Highlighting a cell if its value is second in range A1:A20:
=COUNTIF($A$1:$A1,A1 )=2
Highlight cells containing errors ( #VALUE!; #N/A; #LINK! and so on.). In addition to simply identifying cells with errors, you can use it when you need to hide erroneous values in cells (by assigning the font color the same as the fill color):
=ERROR(A )
Highlighting non-blank cells in column A:
=$A1 ""

Did the article help? Share the link with your friends! Video lessons

("Bottom bar":("textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24," textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance" :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600 ,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500," textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100% ; background-color:#333333; opacity:0.6; filter:alpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive": "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))

Here is a simple example of a pivot table that describes sales volumes of a product by region:

So here are ready-made scripts that can help in most situations:

— first 10 elements;

— last 10 elements;

— first 10%;

— last 10%;

- more than average;

- less than average.

However, you can easily create your own formatting rules.

— selected (“Selected Cells”);

— included in the “Sales_Amount” Values column (“All Cells Showing “Sales_Amount” Values”), including subtotals and grand totals. This option, by the way, is well suited for analyzing those data that require determining the average, percentage, or other values that are one way or another different levels of the same value;

- included in the “Sales Volume” category only for the “Sales Market” (“All Cells Showing “Sales_Amount” Values for “Market””). This option completely excludes general and subtotals, which is convenient for analyzing some individual values.

Note that the “Sales Volume” and “Sales Market” commands when creating rules change depending on the available worksheet.

According to our example, the most profitable option is “3”, so this option is used:

When choosing a rule (section “Select a Rule” or “Select a Rule Type”), we indicate exactly the one that meets our requirements.

It could be:

— “Format Only Top or Bottom Ranked Values”.

- “Format Only Values That Are Above or Below the Average”.

With this configuration, Excel will automatically add icons to cells, following the function:

>=67, >=33 and<33.

This will allow you to distribute the icons into 3 categories at once, which will visually make it easier to obtain information when working with the table.

Let us add that in each case the boundary values can be easily changed, and in the example we simply used the “default” values.

All that remains is to apply our rules to the table. As a result, it will change like this:

It remains to apply the same rule to the “Revenue per hour” column.

But don’t rush to close the example right away - it’s better to analyze it. After all, any more or less experienced manager will immediately notice the relationship between revenue per hour and total income. For example, someone in charge of a sales market in Dallas will determine that his sales volume is minimal, although his revenue per hour is the largest. This leads to the conclusion that the revenue per hour is too high for his market.

His colleague from New York, meanwhile, has a different situation. Given his high overall income level, hourly revenue is minimal. And this is already a problem, despite the large gap in terms of total income.

What was the last explanation for? Just to give you a clear example of how conditional formatting in Excel 2010 makes it easier to understand different types of information. With the necessary skills to create formatting filters, you can quickly get the information you need.

A collection of formulas for conditional formatting. Formatting cells

Conditional Formatting Basics in Excel

Where is conditional formatting in Excel?

How to do conditional formatting in Excel?

How to create a rule

Conditional formatting based on the value of another cell

How to apply multiple conditional formatting rules to one cell

Cell selection rules

Rules for selecting the first and last values

Creating Rules

Rule management

How to do conditional formatting in Excel

Conditional formatting based on the value of another cell

Conditional formatting - multiple conditions

Conditional date formatting in Excel

Conditional formatting in Excel using formulas

Conditional formatting of a string by cell value

The simplest options for conditional formatting

Cell selection rules

Rules for selecting the first and last values

Creating Rules

Rule management

Collection of formulas for conditional formatting

Popular articles

Latest articles

Sections

Pages

Special projects

Contacts