Using conditional formatting in MS Excel spreadsheets, you can make cells more expressive, which is achieved by changing the color, frame, fill, and other parameters during certain actions.
The concept of the instrument in question
It needs to be used where there is no access to "1C", but it is necessary to display relevant information in minutes or even seconds. For this purpose, you can use the Conditional Formatting tool of the Microsoft table processor.
Data on various products can be entered in this program. There can be a very large number of them, and it may be necessary to track them by various parameters.
Conditional formatting in Excel according to the conditions that are specified is carried out by highlighting cells or a certain range with color, a set of icons or histograms.
Tracking of incoming and shipped products can be carried out using a filter. But it’s more convenient to render. Expired products may be assigned a red color, for those goods for which the expiration date will soon come - yellow, etc. These data are entered once when filling out the table. After that, when you open the data, you will see that it turns red, that it turns yellow.
Possibilities of use in different versions
Conditional formatting in Excel 2003 and later differs dramatically. This consists in the fact that in the first program there are only three possible options for setting this condition - based on values or formulas.
Since 2007, the number of formatting conditions has increased dramatically and there are more visual effects.
Let's look at examples when using Excel 2016. Earlier versions starting from 2007 have minor differences that the user can figure out on their own.
Simple example
Create a new book. On the tab “Home” we are looking for the section “Styles” and in it we see the button “Conditional formatting”. The arrow located on it allows you to select conditions and set certain rules.
Before that, you must enter certain data in this book. Then you need to make their choice. After that, click on the previously named button and select "Cell selection rules." Here we set the condition by the arrow directing to the drop-down menu on the right.
A dialog box opens, on the left we set the specific parameter that we need. On the right we indicate the condition for displaying values that will coincide with the condition specified earlier.
We confirm the changes made by pressing Enter or by clicking on OK.
Using the tool by the value of another cell
Suppose that in some database you need to determine numbers that exceed certain standards. This suggests that certain values can be set in one column, and a specific standard in the other.
In this case, the conditional formatting rules are set according to the range in which certain values that make up the database are entered. After selecting it, we click on the above button and through the first item we select the necessary condition (more, between, equal, etc.). Then we point to the cell in which the norm is entered. This link will be automatically inserted into the dialog box with the same name with the condition that was set earlier. On the right in this window, select the rule for displaying information that is formatted. At the bottom of the list there is the opportunity to create your own version if we do not agree with the proposals.
Compare different databases
Conditional formatting of cells in Excel can take place over some ranges. For example, in column A, some values are entered, and in column B, others. We select the first of them from top to bottom. Click on the familiar button, select the same items. After the appearance of the dialog box with the same name with the selected condition, in the left part we enter the relative link (= {cell_address}) from the second range. If you simply click on the desired cell in this range, a link appears with signs of the American dollar, they will need to be removed.
Relative link refers to one of them that changes in the formula with the dynamics of the address in relation to a new place. If both the alphabetic and the digital values of the cell address are preceded by an American dollar sign, then they are talking about an absolute link. Here in the formula, the values of this cell will not change with any movement. Mixed links are also highlighted, in which a dollar sign is placed in front of one component of the cell address. In this case, the movement will be carried out either by column or by line with the appropriate copying of the formula.
In the case of the wrong selection of one range and the same choice of links in the second, the analysis will fail. The correct algorithm assumes the selection of cells in the first column from the bottom up, and in the second column of the first significant cell.
The first option for creating rules for several conditions
We select a range of cells (by the way, in Excel, conditional formatting of a row can be freely carried out, not just columns). We go all the way to create a specific rule. In the dialog box, we set the necessary parameter on the left, and on the right - the way the data is displayed, which will be applied to the information that matches the previously selected condition. We confirm the entered changes.
We repeat the procedure for another parameter, but with a different way of displaying data. This action can be repeated as many times as necessary. As a result, we get a multi-colored table.
You can use another option, which will be discussed below.
The second option for creating rules for several conditions
Select the required range and when you click on the button in the "Styles" group, select "Create Rule". In the dialog that appears in the upper part, click on "Use formula ... formatted cells". At the bottom of this window, specify a relative or mixed link in manual or automatic mode by pointing to a specific cell. After the link, we put the arithmetic sign of the applicable condition. In the lower part of this window, click on the "Format" button and select the selection condition.
The same actions are performed for other conditional formatting values.
The process in relation to the date
Many databases contain columns or rows with the name "Date". To do this, select the specified range, click on the button of the same name with the tool used, select the rule for selecting cells and there we find the “Date” tab.
In the left part of the dialog box that appears, select the condition for their selection (for example, next week), confirm and get as a result the selection of those cells by conditional formatting, which by dates correspond to the next week. Similarly, you can set similar rules for other parameters.
Applying the tool to a row by the value specified in the cell
In this case, a row is highlighted, and the condition for the formatting under consideration is a certain value in a certain cell. Here, logical functions that begin with the word “IF” are most often used, the link to the row is absolute, and the link to the cell is mixed.
When you click on "Format", select a color for a given condition. As a result, we get a multi-colored table in which rows, not columns, will differ in color.
Using formulas when using this tool
Suppose we need to isolate those numbers that are odd from the existing range. To do this, click on the button on the toolbar of the same name with the tool in question, select "Create Rule", and there "Use the formula ... formatted cells". At the bottom, enter the following formula: = OSTAT ($ A1: 2) = 1 - and set the format that suits us. We confirm the introduced changes, as a result, in the table those numbers that are odd in column A will be highlighted in the specified color.
Creating an Informational Message
We select a certain range, for example, in column A. In cell B1 we enter the formula (= IF (A1> {certain_number}; "excess"; IF {A1> {next_defined_number}; "pay attention")). This formula can include many values, depending on how many informational messages are needed.
We click on Enter, as a result, in B1 one of the values of the formula appears, which was entered in quotation marks in it. We stretch this formula to the end of the column in accordance with the number of rows with values in the selected range.
After that, for each obtained value in column B, we create a conditional formatting rule. In the dialog box, select "Format only cells that contain", at the bottom select "Text", "contains" and what it should contain. Set the format. Repeat this operation for each informational message.
Finally
Conditional formatting allows the user to select some data according to specified conditions for visual displaying by those that are important for a particular person in a certain time period. The article discusses how this tool can be used in simple cases, as well as in more complex ones - using formulas, informational messages. With these examples, the use of the tool in question is not limited. Only the most common use cases are shown here.