How To Highlight Words in Excel (Plus Why It Matters)

Updated June 24, 2022

Microsoft Excel offers users a range of useful functions, including the ability to highlight words or cells. Knowing how to use the highlighting feature can help make your spreadsheets easier for professionals to understand. With multiple methods for highlighting words within Excel, it's important to know which tactic can best alter your spreadsheet in a way that's useful for your professional needs. In this article, we discuss why it might be helpful to know how to use the highlighting feature in this program and explore seven distinct methods for how to highlight words in Excel.

Why might it be helpful to highlight words in Excel?

Highlighting words in Excel can help improve the readability of your spreadsheet. When you have lots of data in your Excel documents, others may find it challenging to quickly comprehend what the data means. By highlighting the most significant elements in your data, such as the answers to different calculations, you can make it easier for others to evaluate and extract meaning from your spreadsheets.

How to highlight words in Excel

There are multiple methods for highlighting words in Excel. Some of these methods give you the option to highlight entire cells, while others focus on highlighting specific text within a cell. Here are seven different methods that you can use to highlight words in Excel:

How to highlight cells

If you want to highlight individual cells within your Excel spreadsheet, you can use the following method:

Choose the cell that you want to highlight.
Navigate to the top menu and select "Home," then "Cell Styles." This action generates a pop-up menu with different color options.
Hover over the different color options in the pop-up menu to see a preview of how they can change your cell. Click on the color to make the change permanent.
Repeat steps one through three for other cells that you want to highlight.

How to use "Format Painter" to highlight other cells

If you want to highlight a group of cells near one another, using the "Format Painter" function can make this process easier. Here are four steps for using "Format Painter" in Excel to highlight a section of cells:

Apply the color and style of the highlighting that you want to use on an individual cell.
Go to the "Home" tab. Under the "Clipboard" section, double-click on "Format Painter."
Drag your mouse to select as many cells or ranges as you want to highlight.
Click on the "Format Painter" button again after you finish selecting the cells or ranges you want to highlight. Alternatively, you can press the "Escape" button on your keyboard.

How to highlight text using pop-up menus

If you want to highlight specific text within a cell, you can use an Excel pop-up menu. Following are four steps to highlight words within a cell through pop-up menu options:

Locate the cell that contains the text you want to highlight and double-click on it.
While holding down your left mouse button, drag your pointer across the words that you want to highlight within the cell. This action causes a pop-up menu with color options to appear.
Choose the font color icon to select the default choice for coloring text, or click on the arrow next to this icon to decide on a customized color. If you decide to pick your own color, you can select this color from the next pop-up menu that displays on your screen after you click on the new arrow.
Once you pick a color option, the selected text in your cell displays with that color choice.

How to highlight text using VBA Code

If you're comfortable using basic coding skills, you may want to highlight words in Excel using the Microsoft Visual Basic for Applications (VBA) program. To use VBA within Excel to highlight text, perform the following steps:

Select the cells or ranges that contain the words you want to highlight.
On your keyboard, simultaneously press the "Alt" and "F11" keys. This step causes the VBA application window to open.
Within the VBA window, click on "Insert," followed by "Module."
Copy and paste the VBA code that allows you to highlight select words within an Excel spreadsheet.
Press the "F5" button on your keyboard. Doing this allows the code to run in Excel.
After you press "F5," a pop-up window appears asking which words in Excel you want to highlight. Enter the text you want the program to highlight in this dialog box.
Press the "OK" button. Your Excel spreadsheet should now show highlighted text in every cell that contains your chosen words.

Here is the code that you can copy and paste into the VBA window:

"Sub HighlightStrings()

Dim xHStr As String, xStrTmp As String

Dim xHStrLen As Long, xCount As Long, I As Long

Dim xCell As Range

Dim xArr

On Error Resume Next

xHStr = Application.InputBox("What is the string to highlight：", "KuTools For Excel", , , , , , 2)

If TypeName(xHStr) <> "String" Then Exit Sub

Application.ScreenUpdating = False

xHStrLen = Len(xHStr)

For Each xCell In Selection

xArr = Split(xCell.Value, xHStr)

xCount = UBound(xArr)

If xCount > 0 Then

xStrTmp = ""

For I = 0 To xCount - 1

xStrTmp = xStrTmp & xArr(I)

xCell.Characters(Len(xStrTmp) + 1, xHStrLen).Font.ColorIndex = 3

xStrTmp = xStrTmp & xHStr

End If

Application.ScreenUpdating = True

End Sub"

How to highlight text with Conditional Formatting

You can also use "Conditional Formatting" to highlight specific words throughout a spreadsheet. Here are the steps for using "Conditional Formatting" to highlight words in Excel:

Select a group of cells in your spreadsheet by dragging your mouse. Alternatively, if you want to select all cells within a spreadsheet, you can press your "Control" and "A" keys simultaneously.
Navigate to the "Home" tab and find the "Styles" group. Click on the "Conditional Formatting" option.
From the drop-down menu that appears, select "Highlight Cells Rules," followed by "Text That Contains...".
A new dialogue box appears on your screen. Enter the words that you want to depict with highlighting.
Use the drop-down menu on this dialog box to select a color scheme. If you want a greater range of formatting options, select "Custom Format."
Click on the "OK" button to apply your highlighting to your selected words within your spreadsheet.
If you decide later that you want to turn off highlighting on your spreadsheet, navigate back to the "Conditional Formatting" option. Click on "Clear Rules," followed by "Clear Rules From Entire Sheet."

How to design a highlight style

Microsoft Excel comes with a range of pre-made highlighting options. However, you can also create your own customized highlighting style by following these steps:

Click on the "Home" tab, followed by "Cell Styles."
From the drop-down menu that appears, select "New Cell Style."
Enter a name for your personalized style.
Below the name for your new customized style is a "Format..." button. Click on it to make a new pop-up menu appear.
Choose the "Fill" tab at the top of the window. From here, you can choose the palette color for your style.
If you want to change other aspects of your customized style, such as the alignment or font, navigate to the other tabs on this pop-up window and make your selections. For example, if you want to adjust the font size or style, click on the "Font" tab and browse your options.
To save your style changes, press the "OK" button.

How to change the color or format of your highlighted text

Sometimes, you may want to alter the style or color of your highlighted text in Excel. You can change the format or color of your highlighted words by performing the following steps:

Choose the cell where you want to change the color or style.
Click on the "Home" tab, followed by the "Font" group.
If you want to change the formatting of your text, you can click on the bold, italic or underline icons.
If you want to change the text color, select the arrow next to the font color icon and then choose a color from either the "Theme Colors or "Standard Colors" palette. To use a customized color instead, click on "More Colors" and then create the color you want using the "Colors" dialog box.

Please note that none of the companies mentioned in this article are affiliated with Indeed.