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
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
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
Lists and Tables
Vs. Advanced Filter
Chapter 2: Excel’s Pivot Tables
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
for Common Sizing
Common Sizing Income Statements
Other Uses of Common Sizing
Preparing Comparative Reports from QuickBooks
Chapter 4: Charting
Charts that Show Time Series
Pivot Charts Vs. Standard Charts
Budget Variances Over Time
Chapter 5: Tools for Accountants
What Tools do
Building Custom Lists
Using the Macro Recorder
Using Keyboard Shortcuts
Chapter 6: Scenarios In Excel
Defining an Implicit Intersection
Scenario Management: More Tools
Goal Seek and Solver
Scenarios: A Summary
Chapter 7: Payment and Depreciation Functions
Excel’s Depreciation Functions
The Family of Depreciation Functions
Chapter 8: Exchanging Data Between Excel and QuickBooks
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