Locked lesson.
About this lesson
Ask Copilot to organize information in a table using sort and filter options.
Exercise files
Download this lesson’s related exercise files.
2.05 sort-and-filter-data - Exercise.docx53 KB 2.05 sort-and-filter-data - Exercise solution.docx
5.9 MB 02-05-Sort and Filter Data-Start.xlsx
773.4 KB 02-05-Sort and Filter Data-Finish.xlsx
777.7 KB 02-05-Sales Data Exercise-Start.xlsx
20.2 KB 02-05-Sales Data Exercise-Finish.xlsx
20.2 KB
Quick reference
Sort and Filter Data
Use Copilot to sort and filter data in an Excel table.
When to use
We use Copilot to sort and filter our table whenever we want to refine our data and focus on what's most important.
Instructions
Copilot has the ability to sort data and apply filters to one or more columns.
Filter Columns
In this first example, we are going to filter the table by the 'Region' column to only show results that are equal to 'Midwest'.
- Type the prompt: 'Filter the Region column to only show results that are equal to Midwest'.
- Press Enter.
The results will now be filtered.
Clear the Filter
We can use Copilot to clear filters from our data.
- Type the prompt: 'Clear the filter from the Region column'.
- Press Enter.
Apply Multiple Filters
We can use Copilot to apply filters to more than one column.
- Type the prompt: 'Filter the State column to only show results equal to 'California' and then filter the Product column to only show results equal to 'Menswear'.
- Press Enter.
Sort Table Data
Copilot can sort data in any column in ascending or descending order.
- Type the prompt: 'Sort the data in the Total Sales column largest to smallest'.
- Press Enter.
Now let's sort by another column.
- Type the prompt: 'Sort by the Retailer column A to Z'.
- Press Enter.
- 00:04 In this lesson, we're going to take a look at how we can use Copilot to sort and
- 00:09 filter a data set in Excel.
- 00:10 Sorting and filtering really just helps us organize our data.
- 00:14 For example, if I wanted to filter this data set by region, for
- 00:18 example, maybe I only wanted to see the results related to Midwest,
- 00:22 I can select it from the filter drop down and my data is going to be filtered.
- 00:27 We can do all of this with Copilot.
- 00:30 Now, I can see here that I need to toggle on Autosave, and
- 00:33 then I can launch Copilot.
- 00:36 The pane is going to open on the right hand side and
- 00:38 I can click in the prompt area and type in my prompt.
- 00:42 So let's say filter the region column to only show rows that are equal to Midwest.
- 00:49 So we're effectively doing the same thing that we just did manually.
- 00:53 Let's send that through.
- 00:55 And there we go, Copilot has understood that request and
- 00:58 it's filtered my data by the region.
- 01:01 Let's type in another prompt.
- 01:03 This time we're going to clear the filter from the region column.
- 01:07 And there we go, all of the results have been restored.
- 01:11 So you can use Copilot to filter and also clear the filter from a dataset.
- 01:15 Let's try another filter.
- 01:17 This time we're going to filter the State column to only show
- 01:22 results equal to California.
- 01:25 And then we're going to filter the product column
- 01:29 to only show results equal to Menswear.
- 01:32 So we're applying two filters at the same time in this example.
- 01:37 Let's send it through and see if it works.
- 01:40 And there we go.
- 01:41 We can see now, we're only seeing the results for the State California and
- 01:45 the product Menswear.
- 01:47 So that's worked perfectly.
- 01:49 And if we scroll up we can take a look, because it's providing us with
- 01:53 an explanation of exactly what Copilot has applied.
- 01:57 Notice underneath, we also have some suggested prompts that we could use,
- 02:00 we're not going to use those.
- 02:01 We're going to do a sort in our data this time by sorting the total
- 02:05 sales column largest to smallest.
- 02:08 So let's see if it can handle that.
- 02:12 And there we go.
- 02:13 We now have the total sales column sorted largest all the way down to smallest.
- 02:18 Let's apply another sort.
- 02:20 This time we're going to sort by the retailer column, A to Z, and it's always
- 02:25 going to be as specific as possible when you're constructing your prompts.
- 02:29 You're going to get the best results, the more descriptive that you are.
- 02:33 So I've told Copilot if I want ascending or
- 02:36 descending order by specifying that A to Z.
- 02:39 To what I would expect to see in a moment is this retailer column is going to update
- 02:44 and West gear is going to be pushed all the way down to the bottom because
- 02:47 we're sorting A to Z.
- 02:48 And you can see there we go, it's applied that sort.
- 02:51 So Copilot is perfectly able to handle sorting and filtering on one or
- 02:56 more columns.
- 02:58 Have a little play around with it and I'll see you in the next lesson.
Lesson notes are only available for subscribers.