home » IT technologies » Conditional formatting in Excel. Simplest Conditional Formatting Options

Conditional formatting in Excel. Simplest Conditional Formatting Options

Conditional formatting is a handy tool for analyzing data and visualizing results. Knowing how to use it will save a lot of time and effort. A quick glance at the document is enough - the information you need is received.

How to do conditional formatting in Excel

The Conditional Formatting tool is located at home page For more information, see the Styles section.

Clicking on the arrow on the right opens a menu for formatting conditions.

Let's compare the numeric values \u200b\u200bin the Excel range with the numeric constant. The most commonly used rules are "greater than / less than / equal to / between". Therefore, they are placed in the "Cell selection rules" menu.

Let's introduce 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, "more".

Let's enter the number 15 in the left field. In the right field - a method of highlighting values \u200b\u200bthat correspond to a given condition: "greater than 15". The result is immediately visible:

Exit the menu by pressing the OK button.

Conditional formatting by the value of another cell

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

Select the original range and open the Conditional Formatting tool window (abbreviated as “UV” below). For this example, we will apply the condition "less" ("Cell selection rules" - "Less").

The result of formatting is immediately visible on the Excel sheet.

Values \u200b\u200bof range A1: A11, which are less than the value of cell B2, are filled with the selected background.

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

Select the original range (A1: A11). Press "UV" - "Cell Selection Rules" - "Equal". In the left margin is a link to cell B1. The link must be MIXED or RELATIVE !, not absolute.

The program compared each value in column A with the corresponding value in column B. Identical values \u200b\u200bare highlighted.

Attention! When using relative references, you need to keep track of which cell was active at the time the "Conditional Format" tool was called. Since it is to the active cell that the link in the condition is "bound".

In our example, when 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 a 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:

Keep an eye on this point for the Conditional Formatting tool to do the job correctly.

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

Select the first cell in the conditionally formatted range.
Open the tool menu, click "Manage Rules".

In the window that opens, you can see which rule and to which range is applied.

Conditional Formatting - Multiple Conditions

The original range is A1: A11. It is necessary to highlight in red numbers that are more than 6. Green - more than 10. Yellow - more than 20.

1 way. We select the range A1: A11. Apply "Conditional Formatting" to it. "Cell selection rules" - "More". In the left field, enter the number 6. In the right - "red fill". OK. Select the range A1: A11 again. We set the formatting condition "more than 10", the method is "fill with green". By the same principle, fill in yellow numbers greater than 20.
Method 2. From the Conditional Formatting tool menu, select Create Rule.

We fill in the formatting parameters according to the first condition:

Click OK. Similarly, we set the second and third formatting conditions.

Please note: the values \u200b\u200bof some cells 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 "Dispatcher" - "Manage rules".

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

Conditional date formatting in Excel

Select the date range.

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

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

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

Cells with dates are highlighted in red last week (date of writing the article - 02.02.2016).

Conditional formatting in Excel using formulas

If the standard rules are not enough, the user can apply the 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: \u003d OSTAT ($ A1; 2) \u003d 0.

Select the range with numbers - open the "Conditional Formatting" menu. Select "Create Rule". Click "Use a formula to define formatted cells." We fill in the following way:

To close the window and display the result - OK.

Conditional string formatting by cell value

Task: highlight the line containing the cell with a specific value.

Example table:

It is necessary to highlight in red the information on the project, which is still in work ("R"). Green - completed ("Z").

Select the range with table values. Click "UV" - "Create Rule". The rule type is formula. Let's apply the IF function.

The procedure for filling in the conditions for formatting "completed projects":

Similarly, we set the formatting rules for unfinished projects.

In the "Dispatcher" conditions look like this:

We get the result:

When formatting options are set for the entire range, the condition will be met at the same time that the cells are filled. For example, we will "complete" Dimitrova's project in 28.01 - we will put "Z" instead of "R".

The "coloring" has automatically changed. Standard means Excel would take a long time to reach such results.

Every manager, accountant or even the most ordinary user has repeatedly come across the great features of Excel 2010, which allows you to quickly and conveniently work with tables. This version of the program has advanced features of conditional formatting of pivot tables, which improves the entire workflow.

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

Let us give simplest example pivot tabledescribing the volume of sales of a product by region:

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

The easiest option is to use color scales. To do this, select the field "Sales volume", covering all periods. It remains to open the "Home" tab, where we press the "Conditional Formatting" button (if you suddenly use the English version, then this function is called "Conditional Formatting"). Move the cursor over "Histograms".

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

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

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

First 10 items;

The last 10 items;

The first 10%;

The last 10%;

More than average;

Less than average.

Deletion of already used conditional formatting in Excel 2010 occurs as follows: 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" - "Delete rules from this pivot table "(in English -" 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, so let's go straight to the example. Let's track sales and revenue per hour. We'll use Excel 2010's conditional formatting to speed up finding matches and differences. We select "Sales volume". Further, according to the standard procedure, we activate the script ("Main" - "Conditional formatting"), but we choose not the ready-made option, but the function "Create rule" (or "New Rule").

This is where you can determine the cells that will be applied conditional formatting in Excel 2010, the type of rule used and, in fact, formatting options. The cells are set first, and there is a fairly simple choice here:

Selected ("Selected Cells");

Included in the "Sales Volume" 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 the determination of the average, percentage or other values, one way or another, are different levels of the same value;

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

Note that the commands "Sales volume", "Sales market" when creating rules change depending on the available worksheets.

According to our example, the most advantageous option is "3", so the following 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.

This could be:

- "Formatting cells based on values" ("Format All Cells Based on Their Values"). Used to format cells that match the range of values \u200b\u200bused. Best suited for identifying a wide variety of variances when dealing with a huge dataset.

- "Formatting cells containing" ("Format Only Cells That Contain"). Formats cells that match suitable conditions. In this case, the comparison of the values \u200b\u200bof formatted cells with normal cells is not performed. Used to compare a common dataset with a previously specified characteristic.

- "Formatting the first and last values" ("Format Only Top or Bottom Ranked Values").

- "Format values \u200b\u200bbelow or above average" ("Format Only Values \u200b\u200bThat Are Above or Below the Average").

- "Use a Formula to Determine Which Cells to Format". Here, the conditional formatting conditions are based on a formula set 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. In the case of false, no formatting is applied.

The use of histograms, icon sets and color scales is possible only when the formatting of the selected cells is based on the values \u200b\u200bentered in them. To do this, set the first radio button to "Format All Cells Based on Their Values". A set of icons can be used to indicate problem areas, which is also well suited 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 problematic cells, we use the Format Style dropdown menu and select Icon Sets.

In the "Icon Style" list, it remains to select the value "3 characters". This is good if the existing table cannot be fully colored. As a result, we should get the following in the window:

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

\u003e \u003d 67,\u003e \u003d 33 and

Looking at the dry numbers of the tables, it is difficult at first glance to grasp the overall picture they represent. But, in the program Microsoft Excel there is a graphical visualization tool with which you can visualize the data contained in the tables. This allows you to more easily and quickly assimilate information. This tool is called conditional formatting. Let's see how to use conditional formatting in microsoft program Excel.

Simplest Conditional Formatting Options

In order to format specific area 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" toolbar.

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

Histograms;
Digital scales;
Badges.

In order to make conditional formatting in the form of a histogram, select the column with 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 you think best matches the style and content of the table.

As you can see, histograms appeared in the selected cells of the column. The larger the numeric 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 \u200b\u200bin a histogram. But, the 2007 version does not have such an opportunity.

When using a color scale instead of a histogram, it is also possible to select different options for this tool. In this case, as a rule, the higher the value is in the cell, the richer the color of the scale.

The most interesting and complex tool among this set of formatting functions are icons. There are four main groups of icons: directions, shapes, indicators, and estimates. Each user-selected option involves using a different icon when evaluating the contents of a cell. The entire selected area is scanned by Excel, and all cell values \u200b\u200bare divided into parts, according to the values \u200b\u200bspecified in them. Green icons are applied to the largest values, yellow to the midrange values, and values \u200b\u200bin the smallest third are marked with red icons.

When the arrows are selected, signaling in the form of directions is also used as icons, in addition to color design. Thus, the arrow pointing upwards applies to large values, to the left - to medium values, downwards - to small values. When using shapes, the largest values \u200b\u200bare 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 selection are marked with a certain color or icon, according to the values \u200b\u200blocated in them. But, using the menu, which we already talked about above, you can apply other naming rules.

Click on the menu item "Cell selection rules". As you can see, there are seven basic rules:

More;
Less;
Equally;
Between;
Date;
Duplicate values.

Let's consider the application of these actions by examples. Select the range of cells and click on the "More ..." item.

A window opens in which you need to set the values \u200b\u200bgreater 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, or you can specify the address of the cell that contains this number. The latter option is suitable for dynamic tables in which data is constantly changing, or for a cell where a formula is applied. We set the value to 20,000 for the example.

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

When you go to this item, a window opens in which you can edit the selection, almost as you like, applying various options for the font, fill, and borders.

After we have decided on the values \u200b\u200bin the selection rules settings window, click on the "OK" button.

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

By the same principle, values \u200b\u200bare highlighted when applying the rules "Less than", "Between" and "Equal". Only in the first case, cells less than the value set by you are highlighted; 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 setting window, you should specify a word, a 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 happened or will happen: today, yesterday, tomorrow, for the last 7 days, etc.

Applying the rule "Duplicate values", you can customize the selection of cells, according to the compliance of the data placed in them with one of the criteria: whether it is duplicate data or unique.

Selection rules for the first and last values

In addition, there is another interesting item in the conditional formatting menu - "Rules for selecting the first and last values". Here you can set the selection of only the largest or smallest values \u200b\u200bin the range of cells. At the same time, you can use selection, both by ordinal values \u200b\u200band by percentage. There are the following selection criteria, which are indicated in the corresponding menu items:

First 10 items;
The first 10%;
The last 10 items;
The 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 can select the type of selection, and also, if desired, you can set another selection boundary. For example, after clicking on the item "First 10 elements", in the window that opens, in the "Format first cells" field, we changed the number 10 to 7. Thus, after clicking the "OK" button, not the 10 largest values \u200b\u200bwill be highlighted, but only 7.

Creating rules

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

To do this, you need to click in any subsection of the conditional formatting menu on the item "Other rules ..." located at the very bottom of the list ". Or click on the "Create rule ..." item 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 \u200b\u200bthat are above or below 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 of the description of the rules by setting the values, intervals and other values, which we have already discussed below. Only in this case, the setting of these values \u200b\u200bwill be more flexible. It is immediately set, by changing the font, borders and fill, how the selection will look. After all the settings are done, you need to click on the "OK" button to save the changes.

Rule management

In Excel, you can apply multiple rules at once 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 menu of conditional formatting, go to the item manage rules.

A window opens, where all the rules that apply to the selected range of cells are presented. The rules are applied from top to bottom as they are listed. Thus, if the rules contradict each other, then in fact the screen displays the execution of only the most recent of them.

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

There is also another option. You need to check the box in the column with the name "Stop if true" opposite the rule we need. Thus, going over the rules from top to bottom, the program will stop exactly at the rule near which this mark is located, and will not go lower, which means that this very rule will actually be executed.

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

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

In addition, you can delete rules through the main conditional formatting menu. To do this, click on the "Delete rules" item. A submenu opens where you can select one of the deletion options: either delete the rules only on the selected range of cells, or delete absolutely all the 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 configure the table in such a way that the general information on it will be assimilated by the user at a glance. In addition, conditional formatting adds a lot of aesthetic appeal to the document.

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

Ask your question in the comments, detailing the essence of the problem. Our experts will try to answer as quickly as possible.

Conditional Formatting is a tool in Excel that serves to assign a specific format to cells or entire ranges of cells based on user-defined criteria. You will get acquainted with examples of using conditions based on complex formulas. And also learn to manage such functions as:

data fields;
color palette;
customization of fonts.

Learn to work with the values \u200b\u200bthat you can insert into cells depending on their content.

How to do conditional formatting in Excel

First, let's look at how to select the appropriate formatting criteria and how to change them. The principle of its operation is easiest to understand with a ready-made example:

Let's say a column contains a range of cells with numeric values. If you define them with the appropriate formatting condition, then all values \u200b\u200bwith a number greater than 100 will be displayed in red. To accomplish this task, this Excel tool will analyze the value of each cell in the specified range according to the criteria. The analysis results are positive, for example (A2\u003e 100 \u003d TRUE), then a predefined new format (red) will be assigned. In the opposite result (A2\u003e 100 \u003d FALSE), the format of the cells does not change.

Naturally, this is a fairly simple example. Acquainted with ample opportunities conditional formatting only when used in large data sets with complex structures, in which it is difficult to even notice specific values. The ability to use formulas as a criterion for assigning a cell format allows you to create complex conditions for quickly finding and exposing numerical or text data.

How to create a conditional formatting rule in Excel

This Excel tool provides you with 3 formatting rules that can mutually exclude values \u200b\u200bthat do not meet the specified criteria. Let's look at the principle of using multiple conditions in conditional formatting with a simple example.

Let's say cell A1 contains the numeric value 50:

Let's define the following conditions for the display format of values \u200b\u200bin A1:

If the number is greater than 15, then the font will be displayed in green.
If the number is greater than 30, then the font will be displayed in yellow.
If the number is greater than 40, then the font will be displayed in red.

You definitely noticed that the value 50 in cell A1 meets all the conditions (A1\u003e 15, A1\u003e 30 and A1\u003e 40 \u003d TRUE). What font color will Excel display the numeric value 50?

The answer is the following: the format will be assigned the one that matches the last condition. Therefore, it is red. It is important to remember this principle when constructing more complex conditions.

Note. In older versions of Excel, when defining formatting conditions, you had to focus on ensuring that the conditions did not overlap. This situation most often used when the exposed data must yield to values \u200b\u200bat a certain level. But starting in Excel 2010, there are no restrictions on imposing conditions.

Create second rule

Second example. Let's say we want to format the expense in column C like this:

All amounts in the range of $ 300-$ 600 must paint their cells with a yellow background, and amounts less than $ 500 must also have a red font color.

Let's try to construct these conditions:

Please note which excel way applied formatting. The amounts in cells C10, C13, and C15 meet both conditions. Therefore, both formatting styles are applied to them. And where the value matches only one of the conditions, they are displayed in the corresponding formats.

This tutorial with examples and video will be devoted to conditional formatting - one of the most interesting and useful tools in Excel.

What is Conditional Formatting

So let's get down to business. Conditional formatting is a way to keep things as simple as possible excel programs... This method of processing information saves a lot of time and makes all calculations easier. You can make the program automatically perform many tasks that you previously did manually, killing days for it.

In addition, for your convenience, you can customize the work of Excel so that it immediately highlights the necessary or important information in documents. In addition, such formatting will help to more clearly display information, quickly and efficiently create reports without using complex graphical models such as charts or graphs.

Let's take a look at some more specific examples of using conditional formatting. In order to apply it in Excel 10, in the "Home" section on the top panel of the program, you need to find the "Conditional Formatting" button. She is not hiding anywhere, so finding her will not be difficult. In order to activate this formatting, we need to select the area on the worksheet with which we will work. Keep in mind that before clicking the "Conditional Formatting" button and proceeding with it, you need to select a column, row or several such elements for which you want to use formatting.

So, the work area is highlighted, the button is pressed - what next? You will see a conditional formatting menu with the following items:

Selection rules for the first and last values.
Color scales.
Optional: create, delete, rule management.

What to do about it? Let's go in order. This item, in turn, contains such standard functions, as

More;
Less;
Equally;
The text contains;
Date;
Repeating icons.

Working with these formatting models is not difficult at all. By clicking on any of them, you will open a small window where you will need to enter the data you need and select a color for highlighting the table cells that suit you.

Click "Between" and in the new window that opens in the appropriate cells, enter the parameters from and to.
Then specify the color that you want to highlight the options that suit you (let's say we have it "Light red fill and dark red text"). That is, if you work with a price column for mobile phones, then enter the numbers of the minimum and maximum cost that suits you (let it be 50 and 100 for us).
After you have confirmed that it is BETWEEN these values \u200b\u200b\\ u200b \\ u200bwant to start the search, the cells in the table will be highlighted accordingly and we will see ALL cells with a price from 50 to 10 dollars painted in light red and with dark red text.

All this is not difficult at all when in practice to start working with the program.

All of the formatting methods in the Cell Selection Rules menu work in much the same way, so we will not dwell here.

Selection rules for the first and last values

The next point is before us. How it works? If you need to select the first or last few cells according to the entered data, then you are exactly where you need it. There is nothing more to explain, so let's move on to an example.

By clicking "First 10 Items" we will bring up a window where you can control this formatting.
Here we indicate the number of cells that we need to select: initially it was named 10, but we only need 5, so we fix it in the corresponding field.
Then we choose the formatting color: let it be "Red Border".
Then the 5 cells with the highest values \u200b\u200bwill be highlighted with a red frame.

Let's go further. Everything is very simple here. You just need to select the column or line we need and click on the corresponding button. Then we will see how all the cells are more or less filled with color, depending on the values \u200b\u200binside them. It's like a real histogram.

Press "Histogram" and select any model you like from the menu (they differ only in design).
As a result, our column with the number of phones will change so that the entire cell of the largest digit will be filled with color completely, and all the rest will be filled in percentage to the maximum value.

Color scales

They allow us to color our cells in ascending or descending order of values \u200b\u200bin them. You only need to choose in which color scheme this will happen (for example, the maximum value is green, the minimum value is red, and all intermediate values \u200b\u200bwill be colored in the corresponding transition shades). We won't even give an example here.

The icons are needed to indicate the difference between the values \u200b\u200bin our column or row. It's a bit difficult to explain in theory, so let's jump straight to the examples.

select "Icon Sets" and in the "Directions" section click on "5 colored arrows". Thus, in each cell of the field in which we work, one of 5 types of arrows will appear.
Let's explain how they work: the entire range of values \u200b\u200bin the cells we have selected is 100%, and each arrow in turn is responsible for the numbers that enter every 20% in order. Suppose that we have values \u200b\u200bfrom 0 to 100 in the column for the number of phone purchases.Then the first arrow (green up) will stand near each value from 80 to 100, and the last (red down) - near each value from 0 to 20. Accordingly, all intermediate arrows.

The percentage or the entire range can be configured in the "Manage Rules" menu, here you can also play around with the settings for other rules.

With an exit new version pC programs Microsoft Office there were also new opportunities. The developers have improved some components, made even more comfortable work with programs. Excel 2010 and the new infographic features in it cannot be ignored. Therefore, in this article, we will tell you by example how to work with the new components of Excel 2010.

Conditional table formatting in Excel 2010

It is not always convenient to view a large number of values \u200b\u200band compare them with the planned ones. Suppose that the amount of revenue for each manager per month should be at least 100,000 rubles. But it is not necessary to estimate the metrics manually, looking at each value, it is easier to trust the built-in component Excel... Let's select the data area. Go to the tab "Insert - Conditional Formatting - Icon Set" from the drop-down menu, select the template you like, I like the traffic light, as it is very convenient to work with. After choosing a template, we will see the "Create formatting rules" window. Here it is necessary, opposite these very icons, to enter data, after exceeding which the employee's work is assessed as: excellent, satisfactory and unsatisfactory. The data is entered into the "Value" parameter opposite each of the circles, and the "Type" field in this case must be changed from "Percentage" to "Numbers". I have set the following parameters: 110, 90. The third parameter is set automatically, it is evaluated as all values \u200b\u200bless than satisfactory. Press the "Ok" button.

Circles of three different colors appeared in the cells of all values. Based on the information presented in this form, it is much easier to evaluate the performance of managers over a certain period of time. We can compare the quality of work of employees, determine which of the employees achieves the most outstanding results, and who, on the contrary, requires close attention.

But this is not the last way to conditionally format data. In the appeared such infographic elements as "Histograms" and "Color scales". Let's consider them in more detail. Select the values \u200b\u200bin the cells and move to "Insert - Conditional Formatting - Histograms". A list of templates will appear in the drop-down menu; when you hover over any of them, the result is previewed. Choose the color scheme you like and see that the cells are filled with horizontal columns of different sizes. They graphically display the values \u200b\u200bthat are present in the cells. If a number is entered with a minus sign, the graph will shift to the opposite side of the cell, indicating negative values.

The Color Scales component fills the cells with a color that matches the value entered into it. For example, the smallest values \u200b\u200bwill be filled in red, the middle ones in yellow, and the largest in green. The color scheme can be selected by you individually, but the essence remains approximately the same as when using the "Icon Set".

Slices and more

But that's not all of the data visualization capabilities included in the package. Consider another convenient function as "Slices". The selected employees have worked in the company for a very impressive period and it is difficult to select a particular date when forming a pivot table. There are two ways to refer to a specific date. When we build a pivot table on the right side, we have elements that we can place in various fields. We turn to the "Dates" element and call the drop-down menu by clicking on the marker with the arrow. We find the item "Filter by date". A huge list opens up with various formatting options, but we need monthly sorting. Open "All dates for the period" and select "October". The pivot table has been significantly reduced, it only contains the values \u200b\u200bfor October. This is the first way to sample data.

The second way is organized with the help of the new Slice function, an interesting digital data analysis tool. Let's move on to "Insert - Slice". The "Insert Slicer" window opens, in it you need to mark the indicator by which the values \u200b\u200bwill be sampled, that is, the table column by which you can view the slices of your report. We mark the "Dates" and press the "OK" button. A frame with the values \u200b\u200brecorded in it will appear on the sheet.

Drag it to any place convenient for us and adjust its size so that you can see all the values \u200b\u200bpresented in it. You can also change the color of the slice, all templates are displayed on the top panel. Now we can select a specific date with one click, and see what results the employees have achieved during these days. This function is an order of magnitude more convenient than the "Filter by Date", since it is more flexible. In it, you can select several values \u200b\u200bat once, by which the selection will go.

Infocurves

The next way to visually analyze data is with sparklines. We make an active free cell opposite the data lines. In the "Insert" tab we find the "Sparklines" section (in my version they were called for some reason "Sperklines"). Select the data range - this will be our line, and click the "Ok" button. You can see how a mini graph is built in the cell we have selected, this is the info curve.

Stretch this cell to all other lines by pulling the edge with a dot or by double-clicking on it. If you want, you can change the style of the sparkline; you can select it on the top panel in the sparkline design mode. The resulting graph allows you to see the trend, trend. With a huge amount of data, the Sparkline provides a general visual analysis of the entire set. From it you can easily identify peaks and falls, the beginning of growth or its slowdown.

Sparklines are of three types: "Graph" - we just considered it; "Column" - displays data in the form of small columns, graphically showing the maximum and minimum values; “Win / Loss” - the cell is divided into de parts, in the lower part there are squares with negative values, in the upper part with positive ones, zero is not displayed at all.

Output

In this article, we not only learned how to quickly draw up a table, but also conduct visual data analysis. We also got acquainted with such a concept as a pivot table, learned how to filter values \u200b\u200band conditional formatting. digital values, make slices. In addition, we have visually figured out a new feature called Sparklines. It should be noted that the improvements are visible on the face, and almost all new functions are aimed at facilitating the work of a specialist and visual presentation of data. If you are interested in the new functionality of the spreadsheet editor, then you can contact 1CSoft partners.

Alexander Neberekutin

Conditional formatting helps provide visibility when exploring and analyzing data, detecting critical issues, and identifying patterns and trends.

Conditional formatting makes it easy to highlight cells or ranges that you want, underline unusual values, and visualize your data using bar charts, color bars, and icon sets.

When applying conditional formatting appearance cells changes based on the conditions you specified. If the conditions are met, then the formatting is applied to the range of cells. If the conditions are not met, formatting is not applied. There are many built-in conditions, and you can also create your own conditions (including using a formula that evaluates to True or False).

Conditional formatting of monthly high temperature data record for different locations, colors intuitively match values \u200b\u200b(higher temperature values \u200b\u200borange and red, low temperature values \u200b\u200byellow and green)
Conditional formatting using cell background color to highlight different product categories; the three-arrow icon shows price trends (rising, maintaining, declining), and histograms show the difference in price increases.

Conditional formatting can be applied to a range of cells (selected or named), to excel spreadsheet and even a PivotTable report in Excel for Windows. Note a number of additional factors affecting conditional formatting in a PivotTable report - see Applying Conditional Formatting to a PivotTable Report below on the Windows tab.

Download a sample book

You can download a sample book that contains various examples of conditional formatting using standard rules such as top and bottom, duplicate data, histograms, icon sets and color rails, and your own custom rules.

Formatting cells with a two-color scale

Color bars are visual elements that help you understand the distribution and dispersion of data. A two-color scale helps you compare a range of cells by using a two-color gradation. The brightness level of a color is high, medium, or low. For example, in the yellow-green scale shown below, you can specify that cells with high values \u200b\u200bwill be greener and cells with low values \u200b\u200bwill be more yellow.

Advice: THERE IS or IFERROR to return a value other than error.

Quick format

Formatting options

Advanced formatting

In the tab home in a group Styles and select item Rule management... A dialog box will open.

Create rule... A dialog box will open.
1. Minimize dialog in field It applies to Expand dialog.
  
  Change rule... A dialog box will open.

In chapter Apply rule

to selected cellsto select cells by selection;

<метка значения> to select cells with a specific label.

to all cells containing values<метка значения> for<название строки> to select cells with a specific label other than subtotals and grand totals.

In chapter Select the type of rule select (default).

In a group Change the description of the rule in the combo box Format style select item Two-color scale.

In the fields A type parameters Minimum value and Maximum value select their types.

Please select minimum value and maximum value.

In this case, do not enter minimal and maximummeaning.

Select item Numberand then enter minimal and maximummeaning.

Enter minimal and maximummeaning.

Percentile formatting. Select item Percentile Minimum value and Maximum value

Use a percentile when you want to render a group of high values \u200b\u200b(for example, the top 20th percentile) with one shade, and a group of low values \u200b\u200b(for example, the bottom 20th percentile) with another, because they correspond to extreme values \u200b\u200bthat can displace the data visualization.

Select item Formulaand then enter minimal and maximum values.
- Notes:
  - Make sure that minimal value less than maximum.
    
    For parameters Minimum value and Maximum value minimum value and percentage type for maximum value.

To select options Minimum value and Maximum value color bar, click an item Colour for each value, and then choose a color.

Other colors... The selected color scale appears in the window preview.

Formatting cells using the three-color scale

Color bars are visual elements that help you understand the distribution and dispersion of data. The tri-color scale helps you compare a range of cells by using a three-color gradation. The brightness level of a color is high, medium, or low. For example, for a red-yellow-green scale, you can specify that cells with high values \u200b\u200bare green, cells with medium values \u200b\u200bare yellow, and cells with low values \u200b\u200bare red.

Quick format

You can change how the fields are scoped from the Values \u200b\u200barea in a PivotTable report by using the button Formatting options next to a PivotTable field that has conditional formatting applied.

Advanced formatting

Select one or more cells in a range, table, or PivotTable report.

In the tab home in a group Styles click the arrow next to the button and select Rule management... A dialog box will open Conditional Formatting Rules Manager.

Do one of the following:

To add conditional formatting, click Create rule... A dialog box will open.

Follow these steps to change conditional formatting.
1. Make sure the combo box Show formatting rules for the appropriate sheet, table, or pivot table report is selected.
  
  You can change the range of cells as needed. To do this, press the button Minimize dialog in field It applies toto temporarily hide the dialog. Then select a new range of cells on the sheet and click Expand dialog.
  
  Select a rule and then click Change rule... A dialog box will open Changing the formatting rule.

In chapter Apply rule select one of the following options to change the field selection in the value area of \u200b\u200bthe PivotTable report:

Please select only these cells;

Please select all cells<поле значения> with the same fields;

Please select all cells<поле значения> .

In a group Select the type of rule select item Format all cells based on their values.

In a group Change the description of the rule in the combo box Format style select item Three-color scale.

Select parameter type Minimum value, Mean and Maximum value

Formatting the minimum and maximum values. Select item Mean.

In this case, do not enter minimal and maximum meaning.

Formatting a numeric, date, or time value. Select item Numberand then enter minimal, the average and maximum values.

Percentage formatting. Select item Percentand then enter minimal, the average and maximum values. Valid values \u200b\u200bare 0 (zero) through 100. Do not enter a percent sign.

Percentile formatting. Select item Percentileand then enter Minimum value, Mean and Maximum value.

Use a percentile when you want to render a group of high values \u200b\u200b(for example, the top 20 percentile) with one shade, and a group of low values \u200b\u200b(for example, the bottom 20 percentile) with another, because they correspond to extreme values \u200b\u200bthat can displace the data visualization.

Formatting the result of a formula. Select item Formulaand then enter minimal, the average and maximum values.

The formula must return a number, date, or time. Start entering a formula with an equal sign ( = ). An invalid formula will prevent formatting from being applied. Check the formula to make sure it doesn't return an error value.

Notes:
- You can set the minimum, average, and maximum values \u200b\u200bfor a range of cells. Make sure that minimal value less middle, and it, in turn, is less maximum.
  
  For parameters Minimum value, Mean and Maximum value can choose different types... For example, you can choose a numeric type for minimum value, percentile for mean value and percentage type for maximum value.
  
  In many cases mean the default of 50 percent is optimal, but you can change it to suit your specific requirements.

To select a color scale Minimum value, Mean and Maximum value click an item Colour for each one, and then choose a color.

To select additional colors or create a custom color, click Other colors.

The selected color scale appears in the window preview.

Formatting cells with histograms

Bar charts help you view the value in a cell relative to other cells. The length of the histogram corresponds to the value in the cell. The longer it is, the greater the value. Bar charts are great for identifying key metrics, especially in large amounts of data, such as the maximum and minimum toy sales in a holiday sales report.

The example shown here uses histograms to highlight extreme positive and negative values. Bar charts can be formatted to start in the middle of a cell and stretch to the left to negative values.

Advice: If any of the cells in the range contain a formula that returns an error, conditional formatting is not applied to it. To ensure that conditional formatting is applied to these cells, use the function THERE IS or IFERROR

Quick format

Advanced formatting

Select one or more cells in a range, table, or PivotTable report.

In the tab home in a group Styles click the arrow next to the button and select Rule management... A dialog box will open Conditional Formatting Rules Manager.

Do one of the following:

To add conditional formatting, click Create rule... A dialog box will open.

Follow these steps to change conditional formatting.
1. Make sure the combo box Show formatting rules for the appropriate sheet, table, or pivot table report is selected.
  
  You can change the range of cells as needed. To do this, press the button Minimize dialog in field It applies toto temporarily hide the dialog. Then select a new range of cells on the sheet and click Expand dialog.
  
  Select a rule and then click Change rule... A dialog box will open Changing the formatting rule.

In chapter Apply rule select one of the following options to change the field selection in the value area of \u200b\u200bthe PivotTable report:

to select fields by selection, select only these cells;

to select fields by the corresponding field, select all cells<поле значения> with the same fields;

to select fields by value field, select all cells<поле значения> .

In a group Select the type of rule select item Format all cells based on their values.

In a group Change the description of the rule in the combo box Format style select item bar graph.

Select values Minimum and Maximum for type... Do one of the following:

Formatting the minimum and maximum values. Please select Minimum value and Maximum value.

In this case, do not enter Minimum value and Maximum value.

Formatting a numeric, date, or time value. Select item Numberand then enter minimal and maximummeaning.

Percentage formatting. Select item Percentand then enter minimal and maximum values.

Valid values \u200b\u200bare 0 (zero) through 100. Do not enter a percent sign.

Use percentages if you want to render all values \u200b\u200bproportionally, since the distribution of values \u200b\u200bin this case is proportional.

Percentile formatting. Select item Percentileand then enter the parameter values Minimum value and Maximum value.

Valid percentile values \u200b\u200bare from 0 (zero) to 100.

Use percentiles when you want to render a group of high values \u200b\u200b(for example, the top 20th percentile) in one proportion of the histogram and a group of low values \u200b\u200b(for example, the bottom 20th percentile) in another, because they correspond to extreme values \u200b\u200bthat can displace the data visualization.

Formatting the result of a formula. Select item Formulaand then enter the parameter values Minimum value and Maximum value.
- The formula must return a number, date, or time.
  
  Start entering a formula with an equal sign ( = ).
  
  An invalid formula will prevent formatting from being applied.
  
  Check the formula to make sure it doesn't return an error value.

Notes:

Make sure that minimal value less than maximum.

For parameters Minimum value and Maximum value different types can be selected. For example, you can choose a numeric type for minimum value and percentage type for maximum value.

To select color scales Minimum value and Maximum value select item Column color.

If you want to select additional colors or create a custom color, click Other colors... The selected column color will appear in the window preview.

To display only the histogram, but not the value in the cell, select the checkbox Show Column Only.

To apply a solid border to the columns of the histogram, select Border option Solid border, and then choose a border color.

To make the columns solid or gradient, select from the list Fill option Solid fill or Gradient fill.

To format columns for negative values, click Negative values \u200b\u200band axisand then in the dialog Setting negative values \u200b\u200band axis adjust the fill and border color options for columns corresponding to negative values. You can customize the position and color for the axis. After selecting options, click OK.

The direction of the histogram can be set in the field Column direction... By default, the value is selected there. by contexthowever, you can choose left-to-right or right-to-left direction depending on how you want to present the data.

Formatting cells using the icon set

A set of icons is used to annotate and classify data into three to five categories, separated by a threshold. Each icon corresponds to a range of values. For example, in icon set 3 arrows, a red up arrow corresponds to high values, a yellow to the side arrow corresponds to medium values, and a green down arrow corresponds to low values.

The example shown here uses multiple sets of icons in conditional formatting.

You can choose to show icons only for cells that meet certain conditions (for example, showing a warning icon for cells below the critical level, and no icon for cells above this level). To do this, when setting up conditions, hide the cells by choosing the option No cell icon in the drop-down list next to the desired icon. In addition, you can create your own icon sets, such as a green check mark, a yellow traffic light, and a red flag.

Advice: If any of the selected cells contain a formula that returns an error, conditional formatting is not applied to those cells. To ensure that conditional formatting is applied to these cells, use the function THERE IS or IFERROR to return a value (such as 0 or N / A) other than error.

Quick format

You can change how the fields are scoped from the Values \u200b\u200barea in a PivotTable report by using the radio button Apply formatting rule to.

Advanced formatting

In the tab home in a group Styles click the arrow next to the button and select Rule management... A dialog box will open Conditional Formatting Rules Manager.

Do one of the following:

To add conditional formatting, click Create rule... A dialog box will open.

Follow these steps to change conditional formatting.
1. Make sure the combo box Show formatting rules for the appropriate sheet, table, or pivot table report is selected.
  
  You can change the range of cells as needed. To do this, press the button Minimize dialog in field It applies toto temporarily hide the dialog. Then select a new range of cells on the sheet and click Expand dialog.
  
  Select a rule and then click Change rule... A dialog box will open Changing the formatting rule.

In chapter Apply rule select one of the following options to change the field selection in the value area of \u200b\u200bthe PivotTable report:

to select fields by selection, select only these cells;

to select fields by the corresponding field, select all cells<поле значения> with the same fields;

to select fields by value field, select all cells<поле значения> .

In a group Select the type of rule select item Format all cells based on their values.

In chapter Change the description of the rule in the list Style format select item Icon set.

Select a set of icons. The default set is 3 traffic lights without framing... The number of icons, default comparison operators, and thresholds for each icon can be different for different icon sets.

You can change comparison operators and thresholds. The default range sizes for all icons are the same, but you can change them to suit your specific requirements. Make sure the thresholds are in a logical sequence from highest to lowest, top to bottom.

Do one of the following:
- Formatting a numeric, date, or time value. Select item Number.
  
  Percentage formatting. Select item Percent.
  
  Valid values \u200b\u200bare 0 (zero) through 100. Do not enter a percent sign.
  
  Use percentages if you want to render all values \u200b\u200bproportionally, since the distribution of values \u200b\u200bin this case is proportional.
  
  Percentile formatting. Select item Percentile... Valid percentile values \u200b\u200bare from 0 (zero) to 100.
  
  Use percentiles if you want to visualize a group of maximum values \u200b\u200b(for example, the top 20th percentile) with one icon and a group of minimum values \u200b\u200b(for example, the lower 20th percentile) with another, because they correspond to extreme values \u200b\u200bthat can displace the data visualization.
  
  Formatting the result of a formula. Select item Formulaand then enter formulas in each field Value.
  - The formula must return a number, date, or time.
    
    Begin entering a formula with an equal sign (\u003d).
    
    An invalid formula will prevent formatting from being applied.
    
    Check the formula to make sure it doesn't return an error value.

To have the first icon correspond to lower values \u200b\u200band the last icon to higher values, select the option Reverse order of icons.

To display only the icon and not the value in the cell, select the option Show only icon.

Notes:

You may need to adjust the width of the columns to accommodate the icons.

The displayed icon size depends on the font size used in that cell. Increasing the font size increases the icon size proportionally.

Formatting cells containing text, numeric values, date, or time

To make it easier to find certain cells, you can format them using the comparison operator. For example, in an inventory list sorted by category, you might highlight in yellow products that have fewer than 10 remaining. In the summary sheet retail store all stores with an income of more than 10%, with a sales volume of less than $ 100,000 and with the South-East region can be distinguished.

The examples provided here demonstrate built-in conditional formatting criteria such as greater than and% best. Cities with populations over 2,000,000 are highlighted in green, and the top 30% (highest) average monthly maximum temperatures are highlighted in orange.

Note: You cannot apply conditional formatting to fields in the Value area of \u200b\u200ba PivotTable report by text or by date (only by numbers).

Quick format

You can change how the fields are scoped from the Values \u200b\u200barea in a PivotTable report by using the radio button Apply formatting rule to.

If you'd like to watch a video about these methods, see Video: Conditional Text Formatting and Video: Conditional Date Formatting.

Advanced formatting

Select one or more cells in a range, table, or PivotTable report.

On the Home tab, in the Styles group, click the arrow next to the Conditional Formatting button, and then click Manage Rules. A dialog box will open Conditional Formatting Rules Manager.

Do one of the following:

To add conditional formatting, click Create rule... A dialog box will open.

Follow these steps to change conditional formatting.
1. Make sure the combo box Show formatting rules for the appropriate sheet, table, or pivot table report is selected.
  
  You can change the range of cells as needed. To do this, press the button Minimize dialog in field It applies to Expand dialog.
  
  Select a rule and then click Change rule... A dialog box will open Changing the formatting rule.

In chapter Apply rule select one of the following options to change the field selection in the value area of \u200b\u200bthe PivotTable report:

to select fields by selection, select only these cells;

to select fields by the corresponding field, select all cells<поле значения> with the same fields;

to select fields by value field, select all cells<поле значения> .

In the list Select the type of rule select item Format only cells that contain.

In a group Change the description of the rule in the combo box Format only cells that meet the following condition do one of the following.

To set the format, click Format... A dialog will be displayed Cell format.

Select the number, font, border, or padding format that you want to apply if the value in the cell meets the condition, and then click OK.

Several formats can be selected. The selected formats will appear in the window preview.

Quick format

You can change how the fields are scoped from the Values \u200b\u200barea in a PivotTable report by using the radio button Apply formatting rule to.

Advanced formatting

Quick format

You can change how the fields are scoped from the Values \u200b\u200barea in a PivotTable report by using the radio button Apply formatting rule to.

Advanced formatting

Formatting only unique or duplicate values

Note: You cannot apply conditional formatting to fields in the Values \u200b\u200barea of \u200b\u200ba PivotTable report with unique or duplicate values.

The example here uses conditional formatting in the Teacher column to highlight teachers teaching more than one subject (duplicate names are highlighted pink). Grade values \u200b\u200bthat appear only once in the Grade column are highlighted in green.

Quick format

Advanced formatting

Create your own conditional formatting rules

If none of the above options work for you, you can create your own conditional formatting rule in a few simple steps.

Selecting cells for formatting using a formula

If you don't have the options you want when creating your own conditional formatting rule, you can use a Boolean formula to set the formatting conditions. For example, you can compare values \u200b\u200bin selected cells with the result returned by a function, or evaluate data in cells outside the selected range that might be on another sheet of the same workbook. The formula should return True or False (1 or 0), but you can use conditional logic to combine a set of appropriate conditional formats, such as different colors for each of small sets text values \u200b\u200b(for example, the names of product categories).

Note: You can enter cell references in a formula. To do this, select cells directly on the sheet or other sheets. When you select cells in sheets, absolute cell references are created. To have Excel adjust the cell references in the selected range, use relative references. For more information, see Create and Modify a Cell Reference and Change the Reference Type: Relative, Absolute, Mixed.

Advice: If any cells contain a formula that returns an error, conditional formatting is not applied to those cells. To solve this problem, use in the formula function IS or function IFERRORto return a value you specify (for example, 0 or "N / A") instead of an error value.

In the tab home in a group Styles click the arrow next to the button and select Rule management.

A dialog box will open Conditional Formatting Rules Manager.

Do one of the following:

To add conditional formatting, click Create rule... A dialog box will open.

Follow these steps to change conditional formatting.
1. Make sure the combo box Show formatting rules for the appropriate sheet, table, or pivot table report is selected.
  
  You can change the range of cells as needed. To do this, press the button Minimize dialog in field It applies toto temporarily hide the dialog box, then select a new range of cells on this or other sheets and click Expand dialog.
  
  Select a rule and then click Change rule... A dialog box will open Changing the formatting rule.

In chapter Apply rule do one of the following to change the field selection in the value pane of a PivotTable report:

To define an area by a selection , click Selected cells.

To determine the area by the corresponding field , click to all cells containing values<поле значения> .

To define an area by the "Value" field , select to all cells containing values<поле значения> for<строка> .

In a group Select the type of rule press the button Use a formula to define formatted cells.

The following video shows the basics of using conditional formatted formulas.

Copy and paste conditional formatting

If you want to apply a conditional formatting style to new or other data in the worksheet, you can copy it using the command Sample format.

Note: If you've used a formula in a rule that applies conditional formatting, after you've inserted that formatting, you might need to adjust the cell references in that formula. For more information, see the article Switch between relative, absolute, and mixed links.

Finding cells with conditional formatting

If the sheet contains conditional formatting, you can quickly find those cells to copy, modify, or delete conditional format... To find cells with a specific conditional formatting or all cells with conditional formatting, use the command Select a group of cells.

Finding all cells with conditional formatting

Find cells with the same conditional formatting

Managing Conditional Formatting Rules

With conditional formatting, you define the rules by which Excel determines when to apply conditional formatting. To manage these rules, you need to understand the sequence in which these rules are checked and what to do if more than one conflict occurs. You should also understand how copy and paste affects the rule check, know how to change the sequence of this check and when to stop it.

Conditional Formatting Rules Manager... (To do this, on the tab home select a command and select Rule management.)

A dialog box will open Conditional Formatting Rules Manager.

When multiple conditional formatting rules are applied, their validation priority is determined by the order in which they appear in this dialog box (top to bottom).

TODAY \u003d B2<СЕГОДНЯ () It applies to \u003d B2<СЕГОДНЯ()+60

Up and Down.

Sometimes, multiple conditional formatting rules can be applied to a range of cells. Here are examples of the application of rules: first, those that do not conflict with each other, and then those that are in conflict.

If any conditional formatting rule is checked with the result "True", it takes precedence over any manually set format for the same selected cells. This means that in the event of a conflict, conditional formatting is applied and manual formatting is not applied. When you remove a conditional formatting rule, the formatting you manually applied to the range of cells remains.

Conditional Formatting Rules Manager

Rule management check the box Stop if true

Stop if true
Check or uncheck the box Stop if true

For a video on managing conditional formatting rules, see Video: Managing conditional formatting.

Clearing conditional formatting

Clearing conditional formatting on a sheet

In the tab home click\u003e Delete rules > .

To clear conditional formatting on a sheet, follow the instructions below.

All over sheet

In the tab home click\u003e Delete rules > Remove rules from entire sheet.

In a range of cells

Find and remove the same conditional formatting in a worksheet

Click the cell with the conditional formatting that you want to remove from the entire sheet.

In the tab home click the arrow next to the button Find and highlight and select the command Select a group of cells.

Select an option Conditional formats.

Select item the same in a group Data validation... All cells with the same conditional formatting rules will be highlighted.

In the tab home press the button, select the item Delete rules, and then - .

Formatting only the first or last values

You can find the maximum and minimum values \u200b\u200bin a range of cells based on a specified threshold. For example, you might find the 5 most popular products in a regional report, 15% of the least popular products in a customer survey, or the top 25 salaries in the department personnel report.

Select the cells to which you want to apply conditional formatting.

In the tab home in a group Tables select.

Select the command you want, for example First 10 items or Last 10%.

Enter the values \u200b\u200byou want to use, then select a format (fill, text, or border color) and click OK.

Formatting only values \u200b\u200bthat are above or below average

You can find values \u200b\u200babove or below the mean or standard deviation in a range of cells. For example, you can find performers who perform above average in an annual performance review, or you can identify materials produced that are below twice the standard deviation.

Select the cells to which you want to apply conditional formatting.

In the tab home in a group Tables select item Selection rules for the first and last values.

Copy and Paste Conditional Formatting Manage Conditional Formatting Rules
When you use conditional formatting, you define the rules by which Excel determines when to apply conditional formatting. To manage these rules, it is important to understand the sequence in which these rules are checked and what to do if more than one conflict occurs. You should also understand how copy and paste affects rule checking, know how to change the sequence of this check and when to stop it.
Changing the Check Order of Conditional Formatting Rules

The order in which conditional formatting rules are checked - their priority - depends on their relative importance: the higher a rule is in the list of conditional formatting rules, the more important it is. This means that if two conditional formatting rules conflict with each other, the rule that is higher in the list is applied, and the one below is not applied.

Removing conditional formatting

You can remove conditional formatting from selected cells or from the entire sheet.
Note: You cannot use conditional formatting with external links to other books.

additional information

You can always ask the Excel Tech Community a question, ask for help in the Answers community, and also suggest new function or improvement on the website