How can conditional formatting be used

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. Click Add New Rule and click Set condition. Click Shipment Order Date in the left field to set a condition in this column. Instead of defining criteria as we have been doing , click select from a list. The list of values in the Shipment Order Date column appear. Click the conditional formatting icon on the toolbar and click Add New Rule.

You can download a sample workbook that contains different examples of applying conditional formatting, both with standard rules such as top and bottom, duplicates, Data Bars, Icon Sets and Color Scales, as well as manually creating rules of your own. Download: Conditional formatting examples in Excel.

Color scales are visual guides that help you understand data distribution and variation. A two-color scale helps you compare a range of cells by using a gradation of two colors. The shade of the color represents higher or lower values. For example, in a green and yellow color scale, as shown below, you can specify that higher value cells have a more green color and lower value cells have a more yellow color. Tip: You can sort cells that have this format by their color - just use the context menu.

Tip: If any cells in the selection contain a formula that returns an error, the conditional formatting is not applied to those cells.

Hover over the color scale icons to see which icon is a two-color scale. The top color represents higher values, and the bottom color represents lower values. You can change the method of scoping for fields in the Values area of a PivotTable report by using the Formatting Options button that appears next to a PivotTable field that has conditional formatting applied.

The Conditional Formatting Rules Manager dialog box appears. To add a completely new conditional format, click New Rule. The New Formatting Rule dialog box appears. To add a new conditional format based on one that is already listed, select the rule, then click Duplicate Rule.

The duplicate rule appears in the dialog box. Select the duplicate, then select Edit Rule. The Edit Formatting Rule dialog box appears. Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box.

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, and then by selecting Expand Dialog.

Select the rule, and then click Edit rule. Under Select a Rule Type , click Format all cells based on their values default. To select a type in the Type box for Minimum and Maximum , do one of the following:. In this case, you do not enter a Minimum and Maximum Value.

Use a percentage when you want to visualize all values proportionally because the distribution of values is proportional. Valid percentiles are from 0 zero to Use a percentile when you want to visualize a group of high values such as the top 20thpercentile 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.

Format a formula result: Select Formula and then enter values for Minimum and Maximum. Make sure that the Minimum value is less than the Maximum value.

You can choose a different type for the Minimum and Maximum. For example, you can choose a number for Minimum a percentage for Maximum. To choose a Minimum and Maximum color scale, click Color for each, and then select a color. If you want to choose additional colors or create a custom color, click More Colors. The color scale you select is shown in the Preview box. A three-color scale helps you compare a range of cells by using a gradation of three colors.

The shade of the color represents higher, middle, or lower values. For example, in a green, yellow, and red color scale, you can specify that higher value cells have a green color, middle value cells have a yellow color, and lower value cells have a red color.

Select a three-color scale. The top color represents higher values, the center color represents middle values, and the bottom color represents lower values. You can change the method of scoping for fields in the Values area of a PivotTable report by using the Formatting Options button that appears next to a PivotTable field that has conditional formatting applied.. To add a new conditional format, click New Rule. The duplicate rule is copied and appears in the dialog box.

Selection: Click Just these cells. Under Select a Rule Type , click Format all cells based on their values. 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. Verifying data is an important task, and Excel's conditional formatting can help by alerting you to inconsistencies.

Figure S shows a common accounting tool known as cross-footing -- the process of double-checking totals by comparing subtotals across rows and columns -- in cell F Adding the conditional format makes the discrepancy hard to miss when the two totaling values don't match. Select either of the cross-foot formulas and apply the rule shown in Figure T.

It's easy to find the smallest or largest value using the predefined rule shown in Figure U , but you'll need a more complex rule to highlight the smallest or largest duplicate value. As you can see in Figure V , the value 3 is the smallest value in the column, but Excel highlights each occurrence of the value 5. This rule is unstable if you use normal referencing, so apply a range name to your data set before applying the conditional formatting rule.

The rule shown in Figure W will highlight the value 3 in the range named List only if 3 is also a duplicate. Be your company's Microsoft insider by reading these Windows and Office tips, tricks, and cheat sheets. Delivered Mondays and Wednesdays. Affiliate disclosure: TechRepublic may earn a commission from the products and services featured on this page.

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals. Watch Now. What's hot at TechRepublic Windows Tips on installation, security and more free PDF 10 ways to avoid work-from-home burnout Windowsfx is the Linux distribution Windows users have been looking for Zorin OS 16 is exactly what a Linux desktop distribution should be.

Microsoft Weekly Newsletter Be your company's Microsoft insider by reading these Windows and Office tips, tricks, and cheat sheets. Delivered Mondays and Wednesdays Sign up today. Editor's Picks. Linux kernel 5. Windows Do these 11 things immediately. Top 5 things to do about your tech before you die.

If you're not using a kanban board, you're not as productive as you could be. Comment and share: 10 cool ways to use Excel's conditional formatting feature. Show Comments. Hide Comments. My Profile Log out. Join Discussion.