

Select the "Custom formula is" item in the "Format cells if" drop-down list and enter the logical formula that will help us identify the day of the week by the date. We will select the range of dates in A2:A22, go to the Format menu and click Conditional formatting.

None of the standard conditions works for us. Say we want to know which among our orders were made during the weekend. Use the last item in the drop-down list to enter your formula: "Custom formula is". In other words, the result of the formula must be either "True" or "False". This formula lets you describe your requirements using standard functions and operators. That's why Google Sheets provide the possibility to enter your own formula as a condition. Sooner or later you will need to create a condition that can't be described using the standard means. The suggested list of the conditions that we can apply to our data range is quite vast. Google Sheets conditional formatting with custom formulas To delete it, just point to it and click the Remove icon. You can add as many conditional formatting rules in Google Sheets as you need. The first one is for values that are greater than 200, the second one concerns values that are less than 100. Let's highlight the orders that are over $200 in Total sales, and those that are under $100.Īs you can see, we have two formatting conditions here. If the color scale seems too bright to you, you can create several conditions under the "Single color" tab and specify a format for each condition separately. Here we created a color scale where the cells get lighter as the order amount gets smaller, and darker as the sum increases.įormat cells in Google Sheets by multiple conditions You can also pick hues for the minimum and maximum points, as well as for the midpoint if necessary. To do this, select Color scale in the conditional format rules sidebar and use ready sets of color. You have the choice of setting up conditional formatting not only with one hue but using a color scale. We can see our formatting rule applied right away: all the necessary cells changed their appearance. This means that within the range we selected, all cells with values that are greater than or equal to 200 will be highlighted using the format we set right at the same place: bold red font in the yellow background. To begin with, let's consider Google Sheets conditional formatting using a single color.Ĭlick Format cells if., select the option "Greater than or equal to" in the drop-down list that you see, and enter "200" in the field below. Then find the Format menu item and click on Conditional formatting. We have these records in column F, so we'll use our mouse to select the range of values with the order amount: F2:F22. What can be interesting for us to see here? First, we can highlight those orders that exceed $200 in total sales. We used drop-down lists in column G to specify if it was completed. Each row in the table contains an order we got from a particular customer. Suppose we have chocolate sales data in our table.
#Best duplicate checker google sheets add ons how to#
How to add a formatting rule with one condition Let's look at some examples together and see how simple and effective it is. Google Sheets can do this work for us, all we need is to explain what we want to get. This is where conditional formatting comes in handy. Wouldn't it be great if such changes to formatting occurred automatically and drew yet more attention to such cells? We would save a lot of time. they are greater or less than some value, they are the greatest or the smallest, or perhaps they contain certain characters or words, then we find such cells and change their font, font color, or background color.

Highlighting particular data with color is a great way to draw attention to the records. Why do we need conditional formatting in a table? Isn't it easier to format cells manually? What is Google Sheets conditional formatting?
