What formatting is applicable to cells. Applying conditional formatting in Excel. Conditionally format cells in Excel based on values ​​in another range

When filling Excel sheets with data, no one will be able to fill everything out beautifully and correctly on the first try.

While working with a program, you constantly need something: change, edit, delete, copy or move. If erroneous values ​​are entered into a cell, naturally we want to correct or delete them. But even such a simple task can sometimes create difficulties.

How to set cell format in Excel?

Contents of each Excel cells consists of three elements:

1. Meaning: text, numbers, dates and times, logical content, functions and formulas.
2. Formats: type and color of borders, type and color of fill, method of displaying values.
3. Notes

All these three elements are completely independent of each other. You can set the cell format and not write anything into it. Or add a note to a blank and unformatted cell.



How to change cell format in Excel 2010?

To change the cell format, you should call the corresponding dialog box using the key combination CTRL+1 (or CTRL+SHIFT+F) or from context menu after pressing right click mouse: “Format Cells” option.

There are 6 tabs available in this dialog box:

If you do not achieve the desired result on the first try, call this dialog box again to correct the cell format in Excel.

What formatting is applicable to cells in Excel?

Each cell always has some format. If there have been no changes, then this is the “General” format. It is also a standard Excel format in which:

• numbers are aligned to the right;
• text is aligned to the left;
• Colibri font with a height of 11 points;
• The cell has no borders or background fill.

Removing a format is a change to standard format“General” (no borders or fills).

It is worth noting that the format of cells, unlike their values, cannot be deleted using the DELETE key.

To remove the format of cells, select them and use the “Clear Formats” tool, which is located on the “Home” tab in the “Editing” section.

If you want to clear not only the format, but also the values, then select the “Clear all” option from the tool (eraser) drop-down list.

As you can see, the eraser tool is functionally flexible and allows us to make a choice of what to delete in cells:

• content (same as DELETE key);
• formats;
• notes;
• hyperlinks.

The Clear All option combines all these functions.

Deleting notes

Notes, as well as formats, are not deleted from a cell using the DELETE key. Notes can be deleted in two ways:

1. Using the eraser tool: Clear Notes option.
2. Right-click on the cell with the note, and from the context menu that appears, select the “Delete note” option.

Note. The second method is more convenient. If you want to delete multiple notes at once, you should first select all of their cells.

Cell borders and grid lines represent different table elements. Gridlines are defined for the entire worksheet. Borders apply to individual cells or a range of cells. Of course, a border is not the only way to format a cell. You can also change text alignment, background pattern, font, number representation, and cell security attributes. There are also conditional formatting and pop-up comments (appear when you hover over a cell).
You can use the toolbar to format cells Formatting , but the most comprehensive set of cell formatting commands is in the menu Format .

2.1.1. Text alignment.

When you enter text into a cell, it is usually aligned to the left edge of the cell. Numeric values, on the other hand, are aligned to the right edge of the cell. To change how a cell's contents are aligned, select the cell and click the corresponding button on the toolbar Formatting . List of these buttons:

§ Left . Aligns text to the left edge of the cell. If the text is wider than the cell it will extend beyond its right border. If the cell on the right is not empty, then the text is truncated and will not be completely visible.

§ Centered . Centers text in a cell. If the text is wider than the cell, it will extend into the adjacent cells to the left and right, provided they are empty. If adjacent cells are not empty, the text is truncated and will not be fully visible.

§ Right . Aligns text to the right edge of the cell. If the text is wider than the cell, it flows into the cell to the left. If the cell on the left is not empty, the text is truncated and will not be fully visible.

§ Combine and place in the center . Centers text in selected cells and also merges them into one cell.

2.1.2. Boundaries.

Another type of formatting is borders -- these are lines drawn around all or just some sides of the selected cells. Button Borders located on the toolbar Formatting . If you click on the arrow next to it, a palette will appear, which is a miniature toolbar and contains 12 icons. You can drag this toolbar by its title bar anywhere in the workbook window. To add a border to a selected cell or group of cells, just click on the icon corresponding to the type of border. The icon located on the left top corner palette, allows you to remove all frames around selected cells.

2.1.3. Colors.

Tool Fill color allows you to change the background color of selected cells, and the tool Font color -- the color of the text characters in these cells. The working principles of these tools and tools Borders are similar, and their palettes can also be dragged to another location on the screen.

2.1.4. Fonts and text formatting.

The default font in Excel is Arial. This is not a monospaced font; the “ttt” characters have a different width than “ooo”. In a monospaced font, all printed characters, including spaces and punctuation, are the same width. Courier and Courier New Cyr fonts are examples of such fonts. To do standard font, other than Arial, you need to select a menu Service > Options, click on tab Are common and select the desired standard font.

2.1.5. Formatting numeric data in cells.

You can use different formats for representing numeric data within the same spreadsheet. The most common formats for representing numerical data.

Basic format is the default, ensuring that numeric data is recorded in cells exactly as it is entered or calculated.

Format with a fixed number of decimal places provides the representation of numbers in cells with a given precision, determined by installed by user the number of decimal places after the decimal point (decimal point). For example, if the formatting mode is set to two decimal places, then the number you enter 12345 will be written as 12345.00, and the number 0.12345 will be written as .12.

Percentage format ensures that the entered data is presented in percent format with a % sign (according to the specified number of decimal places). For example, if the precision is set to one decimal place, then 12.3% will appear on the screen if you enter 0.123, and 12300.0% will appear if you enter 123.

Currency format provides a representation of numbers where every three digits are separated by a comma. In this case, the user can set a certain precision of presentation (rounded to a whole number or two decimal places). For example, the entered number 12345 will be written in the cell as 12.345 (rounded to the nearest whole number) and 12.345.00 (to two decimal places).

Scientific format, used to represent very large or very small numbers, provides a representation of the input numbers as two components:

A mantissa having one decimal place to the left of the decimal point, and a certain (defined by user-specified precision) number of decimal places to the right of it;

Order of numbers.

Applying conditional formatting.

Sometimes you may need to reformat cells based on their content. For example, contained in a cell a negative number must be shown in bold on a red background. There is a special type of formatting for this in Excel: conditional formatting . For use conditional formatting to a cell or range of cells, you must select the range and then select the command Format > Conditional Formatting . This will open a dialog box Conditional Formatting . This dialog box allows you to define three conditions for selected cells. The conditions can be determined by a value in a cell or a user-specified formula. From the drop-down list you need to select one of the options: Meaning or Formula . If you select the option Meaning , then you need to set a condition on the numeric value. For example, you can specify a range from 0 to 100. You can enter values ​​or cell references. If you select the option Formula , then you need to specify a link to the formula. Then you need to click on the button Format and indicate what formatting should be applied if the condition is met.

Using a formula as a condition. If the option was selected Formula , then the specified formula should take the values True (1) or Lie (0). The formula must begin with an equal sign (=). Calculations using this formula can only be performed on the active sheet.

You can also use the formula to evaluate criteria that are not based on worksheet data. For example, the formula =WEEKDAY("12.5.99")=1 takes the value True , if the date May 12, 1999 is a Sunday. The value of the cell does not affect the truth of the condition in any way only if the formula does not contain special references to the selected cells to be formatted. If the formula refers to selected cells, then you must enter cell references into it.

If you need to set different conditional formatting for this range, you need to click the button And . When everything is done, click on the OK button.

You can change the settings so that a number in a cell appears green if it is positive and red if it is negative. To do this, you need to set the cell format as additional and write:

[Green] # , ##0_) ; [Red] # , ##0

Conditional formatting acts like an alarm clock. Onocontrol values.

1). framing and filling

2). text alignment and font format

3). data type, width and height

4). all options are correct

6. Which operator is not included in the group of arithmetic operators?

7. Which of the following is not a characteristic of a cell?

3). size

4). Meaning

What value can a cell take?

1). numeric

2). text

3). returned

4). all listed

9. What can be a function argument?

2). constant

3). function

4). all options are correct

Specifying a cell address in a formula is called...

2). function

3). operator

4). cell name

Excel is used for.

1). creation text documents

2). creating spreadsheets

3). creation graphic images

4). all options are correct

What character does a formula start with in Excel?

3). space

4). no matter from which one

13. What is any diagram based on?

1). Excel Workbooks

2). graphic file

3). text file

4). table data

14. Which option correctly indicates the sequence of execution of operators in the formula?

1). + and - then * and /

2). comparison operators then reference operators

3). reference operators then comparison operators

4). * and then %

The minimum component of the table is...

1). cell

2). formula

4). there is no right answer

16. What is the SUM function used for?

1). To obtain the sum of squares of the specified numbers

2). to get the sum of the specified numbers

3). to obtain the difference between the sums of numbers

4). to obtain the square of the specified numbers

How many types of cell addressing are there in Excel?

4). four

18. What does Excel do if there is an error in the formula?

1). returns 0 as cell value

2). displays error type message as cell value

3). corrects an error in the formula

4). deletes a formula with an error

19. What is the "Form..." command window used for?

1). to fill table entries

2). to format the table

3). to check spelling on a sheet

4). to filter table records by condition

Which link is absolute?

Arranging the values ​​of a range of cells in a specific sequence is called...

1). formatting

2). filtration

3). grouping

4). Sorting

Cell addressing in spreadsheets that maintains a reference to a specific cell or area is called....

1). specific

2). relative

3). absolute

4). Combined

4). True

26. The range of cells A1:D3 of the MS EXCEL spreadsheet is selected. Range contains...

1). 6 cells

2). 9 cells

3). 2 cells

4). 12 cells

The range of criteria is used in MS Excel when...

1). using an advanced filter

2). applying an autofilter

3). data consolidation

4). building pivot tables

Conditional formatting in Excel is the ability to set your own style when some condition is met. This function is necessary for visual data analysis. Highlighting table cells will help you quickly analyze information.

Excel File Format

When there are many rows and columns in an Excel table, it is difficult to take in all the information in the cells. In this case, there is a danger of missing important data and analyzing it incorrectly. If the table is automatically colored in different colors (conditional formatting), this will help you see the full picture without looking at each cell.

Let's look at how to set the cell format in Excel using the example of comparing two columns - the number of products planned for release and those actually produced.

Let's mark in red those lines where the plan of 2000 units was not fulfilled. To do this, you need to specify a criterion for setting the style. Let's click on cell C2 - it is the first and stores information about released products. Conditional formatting is performed using the conditional formatting icon on the toolbar.

After clicking on it we will see a menu where we can implement conditional formatting. Use the “Less” option.


When selected, a window will open in which you can set the value and select colors.


Let's enter the boundary parameter 2000 and select red color. Click "OK". The cell will become colored because its value is less than the specified value.

To perform an action on the entire column at once, select it.


This way we will immediately see the months in which the minimum plan was not met.

Conditionally format cells in Excel based on values ​​in another range

It is often necessary to make a style dependent not on a specific value, but on data from another cell. In this case, we will indicate the address, not the number.


Please note that the absolute cell address ($E$3) is indicated in the window. In this case, this is acceptable, since the comparison is specified for a fixed cell.
After clicking “OK” we will see highlighting in those cells where the value is less than the specified value.


If you enter a new value in cell E3, the appearance in the Excel table will change.


To compare with a range, you need to change the condition slightly. Highlight the column with actual products released and click on the conditional formatting button again. When you select the Less than option in the window, select the first cell in the Planned column. This can be done with the mouse.


We again see absolute addressing, which is set by default. We need to remove the "$" sign if we want to get a conditional formatting shift when setting styles.


In this case, the style will be calculated based on the comparison of two paired cells.

Instead of numbers, you can use any values: string, date, etc. For example, let's select all the cars at the rental point that are in reserve. The value “p” means that the car is free, and “c” means that it is rented.

Let's set the color green for available cars. We will need the "Equal" condition.


We will indicate “r” as the value.


Now we can easily see which car is available.


To check a rule, you need to use the “Manage Rules” item in the conditional formatting menu.

When you select it, a window with a list of rules will open.


You can adjust Excel's conditional formatting rule or set a new one.

Conditional formatting rules in Excel with multiple conditions

Often one condition is not enough. It may be necessary to highlight cells based on two, three, or more values. In this case, you can apply conditional formatting rules sequentially to the same range. For example, in the last example we denote occupied cars in red.
Select the range again and select the “Equal” rule. Let's specify the value "c" and set the color to red.


However, the old conditional formatting rule will remain and will be in effect. This can be seen in management.


Another way is to use the “Create Rule” item. Select the range and select it from the Conditional Formatting menu.


Then set a rule for cells that contain specific value.


Next, select colors for decoration. To do this, click the “Format” button.


Create another rule.

Date Format in Excel

In cases where you need to highlight specific date, you can use conditional formatting for temporary values ​​in Excel. To do this, select "Date" in the "Cell Formatting Rules" section.


You can see everything in the drop-down menu possible options.


It is noteworthy that when opening a document, the date values ​​are compared with the current one, and if any rule has changed (for example, it is no longer “Yesterday”), then the style for it changes. This is very convenient when working with a document again.
For example, by selecting the condition “In the current month” for November, we will get only those rows that fall under this condition highlighted in red.

Excel table format using formulas

You can also make style dependent on formulas and functions. This conditional formatting may be needed if you are counting balances, or amounts, etc.
For our first example, let's highlight those cells in which the value of output is greater than the average for the year.
Select the range and set a new rule using a formula.


The AVERAGE function calculates the average value for a specified range.
This way you can set the style for any cell.

Setting a style for an entire line

To apply the design to the entire table, select it as the range. For example, for our cars we will set the following rule:


And we apply it to the entire range.


This will color the entire table.

Notice the first dollar in the formula. It means that the column is fixed, and only the row number will change. It must be installed, otherwise the style will not be applied to the entire line.

Number format in Excel

Finally, we’ll tell you how to quickly set the number format in Excel. Sometimes your number suddenly turns into text or a date. If this happens, convert the value to required format.
Place the pointer over the cell containing the number and click the square in the lower right corner of the Number panel of the Home tab.


In the window, select the desired value and set the number of decimal places. You can also choose how negative numbers are displayed.


The “Digit group separator” checkbox will allow you to set a space between the digits of a number.


You can also set the currency or financial format.


Use conditional formatting to add clarity to your tables. This will speed up data analysis and processing.

Have a great day!

Looking at the dry numbers in the tables, it is difficult to grasp the overall picture they represent at first glance. But, in the program Microsoft Excel There is 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 see how to use conditional formatting in Microsoft program 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 richer color scales.

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 established in Excel program, 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:

1. Format all cells based on their values;
2. Format only cells that contain;
3. Format only the first and last values;
4. Format only values ​​that are above or below the average;
5. Format only unique or duplicate values;
6. 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.