How one can Use Conditional Formatting in Microsoft Excel
You don’t need to manually format your information with colours and kinds to make it stand out. With conditional formatting in Microsoft Excel, you possibly can routinely format your dataset primarily based on situations. Plus, Excel offers you 5 preset conditional formatting choices to make this process a simple one.
Observe: take into account with all of the conditional formatting guidelines beneath that if you happen to change the cell information, and it now not meets the factors, the formatting might be eliminated.
Spotlight Cell Guidelines
Utilizing guidelines to spotlight cells primarily based on a worth, textual content, or date might be the preferred sort of conditional formatting in Excel. With this feature, you are able to do issues like spotlight cells with values which might be higher than or lower than one other, textual content that incorporates one thing particularly, a date occurring in a sure time-frame, or duplicate values.
Spotlight Values
In the event you’re utilizing values like numbers, currencies, or percentages in your sheet, you possibly can spotlight values as they relate to others. You would format gross sales higher than or stock lower than a given quantity.
- Choose the cells you need to check, go to the “House” tab, open the “Conditional Formatting” drop-down menu, choose “Spotlight Cell Guidelines,” then select the situation you want to use.
- Enter the comparability worth within the area on the left of the pop-up field. Alternatively, enter a cell reference containing that worth. For example, we’re highlighting cells in our chosen vary which might be higher than “3,000.”
- Select the highlighting to use within the drop-down listing to the correct. We’re selecting “Mild Crimson Fill.”
- Click on “OK” to use and save the formatting.
Spotlight Textual content
Maybe it’s specific textual content in your cells that you just need to spotlight. You should utilize a phrase or perhaps a single letter in your situation. You would format names of workers, cities or states, or letter grades.
- Choose the cells you need to verify, go to the “House” tab, open the “Conditional Formatting” drop-down menu, choose “Spotlight Cell Guidelines,” and select “Textual content That Comprises.”
- Enter the textual content within the area on the left of the pop-up field. For instance, we’re highlighting cells that comprise the letter grade “A.”
- Select the highlighting within the listing on the correct. We’re selecting “Inexperienced Fill with Darkish Inexperienced Textual content.”
- Click on “OK” to use your formatting.
Spotlight Dates
One other approach to spotlight values is utilizing dates. You would select a selected colour for due dates for upcoming payments or duties with dates from the previous.
- Choose the cells you need to verify, go to the “House” tab, open the “Conditional Formatting” drop-down menu, choose “Spotlight Cell Guidelines,” and select “A Date Occurring.”
- Select a time-frame within the pop-up field. For example, we’re selecting “This Month.”
- Select the highlighting from the listing on the correct. We’re deciding on “Yellow Fill with Darkish Yellow Textual content.”
- Click on “OK” to avoid wasting the formatting.
Tip: if you happen to’re thinking about highlighting duplicate values in your sheet utilizing that conditional formatting possibility, take a look at our full how-to for locating and eradicating duplicates in Excel.
Apply High or Backside Guidelines
If you wish to spotlight issues like prime gross sales, backside check scores, or above common quarterly earnings, you need to use the High or Backside Guidelines conditional formatting possibility.
Spotlight a High Share
Maybe you’re searching for the highest numbers from a gaggle of cells. You may spotlight a prime proportion and alter that proportion if you need.
- Choose the cells you need to check, go to the “House” tab, open the “Conditional Formatting” drop-down menu, transfer to “High/Backside Guidelines,” and select “High 10%.”
- Go away the quantity “10” within the area on the left of the pop-up field, or alter that proportion by getting into a quantity or utilizing the arrows. For instance, we’re altering it to view the highest “20” p.c.
- Select the formatting you need to apply within the listing on the correct. We’re selecting “Crimson Border.”
- Click on “OK” to avoid wasting and apply the formatting after seeing a preview.
Spotlight Under Common
You too can spotlight the quantities beneath common.
- Choose the cells, go to the “House” tab, open the “Conditional Formatting” drop-down menu, transfer to “High/Backside Guidelines,” and select “Under Common.”
- Select the formatting you need to apply, then click on “OK.” We’re selecting “Customized Format,” then deciding on “Daring Italic” within the subsequent window.
- Choose “OK” to avoid wasting the formatting you see within the preview.
Tip: do you know you can additionally use conditional formatting in Microsoft Outlook to make sure emails pop? Check out our how-to for setting this up.
Use Information Bars
One other helpful conditional formatting in Excel is Information Bars. You may apply coloured bars to your cells, the place the size of every bar represents the cell’s worth. This presents a fast view of excessive and low numbers, like a metropolis’s inhabitants, product stock, or income by location.
- Choose the cells you need to format, go to the “House” tab, open the “Conditional Formatting” drop-down menu, and choose “Information Bars” to see gradient and stable fill choices.
- As you hover your cursor over every Information Bar possibility, you’ll see a preview in your chosen cells.
- Click on the color and style you need to use.
Observe: as an alternative of information bars, you might use Excel’s sparklines to identify a pattern in your information. Sparklines are single cell mini charts that visualize a set vary of information.
Add Coloration Scales
Perhaps you just like the splash of colour for rapidly figuring out values, however choose completely different colours to symbolize the values. With Coloration Scales, the colours change based on how excessive or low the values are.
Any such formatting is used with warmth maps exhibiting temperatures, populations, or revenue by area, however you need to use it simply as simply for gross sales, income, or bills.
- Choose the cells you need to format, go to the “House” tab, open the “Conditional Formatting” drop-down menu, and choose “Coloration Scales” to see the colour choices.
- Hover your cursor over every Coloration Scale to see a preview, then make your choice. You should utilize a two- or three-color scale, relying on the variety of gadgets you’re evaluating.
The primary colour within the scale represents the very best worth within the group. Giant teams of things will present completely different shades of the colours because the variations between the values. For example, we’re deciding on the “Crimson Yellow Inexperienced” possibility.
The best values are in purple, the center values in yellow, and the bottom values in inexperienced, with the values in between in several shades of these colours.
Tip: if you happen to’re struggling to get the correct outcomes as a result of your spreadsheet is a multitude, attempt cleansing up your information first.
Insert Icon Units
One other conditional formatting possibility you’ll see in Excel is the Icon Units. Any such formatting locations icons subsequent to the cells in your group, primarily based on their values.
The gathering of icons you employ is as much as you, however Excel presents some help by categorizing them as Directional, Shapes, Indicators, and Rankings. Moreover, you’ll discover that some units comprise completely different numbers of icons, which can also be helpful in deciding on a set.
Choose the cells you need to use, go to the “House” tab, open the “Conditional Formatting” drop-down menu, choose “Icon Units,” then choose the icon set you prefer to.
For example, we now have a spread of scores for our cities, so we’re deciding on one of many “Rankings” units. Since our scores are on a scale of 1 to 3, the “3 Stars” set is right.
In one other instance, we now have the variety of members in every of our cities and need to rapidly spot the very best and lowest. We’re selecting the “Directional” set and the “3 Triangles” choice to see the very best numbers with a inexperienced up-pointing arrow, the center numbers with a yellow rectangle, and the bottom numbers with a purple down-pointing arrow.
How one can Edit a Conditional Formatting Rule
When you apply conditional formatting to your Excel sheet, it’s possible you’ll need to make a change. Whether or not you’re making a change to the cell vary or the formatting itself, you possibly can edit an current rule simply.
- Go to the “House” tab, open the “Conditional Formatting” menu, and choose “Handle Guidelines.”
- Use the drop-down field on the prime to decide on the principles you need to see. You may view them for chosen cells, the present worksheet, or a selected sheet within the workbook.
- In the event you solely need to alter the cell vary, accomplish that within the corresponding area beneath “Applies to.” In any other case, click on “Edit Rule” on the prime.
- Make your adjustments within the “Edit Formatting Rule” field that opens, then click on “OK,” In our instance, we’re altering the Icon Model.
- Click on “OK,” then “Apply” to avoid wasting your adjustments.
- Your sheet will replace along with your edits.
How one can Delete a Conditional Formatting Rule
Do you have to determine you now not desire a conditional formatting rule you’ve arrange, you possibly can delete it certainly one of two methods.
Use the Conditional Formatting Guidelines Supervisor
You may take away a rule within the Conditional Formatting Guidelines Supervisor with the clicking of a button.
- Go to the “House” tab, open the “Conditional Formatting” menu, and choose “Handle Guidelines.”
- Choose the rule, choose “Delete Rule” on the prime, then click on “OK” and “Apply.”
Use the Clear Guidelines Choice
One other approach to take away a conditional formatting rule is utilizing the Clear Guidelines possibility.
- Go to the “House” tab, open the “Conditional Formatting” drop-down menu, transfer to “Clear Guidelines,” and select to clear the principles from chosen cells, your complete sheet, a desk, or a pivot desk. Observe that if you happen to don’t have a rule arrange for a selected motion, it’ll be grayed out.
- Your rule might be eliminated.
Steadily Requested Questions
Can I’ve two conditional formatting guidelines for a similar cells?
You may apply varied conditional formatting guidelines to the identical set of cells if you need. For example, it’s possible you’ll need to spotlight dates for final month in purple textual content and dates for subsequent month in inexperienced textual content. Observe the identical steps you see right here so as to add extra guidelines to your chosen cells, however watch out to not make your guidelines battle with one another.
If it’s potential that every one guidelines can apply, but it surely might trigger a formatting downside, choose the primary rule for the cell vary within the Conditional Formatting Guidelines Supervisor, then verify the field for “Cease If True.” This stops the remaining guidelines from operating, ought to additionally they apply.
Can I create my very own conditional formatting rule?
Together with the preset conditional formatting guidelines proven right here, you possibly can arrange your individual. Choose the cells you need to use, go to the “House” tab, and click on “Conditional Formatting -> New Rule.” Select a rule sort on the prime, arrange the remainder of the rule within the part on the backside, then click on “OK” to put it aside.
Can I exploit conditional formatting to spotlight a complete row or column?
You may arrange a conditional formatting rule past a selected vary of cells. So, sure, you possibly can spotlight a complete row or column primarily based on situations. Choose the row or column, then arrange your rule as described above. Take into account that you probably have empty cells, it’s possible you’ll not see formatting primarily based on worth, textual content, or date.
Picture credit score: Pixabay. All screenshots by Sandy Writtenhouse.
