Excel Tips and Tricks 2: Sorting, Filtering, and Subtotaling

Here is an explanation of these three extremely useful capabilities and some tips I wish I knew before using them! (I learned these the hard way.) I will give directions for Excel and OpenOffice, adding endnotes where OpenOffice differs from Excel.

All three of these actions are under the Data menu. Sorting is a fantastically useful tool because you can arrange your data nicely. You can sort your columns (not rows, unfortunately) by value (which usually means alphabetical order but can also be by days of the week or months), cell color, font color, and cell icon.* You can also have multiple sorting rules that follow a simple hierarchy based on the order in which you arrange them.

Sorting in ExcelSorting in Excel

Filtering is another way to arrange your data. If you only want to view certain data, filtering is probably the way to go. (As a side note, I have only used filters to filter columns, but if I find out how to filter rows, I will let you know!) To filter, simply go to Data-->Filter. Excel will automatically display downward-facing arrow heads in each column header. Click on the arrow head in the column that you want to filter and choose how you want to filter. You can also sort in this popup. You can use multiple filters, but each successive filter only acts on the already-filtered data.**

Filtering in ExcelFiltering in Excel

Subtotaling is a great shortcut if you want to perform some simple functions on a lot of data. It can subtotal the values in a column (not row) in a list or database. You can perform the following functions: sum, count, average, max, min, product, count numbers, stdev (standard deviation based on a sample), stdevp (standard deviation based on an entire population), var (variation based on a sample), and varp (variation based on an entire population). For this one, let’s use an example. Say I have a database of different kinds of cars made in 2010 based on manufacturer and model, and I want to find out how many cars each manufacturer made. First, I will sort by manufacturer, so that all the cars made by each manufacturer are together. Then, I will go to Data-->Subtotal. A popup box appears where you can customize your subtotal. I want to subtotal by manufacturer, so I will select, “At each change in ‘Make,’” “Use function ‘Count,’” and “Add subtotal to ‘Make.’” Excel will automatically count how many cars each manufacturer produced. You can even collapse the results so you can see only the subtotal results.***

Subtotaling by manufacturerSubtotaling by manufacturer
Subtotal resultsSubtotal results
Subtotal results collapsedSubtotal results collapsed

Tips:

  • If you want to sort or filter a subtotal, make sure your entire table is selected before you filter or sort! Otherwise, your subtotals will get messed up!
  • To sort specific cells, simply select what you want to sort before sorting. Just make sure you don’t ruin your data by incompletely sorting your database!

That’s all for now. Happy sorting, filtering, and subtotaling!

OpenOffice caveats:
*OpenOffice has more and less options for sorting. You can only sort by ascending or descending order, and you are limited to only three sorting rules; however, there are a number of extra options, such as being able to copy the sorted results to a different place and the ability to sort rows as well as columns. There's also a Sort button in the toolbar.
**Filtering is basically the same in OpenOffice, only a little clunkier. Autofilter is very simple, whereas the Standard Filter is similar to the popup box in Excel that opens when you click on the filter arrow head.
***Subtotal is basically the same in in OpenOffice as well. The “Group by” menu is which column to which you want to add the subtotals.