Conrad Carlberg is the president of Network Control Systems, Inc., the recipient of several Microsoft Most Valuable Professional awards, and the author of Excel 2000: The Complete Reference and Excel Sales Forecasting for Dummies. He lives in San Diego.

Excel for Accountants

By Conrad Carlberg

CPA911 Publishing, LLC

Copyright © 2007 CPA911 Publishing, LLC
All rights reserved.
ISBN: 978-1-932925-01-2

Contents

Cover,
Title Page,
Copyright,
Acknowledgments,
From the Author,
Download the Example Files,
Chapter 1: Using Lists in Excel,
Chapter 2: Pivot Tables,
Chapter 3: Common Sizing Using Worksheets,
Chapter 4: Charting,
Chapter 5: Tools for Accountants,
Chapter 6: Scenarios In Excel,
Chapter 7: Payment Functions,
Chapter 8: Excel’s Depreciation Functions,
Chapter 9: Excel and QuickBooks,
Appendix A: From the Web to Excel,
Index,

CHAPTER 1

Using Lists in Excel

Defining a List

Sorting Lists

Filtering lists

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:

=C3+C11+C20+C15

You find yourself pointing-and-clicking at cells and ranges instead of using something quick and simple like this:

=SUM(C2:C6)

List Layout

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:

=SUM(

Then, click the D at the top of column D, and press enter. Your formula will now look like this:

=SUM(D:D)

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