If
you’ve used Excel at all in the past, you’ve seen the millions of
square boxes—cells—all over the screen. These cells make up the rows and
columns in an Excel workbook. In all recent versions of Excel, each sheet in
your workbook can contain up to 65,536 rows of information. Imagine looking for
specific information, or trying to spot trends across this many rows of data!
It would be like looking for a needle in a haystack; difficult to say the
least.

Excel
provides you some quick ways to make it easier to find and analyze your data.

  • Sorting: Sort your worksheet by using
    information from one or more columns. For example, if you have a budget
    spreadsheet that lists expenditures, you could sort the information by the
    budget department and then category, giving you a quick way to see what each
    department is spending in each category. I’ll be using a similar scenario as an
    example in this article. Or, suppose you have a 1,000 line vehicle inventory. If
    you have a customer than wants a 2003 green Honda Civic, you could manually search through your
    entire inventory spreadsheet, or, you could sort your sheet by model year, then
    by model, then by color, for example.
  • Filtering: Filtering is completely different
    way to get information from your data. Filtering allows you to selectively
    block out data that you don’t want to see. More to the point, using a filter,
    you can keep all of your data in your sheet, but have Excel just show you a
    part of the data. For example suppose you want to see all of the purchases
    approved by a particular manager in your expenditure. You could sort the data by the manager column, but you still see all of
    the other data that may not be pertinent. Apply a filter, and you’ll see only the information related to the
    manager you want.

I’ll
go over these data analysis methods in this article.


Data analysis series

This
data analysis series consists of these articles:

  • Part
    1: Sorting and Filtering
  • Part
    2: Subtotals
  • Part
    3: Pivot Tables
  • Part
    4: Simple Graphs
  • Part 5: Advanced graphing and PivotCharts

Before
you get to graphing and PivotTables, you need to know how to manipulate your
Excel data to get the information into a usable form. Read on!

Note: Here’s a look at the budget
spreadsheet (Figure A) we will be
using as an example throughout this and the next three articles.

Figure A

Budget spreadsheet

Sorting

You
already know the basics behind sorting. You probably have a file cabinet with
files arranged (or sorted)
alphabetically. You probably keep a monthly budget (that is, a budget sorted by month). Excel’s sorting
options just give you a way to view data in different orders. Why is this
useful?

Suppose,
this week, your boss wants a spreadsheet with three months’ worth of
expenditures sorted by date. Now, just last week, that same boss wanted the
same information, but sorted by department. Sure, you could have copied the
data to a new workbook and copied and pasted the information until it was in
the requested sort order, but that’s not horribly efficient, especially
considering the fact that Excel’s sorting function can accomplish the same goal
in a matter of seconds.

Not so fast… make sure you have headings

One
thing that you’ll find in your sorting trial and tribulations is that having a
heading for each column in your worksheet makes life a lot less confusing. Why
is this?

When
you tell Excel you want to sort your information, Excel asks you on which piece
of data you want to sort. If you have column headings, Excel automatically
populates a list of items on which you can sort (i.e. Department, Category, Date). If you do not have a column heading (that is, you
have just data), Excel says “Ok… would you like to sort by column A or
column B?” Clearly, if you don’t know exactly which column is which, this
would mean that you need to go back to your worksheet and figure it out. I’ve
found it much easier to just assign
column headings to my data so Excel instead asks, “Ok… would you like to
sort by date or by amount?” It’s much clearer this way and saves me the
time of going back to look at what is where in my workbook.

Sort order

Right
from the Excel help text:

  • Numbers: Excel sorts numbers from the
    smallest negative number to the largest positive number.
  • Dates: Excel sorts dates from earliest to
    latest.
  • Alphanumeric: Excel sorts text from left to
    right, character by character. For example, if you have the contents “H310”
    in a cell, Excel would sort that cell after another cell that contains
    “H3”, but before a cell that contains the entry “H32.”
    Excel sorts text in the following order: 0 1 2 3 4 5 6 7 8 9 (space) ! ” # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` { | }
    ~ + < = > A B C D E F G H I J K L M N O P Q R S T U V W X Y Z. Special
    case: Excel ignores apostrophes (‘) and hyphens (-) unless two text strings are
    the same except for a hyphen, in which case the text with the hyphen is sorted
    last.
  • Blanks: Blanks always come at the end.

The Sort window

Before
you open up the Sort option in Excel, make sure to select a cell that resides
somewhere inside the data you want to sort. Why?

Excel
automatically figures out the boundaries of your table based on where Excel
sees blank cells. So, if you have selected a cell outside your data, Excel will
assume that there is no data to sort. In fact, if you do select any cell inside
your table, when you open the Sort window, Excel highlights what it thinks is
your entire data table! I’ve shown this below in Figure B. I’ll explain how to get to the Sort window next.

Figure B

Table selected

Notice
that cells A2 to E49 are selected, skipping the first
row. When you have placed headings into your columns, Excel does not include
them in the sort because if it did your headings would end up being sorted as
data and placed elsewhere in the table.

Now,
to open the Sort window, from the menu bar, choose Data | Sort. You’ll get a
window similar to the one in Figure C.

Figure C

The Sort window, it all its simple glory.

I’ll
go over each part of the Sort window:

  • “Sort by”: By which column would you like
    to sort your data? Excel lets you support by up to three different fields,
    in the order you specify. For example, in your budget spreadsheet, you
    might sort by Department, and then, for each department, by Category, and
    then by Date. This would show you, for each department, a list of
    expenditures for each category, sorted by date.
  • Ascending, Descending: Do you want to sort from big to
    small, or from small to big? From A to Z, or from Z to A? Choose Ascending
    to sort from A to Z (or from 0 to 9, or from January to December). Choose
    Descending to sort from Z to A (or from 9 to 0, or from December to
    January).
  • My data range has: Your choices here are “Header
    row” and “No header row”. I indicated earlier that you
    should attempt to use a header row whenever you can. However, sometimes
    Excel gets confused about your data and can’t tell that you have a header
    row. If this happens when you open the Sort window, the “No header
    row” option is selected. If this happens to you, just select the “Header
    row” option to get Excel back on track.
  • Options: Personally, I’ve never had a
    need to make use of the advanced sorting options offered through the
    Options button. For any normal sorting operation, you will not need to
    modify sort options. If you like, explore this area, though. One of the
    options there changes the sorting from columns to rows, so you could sort
    left-to-right instead of top-to-bottom, if you wanted.

Example: See budget by department and category

To
view this sample budget spreadsheet by department and then by category, I use
the values shown in the Sort window in Figure
D
.

Figure D

Note that I’ve used two sort options in this example.

In Figure D, you can see that I sorted
this table first by department and then by category. The result of this sorting
operation is shown below in Figure E.

Figure E

I’ve highlighted the columns I used for the sort.

Notice
in Figure E that all of the
Departments are grouped together and, within each department, the categories
are grouped together.

The fast way to sort

Using
the sort window is all well and good, but what if you just want to sort by a
single column? Perhaps you have data in no particular order, and you just want
to sort it by date and be done with it. Excel provides with you a quick way to
handle these super simple sorts. Take a look at Excel’s shortcut toolbar. A
little more than two thirds of the way across the toolbar, notice two buttons:
one is labels AZ with a down arrow and another ZA with an up arrow. See Figure F.

Figure F

These two buttons give you quick access to single-column sorts.

To use
these buttons, select any cell inside the column on which you’d like to sort
and then click one of these buttons. If you click the AZ button, you’ll sort
that column ascending; clicking ZA results in a descending sort. For example,
if you wanted to sort this sample spreadsheet ascending by date, select any
cell in column A and click the AZ button.

You’ll
learn about one more sorting option in the next section on filtering.

Filtering

When
you filter information in Excel, you effectively eliminate the extraneous
information that you don’t need. Like sorting, you probably filter stuff in
your office every day. For example, suppose you have an eight-inch high stack
of invoices on your desk, and you need to pay the ones due to “Acme, Inc.”.
To accomplish this, you probably go through the stack of invoices and pull out
the ones for Acme, Inc. In effect, you’ve filtered the invoices so you have
only the ones you need and don’t have to deal with the rest right away.

Before you filter

As I
mentioned with sorting, having headings in your columns is pretty useful for
filters, too. Likewise, make sure that you select a cell somewhere inside your
worksheet data in order to be able to use the Filtering function. Look back
under Sorting if you need more clarification on these items.

AutoFilter – Excel’s down and dirty (but good!) filter

The
best way to better explain filtering and how to use it is to jump right into
the use of Excel’s AutoFilter feature. Unlike sorting, Excel’s AutoFilter
function does not have its own dialog window. In fact, Excel’s AutoFilters has
just two modes of operation: on or off. When turned on, AutoFilter adds
drop-down options to your spreadsheet that provide you with the ability to
introduce a variety of filter options on your data.

To
turn on AutoFilter, from the menu, go to Data | Filter | AutoFilter. When you
select this option, not a whole lot changes, but if you look closely, you’ll
see that Excel has added some drop down arrows next to each column heading.

Figure G

Note the down arrows next to each of the column headings.

If you
click on any of the down arrows, you’ll get a shortcut menu with a bunch of
options. Each menu will be different and is tailored with the data in that
particular column. In the example we’ve been using in this article, if you
click the down arrow in Column C, you’ll get the menu shown in Figure H.

Figure H

All of these options are explained below.

Here’s
an explanation of each of the options on the filter menu:

  • Sort Ascending: Sort ascending using this column.
    (I told you there was one more way to sort your table.)
  • Sort Descending: Sort descending using this column.
  • (All): Show all of the records in this
    column. You generally use this if you’ve already filtered the information and
    you now want to un-filter a particular column.
  • (Top 10…): Want to see the top ten most
    expensive expenditures that hit your budget in this example? This option works
    on numeric fields. I’ll be going over this option in more depth in the next
    sections. This option won’t work on the column I showed you in Figure G since it’s a text column, but
    would work very nicely on the Amount column.
  • (Custom…): Allows you to create a custom
    filter. For example, you could create a filter that shows you only expenditures
    that are greater than $10,000. I will go over this in the next sections, too.
  • This column’s choices: This is where the filtering option
    really shines. If you select one of these items, your table will shrink and
    show you only these records. This can
    make it incredibly easy to analyze data since you can easily omit information
    you don’t want. Not to worry, though. Excel doesn’t erase your information. It
    just temporarily hides it from view. In Figure
    I
    , I’ve added a filter that shows just IT’s Equipment expenditures.

Figure I

Notice that these down arrows are now blue. This signifies that a filter is
applied to these columns.

In Figure I, notice that there are blue
down arrows next to both Department and Category. A blue down arrows signifies
that a filter is applied to these columns. By way of this example, you can see
that multiple filters can be applied to your worksheet. In this example, I’ve
selected the “IT” option in the Department filter, and the “Equipment”
option in the Category filter. Also notice the row numbers at the left
hand-side of the window. They don’t go up by ones anymore, and, in this
example, they’re blue. This is another indication that you have a filter
applied and that not all of your rows are being displayed.

Top 10

The
Top 10 option is pretty useful when you’re working with numbers. In the example
we’ve been using in this article, for example, with the Top 10 feature, you
could get a variety of information. The “Top 10” label is a little
misleading. You can actually do a lot more. For example, using the option, you
could get the top ten expenditures in your budget, or you could get the top ten percent of expenditures. Or, you
could get the bottom seven expenditures. Yes… seven (or eight, or nine, for
that matter).

How
can this be? In Figure J, I’ve
selected the Top 10 option from the filter menu. Rather than do something
immediately, choosing this option brings up a window on which you need to make
some selections.

Figure J

Use these three options to control exactly what you see in the filtered
list.

There
are three choices here you need to make:

  • Top or bottom: Show the top ranking or lowest
    ranking items.
  • A number: Show this many items, or use this
    percentage. (i.e. show me the top 10% of expenditures)
  • Items or percent: Show a fixed number of items, or a
    percentage of items.

The
next two figures are examples showing you how the Top 10 option works. The
captions for each figure explain the data set.

Figure K

This filter is showing the top fifteen items
from the amount column.

Figure L

This filter is showing the top fifteen percent
of values from the amount column.

Custom

The
last option to discuss gives you the most flexibility. For example, maybe you’d
like to find all expenditures that are above $5,000, but below $20,000. The
Custom option lets you create a simple search to narrow down your list. You can
specify up to two criteria. The example I mentioned in this paragraph would
meet this two criteria limit.

When
you choose the Custom option, you get a window on which you can make selections
for your criteria. Figure M shows
you the custom criteria window. Figure N
shows you the resulting filtered data.

Figure M

This custom criterion meets the specifications I mentioned in this section.

Figure N

And here is the resulting data set.

Take note in Figure M
that you have two options with an “and” and an “or” in the
middle. As such,
you could build a criterion that says “amount is equal to $1,000 or
greater than $10,000”. In the example in Figures M and N, I used the “and” operator. If I’d used
the “or” operator, I would have gotten all of the records.

You
can use the Custom filter on text fields, too. For this example worksheet, it’s
not the most useful, but you could, for example, use a custom filter to show
records with Departments that start with the letter “A”.

Figure O

Here’s the criteria for departments that start
with the letter “A”.

Figure P

And here are the records for said departments.

Clear filters

There
are a couple of ways to clear filters. First, you can clear them on each
individual column by clicking the filter down arrow and choosing the “(All)”
option. This is useful if you’ve set multiple filters and want to clear just
one of them. If you want to leave the filtering down arrows enabled, but show
all of your records, go to Data | Filter | Show All.

You
can also clear all of your filters in one fell swoop by turning off AutoFilter.
This also removes the AutoFilter arrows from your columns. To do this, go to
Data | Filter | AutoFilter. This removes the checkbox next to the AutoFilter
option.

Up next

With
these two topics under your belt, we’ll move on to subtotals next week after
which you’ll learn how to create graphs and use Excel’s PivotTables feature.