A Quick Guide to Conditional Formatting in Excel

A Swift Guidebook to Conditional Formatting in Excel


Let’s faux you have a spreadsheet with 1,000 rows of info — it would be pretty challenging to spot styles in the knowledge with the naked eye. Enter conditional formatting.

Download 10 Excel Templates for Marketers [Free Kit]

This strong resource highlights cells that meet up with a particular problem or “rule.” In other words and phrases, it delivers your spreadsheet to life by adding colour to designs and developments.

Conditional Formatting highlighting who was absent in an attendance sheet.

Listed here, we’ll address how to use, edit, and copy and paste conditional formatting.

Conditional Formatting Based mostly on Text

In this illustration, let’s use conditional formatting to an attendance list to emphasize who was absent. The impression underneath is the information established I’ll use to operate as a result of this rationalization:

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

1. Initial, choose the column or row you want to implement conditional formatting to. In this case, we will choose 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 proven in the graphic underneath.

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

3. When the Conditional Formatting drop-down menu appears, pick Highlight Cells Principles, then Equivalent To.

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

4. In the New Formatting dialog box, transform Cell Value to Particular Textual content. Then, kind “Absent” in the textual content box. Reference the picture below:

The New Formatting Rule dialog box

5. From the New Formatting dialog box, we can also pick out how we want to structure the cells that contains the term “Absent.” Test out the options down below.

The "Format With" drop-down menu.

For this illustration, let’s stick with the default option (Light-weight Pink Fill with Darkish Crimson Textual content).

6. Click on Okay. Now — thanks to conditional formatting — we can promptly detect which pupils were being absent.

Conditional Formatting highlighting who was absent in an attendance sheet.

In the future section, we will address how to apply conditional formatting centered on one more cell in the spreadsheet.

Conditional Formatting Dependent on One more Mobile

In this illustration, the intention is to spotlight the cells that match the fall-down menu in mobile E1. The impression down below is the sample facts set I’ll use for this explanation:

A spreadsheet that uses Conditional Formatting based on another cell.

1. Very first, pick out column B.

Selecting Column B to apply conditional formatting to.

2. Navigate to the header toolbar and decide on Conditional Formatting. When the Conditional Formatting fall-down menu appears, choose Spotlight Cells Rules, then Equal To.

A screenshot of the Conditional Formatting drop-down menu.

3. In the New Formatting dialog box, pick Cell Price and Equal To.

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

The New Formatting Rule dialog box.

4. As you can see in the picture earlier mentioned, we also altered the formatting to Yellow Fill with Darkish Yellow Textual content. Even so, you can change this selection to your desire. Click on Ok.

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

  • When the status is Existing:

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

  • When the position is Absent:

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

How to Edit Conditional Formatting

Here is some fantastic news — conditional formatting is not established in stone, this means you can edit or delete it afterwards. Right here are the methods to do that:

1. Begin by deciding upon the mobile (or cell range) 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 Control Rules.

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

3. The Handle Policies dialog box will listing the latest regulations for your assortment. Decide on the rule you want to edit and click on Edit Rule.

Screenshot of the Manage Rules dialog box.

How to Copy Conditional Formatting in Excel

You can effortlessly copy a conditional formatting rule to another cell to (or selection of cells) by using one particular of the adhering to methods.

1. Straightforward copy/paste.

The 1st method is fairly simple. Start off by choosing the mobile you want to duplicate and strike the Copy button in the header toolbar — or click Regulate-C (or Command-C on a Mac).

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

2. Structure Painter

The next method makes use of the device Format Painter, which is positioned in the header toolbar. Verify out the image below:

Screenshot of the Format Painter tool in the header toolbar.

To begin, click on on the cell you want to duplicate, then click Format Painter. Your mouse icon will alter to a paintbrush. Then, drag the paintbrush to the cell (or range of cells) exactly where you want to paste the format. Last of all, to quit applying the paintbrush, press Esc on your keyboard.

Conditional formatting is a impressive way to visualize the data in your spreadsheet. With just a several clicks, you can emphasize vital tendencies or designs you might have normally missed. With the guidelines in this article, you are going to be capable to use Conditional Formatting to its fullest extent.

excel marketing templates

Leave a Reply

Your email address will not be published.