To highlight text is one of the most used and basic techniques that we need to perform in Excel. It is super easy to highlight texts within a moment. Besides, there are multiple ways available to do this. To facilitate you with all the ways, we have come up with 8 techniques throughout this article that you can use to highlight text with ease in Excel.
Download the Practice Workbook
You are recommended to download the Excel file and practice along with it.
8 Ways to Highlight Selected Text in Excel
In this article, we will be using a sample product price list as a dataset to demonstrate all the methods. So, let’s have a sneak peek of the dataset:
So, without having any further discussion let’s dive straight into all the methods one by one.
1. Highlight Selected Text in Excel Using Font Color
There’s a dedicated tool for text highlighting under the Home ribbon. It’s easily accessible as well as very convenient to use at the same time. To use this tool to highlight your texts,
❶ Select the range of cells ▶ that you want to highlight.
❷ Then go to the Home ribbon.
❸ Now navigate to the Font group.
Within this group, hit the Font Color icon to highlight your selected text with color.
You can use the same feature of Excel using another way. This technique requires even less time and is easy to use. All you need to do is,
❶ Select the range of the cells.
❷ Right-click on your mouse.
This will bring a pop-up list on spot. On the top of the list, you will easily see the Font Color icon.
❸ Just hit the Font Color icon.
2. Highlight Selected Text Using Cell Styles
You can use another feature inside Excel called Cell Styles. This feature will offer you to highlight your cells as well as your texts within a moment. All you need to do is,
❶ Select the range of cells ▶ to highlight the texts within them.
❸ Then go Home ▶ Cells Styles.
After hitting the Cell Styles command, you will see a bunch of formatting options to highlight cells as well as texts within them. From the list,
❹ Click on Warning Text.
This will highlight your text with red color.
Read More: How to Highlight a Cell in Excel (5 Methods)
3. Highlight Specific Text Using Format Cells
Format Cells is an amazing feature inside Excel that enables us to format everything that we need inside Excel worksheets. It’s also super easy to use. All you need to do is,
❶ Select the range of cells ▶ where you want to apply the text formatting.
❷ After the press CTRL + 1 to open up the format cells dialog box.
❸ Select the Font ribbon within the dialog box.
❹ Now choose a color within the Color box.
❺ Finally hit the Ok option.
When you are done with all the steps above, you will find your texts highlighted as the picture below:
4. Highlight Specific Text in Excel Using Conditional Formatting
Maybe one can get the most flexibility about text or cell whatever formatting is required using the Conditional Formatting feature in Excel. Now in this section, we will see how we can format cells that contain specific text using Conditional Formatting.
❶ First of all select the whole data table.
❷ Then go to Home ▶ Conditional Formatting ▶ Highlight Cells Rules ▶ Text that contains.
After hitting the Text that Contains command, you will see a dialog box pops up on the screen. Within the box,
❶ Type texts based on which you want to format cells.
For instance, we’ve typed OP. This will highlight all the cells that contain the text OP in them.
❷ After that hit the Ok command.
When you are done with all the steps above, you will see your texts are highlighted as the picture below:
- Excel Formula Based on Cell Color (5 Examples)
- How to Fill Color in Excel Cell Using Formula (5 Easy Ways)
- How to Fill Cell with Color Based on Percentage in Excel (6 Methods)
5. Highlight Text in Excel Using Formula
Now we will highlight specific text with the help of a formula. We will set criteria using an Excel formula that will highlight all the texts that meet up the set criteria.
Suppose we want to highlight all the records that contain a quantity of more than 25. Now follow the steps below to see how to do this trick.
❶ First of all select the range of cells.
❷ Go to Home ▶ Conditional Formatting ▶ New Rule.
After hitting on the New Rule command, the New Formatting Rule dialog box will appear. Within the box,
❶ Select Use a formula to determine which cells to format.
❷ Then enter the formula:
❸ Pick a formatting color using the Format option.
❹ Finally hit the Ok command.
As soon as you are done with all the steps above, you will see your intended records have been highlighted as the picture below:
6. Highlight Text Using Keyboard
You can use your keyboard only to highlight texts with ease. All you need to do is,
❶ Select a cell first.
❷ Then hold down the SHIFT and press any of the Arrow Keys to highlight cells.
7. Highlight Text Using Mouse
You can highlight text more easily with a mouse than with the keyboard. All you need to do is,
❶ Select a cell.
❷ Left-click the mouse and drag it to highlight your intended cells.
Related Content: How to Highlight from Top to Bottom in Excel (5 Methods)
8. Highlight Specific Text Using VBA Code
For example, you have a large dataset with tons of text and among them, you want to highlight a specific text. If so, you can use the following VBA code to highlight your intended text with ease.
For instance, we want to highlight NPP throughout the dataset. To do so,
❶ Press ALT + F11 to open up the VBA editor.
❷ Go to Insert ▶ Module.
❸ Copy the following VBA code:
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 As Long cFnd = InputBox("Enter the text string to highlight") 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
❹ Now paste and save the code in the VBA editor.
❺ After that go back to the Excel workbook and select the whole data table.
❻ Then press ALT + F8 keys together.
This will open up the Macro window.
❼ From the window select the function TextHighlighter and hit the Run command.
After this, an input box will appear. Within the box,
❽ Insert NPP to highlight the NPP text throughout the table and hit Ok.
❾ Then insert a color code as instructed. For instance, we’ve inserted 3 to pick the red color.
❿ Finally, hit the Ok button.
When you are done with all the steps above, you will see your intended text that is NPP is highlighted with the red color as in the image below:
Things to Remember
📌 Press CTRL + 1 to open the Format Cells dialog box.
📌 You can press ALT + F8 to open the Macro window.
📌 Press the ALT + F11 keys together to open up the VBA editor.
To sum up, we have discussed 8 methods to highlight text in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.