Excel for Accountants

Excel for Accountants

Author: Conrad Carlberg
Price: 36.95
ISBN: 978-1932925265
300 pages


With updated content on Excel 2007 and 2010 as well as new features on QuickBooks, this guide makes it easy for accounting professionals to manipulate and sort financial data. Comprehensive but concise chapters explain how to automate the entry of common business formulas; how to use pivot tables to extract details; how to develop inventory, depreciation, and financial summaries; and how to set up other standard financial calculations required for business plans, pro-formas, and complicated tax issues. Providing accountants with advanced skills to better serve their clients, this accessible reference also includes details on Excel lists, worksheets, and charts.

Excel for Accountants is a must have for accountants supporting clients using QuickBooks. The ability to create a wide variety of lists in Excel and export them to QuickBooks enables accountants to easily move clients from any existing platform to QuickBooks with a minimum of effort. The chapter on exchanging data between Excel and QuickBooks provides clear explanations, shortcuts, and tips to accomplish these tasks with ease.

Click here for an excerpt from Excel for Accountants: 2nd Edition.

Click here to review the Table of Contents from the Excel for Accountants: 2nd Edition.


When the First Edition of this book was written, in late 2006, I was still exploring a beta version of Excel 2007. A problem faced me and everyone else who was writing about Office applications at that time: whether or not the book's figures and instructions should take account of Excel 2007's radically different user interface, based on a new toolbar called the Ribbon.

Anyone who is reading this introduction, and who has been using Excel longer than a couple of years, knows that Excel 2007 – along with the other Office 2007 applications – dispensed with the menu structure that had been in use for roughly twenty years. Uncharitable users concluded that Microsoft had run out of substantive ways to improve the product, and that the only way left to churn the base was to change the user interface. A kinder interpretation was that there had to be a more intuitive way of grouping tasks than the old menu structure.

Even four years later the reaction in the user community remains mixed. An old friend of mine, one of the best known of the Excel cognoscenti and a member of the original group of roughly ten Excel MVPs, wrote me that he has come to like the Ribbon.

On the other hand, a professor of statistics remains so annoyed by the Ribbon that he much prefers to work with Excel 2003. (Neither of us thinks that the new "consistency" statistical functions in Excel 2010 are better than pointless, but that's another book.)

So the Ribbon still causes mixed feelings. Nevertheless, there have now been two versions of Excel that employ the Ribbon and I may as well recognize that if Microsoft sticks with it, so will you – if only because old computers wear out and new copies of Office must be acquired.

Therefore this Second Edition of Excel for Accountants couches all the discussions of Excel tasks in terms of either the Ribbon, when it's needed, or the shortcut menu that appears when you right-click a worksheet object such as a cell or a chart. I have tried where possible to direct your attention to shortcut menus, partly because they make it easier than the Ribbon does to get to what you want to do, and partly because many of the shortcut menu items are the same regardless of Excel version.

Some other changes that have found their way into the Second Edition of Excel for Accountants include:

Discussions of pivot tables that recognize the new terminology and interfaces that Microsoft has introduced. I also include ways in Excel 2010 to get your hands on older pivot table features such as the pivot table wizard that apparently went away with the traditional menus.

Tables in Excel 2010, which are a formal structure that Excel has been gradually moving toward since Excel 2003. Excel has always supported lists, but they have been, and remain, little more than an informal guide to data layouts. Tables are formal structures that resemble lists, but they have advantages that lists do not, including the ability to redefine themselves as new data becomes available. Tables have the potential to make your clients' worksheet structures much stronger.

Exporting comparative and common-sized reports from QuickBooks to Excel for more sophisticated analysis.

Fixes and enhancements to pivot charts that may convince you to occasionally use these visual analyses in preference to standard charts.

How to set up data validation in order to force your clients' staff to enter accurate data.

Brief Table of Contents for Excel for Accountants: 2nd Edition

Chapter 1: Using Lists and Tables in Excel

Defining a List

Sorting Lists and Tables

Filtering Lists and Tables

AutoFiltering in Excel

AutoFilter Vs. Advanced Filter

Using the Data Form

Chapter 2: Excel’s Pivot Tables

The Purpose of Pivot Tables
Getting Data Summaries
Building Pivot Tables
Too Much Information: The Data Cache
Using Named Ranges as Data Sources

Chapter 3: Common Sizing Using Worksheets

The Rationale for Common Sizing
Common Sizing Income Statements
Other Uses of Common Sizing
Preparing Comparative Reports from QuickBooks

Chapter 4: Charting

Why Charts?
Charts that Show Time Series
Pivot Charts Vs. Standard Charts
Budget Variances Over Time

Chapter 5: Tools for Accountants

What Tools do Accountants Need?
Built-in Lists
Building Custom Lists
Using the Macro Recorder
Using Keyboard Shortcuts

Chapter 6: Scenarios In Excel

About Scenarios
Creating Scenarios
Defining an Implicit Intersection
Scenario Management: More Tools
Goal Seek and Solver
Scenarios: A Summary

Chapter 7: Payment and Depreciation Functions

About Functions
Payment Functions
Excel’s Depreciation Functions
The Family of Depreciation Functions
SLN Function
Accelerated Depreciation

Chapter 8: Exchanging Data Between Excel and QuickBooks

About IIF Import Files
Format of an IIF File
Exporting Data into an IIF File
Creating Multiple Lists in One IIF File
Importing an IIF File into QuickBooks
IIF File Keywords for Lists
Profile Lists Import Files
Standard Lists Import Files
Updating Lists with Excel Import Files
Importing Adjusting Entries

span style=