Read an Excerpt
Excel for Accountants
By Conrad Carlberg
CPA911 Publishing, LLCCopyright © 2007 CPA911 Publishing, LLC
All rights reserved.
Using Lists in Excel
Defining a List
Managing a List
Using the Data Form
You put data into an Excel worksheet so that you can chart it, or analyze it, or get its total, or turn it into a report, or for any of a dozen different reasons. But you don't do it for fun. So if you're serious about it, you should know how to organize the data: how to lay it out, how to label it, when to keep it separate from other information, how to edit it, and so on.
Defining a List
Wearing my Excel consultant hat, I've seen some pretty strange ways of laying out data on a worksheet. Granted, the people who designed those worksheets had what they thought were pretty good reasons for their layouts. Sticking totaling rows into the middle of what is in effect a database probably seemed like a good idea at the time.
Still, we all encounter worksheets with strange arrangements of data. One that you've probably run across occurs when someone pastes an existing report from an accounting package into a worksheet. A layout that works well for a report can be spectacularly useless as the basis for an analysis or a chart. See Figure 1-1 for an example.
Suppose you wanted Excel to show you the total of the figures in Column C for Towels plus those for Tablecloths in the Northwest region. You'd have to create a formula like this one:
You find yourself pointing-and-clicking at cells and ranges instead of using something quick and simple like this:
But you can use a simple formula like that one if you've set up your figures properly — and in Excel, that usually means in the form of a list. Figure 1-2 shows what an Excel list looks like.
The data you see in Figure 1-2 is arranged so that it's easy to total. For example, to get the total of the sales dollars, just type this in a blank cell outside column D:
Then, click the D at the top of column D, and press enter. Your formula will now look like this:
It happens that the arrangement of data shown in Figure 1-2 conforms to the requirements of an Excel list:
Each row represents a different transaction. (In this case, anyway. You could also use the list to keep track of your kangaroos; in that case, each row would represent a different kangaroo.)
Each column represents a different variable (or field, which is just another term for the information you're putting in the column).
Each column is headed by the name of the variable.
That's it. If your data conforms to the requirements bulleted above, you have a list. And Excel agrees with you.
What does a list buy you? Quite a bit, in fact:
Some things that you'd like to use Excel for just can't be done without data structured as a list; this chapter discusses a couple of examples and you'll find more in later chapters.
Other things can be done without a list, but only with more effort, sometimes a lot more effort. The SUM situation discussed in the prior section is a good example.
Still, a list is not really a formal structure in Excel. An Excel workbook has all kinds of formal structures — things that you use and manipulate — such as worksheets, charts, menus, cells, rows and so on. These structures are built into what's called the Excel object model.
And you won't find an object like a list in that object model. It's informal, and yet something with a list structure is needed if you want to:
Create a pivot table or pivot chart
Use the Data Form
Use Excel's AutoFilter
Use tools like Histogram and Moving Average
Copy data from a worksheet and paste it into a true database (and vice versa)
Look up a value in a table (for example, a commission rate based on product sold and revenue recognized)
... as well as do other Excel tasks.
So if you set up your data as described in this section, you'll make your life with Excel a lot easier — starting with sorting the data.
Excel enables you to sort on up to three keys simultaneously. This means that you might sort a company's costs first on cost center, then on vendor within cost center, and then on invoice date within cost center and vendor. Each of these (in the example, cost center, vendor and date) is what is usually termed a key.
Sorting on One Key
More frequently, though, you find yourself sorting on just one key. The next few figures show how this works.
The list in Figure 1-2 started out sorted by Region, then by Product, and then by Date Sold. Suppose you wanted to change the sorting priority, and sort the information solely by Date Sold. You can ignore Region and Product, and get your date-only sort by taking these steps:
1. Select any cell in the Date Sold column. (This step isn't a necessary one, but it can make things easier in Step 3.)
2. Choose Sort from the Data menu. The dialog box shown in Figure 1-3 appears.
3. Start by selecting only one sort key. Because you started in Step 1 by choosing Date Sold, that's the field that Excel proposes as the sort key.
4. Click OK.
With the records sorted in ascending date order, earliest to most recent, you can sensibly use a chart to show how revenues have moved over time. See Figure 1-5.
Sorting on Multiple Keys
In practice, you don't often find a reason to sort on more than one key at a time. One reason is that you seldom need to look at the order of records in secondary keys. Another is that tools such as pivot tables do a better job of grouping records than pure sorting does.
But Excel lets you sort on as many as three keys, so here's a look at how you might sort first on Region, then on Product, then on Date Sold. Figure 1-6 shows the unsorted records.
You start the three-sort-key process the same way that you do the one-key process:
1. Select a cell in the column that you want to sort on first; in this example, that's the Region column.
2. Choose Sort from the Data menu.
3. Accept Region as the field in the Sort By drop-down.
4. Click the second drop-down (refer back to Figure 1-3), the first of the two that are labeled Then By. Choose Product from the dropdown list.
5. Click the third drop-down (again, refer back to Figure 1-3), and choose Date Sold from the drop-down list.
6. Click OK.
As you see in Figure 1-7, the full list has been sorted first by Region; then, within each value of Region, by Product; and finally, within each combined value of Region and Product, by Date Sold.
In management accounting situations, sorting on more than one key usually occurs when you want a report distributed in sections. In the example shown in this section, a CFO might want to show sales by date, within product — just as it was sorted in the last set of steps — but have a different report distributed to each regional sales manager. The best way to handle that is to do the three-key sort just as shown above, and then to split the report physically into regions with page breaks.
Unsorting a List
There's one particular popular report layout that makes me nuts. It uses a blank line to separate one category from the next. Even though they may be helpful when it comes to reading a report, I can't stand dealing with those blankety-blank blanks when it comes to analyzing or charting the data. They do nothing but get in the way. Figure 1-8 has an example.
I use Excel to sort the blank rows out of the data, like this:
1. Type Row into cell E1. This isn't necessary but it helps keep things clear.
2. In E2 enter 2. In E3 enter 3.
3. Select E2:E3. Put your mouse pointer over the Fill Handle in the lower right corner of E3, hold down the mouse button, and drag down into the final row (In Figure 1-8, that's row 23). You now have a new field, named Row, in your list.
4. Sort the list, using Sales (or another field or fields) as the sort key. The field to choose is one that has a value in all rows other than the ones that are blank in all columns.
5. You'll find that the blank rows have been sorted to the bottom. In Excel sorts, blank values come after everything else.
6. Delete the blank rows at the bottom. You can do that in two steps, just by dragging through their row headers and choosing Edit -> Delete. (But do this only if you feel compulsive about it; the blank rows are now out of the way, so you can forget about them.)
7. Re-sort the remaining records according to the values in the Row column. This puts the list back in its original order.
When you filter a list in Excel, you're saying that there are some records that you don't want to pay attention to for the time being. You want Excel to leave them out of an analysis (for example, it ignores them when you use the SUM function), or omit them from a chart, or not show them on the worksheet at all.
It's important to keep in mind that phrase in the prior paragraph, "for the time being." In most cases you can filter some records to ignore them temporarily, and then when you're ready you can bring them back into play.
There are a couple of different approaches to filtering in Excel; one is simple and virtually automatic, and the other is a little more complicated and you have to stay involved with what's going on.
AutoFiltering Excel Lists
In keeping with this chapter's main topic, you'll find that it's easier to filter records that are in a list than records that are just hanging around the worksheet. Take a look at Figure 1-9.
Because the report is set up as an Excel list, it's very easy to focus on just a subset of records. Take these steps:
1. Select any cell in the list.
2. Choose Data -> Filter -> AutoFilter.
3. Drop-down arrows appear at the head of each column, adjacent to each list header.
4. Click the drop-down arrow for the field you want to use for filtering, and choose the value that you want to keep. (Other options like Top Ten are discussed later in this chapter.)
That's it. If you chose, for example, Q2 in the Quarter field, Excel displays only those records in the list that have Q2 in Quarter's column.
Figure 1-10 suggests how AutoFilter does its work: the records that don't conform to the filter that you specify are hidden. Their rows are modified to have a height of zero — this is the same as selecting a row or rows and then choosing Format -> Row -> Hide.
Using AutoFilter Options
Figure 1-11 is Figure 1-9, repeated here for convenience. The AutoFilter has already been selected and you see the options available from the Column D dropdown.
Apart from the first three items in the drop-down list, there are only individual values. As you saw earlier, you can select one of those values to filter out all records that do not have that value in Column D.
The other three items work as follows:
(All) ... This option restores all the original records in the list: it unfilters them.
(Top Ten) ... This option lets you select an ordered subset of the records.
(Custom) ... This option enables you to specify more complex filtering criteria than simply choosing a particular value in the list.
If you select the Top Ten option, you next see the dialog box shown in Figure 1-12.
You have the following options:
The drop-down on the left, where Top is now selected, also lets you choose Bottom.
The box in the center, where 10 is now selected, lets you supply any number between 1 and 500.
The drop-down on the right, where Items is now selected, also lets you choose Percent.
What you get depends in part on what's in the list:
If all the items in the column are text (for example, the letters A through H and the word Management) then selecting Top Ten has no effect.
If the items in the column are a mix of numeric and textual values, then only the numeric values will be shown by the Top Ten option.
The number of items that the filter can display can be no greater than the number of numeric values in the column. For example, if the column contains 1, 2, 3 and D, choosing Top 10 and specifying 5 values will display 1, 2 and 3 only.
If you choose to display Percent instead of Items, you will get the top (or bottom) X Percent in the list, subject to the limits on text values and maximum counts I just mentioned.
Entr'acte: Deciding to Use AutoFilter
AutoFilter is a convenient way to look at a subset of your list, and it's quick to set up. The drop-down makes it easy to focus on records that have a particular value in a column. But as a practical matter, you're probably not going to have much use for more advanced AutoFilter features, such as Custom filtering.
As you'll see in the next section, it's entirely feasible to use AutoFilter's Custom criteria to focus on, say, office revenues for 2005 in the Northwest region. But setting up AutoFilter to do that takes a little time, and when you subsequently want to look at office revenues for 2005 in the Southwest region, you'll have to rebuild your Custom filter. That extra time and effort tends to defeat the purpose of something intended to be a handy tool.
It's much easier to use Advanced Filter or, better yet, a pivot table to do that kind of analysis. Both are based on lists, just like AutoFilter, and they take about as long to set up. But once you've done the setup, you have the results you want — to continue the present example, you have office revenues for each year in each region — and you don't need to rebuild anything when your interest shifts from one region to another.
So: think of AutoFilter as a quick-and-dirty way of getting a look at a subset of data: it's great for ad hoc peeks into a list. But even though Excel provides the tools to make it more complex, resist the temptation. You'll save a lot of time and energy in the long run, and you can save your best swear words for Microsoft itself, rather than its innocent products.
Now that's off my chest, I can move on the AutoFilter's Custom filter with a clear conscience, knowing that you'll skip it if you want to.
Using the Custom Criteria
With a list set up and the AutoFilter drop-downs visible, click one of the drop-downs and choose Custom from the drop-down list. You see the dialog box shown in Figure 1-13.
There are twelve operators you can choose from in the left hand drop-downs. We won't enumerate them here (if you're interested, you can check by clicking one of the drop-downs) beyond mentioning that they include:
Is less than
Is greater than
You can do the same with another field in your list, and then the custom criteria act as though they were connected by an and. Therefore, you could set up a custom criterion for the Region field that calls for Region to equal "Northwest" and one for the Product field that calls for Product to not equal "Towels." That would give you sales in the Northwest for products other than towels.
And when you wanted to look at sales of towels in the Southeast, you do it over again, changing the Region and Product custom criteria.
Using Advanced Filters Instead of AutoFilter
There are some things you can do with Excel's Advanced Filters that you can't do with AutoFilter. Here they are:
Copy the filtered list to some other place on the worksheet.
Show unique records only. This can be helpful if you want to find out if you have duplicate records (the filtered list will normally be shorter than the original) but it won't help you find the duplicates.
Use complex criteria to filter the list. As you've already seen, AutoFilter gives you selection criteria to work with, but the Advanced Filter's criteria can be more sophisticated.
Getting More from Advanced Filter
One of the strongest points of the Advanced Filter is the ability to filter unique records only. Suppose that these are the names of regions as they exist in your list:
Notice that there are two instances of Southeast. Although you can't see it here, one of them is followed by a blank space. Apparently, someone mistyped the name of the region, or some really esoteric glitch took place inside the black box.
You wouldn't know that this had happened — that is, an extra version of Southeast sneaking into your list — without either poking around for one, or using a tool such as Advanced Filter (or pivot tables) that highlight the presence of an erroneous value. Using Advanced Filter, you arrange for a shorter list consisting of the original list's unique values.
Here's how to filter for unique values in your list:
1. Select a cell in the list.
2. Choose Filter from the Data menu.
3. Choose Advanced Filter from the Filter menu.
4. In the Advanced Filter dialog box (see Figure 1-14) verify the List Range address.
5. Click the Copy to Another Location option button. The Copy to box becomes enabled.
6. Click in the Copy to box, and then click in the cell where you want the filtered list to begin.
6. Fill the Unique Records Only checkbox.
7. Click OK. You'll get the result shown in Figure 1-15.
Excerpted from Excel for Accountants by Conrad Carlberg. Copyright © 2007 CPA911 Publishing, LLC. Excerpted by permission of CPA911 Publishing, LLC.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.