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.
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.
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:
1. Initial, choose the column or row you want to implement conditional formatting to. In this case, we will choose column B.
2. To highlight who was absent, navigate to the header toolbar and select Conditional Formatting, as proven in the graphic underneath.
3. When the Conditional Formatting drop-down menu appears, pick Highlight Cells Principles, then Equivalent To.
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:
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.
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.
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:
1. Very first, pick out column B.
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.
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.
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:
- When the position is 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.
2. Navigate to the header toolbar and choose Conditional Formatting, then Control Rules.
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.
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:
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.