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.

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.

• The output will be similar to the following image.

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.

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

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.

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

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

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.

The output image will be as follows.

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.

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

• Your output will look like the following image.

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.

• Apply the formula below in the box Format values where this formula is true:

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

• The output will be similar to the following image.

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.

• 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.

• 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.

• 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.

• Your output will look like the following image.

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.

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

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

We will also change the default Fill.

• 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 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.

• 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.

• 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.

• 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.

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.

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

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

Read More: How to Highlight Text in Text Box 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

• How to Highlight a Column in Excel
• How to Highlight from Top to Bottom in Excel
• How to Compare Two Excel Sheets and Highlight Differences