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.

Advertisement

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.

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.

Advertisement

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

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

Let’s use an example.

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.

Advertisement

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
Keep an eye on what we are doing
Be the first to get latest updates and exclusive content straight to your email inbox.
We promise not to spam you. You can unsubscribe at any time.
Invalid email address

MARKETING

How To Develop a Great Creative Brief and Get On-Target Content

Published

on

How To Develop a Great Creative Brief and Get On-Target Content

Every editor knows what it feels like to sit exasperated in front of the computer, screaming internally, “It would have been easier if I’d done it myself.”

If your role involves commissioning and approving content, you know that sinking feeling: Ten seconds into reviewing a piece, it’s obvious the creator hasn’t understood (or never bothered to listen to) a damn thing you told them. As you go deeper, your fingertips switch gears from polite tapping to a digital Riverdance as your annoyance spews onto the keyboard. We’ve all been there. It’s why we drink. Or do yoga. Or practice voodoo.

In truth, even your best writer, designer, or audiovisual content creator can turn in a bad job. Maybe they had an off day. Perhaps they rushed to meet a deadline. Or maybe they just didn’t understand the brief.

The first two excuses go to the content creator’s professionalism. You’re allowed to get grumpy about that. But if your content creator didn’t understand the brief, then you, as the editor, are at least partly to blame. 

Advertisement

Taking the time to create a thorough but concise brief is the single greatest investment you can make in your work efficiency and sanity. The contrast in emotions when a perfectly constructed piece of content lands in your inbox could not be starker. It’s like the sun has burst through the clouds, someone has released a dozen white doves, and that orchestra that follows you around has started playing the lovely bit from Madame Butterfly — all at once.

Here’s what a good brief does:

  • It clearly and concisely sets out your expectations (so be specific).
  • It focuses the content creator’s mind on the areas of most importance.
  • It encourages the content creator to do a thorough job rather than an “it’ll-do” job.
  • It results in more accurate and more effective content (content that hits the mark).
  • It saves hours of unnecessary labor and stress in the editing process.
  • It can make all the difference between profit and loss.

Arming content creators with a thorough brief gives them the best possible chance of at least creating something fit for purpose — even if it’s not quite how you would have done it. Give them too little information, and there’s almost no hope they’ll deliver what you need.

On the flip side, overloading your content creators with more information than they need can be counterproductive. I know a writer who was given a 65-page sales deck to read as background for a 500-word blog post. Do that, and you risk several things happening:

  • It’s not worth the content creator’s time reading it, so they don’t.
  • Even if they do read it, you risk them missing out on the key points.
  • They’ll charge you a fortune because they’re losing money doing that amount of preparation.
  • They’re never going to work with you again.

There’s a balance to strike.

There’s a balance to be struck.

Knowing how to give useful and concise briefs is something I’ve learned the hard way over 20 years as a journalist and editor. What follows is some of what I’ve found works well. Some of this might read like I’m teaching grandma to suck eggs, but I’m surprised how many of these points often get forgotten.

Who is the client?

Provide your content creator with a half- or one-page summary of the business:

Advertisement
  • Who it is
  • What it does
  • Whom it services
  • What its story is
  • Details about any relevant products and services

Include the elevator pitch and other key messaging so your content creator understands how the company positions itself and what kind of language to weave into the piece.

Who is the audience?

Include a paragraph or two about the intended audience. If a company has more than one audience (for example, a recruitment company might have job candidates and recruiters), then be specific. Even a sentence will do, but don’t leave your content creator guessing. They need to know who the content is for.

What needs to be known?

This is the bit where you tell your content creator what you want them to create. Be sure to include three things:

  • The purpose of the piece
  • The angle to lead with
  • The message the audience should leave with

I find it helps to provide links to relevant background information if you have it available, particularly if the information inspired or contributed to the content idea, rather than rely on content creators to find their own. It can be frustrating when their research doesn’t match or is inferior to your own.

How does the brand communicate?

Include any information the content creators need to ensure that they’re communicating in an authentic voice of the brand.

  • Tone of voice: The easiest way to provide guidance on tone of voice is to provide one or two examples that demonstrate it well. It’s much easier for your content creators to mimic a specific example they’ve seen, read, or heard than it is to interpret vague terms like “formal,” “casual,” or “informative but friendly.”
  • Style guide: Giving your content creator a style guide can save you a lot of tinkering. This is essential for visuals but also important for written content if you don’t want to spend a lot of time changing “%” to “percent” or uncapitalizing job titles. Summarize the key points or most common errors.
  • Examples: Examples aren’t just good for tone of voice; they’re also handy for layout and design to demonstrate how you expect a piece of content to be submitted. This is especially handy if your template includes social media posts, meta descriptions, and so on.

All the elements in a documented brief

Here are nine basic things every single brief requires:

  • Title: What are we calling this thing? (A working title is fine so that everyone knows how to refer to this project.)
  • Client: Who is it for, and what do they do?
  • Deadline: When is the final content due?
  • The brief itself: What is the angle, the message, and the editorial purpose of the content? Include here who the audience is.
  • Specifications: What is the word count, format, aspect ratio, or run time?
  • Submission: How and where should the content be filed? To whom?
  • Contact information: Who is the commissioning editor, the client (if appropriate), and the talent?
  • Resources: What blogging template, style guide, key messaging, access to image libraries, and other elements are required to create and deliver the content?
  • Fee: What is the agreed price/rate? Not everyone includes this in the brief, but it should be included if appropriate.

Depending on your business or the kind of content involved, you might have other important information to include here, too. Put it all in a template and make it the front page of your brief.

Prepare your briefs early

It’s entirely possible you’re reading this, screaming internally, “By the time I’ve done all that, I could have written the damn thing myself.”

But much of this information doesn’t change. Well in advance, you can document the background about a company, its audience, and how it speaks doesn’t change. You can pull all those resources into a one- or two-page document, add some high-quality previous examples, throw in the templates they’ll need, and bam! You’ve created a short, useful briefing package you can provide to any new content creator whenever it is needed. You can do this well ahead of time.

I expect these tips will save you a lot of internal screaming in the future. Not to mention drink, yoga, and voodoo.

Advertisement

This is an update of a January 2019 CCO article.

Get more advice from Chief Content Officer, a monthly publication for content leaders. Subscribe today to get it in your inbox.

HANDPICKED RELATED CONTENT: 

Cover image by Joseph Kalinowski/Content Marketing Institute

Source link

Keep an eye on what we are doing
Be the first to get latest updates and exclusive content straight to your email inbox.
We promise not to spam you. You can unsubscribe at any time.
Invalid email address
Continue Reading

MARKETING

Quiet Quitting vs. Setting Healthy Boundaries: Where’s The Line?

Published

on

Quiet Quitting vs. Setting Healthy Boundaries: Where's The Line?

In the summer of 2022, we first started hearing buzz around a new term: “Quiet quitting“.

(more…)

Keep an eye on what we are doing
Be the first to get latest updates and exclusive content straight to your email inbox.
We promise not to spam you. You can unsubscribe at any time.
Invalid email address
Continue Reading

MARKETING

Microsoft unveils a new small language model

Published

on

Microsoft unveils a new small language model

Phi-3-Mini is the first in a family of small language models Microsoft plans to release over the coming weeks. Phi-3-Small and Phi-3-Medium are in the works. In contrast to large language models like OpenAI’s ChatGPT and Google’s Gemini, small language models are trained on much smaller datasets and are said to be much more affordable for users.

We are excited to introduce Phi-3, a family of open AI models developed by Microsoft. Phi-3 models are the most capable and cost-effective small language models (SLMs) available, outperforming models of the same size and next size up across a variety of language, reasoning, coding and math benchmarks.

Misha Bilenko Corporate Vice President, Microsoft GenAI

What are they for? For one thing, the reduced size of this language model may make it suitable to run locally, for example as an app on a smartphone. Something the size of ChatGPT lives in the cloud and requires an internet connection for access.

While ChatGPT is said to have over a trillion parameters, Phi-3-Mini has only 3.8 billion. Sanjeev Bora, who works with genAI in the healthcare space, writes: “The number of parameters in a model usually dictates its size and complexity. Larger models with more parameters are generally more capable but come at the cost of increased computational requirements. The choice of size often depends on the specific problem being addressed.”

Advertisement

Phi-3-Mini was trained on a relatively small dataset of 3.3 trillion tokens — instances of human language expressed numerically. But that’s still a lot of tokens.

Why we care. While it is generally reported, and confirmed by Microsoft, that these SLMs will be much more affordable than the big LLMs, it’s hard to find exact details on the pricing. Nevertheless, taking the promise at face-value, one can imagine a democratization of genAI, making it available to very small businesses and sole proprietors.

We need to see what these models can do in practice, but it’s plausible that use cases like writing a marketing newsletter, coming up with email subject lines or drafting social media posts just don’t require the gigantic power of a LLM.



Dig deeper: How a non-profit farmers market is leveraging AI

Source link

Advertisement
Keep an eye on what we are doing
Be the first to get latest updates and exclusive content straight to your email inbox.
We promise not to spam you. You can unsubscribe at any time.
Invalid email address
Continue Reading

Trending

Follow by Email
RSS