The Conditional Formatting (CF) function in Excel 2021 lets you to style a range of numbers in such a way that unexpected or undesired values, as well as values separate of particular boundaries, are instantly structured to draw attention to them. A drop-down menu displays when you select the Conditional Formatting icon in the Styles category on the Ribbon’s Home tab. It has the following features:
- High spot Cells Rules provide a window with more options for establishing formatting guidelines that marks cells in your cell choice that have specific text, values, or the dates, or values more or lower than a specific value, and that drop within specific value ranges.
- Top/Down Rules brings up a new menu with more options for establishing formatting rules which emphasize the cell selection’s upper and bottom values, fractions, and directly above and underneath the overall average.
- By selecting the data bar icon, a palette with several shaded data bars appears which you may apply to the cell selection to represent their values compared to each other.
- By selecting the color scale thumbnail, a palette with different three- and two-colored scales appears, which you can use to the cell selection to display their values compared to one another other.
- Icon Sets exposes a palette with several icons that you may use to represent the relative values of cell selections by selecting the image set.
- The New Rule dialogue box appears where you may make a personalize conditional formatting rule to use to the cell choice.
- Clear Rules brings up a menu where one may eliminate conditional formatting (CF) guidelines for the chosen cells, the complete worksheet, or just the active data table by choosing Selected Cells, Entire Sheet, or This Table.
- Manage Rules brings up your Conditional Formatting (CF) Rules Manager dialogue box, in which you may delete and edit specific rules and change your rule priority by shifting them down or up in your Rules selection box.
Conditional Graphical Formatting
The collections of graphical markers pop-up palettes associated with the Color Scales, Data Bars, and icon Sets choices on the Conditional Formatting key’s drop-down menu are perhaps the nicest (and the simplest) conditional styling that you can add to a cell range:
- The length of the color bar in each cell shows the relative values in the cell choice – data bars are a wonderful method to rapidly find the high and low values within a vast pool of observations.
- Color scales use a one-, two-, or three-color gradient to categorize the relative values in a cell selection; they’re useful for detecting the distribution of values across a wide range of data.
- Icon Sets divide the values in a cell pick into three to five subcategories, with each icon representing a range of values ranging from high to low. Icon sets are useful for quickly distinguishing different intervals in a large amount of data.
Using the Quick Analysis Tool to Format
The Quick Analysis feature in Excel 2021 allows you to apply conditional formatting to chosen cell ranges. The diagram below shows how this works. Before choosing the Quick Analysis button, which displays in the lower-right below the last selected cell, we picked the cell range E3:E13, having a copy of the range of outcomes from 6 to 1 and 1 to 6 for this figure.
Creating Your Custom Style Guidelines for Conditional Formatting
Even though Excel 2021 provides a plethora of pre-defined Highlight Cell Rules and Top/Bottom Rules to design, you may still need to develop your own conditional formatting rules. To do so, click the New Rule icon in the Conditional Formatting Rules Editor dialogue box or select the New Rule option near the bottom of the Conditional Formatting button’s drop-down menu.
Once you select the New Rule option or button, the New Formatting Rule dialogue box opens, as shown in the image below. To make a new conditional formatting rule, select the type of rule you want to make in the Select a Rule Type list box, then explicitly state the requirements and describe the formatting using the choices in the Update the Rule Summary section below. Note that the choices differ greatly depending on the type of rule you choose in the Select a Rule Type list