Inside The Web
    Facebook Twitter Instagram
    Inside The Web
    • Office
      • Excel
      • Ms Word
      • OneDrive
      • Google Docs
      • Google Sheets
      • Google Drive
    • Email
      • Gmail
      • Outlook
      • Emal Etiquette
    • Office Etiquette
    • Productivity
    Inside The Web
    Home»Office»Excel»How to Change Cell Color in Excel Based on Text Input

    How to Change Cell Color in Excel Based on Text Input

    Asmi DhakalBy Asmi DhakalSeptember 15, 2023 Excel 6 Mins Read

    You can enter all sorts of data in Excel, including hierarchical ones. When dealing with such values, you may want to color code them using unique colors, or even create a heat map based on the values.

    For such formatting, Excel has a dedicated tool called Conditional Formatting. You can use Conditional formatting to assign a color to specific values whether they’re text, numbers, or even special characters. What’s more, is that you can even create custom rules using Excel formulas to change the cell color.

    Format Number Values Based on Inequalities

    If you’re looking to format inequalities like greater than, less than, between, or equal, you can use the Highlight cell option in the Conditional Formatting tool. All you have to do is specify your number or range, and the formatting you wish to apply to the targeted cells.

    1. Select your data range.
    2. From the Home tab, select Conditional Formatting from the Styles section.
    3. Hover your cursor over Highlight Cells Rules.
      Highlight Inequalities Excel

    Highlight Numbers Greater Than

    1. Choose Greater Than…
    2. On the text box, enter the reference value. Conditional Formatting will format cells containing numbers greater than this value.
      Format Cell Greater than
    3. Next to with, specify a format. For more options, click Custom Format.
    4. Click OK.

    Highlight Numbers Lesser Than

    1. Choose Less Than…
    2. Enter than reference value. Excel will format cells containing numbers less than this value.
      Format Cell Less than
    3. Next to with, specify a format. For more options, click Custom Format.
    4. Click OK.

    Highlight Numbers in Between

    1. Select Between.
    2. Enter the minimum value on the left and the maximum on the right.
      Format Cells in Between Excel
    3. Next to with, enter the format.
    4. Select OK.

    Highlight Numbers Equal to

    1. Click Equal To.
    2. Enter the reference number.
      Format Cells that are equal to
    3. Choose your preferred formatting next to with.
    4. Click OK.

    Format Cells Containing a Text

    You can also change the color of your cell based on whether it contains a specific text.

    1. Go to Home.
    2. Select Conditional Formatting from Styles.
    3. Choose Highlight Cells Rules > Text that Contains.
      Highlight Cells containing a text
    4. Enter your text in the text box.
    5. Choose formatting next to with.
    6. Click OK.

    Format Cell Containing Dates

    Using Conditional Formatting, you can format cells containing dates for yesterday, today, tomorrow, last week, and so on. Excel will your system’s date and time to format the cells, so make sure your device is running the correct date.

    1. From the Home tab, select Conditional Formatting.
    2. Choose Highlight Cells Rules > A Date Containing.
      Highlight dates in Excel
    3. Select the left fly-out and choose a date.
    4. Next to with, select a format.
      Format Text that contains excel
    5. Click OK. 

    Format Duplicate or Unique Values

    If your spreadsheet contains repeated values that you wish to locate, you can use Conditional Formatting to highlight such values. The tool will apply the set format to all values that have been repeated more than once on your spreadsheet.

    Similarly, you can use the same interface to format cells containing Unique values.

    1. Select Conditional Formatting from the Home tab.
    2. Choose Highlight Cells Rules > Duplicate Values.
      Highlight Duplicate Values Excel
    3. From the left fly-out, choose Duplicate to format repeated values else, select Unique.
    4. Choose a formatting next to values with.
    5. Click OK.

    Change Cell Color Containing Top and Bottom Values

    You can also apply the rule to format the cells containing top values in Excel. These rules contain formatting the top and bottom values, and the values that fall under the top 10% of your range. You can also change the percentile and item values from the conditional formatting interface.

    1. Select your range.
    2. From Home, select Conditional Formatting.
    3. Go to Top/Bottom Rules.
      TopBottom Rules Excel

    Format Cells Containing Top Items

    1. Select Top 10 Items.
    2. In the left text box, to increase the item number, use the up and down arrow keys.
      Highlight Top 10 items excel
    3. Next to with, choose a format.
    4. Click OK.

    Format Cells Containing Top Percentages

    1. Choose Top 10%.
    2. Increase or decrease the percentage value using the top and down arrow keys.
      Highlight top percentage excel
    3. Select a cell format next to with.
    4. Click OK.

    Format Cells Containing Bottom Items

    1. Select Bottom 10 Items.
    2. If you wish to format cells containing a different bottom value, use the arrow keys to change the value from 10.
      Highlight bottom 10 items
    3. Next to with, choose a different format.
    4. Click OK.

    Format Cells Containing Bottom Percentages

    1. Select Bottom 10%.
    2. To change the percentile value, use the arrow keys.
      Highlight bottom 10 percentage
    3. Change the cell formatting next to with.
    4. Select OK.

    Format Cells Containing Above or Below Average Values

    Statistically, an average value refers to the sum of the given values divided by the number of items. If you’re analyzing a vast data set, you may want to look into which values are above and below the calculated average value.

    For such analysis, you can use the Top/Bottom Conditional Formatting Rule.

    1. Select the range containing your data set.
    2. Head to Home > Conditional Formatting.
    3. Choose Top/Bottom Rules.
    4. Select either Above Average or Below Average.
      Highlight above and below average excel
    5. Click on the fly-out to specify a format.
      Format Cells Above Average
    6. Click OK.

    Create Heat Map

    If your spreadsheet contains numerical data of the same kind, like temperature, you can create a heat map. Excel offers the option to create a heatmap using two or three-color scales. These colors can be both cool and warm-toned. Aside from a few library color palettes, you can customize your heatmap using your preferred colors.

    1. From Home, select Conditional Formatting.
    2. Choose Color Scales from the fly-out.
      Create Heat Map
    3. Select a color option to create a heatmap.
    4. If you wish to customize the colors, select More Rules.
    5. Under Edit the Rule Description, choose a Format Style.
    6. Select the fly-outs in the Color section to pick colors for your heat map.
      Format Heatmap colors
    7. Choose OK.

    Change Cell Color Using a Custom Formula

    If you want to format your cells based on a specific formula, you can create a New Rule for conditional formatting. Let’s say you want to format cells based on their location. You can use the MOD and ROW functions to create a formula to highlight every few other rows.

    1. Select Conditional Formatting from the Styles section in the Home ribbon.
    2. Click on New Rule.
      Conditional Formatting New Rule
    3. Under Select a Rule Type, choose Use a formula to determine which cells to format.
    4. Enter your formula in the text box.
      Create new rule for conditional formatting
    5. Click on the Format button to choose a format.
      Format button
    6. After previewing your format, click OK to confirm changes.
      Customize formatting in conditional formatting'
    Cell Formats
    Asmi Dhakal

      Asmi is a content writer specializing in MS Office. She loves writing Excel-related content to assist other users to learn their way around the program. She started using Excel when she first entered the industry as a marketing intern. Soon, she realized that learning the often-overlooked spreadsheet program was no piece of cake. In her articles, she attempts to brief out complex topics to help beginner to intermediate-level Excel users. Asmi uses Excel for everything, including creating a grocery list to managing her personal finance. She’s a bit dramatic when it comes to the Flash Fill feature; so much so that could write a eulogy out of it. When she’s not working, you’ll find her learning a new recipe, or cleaning up her mess (out of the failed recipe).

      Related Posts

      Excel By Nisha Gurung

      How to Compare Two Columns in Excel Using VLOOKUP

      Excel By Nisha Gurung

      How to Share Excel Files with Multiple Users

      Excel By Nisha Gurung

      How to Filter Based on a List in Excel

      Add A Comment
      Table of ContentsToggle Table of ContentToggle
      • Format Number Values Based on Inequalities
        • Highlight Numbers Greater Than
        • Highlight Numbers Lesser Than
        • Highlight Numbers in Between
        • Highlight Numbers Equal to
      • Format Cells Containing a Text
      • Format Cell Containing Dates
      • Format Duplicate or Unique Values
      • Change Cell Color Containing Top and Bottom Values
        • Format Cells Containing Top Items
        • Format Cells Containing Top Percentages
        • Format Cells Containing Bottom Items
        • Format Cells Containing Bottom Percentages
      • Format Cells Containing Above or Below Average Values
      • Create Heat Map
      • Change Cell Color Using a Custom Formula
      • Home
      • About Us
      • Privacy Policy
      © 2025 Inside The Web

      Type above and press Enter to search. Press Esc to cancel.