1. Formulas and tidying up the dashboard
In addition to the 18 rules that Sheets provides, you can use formulas to determine which cells are formatted. This gives conditional formatting lots of versatility.
2. Some common uses for formulas
Formulas can be used to highlight rows that meet certain conditions, rather than a specified cell, compare lists and values, highlight duplicates and discrepancies, highlight a specific number or text entry, highlight alternate rows, enable dynamic named ranges, and find errors and blanks.
3. Highlight a row using a formula
Let's discuss highlighting a row using conditional formatting if a figure is less than a set amount.
Using the Why do people come to Australia dataset, we can add conditional formatting to highlight entire rows where the number of visitors traveling to Australia is less than 200,000. We want to color the data in a row blue, and bold and italicize it. To do this, highlight the range, go to Format, conditional formatting, and select Custom formula under Format cells if. Then, enter in the formula, equals dollar symbol R3 less than 200,000. When a row in the selected range meets the condition, meaning the condition is TRUE, it will have the specified formatting.
Note that, as with copying formulas, partial and full cell absolution are important when using formulas with conditional formatting.
4. Highlight duplicates
Highlighting duplicates is another common use for conditional formatting using a simple formula. In this example, we are looking at column S. To highlight duplicates here, we use the formula equals countif and, in parentheses, dollar symbol S colon dollar symbol S comma S3, then, outside the parentheses, specify greater than 1.
5. Wildcard characters
Wildcard characters substitute for any other character. The two most common wildcard characters you use in Sheets are the question mark, to match any single character, and the asterisk, to match zero or more characters. You can use them in custom formulas and within the Text contains and Text does not contain fields.
Let's use wildcard characters on a set of product numbers. Using the formula M question mark 2 within Text contains applies formatting to all cells with 1 character between the M and the 2, shown here in blue. Specifying two question marks between M and 2 formats all cells with two characters between M and 2, shown in green.
Replacing the question mark with an asterisk, making the formula M asterisk 2, will format all cells with zero or more characters between the M and the 2, shown in yellow.
6. Tidying up the dashboard
Smartening up your dashboard is always best done at the end. There are a number of things you can do to tidy it up.
By default, Sheets shows gridlines. You can hide them by selecting the View option then clicking on gridlines to remove the tick. You can turn the gridlines on again by selecting them again.
7. What else should you do?
Using borders will help group certain sets of data. You can also hide irrelevant sheets by right-clicking the sheet name and selecting Hide sheet.
8. Hiding the toolbars
If you want your audience to simply view your dashboard without being able to edit it, you should hide the toolbars. Select View and formula bar to hide the formula bar and View and full screen to hide the other toolbars.
To get out of full-screen mode, press the escape key on the top left-hand side of your keyboard. To reinstate the formula bar, you will need to select View then Formula bar again.
9. Practice time!
Your dashboard is nearly complete! Let's tidy it up so you are ready to showcase your work.