Why is conditional formatting used in excel

Custom Conditions: For situations where you want to manipulate a preset condition, you can create your own rules. If appropriate, you can use Excel formulas in the rules you write. Applying Multiple Conditions: You can apply multiple rules to a single cell or range of cells. Step 1: Apply Highlight Rules to Your Excel Spreadsheet Highlight rules apply color formatting to cells that meet specific criteria that you define. Open an existing spreadsheet in Excel, or start from scratch and manually enter new data.

Click the top of the Total value of stock column to select these cells. Step 3: Apply Data Bars Data bars apply a visual bar within each cell. Click the top of the Qty. They function identically; just select the option and color you prefer. Click the top of column D to select this range. Step 5: Apply Icon Sets Icon sets apply colorful icons to data. Click the top of the Purchase price column to select the range of values.

You can choose any of these to fit the needs of your data. Click Conditional Formatting and select Manage Rules… from the dropdown list. How to Apply Basic Conditional Formatting in Smartsheet Smartsheet is a spreadsheet-inspired work management tool that also provides conditional formatting. Then, select Import under the Solution Center section. A box will appear where you can set conditional formatting rules. Click Add New Rule in the top left corner. The if-then logic is already written into the new rule, so you can simply create the conditions.

To create Progress Bars in Smartsheet, you first need to create a new Symbols column. Cells in a Symbols column will only hold special characters, such as progress bars. To create a new column, right-click the Qty. Right-click the Qty. Step 5: Edit and Delete Conditional Formatting Rules Editing and deleting conditional formatting rules in Smartsheet is extremely easy.

To edit a rule , click the Conditional Formatting icon on the toolbar to open the list of rules. Click the condition you wish to change and edit the information in the box that opens. Click OK. Advanced Conditional Formatting Functions in Excel For more advanced Excel users, familiarize yourself with the following conditional formatting functions to add even more customization to your spreadsheet.

Step 1: Create a New Rule and Apply Stop if true Rule In some instances, you might want to stop a certain condition, without deleting the entire rule.

In our example, we applied an icon set of three directional arrows to the Purchase price column to indicate low, medium, and high price ranges.

However, we might actually only want to call attention to the lowest cost items, as three icons can clutter the sheet and provide more information than actually needed. First, create a separate condition on this column. Click the Purchase price column to select these values. Keep the default Show formatting rules for: Current Selection from the dropdown menu, because we are only adjusting the rule on this column. Click the top of the Item column to select this range of cells.

In the dialogue box, select Classic from the top dropdown list. We want to search cells in the Item column for a B and L , and highlight that cell when both conditions are true. Step 3: Conditional Formatting Based on Another Cell You can also create rules to highlight certain cells based on the value of another cell. However, that threshold might change over time, depending on manufacturing or selling rates. So, we want to create a highlight rule that is dependent upon a threshold that we set.

To the right of your data, create a box for Quantity Threshold and choose an amount. In this example, we use Note the cell you type the amount in. Step 4: Data Validation and Dropdown Lists While data validation is not technically monitored through conditional formatting rules, you can use it to a similar effect: controlling the formatting of your sheet.

First, click where you want to add the column on the sheet. Step 5: Rule Hierarchy and Precedence As you accumulate conditional formatting rules, watch out for rule hierarchy. Adjusting rule hierarchy in Excel is straightforward, but you should also understand the logic behind rule precedence: Newer rules will always assume precedence over older rules. This means that the precedence of your rules will be in the reverse order of how you created them. When multiple rules evaluate as true to a cell, they may or may not conflict.

Applying multiple rules to a cell does not necessarily mean that they will interfere. For instance, if one rule changes text color and another changes fill color both rules should co-exist in the cell.

Rules conflict when the outcomes are the same. For instance, if one rule changes a font color to green and another changes the font color to blue the newer rule takes precedence. To adjust rule hierarchy in Excel, follow these steps: All rule hierarchy is controlled through the Rule Manager in Excel.

Select This Sheet from the top dropdown list to pull up all lists applied to the current sheet. To change the order hierarchy of rules, select a rule and click the up and down arrows in the top right corner to move it.

Remember the rule closest to the top will take precedence. Add a new rule by clicking the conditional formatting icon on the toolbar. Rule 2 clearly states that all cells in column C Qty. Now if we change the order of the rules, the cells will actually be colored red instead of yellow. Remember: The most important rules go on top and the order descends according to importance.

When going through large sets of data, the chance of you missing a specific cell increases as the data increases. Coloring the specific important cells may be the solution in some cases, but in other cases where the data sets are huge, we need a more visual solution. We change the entire column to be colored if one cell in it meets a certain criterion. He has 4 different assignments to do on his job. Some he does every day. Others every now and then. Interested in learning more about formatting your spreadsheet?

One last step: just let us know where should we should send it. Tip 1: Skip to important data with highlighted cells. That can be a pretty tedious process — especially if you have a large set of data. Instead, let conditional formatting do the job! Download Our Free Sample File. If you just click OK now, all the quantities that are below will be formatted this way. Read the next tip to learn how…. Tip 2: Highlight cells that contain text.

In tip 1 you learned how to highlight values in your sheet that are below Basically, you just enter the text that you want the formatting to apply to. Tip 3: Editing a conditional formatting rule. You can edit the entire rule from the conditional formatting rules manager. Find the rule that you want to change in the list. Another pop-up box appears. From here you can change anything about the rule.

You can also make the rule more advanced by basing it on a formula. This means that the formatting applies when a cell is empty or, for example, holds a value greater than, or less than, Here you enter your new text. From this little familiar box you can change the formatting that you applied in the first place. Click the formula and change the area. So if you screw one up? Tip 4: Delete a conditional formatting rule.

Sometimes you see a workbook with too many conditional formatting rules. Removing all conditional formatting rules in a sheet is very straightforward. And that is, very simply, how you delete conditional formatting rules in Excel.

Tip 5: Finding the bottom 8 items of your data. In the field to the left, you can click the tiny arrows to change the number of items from the bottom. If you want to look at the 20 items that are lowest in your inventory, then set the number to The preset is 10, so we change this to 8.

In the field to the right, you can change which formatting that is applied to the 8 bottom items. Click the drop-down arrow to choose between the different presets. Tip 6: Using data bars to give immediate overview of highs and lows. Select a type for Minimum , Midpoint , and Maximum. Do one of the following:. Format lowest and highest values: Select a Midpoint. In this case, you do not enter a Lowest and Highest Value. Format a number, date, or time value: Select Number and then enter a value for Minimum , Midpoint , and Maximum.

Valid values are from 0 zero to Use a percentage when you want to visualize all values proportionally, because using a percentage ensures that the distribution of values is proportional. Use a percentile when you want to visualize a group of high values such as the top 20th percentile in one color grade proportion and low values such as the bottom 20th percentile in another color grade proportion, because they represent extreme values that might skew the visualization of your data.

The formula must return a number, date, or time value. Invalid formulas result in no formatting being applied. It's a good idea to test the formula to make sure that it doesn't return an error value. You can set minimum, midpoint, and maximum values for the range of cells. Make sure that the value in Minimum is less than the value in Midpoint , which in turn is less than the value in Maximum. You can choose a different type for Minimum , Midpoint , and Maximum.

For example, you can choose a Minimum number, Midpoint percentile, and Maximum percent. In many cases, the default Midpoint value of 50 percent works best, but you can adjust this to fit unique requirements. To choose a Minimum , Midpoint , and Maximum color scale, click Color for each, and then select a color. A data bar helps you see the value of a cell relative to other cells.

The length of the data bar represents the value in the cell. A longer bar represents a higher value, and a shorter bar represents a lower value. Data bars are useful in spotting higher and lower numbers, especially with large amounts of data, such as top selling and bottom selling toys in a holiday sales report. The example shown here uses data bars to highlight dramatic positive and negative values. You can format data bars so that the data bar starts in the middle of the cell, and stretches to the left for negative values.

Tip: If any cells in the range contain a formula that returns an error, the conditional formatting is not applied to those cells. On the Home tab, in the Style group, click the arrow next to Conditional Formatting , click Data Bars , and then select a data bar icon.

You can change the method of scoping for fields in the Values area of a PivotTable report by using the Apply formatting rule to option button. To add a conditional format, click New Rule. Select a Minimum and Maximum Type. In this case, you do not enter a value for Minimum and Maximum. Format a percentage: Select Percent and then enter a value for Minimum and Maximum.

Format a percentile Select Percentile and then enter a value for Minimum and Maximum. Use a percentile when you want to visualize a group of high values such as the top 20th percentile in one data bar proportion and low values such as the bottom 20th percentile in another data bar proportion, because they represent extreme values that might skew the visualization of your data.

Format a formula result Select Formula , and then enter a value for Minimum and Maximum. You can choose a different type for Minimum and Maximum. For example, you can choose a Minimum number and a Maximum percent.

To choose a Minimum and Maximum color scale, click Bar Color. The bar color you select is shown in the Preview box. To show only the data bar and not the value in the cell, select Show Bar Only. To apply a solid border to data bars, select Solid Border in the Border list box and choose a color for the border. To choose between a solid bar and a gradiated bar, choose Solid Fill or Gradient Fill in the Fill list box. To format negative bars, click Negative Value and Axis and then, in the Negative Value and Axis Settings dialog box, choose options for the negative bar fill and border colors.

You can choose position settings and a color for the axis. When you are finished selecting options, click OK. You can change the direction of bars by choosing a setting in the Bar Direction list box. This is set to Context by default, but you can choose between a left-to-right and a right-to-left direction, depending on how you want to present your data.

Use an icon set to annotate and classify data into three to five categories separated by a threshold value. Each icon represents a range of values. For example, in the 3 Arrows icon set, the green up arrow represents higher values, the yellow sideways arrow represents middle values, and the red down arrow represents lower values.

Tip: You can sort cells that have this format by their icon - just use the context menu. You can choose to show icons only for cells that meet a condition; for example, displaying a warning icon for those cells that fall below a critical value and no icons for those that exceed it.

To do this, you hide icons by selecting No Cell Icon from the icon drop-down list next to the icon when you are setting conditions.

You can also create your own combination of icon sets; for example, a green "symbol" check mark, a yellow "traffic light", and a red "flag. On the Home tab, in the Style group, click the arrow next to Conditional Formatting , click Icon Set , and then select an icon set. Select an icon set. The default is 3 Traffic Lights Unrimmed. The number of icons and the default comparison operators and threshold values for each icon can vary for each icon set. You can adjust the comparison operators and threshold values.

The default range of values for each icon are equal in size, but you can adjust these to fit your unique requirements. Make sure that the thresholds are in a logical sequence of highest to lowest from top to bottom. Format a number, date, or time value: Select Number. Format a percentage: Select Percent. Format a percentile: Select Percentile.

Use a percentile when you want to visualize a group of high values such as the top 20th percentile using a particular icon and low values such as the bottom 20th percentile using another icon, because they represent extreme values that might skew the visualization of your data.

Format a formula result: Select Formula , and then enter a formula in each Value box. To make the first icon represent lower values and the last icon represent higher values, select Reverse Icon Order. The size of the icon shown depends on the font size that is used in that cell.

As the size of the font is increased, the size of the icon increases proportionally. To more easily find specific cells, you can format them by using a comparison operator. For example, in an inventory worksheet sorted by categories, you could highlight products with fewer than 10 items on hand in yellow. Note: You cannot conditionally format fields in the Values area of a PivotTable report by text or by date, only by number.

If you'd like to watch videos of these techniques, see Video: Conditionally format text and Video: Conditionally format dates. Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box to temporarily hide the dialog box, by selecting the new range of cells on the worksheet or on other worksheets, and then by selecting Expand Dialog.

Under Select a Rule Type , click Format only cells that contain. Under Edit the Rule Description , in the Format only cells with list box, do one of the following:. Format by number, date, or time: Select Cell Value , select a comparison operator, and then enter a number, date, or time.

Format by text: Select Specific Text , choosing a comparison operator, and then enter text. For example, select Contains and then enter Silver , or select Starting with and then enter Tri.

Quotes are included in the search string, and you may use wildcard characters. The maximum length of a string is characters. To see a video of this technique, see Video: Conditionally format text. Format by date: Select Dates Occurring and then select a date comparison.

To see a video of this technique, see Video: Conditionally format dates. Format cells with blanks or no blanks: Select Blanks or No Blanks. A blank value is a cell that contains no data and is different from a cell that contains one or more spaces spaces are considered as text.

Format cells with error or no error values: Select Errors or No Errors. To specify a format, click Format. The Format Cells dialog box appears. Select the number, font, border, or fill format you want to apply when the cell value meets the condition, and then click OK. You can choose more than one format. The formats you select are shown in the Preview box.

You can find the highest and lowest values in a range of cells that are based on a cutoff value you specify. Under Select a Rule Type , click Format only top or bottom ranked values. Valid values are 1 to Optionally, change how the format is applied for fields in the Values area of a PivotTable report that are scoped by corresponding field.

By default, the conditional format is based on all visible values. However when you scope by corresponding field, instead of using all visible values, you can apply the conditional format for each combination of:. You can find values above or below an average or standard deviation in a range of cells. For example, you can find the above average performers in an annual performance review or you can locate manufactured materials that fall below two standard deviations in a quality rating.

Select the command you want, such as Above Average or Below Average. Under Select a Rule Type , click Format only values that are above or below average. Under Edit the Rule Description , in the Format values that are list box, do one of the following:. To format cells that are above or below the average for all of the cells in the range, select Above or Below.

To format cells that are above or below one, two, or three standard deviations for all of the cells in the range, select a standard deviation. By default, the conditionally format is based on all visible values. The formats you select are displayed in the Preview box. Note: You can't conditionally format fields in the Values area of a PivotTable report by unique or duplicate values. In the example shown here, conditional formatting is used on the Instructor column to find instructors that are teaching more than one class duplicate instructor names are highlighted in a pale red color.

Grade values that are found just once in the Grade column unique values are highlighted in a green color. Make sure that the appropriate worksheet or table is selected in the Show formatting rules for list box. Under Select a Rule Type , click Format only unique or duplicate values.

Under Edit the Rule Description , in the Format all list box, select unique or duplicate. Notes: If there's already a rule defined that you just want to work a bit differently, duplicate the rule and edit it. The duplicate rule then appears in the list. If you don't see the options that you want, you can use a formula to determine which cells to format - see the next section for steps. If you don't see the exact options you need when you create your own conditional formatting rule, you can use a logical formula to specify the formatting criteria.

For example, you may want to compare values in a selection to a result returned by a function or evaluate data in cells outside the selected range, which can be in another worksheet in the same workbook. Your formula must return True or False 1 or 0 , but you can use conditional logic to string together a set of corresponding conditional formats, such as different colors for each of a small set of text values for example, product category names.

Note: You can enter cell references in a formula by selecting cells directly on a worksheet or other worksheets. Selecting cells on the worksheet inserts absolute cell references. If you want Excel to adjust the references for each cell in the selected range, use relative cell references. For more information, see Create or change a cell reference and Switch between relative, absolute, and mixed references. Tip: If any cells contain a formula that returns an error, conditional formatting is not applied to those cells.

Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box to temporarily hide the dialog box, by selecting the new range of cells on the worksheet or other worksheets, and then by clicking Expand Dialog. Under Apply Rule To , to optionally change the scope for fields in the Values area of a PivotTable report, do the following:.

To scope by selection: Click Selected cells. Under Select a Rule Type , click Use a formula to determine which cells to format. Under Edit the Rule Description , in the Format values where this formula is true list box, enter a formula.