Excel - Use conditional formatting Tutorial

In this video, you will learn about using conditional formatting in Microsoft 365. Conditional formatting provides visual cues to help you make sense of your data by highlighting highs, lows, and other data trends based on specified criteria.
You can apply conditional formatting to specific cells, tables, pivot tables, and worksheets.
The video demonstrates how to quickly apply conditional formatting using the formatting button in the ribbon, as well as how to have more control over the formatting by creating new rules.
You can format cells based on their values, such as using icon sets, and even format cells that are blank or contain errors.
This tutorial will help you enhance your data analysis skills and make your data more visually appealing.

2:02
3927 views

Available in Microsoft365

Objectifs :

Understand how to use conditional formatting in spreadsheets to visually analyze data trends, sales performance, and text attributes effectively.

Chapitres :

Introduction to Conditional Formatting
Conditional formatting is a powerful tool that provides visual cues to help users interpret their data more effectively. By applying specific criteria, users can highlight highs, lows, and other trends in their datasets, making it easier to draw insights.
Analyzing Sales Data
To analyze sales data, start by selecting the range of cells that contain the sales figures. Click on the 'Quick Analysis' button, which will present various options for data visualization. Under the 'Formatting' tab, you can explore different formatting styles. As you hover over each option, a live preview will display how your data will appear with that formatting.
Using Data Bars for Visualization
One effective way to visualize sales data is by using 'Data Bars.' When you select this option, it creates an eye-catching representation of the data, allowing for quick analysis of performance across different sales figures. This visual aid helps in identifying trends and comparing sales among different salespersons.
Applying Multiple Conditional Formats
You can enhance your data analysis by applying multiple conditional formats to the same cells. For instance, you can add an 'Icon Set' to indicate performance levels. By default, these icons represent the upper third, middle third, and lower third of the values, providing a quick visual reference for performance distribution.
Conditional Formatting for Text
Conditional formatting is not limited to numerical data; it can also be applied to text. The formatting options for text differ from those for numerical values. For example, if you want to highlight cells containing the word 'oil,' you can select 'Text Contains' and enter 'oil.' By keeping the formatting set to default, any cell containing 'oil' will be highlighted in red, making it stand out.
Dynamic Formatting Changes
An important feature of conditional formatting is its dynamic nature. If you change the text in a cell to include 'oil,' the formatting will automatically update to reflect this change. This ensures that your visual cues remain accurate and relevant as your data evolves.
Conditional Formatting for Dates
Next, we will explore how to apply conditional formatting to dates. This feature allows users to highlight specific date ranges or trends over time, further enhancing data analysis capabilities.

FAQ :

What is conditional formatting in spreadsheets?

Conditional formatting is a tool that allows users to apply specific formatting styles to cells based on the values they contain, making it easier to identify trends and important data points.

How do I apply data bars in Excel?

To apply data bars, select the range of cells you want to format, click the Quick Analysis button, navigate to the Formatting tab, and choose Data Bars. A live preview will show how your data will look.

Can I apply multiple conditional formats to the same cells?

Yes, you can apply multiple conditional formats to the same cells. For example, you can use both data bars and icon sets to represent different aspects of the data.

What happens if I change the text in a cell with conditional formatting?

If you change the text in a cell that has conditional formatting applied, the formatting will automatically update to reflect the new content based on the defined rules.

Are formatting options the same for all data types?

No, formatting options differ between various data types. For instance, the formatting options for text may be different from those for numbers or dates.

Quelques cas d'usages :

Sales Performance Analysis

A sales manager can use conditional formatting to quickly visualize the performance of each salesperson by applying data bars to their sales figures, allowing for immediate identification of top performers and those needing improvement.

Inventory Management

In inventory management, a user can apply conditional formatting to highlight low stock items by using icon sets, making it easier to prioritize restocking efforts.

Project Deadline Tracking

A project manager can use conditional formatting to track deadlines by applying different colors to tasks based on their due dates, helping to quickly identify overdue tasks.

Financial Reporting

In financial reports, conditional formatting can be used to highlight significant variances in budget versus actual spending, allowing for quick analysis and decision-making.

Customer Feedback Analysis

A customer service team can apply conditional formatting to survey results, using text contains rules to highlight negative feedback, enabling them to address issues promptly.

Glossaire :

Conditional Formatting

A feature in spreadsheet applications that allows users to apply specific formatting to cells based on certain criteria, helping to visually highlight important data trends.

Data Bars

A type of conditional formatting that visually represents the value of a cell relative to others in the selected range, using horizontal bars to indicate magnitude.

Icon Set

A conditional formatting option that uses icons to represent the relative value of cells, typically indicating categories such as upper third, middle third, and lower third.

Text Contains

A conditional formatting rule that formats cells based on whether the text within them includes a specified substring.

Formatting Options

Different styles and settings available for altering the appearance of data in cells, which can vary based on the type of data (e.g., text, numbers, dates).

Live Preview

A feature that allows users to see how changes in formatting will look in real-time before applying them to the data.

00:00:05

to help you make sense of your data.

00:00:08

For example it'll clearly show highs and lows,

00:00:12

or other data trends based on criteria you provide.

00:00:18

Now I want to see how much each salesperson sold.

00:00:22

I select the range of cells in Sales;

00:00:26

click the Quick analysis button;

00:00:29

use the default formatting tab.

00:00:33

When I move across the formatting options, a live preview

00:00:37

shows me what my data will look like.

00:00:41

Data bars provides what I want; so I click it,

00:00:45

and now I have an eye catching visual representation of the data,

00:00:49

making it easier to analyze.

00:00:54

You can apply multiple conditional formats to the same cells:

00:00:59

select the cells; click the button;

00:01:03

and click another format option, such as Icon set.

00:01:08

By default, these indicate the upper third, middle third,

00:01:13

and lower third of the values.

00:01:16

You can also apply conditional formatting to text.

00:01:20

Noticed the formatting options for text are different than those for money.

00:01:25

Formatting options differ between many different types of data.

00:01:30

I select Text contains; and enter oil.

00:01:35

I'm leaving the formatting set to the default;

00:01:39

and the cells that contain "oil" are formatted in red, they really stand out.

00:01:47

If you change the text in a cell so that it contains "oil"

00:01:51

the formatting automatically changes.

00:01:56

Up next: conditionally formatting dates.

Mandarine AI: WHAT YOU SHOULD KNOW

RELATED COURSES This tutorial is part of the following courses

2796 Views

Video Training Microsoft Excel - Discovery level

Discover and familiarize yourself with Excel: from creating and using your tables to enhancing your visuals and collaboration. Our introduct...

ALL COURSES