Connect with us

MARKETING

Conditional Formatting Based on Another Cell in Google Sheets

Published

on

Conditional Formatting Based on Another Cell in Google Sheets


Conditional formatting is a feature in Google Sheets in which a cell is formatted in a particular way when certain conditions are met. The formatting can include highlighting, bolding, italicizing – just about any visual changes to the cell.

Just as it can be done for the cell you’re currently in, conditional formatting can also be set based on conditions met in another cell.

Let’s dive into how to create this condition based on multiple criteria.

How Conditional Formatting Works

To learn how to set conditional formatting, let’s use this workbook as an example.

example of a google sheets workbook to show how conditional formatting works

It’s a workbook showing website traffic year over year from Q4 2020 to Q4 2021, with the page views along with the year-over-year percentage change.

Advertisement

Here’s what we want to accomplish here: When the percentage change is positive YoY, the cell turns green. When it’s negative, the cell turns red. This makes it easy to get a quick performance overview before diving into the details.

Here are the steps to set the conditional formatting.

1. Select the cell you want to format, click on “Format” from the navigation bar, then click on “Conditional Formatting.”

how to set the conditional formatting step 1

2. While staying in the “Single color” tab, double-check that the cell under “Apply to range” is the cell you want to format. how to set the conditional formatting step 2

3. Set your format rules.

how to set the conditional formatting step 3

It may automatically default to a standard conditional formatting formula. In this case, open the dropdown menu under “Format cells if…” to select your rules. Options will look as follows:

4. Choose your formatting style, then click “Done.”how to set the conditional formatting step 4

5. Confirm the rule was applied under “Conditional Formatting Rules.”

conditional formatting step 5

6. Add another rule if needed.

conditional formatting step 6

7. Return to cell to view formatting, then drag the cursor to apply to other cells, if needed.

how to set the conditional formatting step 7

Now that you understand the basics, let’s cover how to use conditional formatting based on other cells.

See also  How to Cut Costs & Improve ROI with Lease Management Software

Conditional Formatting Based on Another Cell Value

1. Select the cell you want to format.

conditional formatting based on another cell value step 1

2. Click on “Format” in the navigation bar, then select “Conditional Formatting.”

conditional formatting based on another cell value step 2

3. Under “Format Rules,” select “Custom formula is.”

conditional formatting based on another cell value step 3

4. Write your formula, then click “Done.”

conditional formatting based on another cell value step 4

5. Confirm your rule has been applied and check the cell.

conditional formatting based on another cell value step 5

Conditional Formatting Based on Another Cell Range

To format based on another cell range, you follow many of the same steps you would for a cell value. What changes is the formula you write.

1. Select the cell you want to format.

conditional formatting based on another cell range step 1

2. Click on “Format” in the navigation bar, then select “Conditional Formatting.”

conditional formatting based on another cell range step 2

3. Under “Format Rules,” select “Custom formula is.”

conditional formatting based on another cell range step 3

4. Write your formula using the following format: =value range < [value], select your formatting style, then click “Done.”

conditional formatting based on another cell range step 4

5. Confirm your rule has been applied and check the cell.

conditional formatting based on another cell range step 5

Google Sheets Conditional Formatting Based on Another Cell Color

Currently, Google Sheets does not offer a way to use conditional formatting based on the color of another cell. You can only use it based on:

  • Values – higher than, greater than, equal to, in between
  • Text – contains, starts with, ends with, matches
  • Dates – is before, is after, is exactly
  • Emptiness – is empty, is not empty
See also  How To Create a LinkedIn Employee Advocacy Program

To achieve your goal, you’d have to use the condition of the cell to format the other.

Let’s use an example.

Advertisement

Google Sheets Conditional Formatting Based on Another Cell Color

Say you want to format cell A2 (September 2020) to be red and match the color of cell E2 (-20%). There’s no formula that allows you to create a condition based on color. However, you can create a custom formula based on E2’s values.

You can say that if cell E2’s values are less than 0, cell A2 turns red. The formula is as follows: = [The other cell] < [value]. In this case, the formula would be =e2<0, as it signifies that cell A2 should turn red if E2’s value is less than 0.

google sheets conditional formatting based on another cell's values

With so many functions to play with, Google Sheets can seem daunting. By following these simple steps, you can easily format your cells for quick scanning.

business google sheets templates



Source link

MARKETING

Old Navy to drop NFTs in July 4th promo update

Published

on

Old Navy to drop NFTs in July 4th promo update

Old Navy will update its yearly Fourth of July promotions by saluting the metaverse with an NFT drop, going live June 29.

In honor of the year they were founded, the retailer will release 1,994 common NFTs, each selling for $0.94. The NFTs will feature the iconic Magic the Dog and t include a promo code for customers to claim an Old Navy t-shirt at Old Navy locations or online.

“This launch is Old Navy’s first activation in web3 or with NFTs,” an Old Navy spokesperson told MarTech. “As a brand rooted in democratization and inclusivity, it was essential that we provide access and education for all with the launch of our first NFT collection. We want all our customers, whether they have experience with web3, to be able to learn and participate in this activation.”

Accessible and user-friendly. Any customer can participate by visiting a page off of Old Navy’s home site, where they’ll find step-by-step instructions.

There will also be an auction for a unique one-of-one NFT. All proceeds for the NFT and shirt sales go to Old Navy’s longtime charitable partner, Boys & Girls Clubs of America.

Additionally, 10% of NFT resales on the secondary market will also go to Boys & Girls Clubs.

Support. This activation is supported by Sweet, who’s played a major role in campaigns for other early NFT adopters like Burger King.

Advertisement

The Old Navy NFTs will be minted on the Tezos blockchain, known for its low carbon footprint.

“This is Old Navy’s first time playing in the web3 space, and we are using the launch of our first NFT collection to test and learn,” said Old Navy’s spokesperson. “We’re excited to enable our customers with a new way to engage with our iconic brand and hero offerings and look forward to exploring additional consumer activations in web3 in the future.”

See also  Roku expands measurement program to boost streaming advertiser experience

Read next: 4 key strategies for NFT brand launches

Why we care. Macy’s also announced an NFT promotion timed to their fireworks show. This one will award one of 10,000 NFTs to those who join their Discord server.

Old Navy, in contrast, is keeping customers closer to their owned channels, and not funneling customers to Discord. Old Navy consumers who don’t have an NFT wallet can sign up through Sweet to purchase and bid on NFTs.

While Macy’s has done previous web3 promotions, this is Old Navy’s first. They’ve aligned a charity partner, brand tradition and concern for the environment with a solid first crack at crypto.


Get the daily newsletter digital marketers rely on.

Advertisement



About The Author

Chris Wood draws on over 15 years of reporting experience as a B2B editor and journalist. At DMN, he served as associate editor, offering original analysis on the evolving marketing tech landscape. He has interviewed leaders in tech and policy, from Canva CEO Melanie Perkins, to former Cisco CEO John Chambers, and Vivek Kundra, appointed by Barack Obama as the country’s first federal CIO. He is especially interested in how new technologies, including voice and blockchain, are disrupting the marketing world as we know it. In 2019, he moderated a panel on “innovation theater” at Fintech Inn, in Vilnius. In addition to his marketing-focused reporting in industry trades like Robotics Trends, Modern Brewery Age and AdNation News, Wood has also written for KIRKUS, and contributes fiction, criticism and poetry to several leading book blogs. He studied English at Fairfield University, and was born in Springfield, Massachusetts. He lives in New York.

Advertisement
See also  Digital ad spend to decrease 10 to 20% in Ireland this year, industry members predict

Source link

Continue Reading

DON'T MISS ANY IMPORTANT NEWS!
Subscribe To our Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

Trending

en_USEnglish