For example, select Contains and then enter Silver, or select Starting with and then enter Tri. Tip: You can sort cells that have this format by their icon - just use the context menu. Format a formula result:    Select Formula, and then enter a formula in each Value box. The above data set has values between 1 and 100. Don't enter a percent sign. To highlight cells that contain one of many text strings, you can use a formula based on the functions ISNUMBER and SEARCH, together with the SUMPRODUCT function. 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. Re: Conditional formatting : Reference a list of names to change cell color. Select the cells you want to apply conditional formatting to. Kind of a hack job in the end, but couldn’t have done it without your help. You can use the same logic, to create variations such as: Once applied, conditional formatting remains in place unless you remove it manually. Use the Quick Analysis button Second rule: if either the down payment or the monthly payment doesn't meet the buyer's budget, B4 and B5 are formatted red. Conditional formatting lets you format cells in an Excel spreadsheet based on the cells’ content. The idea is to type a string in cell C2, and if it matches with the data in any cell(s), then that should get highlighted. The Conditional Formatting Rules Manager dialog box appears. A conditional format is a format which is applied only to cells that meet certain criteria, say values above a particular value, positive or negative values, or values with a particular formula, etc. Under Select a Rule Type, click Format all cells based on their values. 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. Here there, I need some help with conditional formatting; what’s the Formula Rule I can use to change color of an entire column if there is less than a specific numbers of charters in that column? Go to Home –> Conditional Formatting –> Highlighting Cell Rules –> Greater Than.. / Less Than.. Based on what option you select (greater than or less than), a dialogue box would open. Click the first cell in the range, and then drag to the last cell. Conditional formatting in volatile. Or, in a retail store summary worksheet, you can identify all stores with profits greater than 10%, sales volumes less than $100,000, and region equal to "SouthEast.". This conditional format will highlight the subtotaling rows. The user can quickly change the cell that contains the criteria and the matching rows will be highlighted. Conditionally format a set of fields in the Values area for one level in the hierarchy of data. To select a type in the Type box for Minimum and Maximum, do one of the following: Format lowest and highest values:    Select Lowest Value and Highest Value. You can create as well as refer to cells in a workbook by their named range. To show only the icon and not the value in the cell, select Show Icon Only. Do one of the following: Format lowest and highest values:    Select Lowest Value and Highest Value. Tip: If any cells in the range contain a formula that returns an error, the conditional formatting is not applied to those cells. As the name suggests, you can use conditional formatting in Excel when you want to highlight cells that meet a specified condition. Formula for first rule (applies green color), =AND(IF($B$4<=75000,1),IF(ABS($B$5)<=1500,1)), Formula for second rule (applies red color), =OR(IF($B$4>=75000,1),IF(ABS($B$5)>=1500,1)), Example 2: Shade every other row by using the MOD and ROW functions. The New Formatting Rule dialog box appears. Select the dataset in which you want to highlight duplicates. The rule that is applied is the one that is higher in precedence (higher in the list in the dialog box). It would make you look like an Excel rockstar. 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. Highlighting Entries Based on Dates. Create a drop-down list in cell A2. How do I turn a conditionally formatted list into a seperate list that only contains the highlighted cells? Cells are highlighted based on the value in it. I want a blank cell to change colour as the target date approaches today’s date. Use the Go To Special command to find only cells with a specific conditional format, or to find all cells that have conditional formats. You may need to adjust the column width to accommodate the icon. Now Excel would evaluate the formula for each cell. Thanks for commenting Deepak.. Glad you liked it , Thanks for commenting Greg.. Glad you liked it , Thanks for commenting.. Glad you liked it .