Home ► Article Library ► IT, Internet, Computers & Mobile Apps

Conditional Formatting, the Feature
That Changes Everything

When working with Google Sheets, it’s feasible to consider that you could spend an entire workday hunting and finding cells that you want to highlight and search. The good news is, there is an easier method called conditional formatting. Formatting in Google Sheets is the process where you tell Google Sheets what you want your spreadsheet to look like.

You might want all of your employee names to be in bold, for example, and their department to be in italics or all CAPS. Instead of spending the whole day doing this, use conditional formatting in Google Sheets, and you will soon see why it is called the feature that changes everything.

Learn more about that here.

Highlight Duplicates With Conditional Formatting

Conditional formatting is the fancy formula that you can use to change what a cell looks like. If you want cell B117 to be green, for example, you can tell Google Sheets that you want it to look green so that you never have to do that manually again.

Say you want B117 to look green because that notes everybody in your company that is taking “Holiday Vacation” time off. Then you would use conditional formatting to tell Google Sheets that you want the duplicate value “Holiday Vacation” time off to be highlighted in green.

To get here, use the Format tab, click on Conditional formatting, specify the range (B1:B125) for example, specify what value you want to appear (Holiday Vacation), and then specify the formatting “green.”

With this simple formatting, you now have a snapshot look at who is taking holiday vacation for the time specified, and you’ve saved time searching for every entry manually.

Using Color Scale for Conditional Formatting

You can use color scale in conditional formatting to use one color but different shades in your Google Sheets document. Let’s say you have some employees that haven’t booked their holiday vacation time yet, but have expressed an interest. You can use the color scale as your formatting feature to save time here.

In the same column of your employee sheets, go to the Format tab, select Conditional Formatting, and select the color scale tab.

Now you will select the cells you want to put this rule on. Enter the range of cells you are applying your color scale to, and decide what color scale will represent this. You will enter a minimum and maximum value here so that the color scale appears on your sheet.

For left (minimum value), you will enter where potential holiday vacation schedules will occur, and for right (maximum value), you will enter where confirmed holiday vacation schedules will occur. For midpoint, you will express what colors you are working with.

Click the Done button and you should have different shades of color on your spreadsheet for the various values you are working with.

In this case, we have used a color scale to determine who may be taking holiday time off for this sample company.

Conditional Formatting Based on Other Cell Values

With this tool, you can use conditional formatting to format cells based on something that another cell stipulates. So let’s go back to our pretend employee spreadsheet where we are trying to find out who is or isn’t taking holiday time off. Some employees will be taking paid time off, and some won’t be, and paid or unpaid time off is listed on the same spreadsheet.

So now we want to format cells of employees who are taking paid holiday vacation. The two values you have here are going to be “Paid” and “Holiday Vacation.”

For this example, let’s say we want to find out who is taking a holiday vacation that is paid. You will format Holiday Vacation cells based on the other value of Paid Holiday Vacation.

Go to the Format tab, select Conditional Formatting, select single color, and select the range of cells you want to look at. We will say the range here is D5:D500. Click on “Custom formula” and enter =$range>value, where range is the range of cells and value is the thing you are looking for.

So for here, the “Holiday Vacation” range is anything after D5, and the value is “Paid,” and your formula is: =$D5>Paid. Now you are going to format that with the color of your choice, and click Done.

Logic Formulas in Conditional Formatting

You can get as advanced as you want with conditional formatting, but it isn’t as advanced as it feels like when you are learning. So for our holiday vacation example, you may want to find out who could get holiday vacation if they work 160 hours before November 30. You could tell Google Sheets how to do that too, and here you would use an IF function.

Click on Format, select “Conditional formatting,” and select the color of the formatting of your choice. Select the cell range you are applying your logic to, for example, D5:G500. Now you can apply a logic formula here by clicking on “Custom Formula” and entering values into this formula (without quotation marks): =AND($cell>value, OR ($cell = value)).

In this example, the formula then looks like: =AND($D5>160, OR $G2 = Holiday Vacation, $[range where Nov. 30 is located]=November 30)).

Now you will format this range as you wish, and click Done.

Start Using Conditional Formatting