How To Highlighting Data in Google Sheets with Conditional Formatting

How To Highlighting Data in Google Sheets with Conditional Formatting

A fantastic feature at your disposal in Google Sheets is conditional formatting, which makes it simple to highlight particular cells based on specific criteria. Imagine being able to, for example, change a cell’s background color to a bright yellow when its value falls below a set threshold. The functionality also broadens its capabilities to encompass whole rows or columns, giving you the flexibility to highlight material that meets specific criteria.

Highlight Individual Cells:

Imagine that we have a sales chart with the names of the salespeople, their respective states, and their sales goals. The purpose? to highlight specific cells in the ‘State’ column just for California-based salesmen.

Go to the “Format” menu first, then “Conditional Formatting,” without missing a beat. The ‘Add Condition’ button’s single click exposes a world of options. Here, choose “Text is Exactly” as the format requirement and specify the range as B2:B. Enter the letter “CA” in the text box, choose a special backdrop color, and then politely click “Done.”

How To Highlighting Data in Google Sheets with Conditional Formatting

Highlight Entire Row:

Let’s now improve our mastery by concentrating on a bigger canvas. Let’s say we want the rows to shine when the sales goals surpass $8,000 per month.

Include the complete contents of our data table in the formatting rule’s scope by setting the range to A2:C. Selecting ‘Custom Formula is’ as the formatting rules condition will reveal the true potential of =$C2>8000.

How To Highlighting Data in Google Sheets with Conditional Formatting

The formula =ISBETWEEN($C2, 5000, 7000) is useful if you want to shed light on rows where the sales objective is inside a range, such as between $5000 and $7000. Watch the dollar sign’s subtle dance: the $ before C2 orchestrates the formula’s application throughout the C column, while the $ before the number 2 maintains the formula’s dynamic nature.

=$C:$C=max($C:$C)
Formatting based on two cells:

Watch as the scene becomes increasingly intricate, displaying our capacity to balance the circumstances. Let’s imagine salespeople with local roots in a certain state, like “CA,” who also claim sales targets higher than $5,000.

Through a symphony of circumstances directed by the AND function, we unfold this tapestry. Seen in all its splendor is the prowess of

=AND(C2>5000, B2="CA")
Conditional Formatting base on Date:

Immerse yourself in the universe of dates and transcend the domain of numbers. Think of a table covered with bills, each with a due date. By strategically using conditional formatting, we highlight invoices that have been past due for more than 30 days and send out prompt email reminders.

=DAYS(TODAY(),$B:$B)>=30

How To Highlighting Data in Google Sheets with Conditional Formatting

where the present and past collide.

Let’s not, however, stop there. Imagine a student roster with their birthdates listed. With the use of date functions, we can identify pupils who are older than the legal drinking age of 16, with the additional condition that their birthdates fall inside the month in question. See the formula that depicts maturation:

=AND(YEAR(TODAY())-YEAR($B2)>=16,MONTH($B2)=MONTH(TODAY()).
Heatmaps – Format Cells by Color Scale:

Explore the world of heatmaps, where data and color interact to illustrate complex temperature trends. The beauty of color scales is revealed in a workbook decorated with US cities and their monthly average temperatures. While lower temperatures revel in the soothing greens, high temperatures adopt the fierce red colours.

Mark Rows Containing one of the values:

Navigate the data maze, where rows contain a wide range of values. Consider a situation in which the State column acts as a flashlight, spotlighting rows bearing the revered value of CA.

But if your search involves taking into account rows that contain an array of values, the OR function or the splendor of Regular Expressions is there for you. Experience the mysterious

=REGEXMATCH(UPPER($B:$B), "(CA|NY|FL)$" 

How To Highlighting Data in Google Sheets with Conditional Formatting

for a dance of CA, NY, and FL.

Consider a state-filled tapestry instead, living in a different sheet. The storyline develops while showing the rows of destiny with MATCH and INDIRECT as your partners.

=MATCH($B1, INDIRECT("'List of States'!A1:A"),0)
Apply Conditional Formatting to Entire Column:

We have up to this point carefully examined the cells and rows, but the columns have remained shrouded in mystery. Unfortunately, the moment has come to reveal the genius hidden beneath columns.

Imagine a scenario in which selecting a year in cell A9 starts the sales table’s illumination symphony. The enchantment? A unique formula that is both straightforward and profound: =B$1=$A$9. This enchantment is sparked by the dollar signs, which signal a hint of consistency.

How To Highlighting Data in Google Sheets with Conditional Formatting

Conditional Formatting with Google Apps Script:

As your domain grows to include several spreadsheets, think about implementing Google Apps Script. Automate the tedious formatting process to ensure consistency across all of your spreadsheets and to save time.

Let the script’s canvas be your guide:
const applyConditionalFormatting = () => {
  const sheet = SpreadsheetApp.getActiveSheet();
  const color = SpreadsheetApp.newColor().setThemeColor(SpreadsheetApp.ThemeColorType.BACKGROUND).build();
  const rule1 = SpreadsheetApp.newConditionalFormatRule()
    .setRanges([sheet.getRange('B:B')])
    .whenTextEqualTo('CA')
    .setUnderline(true)
    .setBold(true)
    .setBackground(color)
    .build();
  const rule2 = SpreadsheetApp.newConditionalFormatRule()
    .setRanges([sheet.getRange('A1:C15')])
    .whenFormulaSatisfied('=$C1>5000')
    .setBackground('green')
    .setFontColor('#00FF00')
    .build();
  const conditionalFormatRules = sheet.getConditionalFormatRules();
  conditionalFormatRules.push(rule1);
  conditionalFormatRules.push(rule2);
  sheet.setConditionalFormatRules(conditionalFormatRules);
};

Observe this digital maestro as he expertly choreographs formatting. Investigate the depths of ConditionalFormatRuleBuilder’s documentation to discover the tricks of flawless rule transfer.

Leave a Comment