Excel - Sort details Tutorial

In this video, you will learn about sorting details in Microsoft 365. The video covers how to efficiently sort and organize your documents and information using the intelligent search feature in Microsoft 365. This will help you save time and improve your productivity by easily finding the documents and information you need.

4:46
4016 views

Available in Microsoft365

Objectifs :

This video aims to teach viewers how to sort data in Excel, including sorting numbers, text, and dates. It covers basic sorting methods, advanced sorting options, and the use of custom lists for sorting.

Chapitres :

Introduction to Sorting in Excel
Sorting data is a fundamental skill in Excel that helps organize information for better analysis. In this video, we will explore how to sort numbers, text, and dates, enhancing your ability to manage data effectively.
Basic Sorting Techniques
To sort a range of text alphabetically, right-click a cell in the desired column, point to 'Sort', and select either 'A to Z' or 'Z to A'. This action will arrange the data in alphabetical order. For sorting dates, the process is similar: right-click a date cell, point to 'Sort', and choose 'Sort oldest to newest' or 'Sort newest to oldest' to organize the data chronologically.
Advanced Sorting Options
For more complex sorting, such as sorting by multiple columns, click any cell within the range or table, navigate to the 'Data' tab, and select 'Sort'. In the sort dialog, ensure to check the option 'My data has headers' if your data includes column headers. This allows for easier identification of data categories.
Using Custom Lists for Sorting
You can create custom lists for sorting when you need to organize text by specific categories rather than alphabetically. To do this, click 'Sort', set the sort by option to your desired category, and select 'Custom list' from the order dropdown. You can manually enter the order of categories or paste a list separated by commas. After adding your custom list, click 'OK' to sort the data accordingly.
Sorting by Cell and Font Colors
Excel allows sorting by cell and font colors, which can be useful for visually categorizing data. In the sort dialog, set 'Sort by' to the desired color attribute. You can choose from options like 'Cell color', 'Font color', and 'Cell icon'. After configuring your preferences, click 'OK' to apply the sort.
Undoing a Sort
If you need to reverse a sort, you can use the 'Undo' button on the Quick Access Toolbar or the keyboard shortcut Ctrl + Z, provided you haven't saved the workbook since the sort. If the workbook has been saved, the sort cannot be undone.
Sorting by Rows
While sorting by columns is the most common method, Excel also allows sorting by rows. To do this, click 'Sort', and in the sort dialog, change the configuration from sorting by columns to 'Sort left to right'. After making this adjustment, click 'OK' to sort the data by rows.
Conclusion
In this video, we covered various methods for sorting data in Excel, including basic and advanced techniques, the use of custom lists, and sorting by colors. Mastering these sorting options will significantly enhance your data management skills in Excel.

FAQ :

How do I sort text in Excel?

To sort text in Excel, right-click a cell in the column containing the text, point to 'Sort', and select either 'A to Z' for ascending order or 'Z to A' for descending order.

Can I sort dates in Excel?

Yes, to sort dates in Excel, right-click a cell in the column with dates, point to 'Sort', and choose 'Sort oldest to newest' or 'Sort newest to oldest'.

What is a custom list in Excel?

A custom list in Excel is a user-defined order for sorting data, allowing you to sort items in a specific sequence, such as days of the week or months of the year.

How can I sort by multiple columns in Excel?

To sort by multiple columns, click any cell in the range or table, go to the 'Data' tab, and click 'Sort'. In the sort dialog, you can add levels to sort by additional columns.

What should I do if I accidentally sorted my data incorrectly?

If you haven't saved your workbook since sorting, you can click the 'Undo' button on the Quick Access Toolbar or use the keyboard shortcut Ctrl + Z to revert the sort.

Quelques cas d'usages :

Organizing Product Inventory

A retail manager can use sorting features in Excel to organize product inventory by category and product name, making it easier to locate items and manage stock levels.

Scheduling Events

An event planner can sort a list of events by date to ensure that all activities are scheduled in chronological order, improving planning efficiency.

Analyzing Sales Data

A sales analyst can sort sales data by region and product type to identify trends and make informed decisions about marketing strategies.

Sorting Customer Feedback

A customer service manager can sort feedback by categories such as 'Positive', 'Negative', and 'Neutral' to quickly assess customer satisfaction and address issues.

Creating Custom Reports

A data analyst can create custom lists to sort data in specific orders for reports, such as sorting by priority levels or project phases, enhancing report clarity.

Glossaire :

Sort

The process of arranging data in a specific order, either alphabetically, numerically, or chronologically.

Range

A selection of two or more cells in a spreadsheet that can be manipulated as a single unit.

Column Header

A row at the top of a range of cells or a table that contains names for the columns, helping to identify the data contained within each column.

Custom List

A user-defined list that allows sorting data in a specific order that is not alphabetical, such as days of the week or months of the year.

Conditional Formatting

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

Undo

A command that reverses the last action taken in a software application, allowing users to revert changes.

00:00:05

you can also sort text and dates; either alphabetically, by format,

00:00:10

such as color, or by a custom list you create.

00:00:15

To sort a range or table using text,

00:00:19

right click a cell in a column that contains text,

00:00:22

point to Sort, and click A to Z or Z to A,

00:00:29

and the range sorts alphabetically.

00:00:31

To sort a range or table using dates,

00:00:34

right click a cell in a column that contains dates,

00:00:38

point to Sort, and click Sort oldest to newest

00:00:42

or Sort newest to oldest,

00:00:45

and the range sorts chronologically.

00:00:48

For more advanced sorting, such as sorting by multiple columns,

00:00:53

click any cell in the range or table,

00:00:56

click the Data tab, and click Sort.

00:01:00

In the sort dialog, there is an option to the right:

00:01:04

My data has headers.

00:01:07

A column header, is a row at the top of a range of cells or a table,

00:01:11

that contains names for the columns.

00:01:14

It's a good idea to have them.

00:01:17

Without headers you sort by column A, B, C and so on,

00:01:23

but it can be hard to remember which columns contain what data.

00:01:28

With headers you can sort by column names,

00:01:32

such as Category and Product name, in this example.

00:01:36

Set sort by the Category, click Add level,

00:01:41

set them by the Product name, and click OK.

00:01:47

And the cells are now sorted alphabetically, by category, and then product name.

00:01:52

You can also sort using a custom list you create.

00:01:56

You might want to do this, when you want to sort

00:01:58

your text by category, instead of alphabetically.

00:02:02

Click Sort, I set sort by the Category,

00:02:08

click the order dropdown arrow, and click Custom list.

00:02:13

A common reason to not sort alphabetically, is to sort by

00:02:16

the days of the week or months of the year;

00:02:19

and Excel already provides these custom lists for you.

00:02:23

I'm going to create a custom list that sorts

00:02:26

the category column in the order I want.

00:02:29

I type a value that's in the category column and press Enter.

00:02:34

I keep doing this, until I have completed my list.

00:02:38

You can also type or paste a list separated by commas.

00:02:43

I click Add, and click OK;

00:02:47

I click OK again

00:02:48

and the range is sorted by the category column

00:02:51

using the custom list we just created.

00:02:54

You may have noticed, that a few of the cells in the discontinued column

00:02:58

have different cell and font colors.

00:03:01

You can sort by these colors.

00:03:04

In this sort dialog, I set Sort by to discontinued,

00:03:10

I click the sort on down arrow,

00:03:13

in addition to Values,

00:03:14

the list has the Cell color, Font color, and Cell icon options.

00:03:21

These were commonly applied using conditional formatting;

00:03:24

although you can format them manually as well.

00:03:28

I click Cell color, I want the cells with No vell Color, to be on top;

00:03:34

so I leave it set to the defaults, and click copy level.

00:03:40

On the new level, I change order to red

00:03:44

and set it to on bottom;

00:03:48

I click OK and the range is sorted by cell colors in the discontinued column.

00:03:54

You can undo a sort if you haven't saved the workbook since you sorted.

00:03:59

I click the undo button on the Quick Access Toolbar,

00:04:02

you can also use the keyboard shortcut Ctrl-Z.

00:04:07

If you've saved the workbook, you can't undo a sort.

00:04:11

So far we've sorted data in columns, the most common way to sort,

00:04:16

but you can also sort by rows.

00:04:19

This is the data we've been working with,

00:04:21

but the columns, such as Category, are now rows.

00:04:26

To sort by rows, click Sort,

00:04:29

notice that the sort dialog is configured to sort by columns,

00:04:34

click Options,

00:04:36

click Sort left to right, click OK,

00:04:40

and now the sort dialog is configured to sort by rows.

Mandarine AI: WHAT YOU SHOULD KNOW

RELATED COURSES This tutorial is part of the following courses

2494 Views

Video Training Microsoft Excel - Intermediate level

Increase your scope of action with our intermediate tutorials dedicated to formulas, advanced visual enhancement and data analysis with pivo...

ALL COURSES