Ta kontakt med oss

MARKNADSFÖRING

A Quick Guide to Conditional Formatting in Excel

Publicerad

A Quick Guide to Conditional Formatting in Excel


Let’s pretend you have a spreadsheet with 1,000 rows of data — it would be pretty difficult to spot patterns in the data with the naked eye. Enter conditional formatting.

This powerful tool highlights cells that meet a specific condition or “rule.” In other words, it brings your spreadsheet to life by adding color to patterns and trends.

Conditional Formatting highlighting who was absent in an attendance sheet.

Here, we’ll cover how to apply, edit, and copy and paste conditional formatting.

Conditional Formatting Based on Text

In this example, let’s use conditional formatting to an attendance list to highlight who was absent. The image below is the data set I’ll use to run through this explanation:

Screenshot 1-Mar-17-2022-04-58-33-30-PM

1. First, select the column or row you want to apply conditional formatting to. In this case, we’ll select column B.

Selecting Column B which contains the attendance status (Present or Absent)

2. To highlight who was absent, navigate to the header toolbar and select Conditional Formatting, as shown in the image below.

A screenshot of the Conditional Formatting tool in the header toolbar.

3. When the Conditional Formatting drop-down menu appears, select Highlight Cells Rules, then Equal To.

A screenshot of the Conditional Formatting drop-down. menu.

4. In the New Formatting dialog box, change Cell Value to Specific Text. Then, type “Absent” in the text box. Reference the image below:

The New Formatting Rule dialog box

5. From the New Formatting dialog box, we can also choose how we want to format the cells containing the word “Absent.” Check out the options below.

The "Format With" drop-down menu.

For this example, let’s stick with the default option (Light Red Fill with Dark Red Text).

6. Click OK. Now — thanks to conditional formatting — we can quickly identify which students were absent.

Conditional Formatting highlighting who was absent in an attendance sheet.

In the next section, we’ll cover how to apply conditional formatting based on another cell in the spreadsheet.

Conditional Formatting Based on Another Cell

In this example, the goal is to highlight the cells that match the drop-down menu in cell E1. The image below is the sample data set I’ll use for this explanation:

A spreadsheet that uses Conditional Formatting based on another cell.

1. First, select column B.

Selecting Column B to apply conditional formatting to.

2. Navigate to the header toolbar and select Conditional Formatting. When the Conditional Formatting drop-down menu appears, select Highlight Cells Rules, then Equal To.

A screenshot of the Conditional Formatting drop-down menu.

3. In the New Formatting dialog box, select Cell Value och Equal To.

In the text box, you can either click your mouse on cell E1 (the cell that contains the drop-down menu), or manually enter the formula =$E$1. See below.

The New Formatting Rule dialog box.

4. As you can see in the image above, we also changed the formatting to Yellow Fill with Dark Yellow Text. However, you can change this option to your preference. Click OK.

5. Now, the cells that match cell E1 are highlighted in yellow. Notice how the highlighted cells change depending on the status:

  • When the status is Present:

Conditional Formatting when the attendance status is set to "Present"

  • When the status is Absent:

Conditional Formatting when the attendance status is set to "Absent"

How to Edit Conditional Formatting

Here’s some good news — conditional formatting is not set in stone, meaning you can edit or delete it later. Here are the steps to do that:

1. Start by selecting the cell (or cell range) that contains a conditional formatting rule.

Selecting Column B which contains a Conditional Formatting rule.

2. Navigate to the header toolbar and select Conditional Formatting, then Manage Rules.

A screenshot of the "Manage Rules" option under the Conditional Formatting drop-down menu.

3. The Manage Rules dialog box will list the current rules for your selection. Select the rule you want to edit and click Edit Rule.

Screenshot of the Manage Rules dialog box.

How to Copy Conditional Formatting in Excel

You can easily copy a conditional formatting rule to another cell to (or range of cells) by using one of the following approaches.

1. Simple copy/paste.

The first approach is relatively straightforward. Start by selecting the cell you want to copy and hit the Copy button in the header toolbar — or click Control-C (or Command-C on a Mac).

Then, select the target cell and hit the Paste button in the header toolbar, or click Control-V (or Command-V on a Mac).

2. Format Painter

The second approach uses the tool Format Painter, which is located in the header toolbar. Check out the image below:

Screenshot of the Format Painter tool in the header toolbar.

To start, click on the cell you want to copy, then click Format Painter. Your mouse icon will change to a paintbrush. Then, drag the paintbrush to the cell (or range of cells) where you want to paste the format. Lastly, to stop using the paintbrush, press Esc on your keyboard.

Conditional formatting is a powerful way to visualize the data in your spreadsheet. With just a few clicks, you can emphasize important trends or patterns you may have otherwise missed. With the tips in this post, you’ll be able to use Conditional Formatting to its fullest extent.

excel marketing templates



Källlänk

Klicka för att kommentera

Lämna ett svar

Din e-postadress kommer inte publiceras. Obligatoriska fält är märkta *

MARKNADSFÖRING

Trender för e-postmarknadsföring 2023: Förutsägelser från branschfolket

Publicerad

Email Marketing Trends 2023: Predictions by the Industry Stalwarts


Every year, we see new trends entering the world of email marketing.

Källlänk

Fortsätt läsa

MARKNADSFÖRING

5 enkla saker du kan göra för att förbättra innehållsupplevelsen för läsarna

Publicerad

5 Simple Things You Can Do To Improve the Content Experience for Readers

Who doesn’t like to have a good experience consuming content?

I know I do. And isn’t that what we – as both a consumer of content and a marketer of content – all want?

What if you create such a good experience that your audience doesn’t even realize it’s an “experience?” Here’s a helpful mish-mash of easy-to-do things to make that possible.

1. Write with an inclusive heart

There’s nothing worse than being in a conversation with someone who constantly talks about themselves. Check your text to see how often you write the words – I, me, we, and us. Now, count how often the word “you” is used. If the first-person uses are disproportionate to the second-person uses, edit to delete many first-person references and add more “you” to the text.

You want to let your audience know they are included in the conversation. I like this tip shared in Take Binary Bias Out of Your Content Conversations by Content Marketing World speaker Ruth Carter: Go through your text and replace exclusionary terms such as he/him and she/her with they/them pronouns.

Go through your text and replace exclusionary terms such as he/him and she/her with they/them pronouns, says @rbcarter via @Brandlovellc @CMIContent. #WritingTips Click To Tweet

2. Make your content shine brighter with an AI assist

Content published online should look different than the research papers and essays you wrote in school. While you should adhere to grammar rules and follow a style guide as best as possible, you also should prioritize readability. That requires scannable and easily digestible text – headings, bulleted text, short sentences, brief paragraphs, etc.

Use a text-polishing aid such as Hemingway Editor (free and paid versions) to cut the dead weight from your writing. Here’s how its color-coded review system works and the improvements to make:

  • Yellow – lengthy, complex sentences, and common errors
    • Fix: Shorten or split sentences.
  • Red – dense and complicated text
    • Fix: Remove hurdles and keep your readers on a simpler path.
  • Pink – lengthy words that could be shortened
    • Fix: Scroll the mouse over the problematic word to identify potential substitutes.
  • Blue – adverbs and weakening phrases
    • Fix: Delete them or find a better way to convey the thought.
  • Green – passive voice
    • Fix: Rewrite for active voice.

Grammarly’s paid version works well, too. The premium version includes an AI-powered writing assistant, readability reports, a plagiarism checker, citation suggestions, and more than 400 additional grammar checks.

In the image below, Grammarly suggests a way to rephrase the sentence from:

“It is not good enough any longer to simply produce content “like a media company would”.

To:

“It is no longer good enough to produce content “as a media company would”.

Much cleaner, right?

3. Ask questions

See what I did with the intro (and here)? I posed questions to try to engage with you. When someone asks a question – even in writing – the person hearing (or reading) it is likely to pause for a split second to consider their answer. The reader’s role changes from a passive participant to an active one. Using this technique also can encourage your readers to interact with the author, maybe in the form of an answer in the comments.

4. Include links

Many content marketers include internal and external links in their text for their SEO value. But you also should add links to help your readers. Consider including links to help a reader who wants to learn more about the topic. You can do this in a couple of ways:

  • You can link the descriptive text in the article to content relevant to those words (as I did in this bullet point)
  • You can list the headlines of related articles as a standalone feature (see the gray box labeled Handpicked Related Content at the end of this article).

Add links to guide readers to more information on a topic – not just for SEO purposes says @Brandlovellc via @CMIContent. #WritingTips Click To Tweet

You also can include on-page links or bookmarks in the beginning (a table of contents, of sorts) in longer pieces to help the reader more quickly access the content they seek to help you learn more about a topic. This helps the reader and keeps visitors on your website longer.

5. Don’t forget the ‘invisible’ text

Alt text is often an afterthought – if you think about it all. Yet, it’s essential to have a great content experience for people who use text-to-speech readers. Though it doesn’t take too much time, I find that customizing the image description content instead of relying on the default technology works better for audience understanding.

First, ask if a listener would miss something if they didn’t have the image explained. If they wouldn’t, the image is decorative and probably doesn’t need alt text. You publish it for aesthetic reasons, such as to break up a text-heavy page. Or it may repeat information already appearing in the text (like I did in the Hemingway and Grammarly examples above).

If the listener would miss out if the image weren’t explained well, it is informative and requires alt text. General guidelines indicate up to 125 characters (including spaces) work best for alt text. That’s a short sentence or two to convey the image’s message. Don’t forget to include punctuation.

General guidelines indicate up to 125 characters (including spaces) work best for alt text, says @Brandlovellc via @CMIContent. Click To Tweet

For both decorative and informative images, include the photo credits, permissions, and copyright information, in the caption section.

For example, if I were writing an article about Best Dogs for Families, I would include an image of a mini Bernedoodle as an example because they make great family pets. Let’s use this image of my adorable puppy, Henri, and I’ll show you both a good and bad example of alt text.

An almost useless alt-text version: “An image showing a dog.”

Author’s tri-colored (brown, white, black, grey wavy hair), merle mini Bernedoodle, Henri, lying on green grass.

It wastes valuable characters with the phrase “an image showing.”

Use the available characters for a more descriptive alt text: “Author’s tri-colored (brown, white, black, grey wavy hair), merle mini Bernedoodle, Henri, lying on green grass.”

It’s more descriptive, and I only used 112 characters, including spaces.

Want to learn more? Alexa Heinrich, an award-winning social media strategist, has a helpful article on writing effective image descriptions called The Art of Alt Text. @A11yAwareness on Twitter is also a great resource for accessibility tips.

Improve your content and better the experience

Do any of these suggestions feel too hard to execute? I hope not. They don’t need a bigger budget to execute. They don’t need a lengthy approval process to implement. And they don’t demand much more time in production.

They just need you to remember to execute them the next time you write (and the time after that, and the time after that, and the … well, you get the idea.)

If you have an easy-to-implement tip to improve the content experience, please leave it in the comments. I may include it in a future update.

All tools mentioned in the article are identified by the author. If you have a tool to suggest, please feel free to add it in the comments.

If you have an idea for an original article you’d like to share with the CMI audience, you could get it published on the site. First, read our blogging guidelines and write or adjust your draft accordingly. Then submit the post for consideration following the process outlined in the guidelines.

In appreciation for guest contributors’ work, we’re offering free registration to one paid event or free enrollment in Content Marketing University to anyone who gets two new posts accepted and published on the CMI site in 2023.

HANDPLOCKAT RELATERAT INNEHÅLL:

Omslagsbild av Joseph Kalinowski/Content Marketing Institute



Källlänk

Fortsätt läsa

MARKNADSFÖRING

Den ultimata guiden till produktmarknadsföring 2023

Publicerad

The Ultimate Guide to Product Marketing in 2023

Product marketing is essential, even if you only sell one or two products at your organization.

(mer …)

Fortsätt läsa

Trendigt

sv_SESvenska