How to Highlight Text in Excel (7 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

In this article, I will show you different ways to highlight text in Excel, especially how to highlight text using Font Color, Cell Styles, and Format Cells. Apart from that, you will learn highlight techniques using Conditional Formatting, Excel formulas, and code in the VBA Editor.

Generally, we use highlighting to emphasize specific data points. By applying perfect highlighting, we can quickly identify patterns or trends in the dataset. Most importantly, it makes the information more appealing and understandable when creating or presenting data.

Overview of Highlighting


1. Highlight Text in Excel Using Font Color

  • Select the range of text you want to highlight. Then go to the Font group under the Home ribbon and click on Font Color.
  • Select any color from the Theme Colors group according to your needs.

Highlight Using Font Color

  • The output will be similar to the following image.

Output of Highlighting by Font Color

Read More: How to Highlight Lowest Value in Excel


2. Using Excel Cell Styles to Highlight Text

  • First of all, select the cells that you want to highlight. Under the Home ribbon, select the Cell Styles Choose any of the options under the Cell Styles.

Highlight Using Cell Styles

  • If you choose the option Check Cell, your output will look similar to the following image.

Applying Cell Styles to Highlight Text in Excel


3. Highlight Specific Text Using Format Cells

  • You will have to select desired cells first, then press Ctrl+1 together to highlight text using the Format Cells dialog box.

Select Cells to Apply Format Cells Highlighting

  • Change the options in the dialog box, as you can see in the image below.

Format Cells Dialog Box

  • The output of highlighting will look like the following image.

Highlight Only Text by Using Format Cells Dialog Box

Read More: How to Highlight Highest Value in Excel


4. Using Pop-up Menu to Highlight Text in Excel

  • In order to highlight text using the Pop-up menu, first select the text in a certain cell. A pop-up menu would appear like the following image. Click on the Font Color option and select any of the colors available to highlight the text.

Highlight by Pop-up Menu

The output image will be as follows.

Output of Highlighting by Pop-up Menu

Read More: How to Highlight from Top to Bottom in Excel


5. Highlight Text with Conditional Formatting

  • If you wish to highlight text using Conditional Formatting, first select the range and then select Conditional Formatting under the Styles ribbon group from the Home.

Select Options to Apply Conditional Formatting

  • A dialog box appears. Give the condition there based on which Excel will apply to format.

Applying Condition of Highlighting

  • Your output will look like the following image.

Output of Applying Conditional Formatting


6. Highlight Text Using Excel Formula

  • Select the text where you will apply highlighting using the formula. Then go to the Conditional Formatting option under the Home.

How to Highlight Using Formula

  • Apply the formula below in the box Format values where this formula is true:
=$B5>150000

Applying Formula for Formatting

  • When you click on the Format… button, a dialog box will appear prompting you to choose a color for formatting.

Choosing Color for Formatting

  • The output will be similar to the following image.

Output of Highlighting by Formula

Read More: How to Highlight Text in Text Box in Excel


7. Applying Excel VBA Code to Highlight Text

  • Press Alt+F11 together to open the VBA Editor.
  • Under the Insert tab, click on the button Module.

Creating New Module in VBA Editor

  • Copy the following VBA code.
Sub Highlight_Multiple()
Dim Input1 As Variant
Dim rng As Range
' Prompt the user for input
Input1 = Split(InputBox("Enter the words(separated by comma):"), ",")
' Set the range where you want to search for the text
Set rng = Range("B5:D11") ' Modify the range as per your dataset
' Clear any previous highlighting
rng.Interior.Pattern = xlNone
' Search for the words and highlight the matching cells
For Each word In Input1
    For Each cell In rng
        If InStr(1, cell.Value, Trim(word), vbTextCompare) > 0 Then
            'Change the highlighting color as needed
            cell.Interior.Color = RGB(255, 0, 0)
        End If
    Next cell
Next word
End Sub
  • Now, paste the code in the editor and save it.

Code to Highlight Text in Excel

  • Go back to the Excel workbook and select the whole data table.
  • Press Alt+F8 together to open the macro window, where you will select the Highlight_Text code and hit Run.

Running the VBA Code

  • After this, an input box will appear where you will write down the text you want to highlight. We put “Pencil” in the box since we want to highlight it in the data table.

Giving Input to Highlight Text

  • Your output will look like the following image.

Highlight Text by Applying VBA Code in Excel

🔎 How Does the Code Function?

Inp = InputBox(“Enter text to highlight:”)

Prompts the user to provide the text that the code will highlight. Assign the user’s input to a variable named “Inp”.

Set rng = Range(“B2:D11”)

Declares the variable “rng” to hold the range from B2 to D11 since our data is in this range.

For Each cell In rng

Loops through all the cells in the range “rng” and checks whether it meets certain criteria.

If InStr(1, cell.Value, Inp, vbTextCompare) > 0 Then

The VBA InStr function starts the comparison of text from the beginning of each “cell” in the “rng” variable. If it finds a match, then it will return a value greater than zero (the position of the starting substring in the first string). In that case, the condition becomes true, and the code is on the next line under the condition.


How to Create a Highlight Style in Excel

  • Select the Home tab and then Cell Styles. Under the Cell Styles group, click on New Styles.

How to Create New Cell Style

  • In the Style box, give an appropriate name to your cell style and click on the Format… button.

Giving Name to the Style

  • In the Format Cells dialog box, under the Font tab, modify the Font as needed.

Modifying the Font in New Cell Styles

We will also change the default Fill.

Change Default Fill of the Style

  • Finally, click OK twice to create the new style. Select the whole dataset except the heading and click on Cell Styles >> My Style. Have a look at the cell style you have created.

Highlight Text with New Style in Excel


Highlight More Than One Text Based on User’s Needs

  • First, open the VBA Editor in the same way we opened it in the previous method. Then, copy the following code.
Sub Highlight_Multiple()
Dim Input1 As Variant
Dim rng As Range
' Prompt the user for input
Input1 = Split(InputBox("Enter the words(separated by comma):"), ",")
Set rng = Range("B5:D11") ' Modify the range as per your dataset
' Search for the words and highlight the matching cells
For Each word In Input1
    For Each cell In rng
        If InStr(1, cell.Value, Trim(word), vbTextCompare) > 0 Then
            'Change the highlighting color as needed
            cell.Interior.Color = RGB(120, 120,160)
        End If
    Next cell
Next word
End Sub
  • Then, paste the code into the editor and save it.

VBA Code to Highlight Multiple Text

  • Go back to the Excel workbook and select the whole data table.
  • Press Alt+F8 together to open the Macro window, where you will select the Highlight_Multiple code and hit Run.

Running the Code to Highlight Multiple Text in Excel

  • After this, an input box will appear where you will write down the texts you want to highlight. We put “Pencil,3,2” in the box since we want to highlight it in the data table.

Giving Input to Highlight

  • You may give different inputs to the input box depending on your needs. Excel has colored all the cells in the dataset that contains “Pencil”,”2” & “3”. So, the output looks like the following image.

Highlight More Than One Text in Excel

🔎 How Does the Code Function?

The above code is very similar to the code we used in the earlier session. We have modified that code just in 2 places.

  1. Introduced an extra For loop in order to loop all the words that the user gives.
  2. Instead of using the direct input, we first used the VBA Trim function to extract the words free of other delimiters like commas.

How to Highlight Duplicate Text in Excel

  • Select the range where you want to check for duplicates. Under Conditional Formatting, select Highlight Cells Rules >> Duplicate Values.

Highlighting Duplicate Values

  • A user prompt box will appear, giving you the option to choose the format you want to apply.

Option Box to Choose Type of Highlighting

  • You will find that all the duplicate values in your dataset are highlighted now.

Highlight Duplicate Text in Excel


Things to Remember

  • While applying a new formatting to a cell, it is better to clear any existing formatting there.
  • Use consistent highlighting since it helps others to understand and interpret.
  • Keep in mind that some individuals may have visual impairments. Ensure that the highlighted text is readable by those people.

Frequently Asked Questions

1. Does highlighting cells affect the calculation or function results in Excel?

Highlighting is just a visual formatting feature that does not impact the data or formulas.

2. Are there any limitations to highlighting cells in Excel?

Yes, there are some impacts on performance if you highlight excessively in your worksheet. Also, the highlighting you apply to cells may not appear as expected when printing a worksheet. So, it is a good idea to preview and adjust print settings before printing to ensure accuracy.

3. Can I highlight cells in Excel using a formula?

Yes, you can highlight cells in Excel using a formula by applying conditional formatting.


Download Practice Workbook

You may download the following workbook to practice yourself.


Conclusion

Hey! You have reached the end of the article on Highlight Text in Excel. Hopefully, you enjoyed all the items covered in the article, and you will apply these techniques in your worksheet to highlight different things according to your needs. If you find this article helpful, please share it with your friends. Moreover, do let us know if you have any further queries. Finally, please visit our site for more exciting articles on Excel.


Related Articles


<< Go Back to Highlight in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mrinmoy Roy
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

12 Comments
  1. Thank you for this. I was wondering if the code could be written so that it would pick up a certain number of characters after a text of string.

    For example, I want it to hilight the text “Multiplier” as well as the following 7 characters. Is that possible?

    • Hi Scot,
      The following code may fulfill your requirements.

      Sub TextHighlighter()
      Application.ScreenUpdating = False
      Dim Rng As Range
      Dim cFnd As String
      Dim xTmp As String
      Dim x As Long
      Dim m As Long
      Dim y, ext As Long
      cFnd = InputBox(“Enter the text string to highlight”)

      Color_Code = Int(InputBox(“Enter the Color Code: ” + vbNewLine + “Enter 3 for Color Red.” + vbNewLine + “Enter 5 for Color Blue.” + vbNewLine + “Enter 6 for Color Yellow.” + vbNewLine + “Enter 10 for Color Green.”))
      ext = CLng(InputBox(“Input number of additional Character to color”, , 0))
      y = Len(cFnd) + ext
      For Each Rng In Selection
      With Rng
      m = UBound(Split(Rng.Value, cFnd))
      If m > 0 Then
      xTmp = “”
      For x = 0 To m – 1
      xTmp = xTmp & Split(Rng.Value, cFnd)(x)
      .Characters(Start:=Len(xTmp) + 1, Length:=y).Font.ColorIndex = Color_Code
      xTmp = xTmp & cFnd
      Next
      End If
      End With
      Next Rng
      Application.ScreenUpdating = True
      End Sub

  2. Hi!

    Could you help me?… I´m trying to introduce a breakline in the string before cFnd.

    This is the code:
    xTmp = xTmp & Split(rng.Value, cFnd)(x)
    .Characters(Len(xTmp) + 1, 0).Insert vbNewLine

    But my problem is that I get an 1004 error with insert. Could you gave me another alternative code?

    • Hello, GABRIEL!
      Thanks for sharing your problem with us!
      To add a new line you don’t have to write the command .Insert. You can simply use this block of the code to breakline in the string before cFnd.

      xTmp = xTmp & Split(Rng.Value, cFnd)(x)
      .Characters(Start:=Len(xTmp) + 1, Length:=y)
      xTmp = xTmp & vbNewLine & cFnd

      To use vbNewLine, you have to make sure to do the following.
      1. After the ampersand (&) symbol, press the spacebar and get the VBA constant ‘vbNewLine‘.
      2. After the constant ‘vbNewLine‘, press one more time space bar and add the ampersand (&) symbol.
      3. After the second ampersand (&) symbol, type one more space character, and add the next line sentence in double-quotes.

      In VBA, there are three different (constants) to add a line break.
      vbNewLine, vbCrLf, vbLf

      If this is not working for you, follow the steps.
      1. Click on the character you wish to break the line from first.
      2. Then, enter a space ( ).
      3. Type an underscore (_) after that.
      4. Finally, press Enter to finish the line.

      Hope this will help you!
      Good Luck!

      Regards,
      Sabrina Ayon
      Author, ExcelDemy.

  3. Hello, Thanks For 8 methods to highlight text in Excel. i Have a Problem with last method.
    The code works in (English characters) But when i input for example Persian character such as (توسعه), do not Work!

    • Thank you MOhamad for your query. The Persian language is not available in the UNICODE. That’s why you are facing the problem. If you want to use the code for Persian characters, you have to modify the code slightly. The code can not take Persian language in the InputBox. So, instead of InputBox, we have to insert text to be highlighted in a cell. Let’s insert Persian text to be highlighted in the C10 cell. So, the modified vba code should be like this one.
      Sub Text_Highlighter()

      Application.ScreenUpdating = False

      Dim Rng As Range

      Dim cFnd As String

      Dim xTmp As String

      Dim x As Long

      Dim m As Long

      Dim y As Long

      cFnd = Range(“C10”).Value

      y = Len(cFnd)

      Color_Code = Int(InputBox(“Enter the Color Code: ” + vbNewLine + “Enter 3 for Color Red.” + vbNewLine + “Enter 5 for Color Blue.” + vbNewLine + “Enter 6 for Color Yellow.” + vbNewLine + “Enter 10 for Color Green.”))

      For Each Rng In Selection

      With Rng

      m = UBound(Split(Rng.Value, cFnd))

      If m > 0 Then

      xTmp = “”

      For x = 0 To m – 1

      xTmp = xTmp & Split(Rng.Value, cFnd)(x)

      .Characters(Start:=Len(xTmp) + 1, Length:=y).Font.ColorIndex = Color_Code

      xTmp = xTmp & cFnd

      Next

      End If

      End With

      Next Rng

      Application.ScreenUpdating = True

      End Sub

      You should insert your preferred cell instead of C10. And this code will highlight characters of all other languages.

  4. Wow! This could be one particular of the most beneficial blogs We’ve ever arrive across on this subject. Actually Wonderful. I’m also a specialist in this topic therefore I can understand your effort.

  5. I like this post, enjoyed this one appreciate it for posting. “What is a thousand years Time is short for one who thinks, endless for one who yearns.” by Alain.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo