1. A primer on conditional formatting
Welcome to this course on using conditional formatting in Google Sheets. My name is Adam Steinfurth and I work as an CPA looking at other client's spreadsheets every day and I've also developed over 100 videos on using spreadsheets and other software. We're going to work together to develop an understanding of what conditional formatting is and how you can use it to tell a deeper story with your data.
2. When to use conditional formatting
There are several scenarios in which conditional formatting is a really good fit.
If you are working with a small to medium size data set that you can see on one page or with minimal scrolling this could work well. Conditional formatting is meant to be visual so, if you have 50,000 lines of data, it won't help if you highlight a cell in row 37,008.
If the underlying data that you are working with is constantly changing but not the layout of the data, conditional formatting will continue to work with it. The formatting sits on top of the data, rather than in the data, so it remains when the underlying data changes.
3. Parts of conditional formatting
This is the menu that will control the conditional formatting that we'll be doing. All of the options that we will be looking at are driven from here.
Working from the top down, first, you can choose from a Single color or a Color scale. Most of your work will be in a single color.
4. Parts of conditional formatting
The next field is the range in which you can either type it in or select with your mouse.
5. Parts of conditional formatting
The Format cells if drop-down contains pre-built functions such as greater than, less than, and so on, that will help to give us a head start.
6. Parts of conditional formatting
At the bottom of the choices in the drop-down is the custom formula option which will allow us to level-up our formatting, but we'll tread cautiously before we jump into those waters.
7. Parts of conditional formatting
Last is the formatting styles, which contains options that include background color and font styling.
8. Unformatted data
This example contains sales made at a company. We want to format which items were sold by Pam.
9. Select the range
Before you select conditional formatting from the menu, highlight the data that you are looking to format. In this case, highlight the data in column C just below the header.
10. File, conditional formatting
Now that you have your range selected, go to Format on your menu and choose Conditional Formatting.
11. Add a rule
Click on Add another rule.
12. Format cells if
After choosing Add another rule, you'll come to the conditional formatting menu that we discussed previously. The range that you selected is filled out for you.
Click the drop-down under Format cells if, choose Text contains, and type Pam in the input box.
13. Sales rep highlighted
With this simple application of a conditional formatting rule, every cell that has the word Pam is formatted. Conditional formatting does not filter, sort, or otherwise move the underlying data. The data in the table is kept in its original state and the formatting lays on top.
14. Different formatting styles
With the same range highlighted, you can click Add another rule. The old rule remains applied to the Sheet and this rule will work in addition to it.
We'll keep the options the same except change the name to John and, under formatting style, choose a Red background.
Now you have the two styles working together. The two different colors help to differentiate the two sales reps.
15. Let's give it a try!
Now that you have a basic understanding of conditional formatting, let's try a few exercises.