Home Marketing A Quick Guide to Conditional Formatting in Excel

A Quick Guide to Conditional Formatting in Excel

0
A Quick Guide to Conditional Formatting in Excel

Let’s faux you’ve a spreadsheet with 1,000 rows of information — it will be fairly tough to identify patterns within the information with the bare eye. Enter conditional formatting.

Download 10 Excel Templates for Marketers [Free Kit]

This highly effective software highlights cells that meet a particular situation or “rule.” In different phrases, it brings your spreadsheet to life by including shade to patterns and tendencies.

Conditional Formatting highlighting who was absent in an attendance sheet.

Right here, we’ll cowl the way to apply, edit, and duplicate and paste conditional formatting.

Conditional Formatting Primarily based on Textual content

On this instance, let’s use conditional formatting to an attendance checklist to spotlight who was absent. The picture under is the information set I’ll use to run by means of this rationalization:

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

1. First, choose the column or row you wish to apply conditional formatting to. On this case, we’ll choose column B.

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

2. To spotlight who was absent, navigate to the header toolbar and choose Conditional Formatting, as proven within the picture under.

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

3. When the Conditional Formatting drop-down menu seems, choose Spotlight Cells Guidelines, then Equal To.

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

4. Within the New Formatting dialog field, change Cell Worth to Particular Textual content. Then, sort “Absent” within the textual content field. Reference the picture under:

The New Formatting Rule dialog box

5. From the New Formatting dialog field, we will additionally select how we wish to format the cells containing the phrase “Absent.” Try the choices under.

The "Format With" drop-down menu.

For this instance, let’s keep on with the default choice (Gentle Purple Fill with Darkish Purple Textual content).

6. Click on OK. Now — because of conditional formatting — we will shortly determine which college students have been absent.

Conditional Formatting highlighting who was absent in an attendance sheet.

Within the subsequent part, we’ll cowl the way to apply conditional formatting primarily based on one other cell within the spreadsheet.

Conditional Formatting Primarily based on One other Cell

On this instance, the aim is to spotlight the cells that match the drop-down menu in cell E1. The picture under is the pattern information set I’ll use for this rationalization:

A spreadsheet that uses Conditional Formatting based on another cell.

1. First, choose column B.

Selecting Column B to apply conditional formatting to.

2. Navigate to the header toolbar and choose Conditional Formatting. When the Conditional Formatting drop-down menu seems, choose Spotlight Cells Guidelines, then Equal To.

A screenshot of the Conditional Formatting drop-down menu.

3. Within the New Formatting dialog field, choose Cell Worth and Equal To.

Within the textual content field, you may both click on your mouse on cell E1 (the cell that incorporates the drop-down menu), or manually enter the system =$E$1. See under.

The New Formatting Rule dialog box.

4. As you may see within the picture above, we additionally modified the formatting to Yellow Fill with Darkish Yellow Textual content. Nonetheless, you may change this selection to your desire. Click on OK.

5. Now, the cells that match cell E1 are highlighted in yellow. Discover how the highlighted cells change relying on the standing:

  • When the standing is Current:

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

  • When the standing is Absent:

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

How you can Edit Conditional Formatting

This is some excellent news — conditional formatting is not set in stone, which means you may edit or delete it later. Listed here are the steps to do this:

1. Begin by choosing the cell (or cell vary) that incorporates a conditional formatting rule.

Selecting Column B which contains a Conditional Formatting rule.

2. Navigate to the header toolbar and choose Conditional Formatting, then Handle Guidelines.

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

3. The Handle Guidelines dialog field will checklist the present guidelines on your choice. Choose the rule you wish to edit and click on Edit Rule.

Screenshot of the Manage Rules dialog box.

How you can Copy Conditional Formatting in Excel

You may simply copy a conditional formatting rule to a different cell to (or vary of cells) by utilizing one of many following approaches.

1. Easy copy/paste.

The primary method is comparatively easy. Begin by choosing the cell you wish to copy and hit the Copy button within the header toolbar — or click on Management-C (or Command-C on a Mac).

Then, choose the goal cell and hit the Paste button within the header toolbar, or click on Management-V (or Command-V on a Mac).

2. Format Painter

The second method makes use of the software Format Painter, which is situated within the header toolbar. Try the picture under:

Screenshot of the Format Painter tool in the header toolbar.

To begin, click on on the cell you wish to copy, then click on Format Painter. Your mouse icon will change to a paintbrush. Then, drag the paintbrush to the cell (or vary of cells) the place you wish to paste the format. Lastly, to cease utilizing the paintbrush, press Esc in your keyboard.

Conditional formatting is a robust solution to visualize the information in your spreadsheet. With just some clicks, you may emphasize vital tendencies or patterns you might have in any other case missed. With the guidelines on this put up, you’ll have the ability to use Conditional Formatting to its fullest extent.

excel marketing templates

LEAVE A REPLY

Please enter your comment!
Please enter your name here