Spreadsheets normally maintain a wealth of knowledge, however it may be tough to see what’s vital in them, comparable to key knowledge factors and developments. There are a number of methods to make knowledge stand out visually in an Excel spreadsheet, however one of many easiest and handiest is to use conditional formatting.What is conditional formatting in Excel?Conditional formatting is a characteristic used to make distinctive, vital, or duplicate values stand out or to emphasise developments in a knowledge set. As the identify suggests, the characteristic means that you can format the cells and their knowledge based mostly on circumstances you specify, which makes vital data simple to see at a look.Some widespread examples:
Formatting cells that meet particular standards with a spotlight coloration and/or font
Applying totally different colours to totally different cells based mostly on their values
Applying coloration scales or gradients to visually symbolize cells’ values
Crucially, conditional formatting is dynamic, so it retains up when your knowledge set modifications. For occasion, if you happen to create a conditional formatting rule that highlights cells with values over 100, and one of many cells in your set modifications from 95 to 102, that cell will turn into highlighted.In this text we’ll present you alternative ways to use conditional formatting to your knowledge units. When utilizing conditional formatting, the primary choice you’ve got is to make use of preset circumstances — formatting choices which might be constructed into Excel. We’ll go over a few of the finest preset circumstances for formatting, after which cowl the right way to create your personal customized formatting guidelines. Shimon Brathwaite / IDG
Excel provides a number of built-in conditional formatting guidelines you possibly can apply to your knowledge. (Click picture to enlarge it.)
We’ll exhibit utilizing Excel for Windows underneath a Microsoft 365 subscription. If you’re utilizing a special model of Excel, you may not have the identical interface and choices, however the options ought to work roughly the identical method. Before we start: How to clear conditional formattingThroughout this story, we’ll use the identical instance knowledge set, making use of one kind of formatting, then clearing it away earlier than making use of the following kind of formatting. So earlier than we start, we’ll rapidly go over the right way to clear conditional formatting from a knowledge set.Simply navigate to the Excel Ribbon’s Home tab. Click the Conditional Formatting button after which choose Clear Rules. There you possibly can decide to clear conditional formatting from the chosen cells, the complete worksheet, the present desk, or the present PivotTable. Shimon Brathwaite / IDG
It’s simple to take away conditional formatting guidelines.
How to make use of spotlight cells guidelinesWith that out of the best way, we’re going to start with spotlight cells guidelines. These may be discovered on the Home tab underneath Conditional Formatting > Highlight Cells Rules. Shimon Brathwaite / IDG
The spotlight cells guidelines choices.
Here you possibly can see a number of choices for highlighting cells based mostly on their values, together with higher or lower than a sure worth, equal to a sure worth, between sure values, containing sure textual content, with a sure date, with duplicate values, and extra.We’ll use the Greater Than choice for example. This will spotlight any cells which might be higher than a specified quantity inside the knowledge set. For instance, you possibly can select to focus on all cells which might be higher than the quantity 200.Simply choose the set of cells you wish to format, then click on on Home > Conditional Formatting > Highlight Cells Rules > Greater Than to open the next dialog field. Shimon Brathwaite / IDG
Filling out the circumstances for a Greater Than formatting rule.
On the left, choose the quantity to filter by. In this case we’re going to spotlight any quantity that’s higher than 200. On the best, choose the kind of highlighting to use or create a customized format.As you possibly can see from the screenshot under, the formatting is finished in actual time, so you possibly can see the results of your selection earlier than you commit. To finalize your selection, click on OK. Shimon Brathwaite / IDG
When you make a choice, the formatting is immediately utilized to your knowledge. (Click picture to enlarge it.)
How to make use of high/backside guidelinesHighlight cells guidelines are good when you’ve got a particular worth in thoughts, comparable to numbers higher than 100, cells containing particular phrases, and so forth. But what occurs if you happen to want a relative measure — for instance, you wish to know the very best 10% of values in a knowledge set? In that case, it’s finest to make use of the Top/Bottom Rules choice.This choice consists of presets for the highest or backside 10 objects within the knowledge set, objects within the high or backside 10%, objects above or under the typical of the info set, and extra. The high/backside presets may be adjusted to indicate a special proportion or variety of objects. Let’s see how these guidelines work utilizing the Top 10% choice. Once you’ve got cleared the spotlight cells guidelines from the info set, choose the cells you wish to apply the rule to, go to Home > Conditional Formatting > Top/Bottom Rules and choose Top 10%. Shimon Brathwaite / IDG
Choosing a high/backside guidelines preset.
Once you choose that choice, you’ll get a dialog field the place you possibly can modify the precise high proportion you wish to spotlight and select the model of the formatting. Shimon Brathwaite / IDG
Adjusting Top 10% circumstances.
Once you’ve got made your picks, hit OK to verify the formatting.Note which you could apply a couple of set of conditional formatting guidelines to a knowledge set — so you could possibly, as an example, spotlight the highest 5 values in crimson and the underside 5 values in yellow. Shimon Brathwaite / IDG
Our knowledge set with the highest 5 and backside 5 values highlighted in numerous colours. (Click picture to enlarge it.)
How to make use of knowledge barsData bars present a visible illustration of how a particular knowledge level pertains to the utmost worth of the info set. For instance, if the biggest worth within the dataset is 1,000, then a cell with a price of 500 would have a bar that’s precisely 50% of the total width of the cell. This means that you can inform at a look how massive a price is in comparison with the utmost worth. Shimon Brathwaite / IDG
Applying knowledge bars formatting to a knowledge set. (Click picture to enlarge it.)
To arrange knowledge bars formatting, choose the cells you wish to apply the rule to, go to Home > Conditional Formatting > Data Bars, and select one of many kinds underneath Gradient Fill or Solid Fill.How to make use of coloration scalesThe coloration scales choice highlights cells with totally different shades of coloration relying on how shut they’re to the info set’s most or minimal worth. With the goal cells chosen, select Home > Conditional Formatting > Color Scales. Shimon Brathwaite / IDG
Applying coloration scales formatting to a knowledge set. (Click picture to enlarge it.)
You can choose any of the preset coloration scales supplied, or you possibly can choose More Rules to be able to change colours or add extra colours for extra personalized knowledge evaluation. For instance, if you happen to select a three-color scale, you possibly can choose any percentile you wish to be represented by the third worth. In the instance proven right here, I chosen the 50th percentile. Shimon Brathwaite / IDG
Creating a customized coloration scale.
The closing result’s proven under. Shimon Brathwaite / IDG
Our customized coloration scale in motion. (Click picture to enlarge it.)
How to make use of icon setsFinally, there are icon units, which let you add icons comparable to arrows, shapes, flags, or stars to the cells that meet a predefined criterion. You can entry this feature through Home > Conditional Formatting > Icon Sets.Select any of the icon units proven, and it’ll rank every of the cells based mostly on how shut it’s to the minimal and most quantity within the knowledge set. For the instance under, I chosen the star icon set. The high worth will get a yellow star and the midpoint a half-yellow star. Because all the opposite cells are within the backside third by worth, they get white stars. Shimon Brathwaite / IDG
Applying an icon set to the chosen cells. (Click picture to enlarge it.)
You may use the More Rules choice on the very backside of the icon units menu to customise the formatting to your liking. Shimon Brathwaite / IDG
Adjusting the worth ranges for every icon.
How to create customized formatting guidelinesIf none of those guidelines be just right for you, chances are you’ll wish to create absolutely customized guidelines from scratch. To achieve this, choose the goal cells, go to Home > Conditional Formatting > New Rule, after which choose the suitable rule kind. In this case we are going to select the second-to-last choice, Format solely distinctive or duplicate values, after which underneath “Format all:” within the “Edit the Rule Description” space, choose duplicate. Shimon Brathwaite / IDG
When making a customized formatting rule, first choose the rule kind, then edit its description under. (Click picture to enlarge it.)
Once that’s executed, click on the Format button. On the pane that seems, you possibly can select the way you wish to format the cells, together with altering quantity structure, altering the font, altering the border, or filling the cell with a sure coloration. In this case we are going to choose the Fill tab and select the crimson fill coloration. Shimon Brathwaite / IDG
Choosing the styling for a customized formatting rule. (Click picture to enlarge it.)
With the model choice made, click on OK, and this model seems within the Preview space of the New Formatting Rule pane. Once you click on OK or hit Enter, the cells with duplicate values are highlighted. Shimon Brathwaite / IDG
Only the cells with duplicate values are coloured crimson.
Managing your conditional formatting guidelinesAs talked about beforehand, you possibly can apply a couple of conditional formatting rule to the identical set of cells, and you may as well have totally different formatting guidelines for various units of cells in the identical worksheet. To handle all these guidelines, go to Home > Conditional Formatting > Manage Rules. You’ll see a dialog field together with your guidelines listed. Shimon Brathwaite / IDG
You can handle your guidelines within the Conditional Formatting Rules Manager. (Click picture to enlarge it.)
To delete a rule, merely choose it and click on the Delete Rule button.You may create a brand new rule from this interface. Click the New Rule button, and also you’ll be taken to the identical New Formatting Rule dialog field coated within the earlier part of the story.To edit an present rule, choose it within the record and click on the Edit Rule button. You’ll see the Edit Formatting Rule dialog field, the place you possibly can edit the formatting specifics of the at present chosen rule. You may change the cells the rule applies to by clicking the up arrow icon to the best of its “Applies to” field.Before you edit a rule, it’s not a nasty concept to create a duplicate of it. That method you’ll have a backup of the unique rule which you could revert to if wanted. To make a duplicate, choose the rule and click on Duplicate Rule.A reproduction additionally turns out to be useful if you wish to apply a rule to a special set of cells. You can duplicate the rule, then change the “Applies to” choice for the duplicate.
Copyright © 2023 IDG Communications, Inc.