Conditional Formatting in Google Sheets (Complete Guide)

Are you tired of manually sorting through endless data in your spreadsheet? Conditional formatting in Google Sheets can transform your workflow! In this video, Scott Friesen breaks down the power of conditional formatting and how to highlight information in your spreadsheets effortlessly.

Video Transcript

Are you trying to highlight or find the most relevant information within your spreadsheet? Well, that's exactly where conditional formatting can help us out so much. So instead of staring at your spreadsheet aimlessly, let's take advantage of conditional formatting. This video is sponsored by SaneBox, the email inbox lifesaver. With AI technology, SaneBox prioritizes essential messages, saving you hours of time each week. And by learning from your behavior, SaneBox will clean up your inbox, get rid of spam, and organize your emails into relevant folders. Best of all, SaneBox works with any email provider. To get started, click the link in the description to get your free 14-day trial. Here within Google Sheets, we need to come up to Format, and then near the bottom, we want to select Conditional Formatting. And on the right-hand side, we will have the opportunity to apply some conditional format rules. Now, there are two choices, we can either choose a single color or a color scale, and I'm going to be covering both, including an advanced option that you may find helpful as well. So the first thing we need to do is choose the range where we want this formatting to take place. Now, you can either type it in here if you're comfortable with that. I prefer to select the range with my cursor. So in this example, I want to highlight things here within the Units column. So I'm going to select everything within that column and I'm going to say, OK. Now, by default, it is going to color everything in here because our default settings are if the format cells is not empty, we want to apply this color. And because nothing is empty here, everything has a value, everything has received this green shade. But even this default can be handy at times. Let's say that I forgot to enter in a value here and let's say that this individual has forgotten to give me their sales numbers. Well, that stands out pretty clearly where I am missing data, so even this simple example may be helpful. But let's take a closer look at this drop-down menu so that we can make this conditional formatting that much more valuable. In my example, I want to highlight anything here that is less than 30 units. Maybe if something has sold fewer than 30 units, that's a cause for concern and I would like to investigate further. So I'm going to select this drop-down and you can see that there are three broad categories we can use. The first section is specific to text-based findings. So this would be very helpful if we were looking at one of these other columns, such as Region, Rep, or Item. Next we have a date area, so if we want to zero in on a specific date or before or after, that would be most helpful here in my first column. But for my example, I want to look at units. I want to look at a numerical example, so I'm going to say less than or equal to. I'm going to select that option here, and then down below, all I need to do is enter in what that value is. So I said the number 30, I'm going to enter in the number 30, and instantly all of those cells that have 30 or fewer units are going to be highlighted here. This makes it so much easier for me and others that I share this sheet with to find specific or important pieces of information. And remember, the conditional in conditional formatting means that it is dynamic. So let's say that this unit is actually incorrect and they come back and say they actually sold 32. Well, as soon as I enter in the number 32, that color is going to be removed. But maybe this one is also incorrect, and we review on the order form that they only sold 29 units, well, now that will be shaded this color green. Speaking of color, we have a lot of options here when it comes to formatting that style. Now, we have six different defaults ranging from green, yellow, and red. We can either choose to have a full color background like this, or if we want, we can just choose to have the text itself change color. This may be a little too subtle, so I often prefer to use the full background here. But we're not limited just at those six selections, you can see down below we can add other options as well. So if I want the numbers to be bolded in this case, I could do that. If I want them to be struck through, I could do that as well. Or if I want to change it to a custom color, I can make that option here as well. So you've got an awful lot of choice when it comes to choosing your color. Now, when you're happy with editing your conditional format rules, all you need to do is come down and select Done, and now those will be baked into your spreadsheet. At this stage, I can either close this window here or add another rule. Now, if you ever need to go back and edit an existing conditional rule, all you need to do is select the cell which is being applied to that conditional rule. You can see, even if I choose something else here within column E, even though it's not highlighted in red, this option here to edit or remove that conditional formatting rule is available to me. So if I click on this, this menu will be revealed again. And maybe I want to change that number to anything less than 25 this time, and you can see at least one of those changed as a result. I'm now going to go ahead and hit Done. And if I want to remove it, all I need to do is select Remove This Rule and we were brought back to our standard spreadsheet. But let's go ahead and add another conditional rule, this time we are going to make use of the color scale. So I'm going to select this tab here, the Color Scale, and once again, we will need to select the range where we want this rule to be applied. In this example, I'm going to select everything here within this G column and select OK, and immediately you can see that we have a color scale applied. Now, in many cases, you may want to come up to this range and actually remove the end row and just leave it as the column name. The reason being is so that as you add additional numbers down below, your same conditional formatting rule will apply. If I left things at just G21 or G22, these rules would only be applied here. But I imagine I'm going to be adding further information in the future, so if I add in another dollar value here, I want the same color scale to be applied as well. So taking a closer look at our color scale, here you can see that the highest number here, roughly $2,800, has no color applied to it, and the darkest shades of green are going to be our lowest numbers. However, I personally find this a little confusing. It's given a green shade, many of them very, very similar, to every single thing in this column and only one is standing out with no color at all. Well, if we select the preview option here, we have a number of different color scales that we can choose from. Now, my personal preference is this red to white to green, and let me show you why. I'm going to select it here, and now we can see that the highest number within this column is that dark green. Some of our other higher numbers have a shade of green, but our lowest numbers are now in the red. To me, this color scale is a lot more helpful and makes a bigger distinction between the greatest totals and the lowest totals here as well. Now, down below the preview, we can continue to customize our rules for both the minimum point, the midpoint, and the maximum point as well. But I find that this is going to be helpful enough for my needs right here. Keep in mind these color scales are not only dynamic, but the level of shading will change as well. In this example, we can see that this is a dark green for the $2,875, the largest total in this column. But let's say we have a record sale and they actually sold 99 of these units. Now because this is such an extreme difference from everything else on the list, this is the only one with a green shade. We have a lot more white, meaning things that are more in the midpoint compared to this, and then the other shades that are red. Maybe if this is a more reasonable number, something like 43, now we start to see some other green shades here, but if we change it to something like, oh, maybe three, now you can see what else jumps out as the highest point. Again, we can see the effects of making these changes and how it will dynamically affect our conditional rule. When you're done or happy with your edits here, all you need to do is select the Done button. But now that you know how to apply a simple single or multi-color conditional formatting rule, let's take a look at how we can apply an advanced rule if we want to highlight multiple things and also highlight complete rows. I'm going to select Add Another Rule and we're going to stay within the Single Color tab. This time, I'm actually going to select everything on my spreadsheet because I want to highlight entire rows. Now, again, by default, because nothing is empty, it has actually made my entire spreadsheet green, but we're going to change that in just a moment. Here we're going to select what type of rule we want to apply, and I'm going to come all the way down to the bottom and select this custom formula option. And here we have the opportunity to include a specific formula or a specific set of instructions. So I'm going to come in here and select the equal sign and then I'm going to select the dollar sign because I wanted to identify the entire row. And in my example, I'm going to say B, because we're going to be focusing on this B column. In particular, I want to highlight all of the rows that were sold in the East region. So what I need to do next is enter in the number two, identifying that I want it to start looking at the top of this column and then continue to persistently look the remainder of this column. I'm again going to enter the equal sign and I'm going to use quotes to look for the word East and then close it with my quotations once again. And now you can see not only has it highlighted the cells that contain the East region, but because we selected the entire range of this spreadsheet, it is highlighting the entire row as well, so I can really zero in on those areas. I'm going to select Done, but we're not quite done because I also want to highlight the West region, but use as little effort as possible. Now, to make it easier for me, what I'm going to do is actually copy this formula. I'm going to select Done and say Add Another Rule. Now, that entire range is already selected. That's great, I don't have to do that again. But once again, I want to come down to the custom formula. I'm going to paste in the same formula that we had before, but this time I'm going to type in the word West because that's what I want to highlight. But I don't want East and West to have the same color, so I'm going to come down here and select this pinkish shade here. Now I can select Done and I can easily see the difference between everything that was sold in the East region versus everything else that was sold in the West region. And remember, no matter how I sort my spreadsheet, these conditional formatting rules will still apply as long as they're within the range that we specify. So now that you know how to apply conditional formatting rules right here within Google Sheets, what further questions do you have? Be sure to let me know in the comments down below. Thank you so much for watching. And remember, being productive does not need to be difficult, in fact, it's very simple.