Excel
is an excellent number cruncher, but it's also great for creating and managing lists. You can track everything from e-mail addresses to inventory items in a list, but for lists to be useful, you need to be able to quickly find the information that you're looking for. Take this tutorial to learn how.
What you'll learn
After completing this tutorial, you'll be able to:
Organize a worksheet to be managed as a list.
Quickly arrange a list alphabetically or numerically.
Filter a list to show specific items.
Sort and filter a list by using several criteria.
Reset and remove filters that have been applied to a list.
Requirements
Excel for Mac 2011
Basic Excel
skills, including how to open a workbook, and select cells, columns, and rows.
In Excel, a list can contain one or many columns of data. To manage a list, you first need to make sure that your worksheet data is organized properly. For example, the data should be arranged in labeled columns and should not contain any blank rows or blank columns.
Let's organize the worksheet for this tutorial so that it's easy to manage as a list.
Open the practice file, which is located at the beginning of this tutorial.
Hold down
CONTROL
and click the heading for row 7 (or right-click the heading), and then click Delete.
Hold down
CONTROL
and click the heading for column B (or right-click the heading), and then click Delete.
Click cell C1, type
Date for the label, and then press
RETURN.
Notice that all columns are now labeled, and that the list no longer contains any blank rows or columns.
Hints
To keep column labels visible as you scroll through a long list, you can freeze the row that contains them. For example, on the practice sheet, click anywhere in row 2 (such as cell A2), and then click Window > Freeze Panes. To unfreeze the row, click Window > Unfreeze Panes.
Before you move on
Make sure that you can do the following:
Prepare a worksheet to be managed as a list by adding heading labels and deleting blank rows and columns.
2. Sort information in a list
One way to arrange data in a list is to sort it alphabetically or numerically. For example, you might want to see all transactions arranged by date.
Let's sort the list to show the transactions by date, with the most recent date shown first.
Click any cell in column C that contains data (for example, C2).
On the Data tab, under Sort & Filter, click the arrow next to Sort, and then click Descending.
Notice that the list is now arranged by date, with the most recent transactions appearing first. Also, all associated data in the other columns is arranged to correspond to the order of the sorted column.
Note When your data contains heading labels (for example, Location, Date, Item), the labels are not included when you sort.
Hints
To view the list in its original order, make sure that one column retains its original order, and then sort by that column (for example, a column of transaction numbers such as the Transaction column in the tutorial practice file).
You can sort columns, but not rows.
You cannot sort a column that contains merged cells. To unmerge cells, select the cells that contain merged data. On the Home tab, under Alignment, click the arrow next to Merge, and then click Unmerge Cells.
When you sort a column of cells that contains text data rather than numerical data, the data is arranged alphabetically.
Before you move on
Make sure that you can do the following:
Sort a list that contains either numerical or text data.
3. Sort by several columns at once
In the last lesson, you sorted the list by date, showing the transactions from newest to oldest. But what if you want to sort the list by additional criteria? For example, you might want to see the transactions listed by date, and then for each date, transactions listed by price from highest to lowest.
Let's sort the list by additional criteria.
Click the arrow next to Sort, and then click Custom Sort.
Notice that the Sort dialog box already includes one sort criterion (for example, Sort by Date) based on the sort done in the previous lesson.
To add another sort criteria, click the + button.
Click the blank area to the right of Then by, and then click Wholesale Price.
On the Order pop-up menu, click Largest to Smallest, and then click OK.
Tip To change the order of sort criteria, click Sort by or Then by, and then drag the criteria in to the order that you want.
Notice that the list is now sorted by date, newest to oldest, and within each date, by wholesale price, largest to smallest.
Before you move on
Make sure that you can do the following:
Sort a list by multiple criteria.
4. Filter a list
Filtering is another way to view data in a list. While sorting arranges all items either alphabetically or numerically, filtering shows only those items that meet certain criteria. For example, you might want to see only those transactions that occurred on a specific date.
Let's filter the list to show transactions that occurred on May 05.
Click any cell in the list that contains data (for example, A2).
On the Data tab, under Sort & Filter, click Filter.
Notice that a filter arrow appears on the cell of each column label.
Click the filter arrow for the Date column label.
In the Filter panel, clear the Select All check box.
Click the arrow next to May, select the 05 check box, and then click X to close the Filter panel.
Notice that the list is filtered to show only the transactions that occurred on May 05, and that the row numbers for the rows that match the filter criteria appear in blue.
Hints
A filter icon appears on the columns that have been filtered.
To move the Filter panel to another location on the sheet, drag the panel toolbar.
Before you move on
Make sure that you can do the following:
Filter a list by several criteria.
5. Filter by custom criteria
You can filter a list by more than one column at a time, as well as by custom criteria. For example, of the items that appear on a specific date, you might want to see only transactions for a specific location within a specific price range.
Let's filter the list to show the May 05 transactions in Oregon and Washington with total sales between $500 and $1000.
Click the filter arrow for the Location column label.
Clear the California check box, and then click X to close the Filter panel.
Click the filter arrow for the Total Sales column label.
On the Choose One pop-up menu, click Between.
In the boxes, type 500 and 1000 as shown below, and then click X to close the Filter panel.
Notice that the list now shows only May 05 transactions in Washington and Oregon with total sales between $500 and $1000.
Before you move on
Make sure that you can do the following:
Filter a list by custom criteria.
6. Reset filters
After you filter a list to see the information that you want, you can reset the filters to show the hidden rows. You can reset filters one at a time or all at once.
Let's reset a filter for a single column and then for the entire list.
Click the filter arrow for the Total Sales column label.
In the Filter panel, click Clear Filter, and then click X to close the Filter panel.
Notice that the Total Sales column is no longer filtered and that the filter icon no longer appears on the column label.
To reset filters for the entire list, click Data > Clear Filters.
Note If the Clear Filters option is unavailable, all filters have been reset.
Notice that the filter icons no longer appear on the column labels and that row numbers no longer appear in blue, indicating that no filters are applied to the list.
Hints
To turn off filtering for the entire sheet, on the Data tab, under Sort & Filter, click Filter.
Before you move on
Make sure that you can do the following:
Reset a filter applied to one column and filters applied to an entire list.
Quick Reference Card
To
Do this
Delete a blank row or column
Hold down
CONTROL
and click the column or row heading (for example, A or 1), and then click Delete. Or, right-click the column or row heading, and then click Delete.
Keep column labels visible while scrolling
Click anywhere in the row below the one that you want to remain visible. Then click Window > Freeze Panes. To unfreeze the row, click Window > Unfreeze Panes.
Sort a column
Click any data cell in the column. On the Data tab, under Sort & Filter, click the arrow next to Sort, and then select an option.
Return a list to the order in which the data was originally entered
Make sure the list includes a column (for example, "Transaction") that organizes the data in the order that it was entered. Then, sort by that column.
Sort by several columns at once
On the Data tab, under Sort & Filter, click the arrow next to Sort, and then click Custom Sort. Then, add your sort criteria.
Turn on filtering
On the Data tab, under Sort & Filter, click Filter.
Show the Filter panel
Click the filter arrow on the column label for the column that you want to filter.
Clear a filter from one column
Click the filter arrow on the column label, and then click Clear Filter.
Clear all filters from a list
Click Data > Clear Filters.
More Hints
Sorting arranges all items in a list either alphabetically or numerically. Filtering shows only those items in a list that meet specific criteria.
Before you sort or filter a list, you should delete empty columns and rows, and add a descriptive name for all column labels (for example, Location, Date, Item).
A filter icon on the column label indicates that the column has been filtered. Also, row headings (for example, 1, 2, 3) appear in blue to indicate which rows meet the selected filter criteria.
You can sort columns, but not rows.
Additional sort and filter options are available on the Data menu (for example, Data > Clear Filters).
Related Content
If you liked this training, you might want to try the following:
