Excel - Use formulas to apply conditional formatting Tutorial

In this video, you will learn how to use formulas to apply conditional formatting in Microsoft 365. The video demonstrates selecting cells in the Product column and using a formula to format them based on the values in the In stock column.
By creating rules with different formulas, you can apply different fill colors to cells based on their values.
This tutorial will help you enhance your formatting skills and make your data more visually appealing.

4:20
4685 views

Description

Objectifs :

Learn how to apply conditional formatting in Excel using formulas to control the formatting of specific cells based on their values.

Chapitres :

Introduction to Conditional Formatting
Conditional formatting in Excel allows users to format cells based on specific criteria. This feature enhances data visualization by applying different formats to cells that meet certain conditions.
Selecting Cells for Formatting
To begin, select the range of cells you wish to format. The first cell selected becomes the active cell, which is crucial for applying formulas correctly. In this example, cell B2 is the active cell.
Creating a New Conditional Formatting Rule
1. Click on 'Create a New Rule'. 2. Choose 'Use a formula to determine which cells to format'. 3. Since B2 is the active cell, enter the formula 'E2 > 300'. This relative cell reference ensures that the formula adjusts for other cells in column B.
Formatting the Cells
After entering the formula, click the 'Format' button to choose the desired formatting style. For this example, a blue fill is selected. Click 'OK' to accept the color and again to apply the format. Cells in the Product column will now be formatted if the corresponding cell in column E is greater than 300.
Dynamic Updates with Conditional Formatting
The beauty of conditional formatting is its dynamic nature. If a value in column E changes to greater than 300, the formatting in the Product column updates automatically.
Creating Multiple Rules
You can create multiple conditional formatting rules for the same cells. For instance, to apply different fill colors based on score ranges: - For values greater than or equal to 90, use the formula 'B2 >= 90' and apply a green fill. - For values between 80 and 90, use 'AND(B2 >= 80, B2 < 90)' and select a different fill color. - Similar rules can be created for scores of 70 and 60, with the last rule for values less than 60.
Visualizing Data with Conditional Formatting
After applying these rules, the cells will display a variety of colors, creating a visual representation of the data. This 'rainbow' of colors helps quickly identify different score ranges.
Conclusion
Conditional formatting is a powerful tool in Excel that enhances data analysis and visualization. By using formulas to set conditions, users can create dynamic and visually appealing spreadsheets that convey information effectively.

FAQ :

What is conditional formatting in spreadsheets?

Conditional formatting is a feature that allows users to change the appearance of cells based on specific criteria. For example, you can highlight cells that meet certain conditions, such as values greater than a specified number.

How do I create a conditional formatting rule?

To create a conditional formatting rule, select the range of cells you want to format, go to the conditional formatting menu, choose 'New Rule', and then select 'Use a formula to determine which cells to format'. Enter your formula and choose the desired formatting options.

Can I apply multiple conditional formatting rules to the same cells?

Yes, you can apply multiple conditional formatting rules to the same cells. Each rule can have different conditions and formatting styles, allowing for a variety of visual cues based on the data.

What happens if I change the value in a cell that has conditional formatting?

If you change the value in a cell that is part of a conditional formatting rule, the formatting will automatically update based on the new value. For example, if a cell's value changes to meet the criteria of a rule, the specified formatting will be applied.

What is the difference between absolute and relative cell references?

Relative cell references change when a formula is copied to another cell, while absolute cell references remain constant. For example, E2 is a relative reference, but $E$2 is an absolute reference.

Quelques cas d'usages :

Sales Performance Tracking

In a sales report, conditional formatting can be used to highlight sales figures that exceed targets. For instance, cells in the sales column can be formatted to turn green if sales are above $10,000, helping managers quickly identify high performers.

Student Grade Analysis

Teachers can use conditional formatting to visualize student grades. For example, grades above 90 can be highlighted in green, while grades below 60 can be marked in red. This allows educators to easily identify students who may need additional support.

Inventory Management

In inventory tracking, conditional formatting can help highlight stock levels. For example, items with stock levels below a certain threshold can be highlighted in red, alerting managers to reorder supplies before they run out.

Project Deadline Monitoring

Project managers can apply conditional formatting to track deadlines. Cells representing project completion dates can be formatted to turn yellow if they are approaching the deadline, and red if they are overdue, ensuring timely project management.

Budget Tracking

In financial spreadsheets, conditional formatting can be used to monitor budget adherence. Cells showing expenses can be formatted to turn orange if they exceed budgeted amounts, helping teams stay within financial limits.

Glossaire :

Conditional Formatting

A feature in spreadsheet applications that allows users to apply specific formatting to cells based on certain conditions or criteria.

Active Cell

The currently selected cell in a spreadsheet, which is highlighted and where data can be entered or formatted.

Relative Cell Reference

A cell reference that adjusts automatically when the formula is copied to another cell. For example, if you use E2 in a formula, it will change to E3 if copied to the next row.

Formula

An expression that calculates the value of a cell. Formulas can include numbers, operators, and cell references.

Fill Color

The background color applied to a cell or range of cells in a spreadsheet to enhance visibility or indicate specific conditions.

AND Function

A logical function in spreadsheets that returns TRUE if all the conditions specified are true. For example, AND(B2>80, B2<90) checks if the value in B2 is greater than 80 and less than 90.

Available in Microsoft365

00:00:07

you can use formulas to apply conditional formatting.

00:00:11

In this example I'm going to format the cells in the Product column,

00:00:16

If the corresponding cell in In stock column

00:00:19

is greater than 300.

00:00:23

I select the cells I want to conditionally format.

00:00:27

When you select a range of cells,

00:00:30

the first cell you select is the active cell.

00:00:33

In this example I selected from B2 through B10,

00:00:39

so B2 is the active cell,

00:00:42

we'll need to know that shortly.

00:00:47

Create a New rule; select Use a formula to determine which cells to format.

00:00:54

Since B2 is the active cell, I type =E2>300.

00:01:07

Note that in the formula I used the relative cell

00:01:10

reference E2 to make sure the formula adjusts

00:01:14

to correctly format the other cells in column B.

00:01:19

I click the Format button and choose how I want to format the cells.

00:01:24

I'm going to use a blue fill;

00:01:27

click OK to accept the color, click OK again to apply the format.

00:01:33

And the cells in the Product column,

00:01:36

where the corresponding cell in column E is greater than 300

00:01:41

are conditionally formatted.

00:01:44

When I change a value in column E to greater than 300,

00:01:49

the conditional formatting in the Product column automatically applies.

00:01:57

You can create multiple rules that apply to the same cells.

00:02:02

In this example I want different fill colors

00:02:06

for different ranges of scores.

00:02:10

I select the cells I want to apply a rule to;

00:02:14

create a new rule that uses the rule type

00:02:19

use of formula to determine which cells to format.

00:02:25

I want to format a cell if its value is greater than, or equal to 90.

00:02:31

The active cell is B2, so I enter the formula =B2>=90,

00:02:45

and configure the rule to apply a green fill

00:02:48

when the formula is true for a cell,

00:02:51

and the cell that has a value greater than, or equal to, 90 is filled with green.

00:02:58

I created another rule for the same cells, but this time

00:03:02

I want to format a cell if its value is greater than, or

00:03:05

equal to 80, and less than 90.

00:03:09

The formula is =AND(B2>=80;B2<90)

00:03:34

and I choose a different fill color.

00:03:43

I create similar rules for 70 and 60.

00:03:47

The last rule is for values less than 60.

00:04:00

The cells are now a rainbow of colors,

00:04:07

and these are the rules we just created to enable this.

00:04:13

Up next: Manage conditional formatting.

Mandarine AI: WHAT YOU SHOULD KNOW

RELATED COURSES This tutorial is part of the following courses

E-learning Intermédiaire

Vu 2822 fois

Video Training Microsoft Excel - Advanced level

With our "advanced" tutorials, discover and master the full potential of Excel, using complex functions, macros and the power of DCTs and Power Pivot, combined with the Power Query tool.

ALL COURSES