Tip:
Highlight text to annotate it
X
In this video, we're going to take a look at sorting, grouping, and filtering, and how
you can use these commands to make your time working with spreadsheets more efficient.
We don't need these totals right now, so let's delete them.
A Microsoft Excel spreadsheet can contain a great deal of information.
Sometimes you may find that you need to reorder or sort that information to be able to use
it most effectively. For example, in our Inventory spreadsheet,
I entered all the items into my spreadsheet so that similar items are together.
But, you may want to organize the information in a different way.
Let's say we want to organize our information by category in alphabetical order.
All you need to do is select a cell in the Category column, and then click the Sort &
Filter command in the Editing group on the Home tab.
Now we have to choose how we want the information organized.
Let's select Sort A to Z. Now the information in the Category column
is organized in alphabetical order. We can do the same thing with the Total Cost
column. Just select a cell in the column, click the
Sort & Filter command, and choose From Smallest to Largest.
The Category column is no longer organized in alphabetical order, but the Total Cost
column does appear to be sorted from smallest to largest amount.
What if you want to organize the spreadsheet so that column A is in alphabetical order
and the like items within the category are organized by Total Cost? You can do this using
a custom sort. To access Custom Sort, click the Sort &
Filter command on the Home Tab, and choose Custom Sort from the list to open the dialog
box. You can also access this same dialog box from
the Data tab. On the Data tab, you'll find the Sort and
Filter group. While you can accomplish many of the same
tasks on the Home tab, you may find that these commands are easier to use.
It really depends on where you are in the spreadsheet.
Click the Sort command to open the Custom Sort dialog box.
I can sort by one item, or multiple items. Let's take a look at how this works.
First, I want to sort the spreadsheet by Category. We'll click the drop-down arrow in Column
Sort by field, and choose Category. Then, we need to choose what to sort on.
We could choose cell or font color or cell icons if we were using those, but we're not,
so we'll leave it as value. Now we need to choose how to order the results.
I'll leave it as A to Z so it is organized alphabetically.
So, this will sort the spreadsheet by Category. Within the category, I'd like to sort by the
Unit Cost so that the lowest priced item will appear first within each category.
To add another item to sort by, click Add Level.
In the Column Sort by field, select Unit Cost. We'll leave Value as it is.
And we'll leave the results ordered smallest to largest.
Click OK and you'll see the spreadsheet has been sorted.
All the categories are organized in alphabetical order, and within each category, the unit
cost is arranged from smallest to largest. Remember all of my information and data is
still here. It's just in a different order.
In addition to sorting, grouping is a really useful Excel feature.
It gives you control over how the information is displayed.
This may sound a little confusing, but I promise it will make sense once you see how grouping
works.One important thing that you must know is that
you must sort before you can group. There are a couple of different ways you can
group items in your spreadsheet. Let's take a look at one way where
we can group our items and find subtotals for those groups.
Select any cell with information in it. Now, click the Subtotal command.
You'll see the information in your spreadsheet is automatically selected and the Subtotal
dialog box appears. In the dialog box, we have some decisions
to make. First, we have to decide how we want things
grouped. I want them organized by Category, so we'll
make sure that is selected. And I'd like to know how much inventory of
each category I have, so we'll leave the SUM function selected.
I do want the subtotal of each category to appear in the total cost column, so we'll
leave that as the default. Let's click OK.
Now you can see that after each category, a row has been inserted with the total cost
for that category. You can see that we have $40 worth of bowls,
and just over $720 worth of cones. And at the bottom we can see that a Grand
Total has been inserted. Everything is sorted by category, but now,
it's organized into groups. You can use the black minus sign which is
the hide detail icon to collapse the group. On the Ribbon you can do the same thing here.
We can also use the plus sign which is the show detail icon to expand the group.
To ungroup the selected cells select the cells you want to remove from the group.
Click the Ungroup command, select Ungroup from the list and a dialog box will appear.
Click Ok. To Ungroup the entire worksheet, select all
the cells with grouping, and click Clear Outline from the menu.
There is another Excel feature I'd like to tell you about and it is the Filter command.
I always like to talk about sorting, grouping, and filtering together because all three are tools
that you can use to make using the data in your spreadsheet easier.
When you're using Excel, you may need to only look at a subset of the data in a worksheet.
To do this, click the Filter command on the Data tab.
Drop-down arrows will appear beside each column heading.
I'd like to see only my actual ice cream inventory, which I have labeled as Flavor in the Category
column. So, if I only want to see the Flavors, I just
need to click the arrow next to Category, uncheck Select All, and then choose Flavor.
Now, click OK. So, we've filtered everything else and are
only looking at my Flavor entries. Don't worry about the other information - it's
still there. Filtering doesn't remove any data
from your spreadsheet. It just hides it.
Filtering may look a little like grouping, but the difference is that now I can filter
on another field, if I want to. For example, let's say I want to see only
the Vanilla-related flavors. I can click the drop-down arrow next to Item,
and select Text Filters. From the menu, I'll choose Contains because
I want to find any entry that has the word vanilla in it.
A dialog box appears. We'll type Vanilla, and then click OK.
Now we can see that the data has been filtered again and that only the Vanilla-related flavors
appear. If we want to clear the filter, just select
the drop-down arrow and choose Clear Filter from Item.
There are many other ways you can filter data. For example, if you filter on numeric values
you can define your filter based on specific numbers or use the filter to locate entries
that meet certain conditions. To remove all the filters, you can just click
the Filter command again and you'll be able to see all your data again.
Sorting, Grouping, and Filtering are three easy ways Excel allows you to manage your data
so you can use your spreadsheets efficiently. I've just shown you the basics of how these
work, so take some time to explore it on your own.