Comprehensive Tutorial for Excel Conditional Formatting

You can apply conditional formatting to your Excel spreadsheets using various tools and features found within the program. This step-by-step walkthrough shows you how to apply the most commonly used formatting for highlighting, data bars, color scales, and more.

Highlight rules apply color formatting to cells that meet specific criteria that you define. They are the most basic type of conditional formatting rule, and Excel provides a variety of preset highlight functions.

1. Open an existing spreadsheet in Excel, orstart from scratch and manually enter new data. In this example, we’re using an inventory list that tracks the number of each item currently in stock, as well as some additional information about each product.
   
   
2. Toapply highlight rules, select the range of values you want to apply a rule to. For this example, we want to highlight any product that’s quantity is less than 100 units. So, select the values in theQty.column (C4:C26).
3. From the Home tab, clickConditional Formattingon the right side of the toolbar, and clickHighlight Cells Rulesfrom the dropdown menu. ClickLess than.
   
   
4. A box will appear. Type100in the empty field. ClickOK.
   
   
5. Your spreadsheet will now reflect this highlight rule, with the quantities less than 100 highlighted red with red text.
   
   Tip:You can change the color of the highlighted cell by clicking on theFormat with:dropdown menu and selecting on another option.
   
   
   
6. Tohighlight text cells, repeat step 2. This time, we want to highlight certain model types (M compatible), so we’ll select the cells in theItem namecolumn.
7. ClickConditional Formatting > Highlight Cells Rules > Text that Contains…
   
   
8. TypeM compatiblein the text box. To differentiate from our previous highlight rule, select green fill with dark green text from theFormat with:dropdown menu. ClickOK.
   
   
9. Now, cells containing the textM compatibleare highlighted.
   
   

Using these same steps and menu options, you can apply highlight rules to findDuplicate Values,Dates, or values that areGreater than…,Equal to…, orBetween…values that you select. All of these possibilities are available through the menu options.

Top/Bottom rules are another useful preset in Excel. These rules allow you to call attention to the top or bottom range of cells, which you can specify by number, percentage, or average.

1. In this example, we’ll highlight the bottom five total stock values. Click the top of theTotal value of stockcolumn to select these cells.
2. ClickConditional Formatting > Top/Bottom Rules > Bottom 10 Items…(The default number and percent in Top/Bottom rules in Excel is 10, but you can change that number in theNew Formatting Rulebox.)
   
   
3. In the box that appears, change10to5, since we only want the bottom five values. Since we already have a red fill highlight rule, click the dropdown menu and select yellow fill with dark yellow text. ClickOK.
   
   
4. Your sheet will now highlight the bottom five values in theTotal value of stockcolumn, and update as you add to your data set.
   
   

Data bars apply a visual bar within each cell. The length of the bar relates the value of the cell to other cell values in the selected range.

1. We’ll apply data bars to theQty.column so we can easily assess the ratios of items in stock. Click the top of theQty.column to select this range of cells.
2. ClickConditional Formatting > Data Bars. You’ll see two options - one forGradient Filland one forSolid Fill. They function identically; just select the option and color you prefer.
   
   
3. Your sheet will now reflect the added rule.
   
   

Color scales are similar to data bars in that they relate a cell’s values in a selected range. However, instead of representing this relationship by the length of a bar, color scales do so with color gradients. One color is assigned the “lowest” value and another the “highest,” with a range of colors in between.

1. We’ll apply color scales to ourSellingpricecolumn. Click the top of column D to select this range.
2. ClickConditional Formatting > Color Scales. You’ll see a variety of different color ranges; select the one you want.
   
   
3. Your spreadsheet now shows the selling prices by color - red cells are the most expensive, and green cells are the least expensive.
   
   

图标集应用彩色图标的数据。他们是年代imply another way to call attention to important data, and relate cells to one another.

1. We’ll apply icon sets to thePurchase pricecolumn to show low, middle, and high priced items. Click the top of thePurchase pricecolumn to select the range of values.
2. ClickConditional Formatting > Icon Sets. You’ll see a variety of options forDirectional,Shapes,Indicators, andRatingsicons. You can choose any of these to fit the needs of your data. In this example, we’ll choose the firstDirectionaloption: red, yellow, and green arrows that indicate high, middle, or low priced items.
   
   
3. Your sheet reflects this new formatting rule.
   
   

现在您已经了解了最常见的条件matting presets in Excel, your spreadsheet provides a lot of information at a glance. However, you might want to edit some of these rules later on, or delete them completely.

1. ClickConditional Formattingand selectManage Rules…from the dropdown list.
   
   
2. TheManage Rulesbox will appear. Click the dropdown menu at the top of the box and clickThis Sheetto list the conditional formatting rules you have applied to the current sheet.
   
   
3. Toedit a rule, click the rule you want to change. In this example, we want to highlight the bottom ten values in theTotal value of stockcolumn, rather than the bottom five that we currently have highlighted. Click theBottom 5row. Then, clickEdit Rule…at the bottom of the box.
   
   
4. A new box opens where you can adjust the conditions of the rule. Type10in the number field and clickOK.
   
   
5. Excel will bring you back to theManage Rulesbox. You must clickOKto save the changes you made to the original rule.
   
   
6. Todelete a rule, return to theManage Rulesbox and chooseThis Sheet. Click the rule you want to delete - in this case, the color grading onSelling price. Click the - symbol (next toEdit Rule…) in the lower left hand corner. ClickOK.
   
   
7. The conditional formatting on theSelling pricecolumn is now deleted.
   
   

You now have everything need to create basic conditional formatting using presets in Excel 2016. For more advanced functionality such as creating a new rule, using Excel formulas, and creating rules dependent on another cell, see the “Advanced” section below.

First, we’ll look at how we can apply the same conditional formatting rules we learned in Excel to another spreadsheet program, Smartsheet.

Use conditional formatting in Smartsheet to apply many of the same rules and effects available in Excel. We’ll guide you through every function described in the previous section, with some modifications that better align with Smartsheet features.

You can create spreadsheets in Smartsheet two ways: by manually entering data into Smartsheet, or by importing an existing spreadsheet from programs like Excel and MS Project. For this tutorial, we’ll use the same data set from the Excel tutorial, so we’ll import the original version (with no conditional formatting) from Excel.

1. From theHomescreen in Smartsheet, click the + icon. Then, select Import under theSolution Centersection.
   
   
2. Click onMicrosoft Exceland select the file you want to import.
3. Name your spreadsheet. We’ll call this oneInventory List. ClickImport.
   
   
4. Your spreadsheet will open, using the same column and row formatting that was applied in Excel. All conditional formatting is controlled through the conditional formatting icon in the toolbar at the top of the app.
   
   

1. Tohighlight a cell, click the conditional formatting icon on the toolbar.
2. A box will appear where you can set conditional formatting rules. ClickAdd New Rulein the top left corner. The if-then logic is already written into the new rule, so you can simply create the conditions.
   
   
3. Clickset condition. Just like in the Excel example, we want to highlight any items that are stocked below 100 units. SelectQty. from the column list on the left, select is less than from the center dropdown list, and then type100in the field on the right. ClickOK.
   
   
4. Now we need to apply the formatting. Clickthis formatand a dropdown menu with formatting options appears. For this example, we’ll use a paint fill. Click the paint bucket icon and select red.
   
   
5. 指定哪个细胞应该得到这种格式。Smartsheet will default to format the entire row, but we only want to highlight the cells in theQty.column. Clickentire rowand clickQty.from the dropdown list. ClickOK.
   
   
6. Your conditional formatting rule is now reflected in your spreadsheet.
   
   
7. Tohighlight text cells,repeat steps 1 and 2. Clickset condition. Now, we want to highlight anyM compatibleitems. SelectItem namefrom the left-most list, contains from the center list, and typeM compatiblein the right field. ClickOK.
   
   
8. Clickthis formatand click the paint bucket item. Select green. For this rule, we want tohighlight the entire row. (Again, Smartsheet defaults to this option.) ClickOK.
   
   
9. Your second rule is now reflected in your data set.
   
   

In place of Excel’s Data Bars preset, you can create Progress Bars in Smartsheet. Progress Bars are a symbol that you can apply to cells to show and compare the level of completeness. In this example, we’ll apply progress bars to denote current inventory levels (from zero to 100 percent).

1. Tocreate Progress Barsin Smartsheet, you first need to create a newSymbolscolumn. Cells in aSymbolscolumn will only hold special characters, such as progress bars.
2. To create a new column, right-click theQty.column and clickInsert Column Right.
   
   
3. A box will open. We’ll name this columnInventory Level, and selectSymbols...from the list of column-types. In the right-hand field, scroll down and select the progress bar icon. ClickOK.
   
   
4. Click into cells in the newInventory Levelcolumn and select the appropriate level of inventory:Empty, Quarter, Half, Three Quarter,orFull. This progress bar relates the current level (from theQty.column) to level you need.
   
   
5. Here’s what your sheet will look like when all progress bars are filled in.
   

Tip:You can also typeEmpty, Quarter, Half, Three Quarter,orFullinto each cell, and Smartsheet will autofill with the appropriate progress bar.

Tip: You can also automate progress bars by using an if-then formula in Smartsheet. For more information on how to do this, check out thisresourceon symbol formulas.

To apply icon sets in Smartsheet with conditional formatting, you’ll have to use formulas (we’ll get into that in the “Advanced” section). Instead, you can add informative icons to your data by creating a special Symbols column.

1. Right-click theQty.column and clickInsert Column Right.
   
   
2. In the box that opens, typeQty. Statusin the name field. ClickSymbolsfrom the dropdown menu and choose from the library of available symbols (Flag, Priority, Decision, Status, Direction,andMeasureicons). In this example, we’ll choose RYG balls in the Status section. ClickOK.
   
   
3. Now, you can update your sheet with the appropriate color status ball by adding conditional formatting as shown above.
   
   

Editing and deleting conditional formatting rules in Smartsheet is extremely easy.

1. Toedit a rule, click theConditional Formatting iconon the toolbar to open the list of rules. Click the condition you wish to change and edit the information in the box that opens. ClickOK.
   
   
2. Todelete a rule, click the carrot on the left side of each rule. ClickDelete Rulefrom the dropdown menu. ClickOK.
   
   

Tip:You can alsodisable a ruleby clickingDisable Rulefrom the dropdown menu and then clickingOK. This will put the rule on “hold” without deleting it, in case you want to re-enable it later on.

Now you know how to use conditional formatting and other colors and symbols to add formatting to your sheet in Smartsheet. In the following sections, we’ll walk you through more advanced conditional formatting functions in Excel and Smartsheet.

Once you have mastered the basics, Excel includes some additional advanced conditional formatting functions. We’ll guide you through applying stop-if-true rules, using the AND formula, setting rule hierarchies and precedence, and other unique situational formatting conditions below.

In some instances, you might want to stop a certain condition, without deleting the entire rule. TheStop if truerule in Excel enables you to do so.

1. In our example, we applied an icon set of three directional arrows to thePurchase pricecolumn 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).
2. First, create a separate condition on this column. Click thePurchase pricecolumn to select these values.
3. ClickConditional Formatting > Manage Rules…to open theManage Rulesbox. Keep the defaultShow formatting rules for: Current Selectionfrom the dropdown menu, because we are only adjusting the rule on this column.
   
   
4. Click the + in the bottom left corner tocreate a new rule. A new box opens where you can assign conditions to the new rule.
5. From theStylemenu, selectClassic.
6. Instead of applying a different icon to bottom, middle, and top price ranges (as we currently have), we only want to show the bottom 50 percent. So, selectTopfrom the left dropdown menu, type50in the middle field, and check thePercentbox.
   
   
7. To apply color formatting, SelectCustom formatfrom theFormat withdropdown menu, and a new formatting box opens. Click theFilltab, and clickNo Color.
   
   
8. Next, click theFonttab, and select black text. ClickOK.
   
   
9. ClickOKagain in the originalNew Formatting Rulebox.
10. You’ll return to the Manage Rules box, where you now have two rules applied to thePurchase pricecolumn. Click theStop if truebox to the right of the new rule you just created. ClickOK.
    
    
11. Your sheet will now only show the icon sets for the items with a purchase price in the bottom 50 percent.
    
    

Adding Excel’s formulas to your conditional formatting rules is one way to elevate your logical rules. The AND formula is one of the most popular, easy-to-use formulas. It lets you add multiple conditions within a single rule, rather than writing out each rule separately. To format cells they must meet both conditions.

1. 我们将create a new rule to highlight any cell in theItem #column that contains both a B and an L. Click the top of theItem #column to select this range of cells.
2. ClickConditional Formatting > New Rule.
3. In the dialogue box, select Classic from the top dropdown list. SelectUse a formula to determine which cells to format, since we’re using a formula.
4. We want to search cells in theItem #column for aBandL, and highlight that cell when both conditions are true. To do this, we’ll use Excel’s ISNUMBER and SEARCH formulas, with the AND formula, to look for the B and L values.
5. =AND(ISNUMBER(SEARCH("B",A1)),ISNUMBER(SEARCH("L",A1)))
   
   
6. ClickOK. Your sheet will highlight all cells inItem #that meet both of these conditions.
   
   

Tip:Understanding the formula:

• We use AND at the beginning of the formula to show thatbothof the following conditions must be met in order to apply the conditional formatting.
• The basic syntax of the nested formula is ISNUMBER(SEARCH(“substring”,text)) where “substring” is the character(s) you are looking for, and “text” is the cell(s) you want to search.
• We are searching for two separate conditions, so we write the ISNUMBER(SEARCH(“substring”,text)) formula twice, separated by a comma.

This example is just one of hundreds of different formulas you could enter with the AND function. For more information on using Excel formulas with conditional formatting, clickhere.

You can also create rules to highlight certain cells based on the value of another cell.

1. In this example, we’ll create a “quantity threshold” - items with inventory level below this threshold will be considered “at risk.” We already have a rule that highlights cells in theQty.column under 100. 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.
2. To the right of your data, create a box forQuantity Thresholdand choose an amount. In this example, we use 200. (Note the cell you type the amount in. Here, it’s I5.)
   
   
3. Create a new rule by clickingConditional Formatting > New Rule.
4. In the dialogue box, clickClassicandUse a formulato determine which cells to formatfrom the dropdown menus.
5. In the formula field, type =C4<$I$5 and keep the default formatting. ClickOK.
   
   
6. Numbers in theQty.column that are less than the Quantity Threshold in I5 (200) will now be highlighted.
7. Now, you’ve created a dynamic rule. This means that as your threshold changes, the rule will still be applied to theQty.column and the formatting will adjust as appropriate.

Tip:Understanding the formula:

• In this formula, we are simply testing whether or not values in column C are less than the value in I5.
• Start with “=C4” to tell Excel where we want to start evaluating values from.
• Use ‘$’ symbol around I because it’s an absolute value - we areonlyevaluating cells in column C against this single cell.

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. You can apply data validation to ensure that any cell-type only allows certain entries (text or numbers only, text length, etc.). In this example, we’ll show you how to create a dropdown list and validate dataonlyfrom that dropdown list.

1. 我们将create an additional column of which employee last updated the sheet, and create a dropdown list to choose from. First, click where you want to add the column on the sheet. Create a new column by clickingInsert > Insert Sheet Columnsfrom the ribbon on theHometab.
   
   
2. We’ll name this columnUpdated By.
3. Now we need to make a list of employees, that the dropdown list will later pull from. Create a second sheet (in the same Excel workbook) by clicking the + sign on the tab at the bottom of the spreadsheet, and write a list of employee names in column B.
   
   
4. Select the range of data (all the names) you want to include in your list. Make this list into a table by clicking theInsert tab > Table.
5. The dialogue box will show the range of data you selected for the table. ClickMy table has headers, since we began the list withEmployees. ClickOK.
   
   
6. Now we have to name this list so that Sheet 1 can pull from it. ClickFormulastab >Define Nameon the ribbon.
   
   
7. In the dialogue box, type the name of the list. We’ll call itEmployees. Double check that the selected range is still correct. ClickOK.
   
   
8. Go back to Sheet 1. Now, we have to make theUpdated Bycolumn into a dropdown list. We’ll use the data validation function to do this.
9. Select theUpdated Bycolumn to apply data validation to this range of cells.
10. Click theDatatab on the ribbon and clickData Validation > Data Validation…
    
    
11. In the dialogue box, chooseListfrom the dropdown menu to restrict data entered in this range to a list. Type=Employeesin the Source field, since we’re pulling from the list we just created (in Sheet 2). ClickOK.
    
    
12. There is now a dropdown list in theUpdated Bycolumn. Your sheet will only accept data in this column that comes from theEmployeeslist on Sheet 2.
    
    
13. You can now fill in the column by selecting a name from the dropdown list.
    
    

As you accumulate conditional formatting rules, watch out for rule hierarchy. Since you can apply multiple conditions to a cell (or row), they occasionally conflict. When this happens, Excel has default precedence rules that may cause one rule to override another, so you can lose your formatting. To combat this, you can change the hierarchy of the rules.

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.
• Rules don’t conflict if they are changing different things about the cell. 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:

1. All rule hierarchy is controlled through the Rule Manager in Excel.
2. From theHometab, clickConditional Formatting > Manage Rules…to open the Rule Manager.
3. SelectThis Sheetfrom the top dropdown list to pull up all lists applied to the current sheet.
4. 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. ClickOK. Remember the rule closest to the top will take precedence.
   
   

Adding a new rule in Smartsheet is easy (you already learned how in the “Basic” section). Now, we’ll apply the same “stop if true” logic from Excel in Smartsheet.

1. Add a new rule by clicking the conditional formatting icon on the toolbar. ClickAdd New Ruleand clickSet condition.
2. For this rule, we want to call attention to the shipment order dates that areafter 06/20/30. The easiest way to do this is to apply the same logic as “stop if true” in Excel.
3. Click装运订单日期in the left field to set a condition in this column.
4. Instead of defining criteria (as we have been doing), clickselect from a list. The list of values in the装运订单日期column appear.
   
   
   
   
5. Click06/20/30. Then, because we want to highlight all the dates that are not this date, check theApply format when this condition is NOT met box. (This functions just like the “stop if true” box in Excel.) ClickOK.
   
   
6. Clickthis formatand apply whatever format you want. We’ll make the text bold.
7. Clickentire rowand select the装运订单日期column. ClickOK.
   
   
   
   
8. Now, all dates other than07/01/15will be bolded in the装运订单日期column.
   
   

Just like in Excel, you can use Smartsheet’s built-in formulas when creating conditional formatting rules. However, Smartsheet makes it easy to perform some of the simple functions that basic Excel formulas provide, so you don’t have to worry about remembering complicated formula syntax. To perform the AND function, you can simply click to add multiple conditions to any conditional formatting rule.

1. Click the conditional formatting icon on the toolbar and clickAdd New Rule.
2. Clickset condition. We want to highlight cells in the Item # list that contain both a ‘B’ and an ‘L.’ Click Item#from the dropdown list on the left.
3. For the conditions, selectcontainsand typeB. Click into the second prompted condition and typeLto create the second condition. (This will perform the AND function.) ClickOK.
   
   
   
4. Apply the formatting to your rule. We’ll use a yellow fill.
5. Changeentire rowtoItem #. ClickOK.
   
   
6. Your sheet will now highlight those values inItem #that contain both a B and an L.
   
   

You can still use formulas with conditional formatting, if you’d like. For an introduction on how to use formulas, check out thisresource.

Smartsheet also makes it easy to apply conditional formatting based on another cell. Instead of using complicated formulas to reference different cells, you can simply control which cells to pull from and format with a few clicks.

1. In this example, we want to highlight theItem Namecolumn if the status bar is red, to call attention to at-risk items. To do this, we’ll create a rule that pulls from theQty. Statuscolumn, but applies formatting to theItem Namecolumn.
2. Click the conditional formatting icon on the toolbar and clickAdd New Rule > set condition.
3. ClickQty. Statusfrom the drop down list and clickRed. ClickOK.
   
   
4. Apply a light red paint fill toformat with.
5. Clickentire rowand selectItem name. This will apply formatting to cells in theItem namecolumn, even though we’re pulling information from theQty. Statuscolumn.
   
   
6. Click your new rule and drag it to the top of your rules list. Since we have multiple highlighting rules, this will ensure that this rule takes precedence over others.
   
   
7. Cells inItem Namewill now be highlighted if theQty.Statusball is red.
   
   

Tip:To eliminate visual redundancy, you can hide theQty.Statuscolumn in your sheet. Simply right-click the column and clickHide column. Your sheet will retain its formatting based on that column, but it will look cleaner.

Managing rule hierarchy is quite simple in Smartsheet. The rules also interact the same way as Excel (you can apply multiple rules to a single cell, but only one will show up if there’s a conflict). However, the default precedence is a little different:

1. Click on the conditional formatting icon to bring up the rules.
2. In Smartsheet, rules higher on this list have precedence. So, the rules have descending hierarchy from the top to bottom of the list.
3. To change hierarchy, simply click on a rule and drag it up or down.