Applying What Formatting Option To Your Excel Workbook Will Make It Easier To Read When Printed Out?

Lesson 24: Conditional Formatting

/en/excel2016/charts/content/

Introduction

Let's say you lot have a worksheet with thousands of rows of data. Information technology would be extremely difficult to see patterns and trends only from examining the raw data. Like to charts and sparklines, conditional formatting provides another way to visualize data and make worksheets easier to understand.

Optional: Download our practice workbook.

Watch the video below to learn more almost provisional formatting in Excel.

Understanding conditional formatting

Provisional formatting allows you to automatically apply formatting—such as colors, icons, and data bars—to one or more cells based on the jail cell value. To do this, you'll need to create a conditional formatting rule. For instance, a conditional formatting rule might exist: If the value is less than $2000, color the cell crimson. By applying this rule, you'd be able to rapidly see which cells contain values less than $2000.

Conditional Formatting in Excel

To create a conditional formatting rule:

In our example, we have a worksheet containing sales data, and we'd similar to come across which salespeople are coming together their monthly sales goals. The sales goal is $4000 per month, so we'll create a conditional formatting rule for whatsoever cells containing a value higher than 4000.

Select the desired cells for the conditional formatting dominion.
From the Home tab, click the Conditional Formatting command. A driblet-down menu volition appear.
Hover the mouse over the desired provisional formatting type, then select the desired rule from the bill of fare that appears. In our instance, we want to highlight cells that are greater than $4000.
A dialog box will appear. Enter the desired value(southward) into the blank field. In our example, we'll enter 4000 as our value.
Select a formatting mode from the driblet-downwardly menu. In our example, nosotros'll choose Dark-green Fill up with Dark Light-green Text, then click OK.
The conditional formatting will be practical to the selected cells. In our example, it'due south easy to meet which salespeople reached the $4000 sales goal for each calendar month.

You can apply multiple conditional formatting rules to a jail cell range or worksheet, assuasive you to visualize different trends and patterns in your information.

Multiple conditional formatting rules on the same area of a worksheet

Conditional formatting presets

Excel has several predefined styles—or presets—you can use to chop-chop apply conditional formatting to your data. They are grouped into three categories:

Data Confined are horizontal bars added to each cell, much like a bar graph.
Color Scales change the colour of each cell based on its value. Each colour calibration uses a two- or three-color gradient. For example, in the Green-Xanthous-Red colour scale, the highest values are green, the average values are xanthous, and the everyman values are cerise.
Icon Sets add a specific icon to each prison cell based on its value.

To use preset provisional formatting:

Select the desired cells for the provisional formatting dominion.
Click the Conditional Formatting control. A driblet-downward carte will appear.
Hover the mouse over the desired preset, and so choose a preset style from the menu that appears.
The conditional formatting will be applied to the selected cells.

Removing conditional formatting

To remove conditional formatting:

Click the Provisional Formatting command. A drop-downward card will appear.
Hover the mouse over Clear Rules, and cull which rules you want to clear. In our case, we'll select Articulate Rules from Unabridged Sheet to remove all conditional formatting from the worksheet.
The conditional formatting will exist removed.

Click Manage Rules to edit or delete individual rules. This is especially useful if yous've practical multiple rules to a worksheet.

The Conditional Formatting Rules Manager

Challenge!

Open our exercise workbook.
Click the Challenge worksheet tab in the bottom-left of the workbook.
Select cells B3:J17.
Permit's say yous're the instructor and want to easily run into all of the grades that are below passing. Employ Conditional Formatting then it Highlights Cells containing values Less Than 70 with a lite red fill.
Now you want to see how the grades compare to each other. Under the Provisional Formatting tab, select the Icon Set called three Symbols (Circled). Hint: The names of the icon sets will appear when you hover over them.
Your spreadsheet should look like this:
Using the Manage Rules feature, remove the lite red fill, merely keep the icon set up.