By Erin Rodrigue

Let’s pretend you have a spreadsheet with 1,000 rows of data — it would be pretty difficult to spot patterns in the data with the naked eye. Enter conditional formatting.

This powerful tool highlights cells that meet a specific condition or “rule.” In other words, it brings your spreadsheet to life by adding color to patterns and trends.

Here, we’ll cover how to apply, edit, and copy and paste conditional formatting.

Conditional Formatting Based on Text

In this example, let’s use conditional formatting to an attendance list to highlight who was absent. The image below is the data set I’ll use to run through this explanation:

1. First, select the column or row you want to apply conditional formatting to. In this case, we’ll select column B.

2. To highlight who was absent, navigate to the header toolbar and select Conditional Formatting, as shown in the image below.

3. When the Conditional Formatting drop-down menu appears, select Highlight Cells Rules, then Equal To.

4. In the New Formatting dialog box, change Cell Value to Specific Text. Then, type “Absent” in the text box. Reference the image below:

5. From the New Formatting dialog box, we can also choose how we want to format the cells containing the word “Absent.” Check out the options below.

For this example, let’s stick with the default option (Light Red Fill with Dark Red Text).

6. Click OK. Now — thanks to conditional formatting — we can quickly identify which students were absent.

In the next section, we’ll cover how to apply conditional formatting based on another cell in the spreadsheet.

Conditional Formatting Based on Another Cell

In this example, the goal is to highlight the cells that match the drop-down menu in cell E1. The image below is the sample data set I’ll use for this explanation:

1. First, select column B.

2. Navigate to the header toolbar and select Conditional Formatting. When the Conditional Formatting drop-down menu appears, select Highlight Cells Rules, then Equal To.

<img src="https://blog.hubspot.com/hs-fs/hubfs/Screenshot%203-1.png?width=500&name=Screenshot%203-1.png" alt="A screenshot of the Conditional Formatting drop-down menu." width="500" …read more

Source:: HubSpot Blog

Author: Aaron