Locked lesson.
About this lesson
How to use conditional formatting to format cells based on their contents.
Exercise files
The exercises below will open in a new tab. When signed in to your Google account, go to File --> Make a copy to get an editable copy of the file.
Conditional Formatting SolutionLink Conditional Formatting
Link
Quick reference
Conditional formatting
Conditional formatting is used to set up rules for automatically formatting the contents of cells in Google Sheets.
When to use
When you want to automatically highlight the data in your sheet using style or color.
Instructions
Creating a conditional formatting rule
- Click “Format”
- Click “Conditional formatting” to open the sidebar
- Select the cells you want to format
- Click “Add a new rule”
- Single color:
- Format cells if: Select the rule for determining which cells will get formatted rules can match:
- Whether the cell is empty or not
- Text
- Dates
- Numbers
- Custom
- Formatting style: Select how the cells will be formatted if they match the rule
- Format cells if: Select the rule for determining which cells will get formatted rules can match:
- Color scale:
- Preview: Choose what color scale to use
- Minpoint/Midpoint/Maxpoint: choose what will match the ends and center of the scale:
- Min value: The lowest number in the range
- Number: A specific number that you set
- Percent: A percentage of the maximum that you set
- Percentile: Numbers within a percentile that you set
- Single color:
- Click “Done”
See conditional formatting rules
- Select the data
- Click “Format”
- Click “Conditional formatting”
- The rules used in the selected data are listed in the sidebar
Delete conditional formatting rules
- Select the data
- Click “Format”
- Click “Conditional formatting”
- Mouse over the rule you want to delete
- Click the trashcan next to that rule
- 00:04 When you are working with your information within a spreadsheet, it can sometimes
- 00:09 be helpful to highlight particular cells based on particular criteria.
- 00:14 So let’s take a look at how we can apply some conditional formatting.
- 00:18 Let’s see how we can change the colors of some of these cells
- 00:21 based on the information that we are going to give the spreadsheet.
- 00:26 In order to apply conditional formatting, start by selecting the cells which you
- 00:30 would like it to apply to and then come up to the menu and select Format.
- 00:36 And near the bottom is Conditional Formatting.
- 00:39 This will open up the Conditional format rules menu
- 00:42 on the right hand side of the screen.
- 00:45 Now let's start off by applying a single color and
- 00:48 then we'll get into an example of applying a color scale.
- 00:52 If you haven't already selected the cells that you'd like the conditional formatting
- 00:56 to apply to, you can do so by clicking in this box here.
- 01:00 Now, by default, the first option is if a cell is not empty.
- 01:06 Well, in this case, we know that none of these cells are empty.
- 01:09 So let's click on the dropdown and see what other options are available to us.
- 01:14 Not only can we choose if a cell is empty or
- 01:16 not empty, we can format based on text, specific text, dates, or
- 01:21 numeric values and that's what I want for this particular example.
- 01:26 I wanna see which of these totals are $500 or above.
- 01:31 I wanna be able to see it really quite quickly so
- 01:33 that's why I wanna apply some color to this particular column.
- 01:37 So I'm gonna select Greater than or equal to.
- 01:41 And then just below, I input my value or a formula if desired,
- 01:46 in this case, I'm gonna type in 500.
- 01:50 And you can see, immediately it applies the formatting to my spreadsheet.
- 01:55 Now I can see that these 5 entries are all $500 or above.
- 02:00 Of course, I can change the color that is applied,
- 02:04 if I don't like this green, I can apply this yellow.
- 02:07 I can even make other edit changes such as applying a bold effect if I really want
- 02:12 them to stand out.
- 02:14 Lastly, I can select Done and
- 02:16 now that formatting has been applied directly to this particular column.
- 02:22 Let's see what we can do with another column.
- 02:25 This time, we're going to apply a scale.
- 02:28 So I'm gonna go over to the Units column.
- 02:32 I'm gonna highlight it and I'm gonna say Add new rule.
- 02:37 Now again, my range is already selected.
- 02:39 But before we start tampering with these details here,
- 02:42 we want to click on Color scale.
- 02:45 This time, we are going to have a range of colors from greatest to least.
- 02:51 So our first choice is to select what do the colors look like, or
- 02:56 what do we want them to look like, here under the Preview area.
- 03:00 I don't mind the green but I actually would like to reverse it.
- 03:02 Here you can see at a glance, it's giving the darkest shades to the lowest number
- 03:08 and then no shade at all to the highest number.
- 03:11 I'd like it in the inverse order.
- 03:13 So I'm gonna click on this Preview bar and
- 03:16 I'm gonna choose this one below White to green.
- 03:20 Perfect.
- 03:20 Now my highest unit, 95, is the dark green and my lowest unit is now number 2.
- 03:27 If you'd like to make a few more modifications,
- 03:30 you can determine the specific midpoint, minimum point, and maximum point,
- 03:35 and also change those colors as well.
- 03:37 But I'm happy with the way this looks, so I'm gonna select Done.
- 03:41 So now I can quickly see at a glance where I had fewer unit sales and
- 03:47 where I had more unit sales based on this color scale.
- 03:52 Now if you'd like to remove any conditional formatting which you've
- 03:56 applied, simply select one or more of the cells that it applies to and
- 04:01 you will see the conditional format rules appear here.
- 04:05 By selecting the trash can, you can remove that rule or
- 04:08 if I'd like to edit it, I can click on that rule.
- 04:11 And just for fun, let's change this color scale to something a little more colorful.
- 04:16 And I can change the values,
- 04:18 I can change the other criteria within the conditional formatting.
Lesson notes are only available for subscribers.