Sorting, Filtering, Subtotaling Lists in Excel

June 26, 2006

A list in Excel is a “chunk of data”. Usually the data has headings across the top of the chunk. A list should contain no empty rows or columns, it can contain empty cells (because sometimes you don’t have the data necessary to fill in the cell.)

Examples of lists – mailing list; list of sales people including their territory and sales figures for several years; Top 100 Businesses in your area including # of employees, sales, name of CEO.

Although we often think of Excel as a tool for performing calculations, some lists may contain only text (such as a name and address list.) The value of creating and maintaining this type of list in Excel vs. Word is the many tools available to sort, filter, subtotal, extract, merge, and otherwise evaluate and manipulate the list data.

Sorting a list is easy.

First, examine the list to ensure no empty columns/rows exist. Select a cell anywhere in the column (field) you want to sort, you need not select the heading. Click the Sort Ascending or Sort Descending button on the Standard toolbar. xl-sort-buttonThe list sorts itself.

Sorting on multiple fields …

Let’s sort a list on several fields (columns) – for example, you’d like to sort a hypothetical Sales list by Region, then by 2005 Sales, then by Last Name. You can do it!

  1. Select a cell anywhere within the list.
  2. From the Data menu, choose Sort. [The Sort dialog box appears. Notice – the list is highlighted, except for the headings. The Sort dialog box offers three opportunities to sort. ]
  3. Work your way down the Sort dialog box, choosing each Sort by and whether you desire Ascending or Descending. [In our example, you would Sort by Region, next Sort by would be 2005 Sales, and third Sort by would be Last Name. It is possible to Sort by more than three fields, but that’s a topic for another day.]
  4. At the bottom of the dialog box specify whether the list has a heading row or not. [The heading row is the row containing the “labels” for the fields. Some people call these column headers. You wouldn’t want these labels sorted into the data, would you?]
    xl-sort.png
  5. Click OK . The list is sorted as you chose.

If any of this makes you nervous … save the file before you sort. If the results of your experiment look odd, just close the file without saving. Also, Undo is still your friend!

See what you want to see by filtering your Excel list …

You can filter an Excel list so you can work with or see subsets of a list’s data. Perhaps you want to only see sales people in the western region who sell printers. Once filtered, the data can be copied, charts created, and the filtered data can be manipulated in other ways.

Let’s look at using Excel’s Autofilter:

  1. Click anywhere within your Excel list.
  2. From the Data menu choose Filter, Autofilter. [Autofilter buttons appear on each heading in the list. ]
  3. Click the drop-down arrow on the Autofilter button. [An Autofilter list of choices appears. From the AutoFilter list you can choose to sort (handy!), or to see Top 10 or create Custom criteria. We’ll cover Top 10 and Custom criteria soon.]
  4. Choose the particular criteria by which you want to filter. [In our example, you want to see see only sales information in a certain region. Choose that region from the Autofilter drop-down list.]
  5. Continue filtering on other headings. [To continue our example, you can autofilter on two criteria: to see only sales people in the western region who sell printers.]

When filtering has been applied, row numbers (look to the left of the spreadsheet window) will be blue and the drop-down arrow on any heading by which you’ve autofiltered will be blue. Also, in the lower left of the status bar, Excel tells you how many filtered records are displayed. These are just reminders for you that filters are in place!

Subtotaling used to be tricky in older versions of Excel.

Not any more! When working with lists, you can quickly and accurately subtotal (as well as Count, obtain the Average, Max, Min) data. When Excel subtotals, it also outlines the list so you can collapse and expand levels of detail.

First, decide on your subtotaling scheme. For example, from that hypothetical Sales List, do you want to subtotal 2003 and 2004 sales by region? You must sort the data by region before beginning the subtotaling process.

  1. Once the list is sorted appropriately, make sure you’ve selected a cell within the list. From the Data menu, choose Subtotals.
  2. From the At each change in drop-down list, choose the field you want to subtotal. [In our example, you want to subtotal by region.]
  3. From the Use function drop-down list, choose the function you want to use. [In our example, we want to add up all sales so the SUM function is the correct choice.]
  4. At Add subtotal to, scroll up and down through the list of fields and select which fields are to be subtotaled. [In our example we want to subtotal 2003 and 2004 sales.]
  5. Click OK. Subtotals appear, in bold.

Look to the left of the list. You can collapse the details of the list by clicking the minus ( – ) buttons, expand by clicking plus ( + ) buttons. Towards the upper left above the column of minus / plus signs, there are three buttons. Click each one – expand, collapse the entire set of subtotals.

Play around and practice with all the cool stuff you can do with lists. Save time, improve accuracy. And let me know if you have questions!

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: