Maximize Excel: Error Prompts

CPE Credit: 2 hours

In this excellent presentation by Excel expert David Ringstrom, CPA, you’ll learn how to create dynamic accounting reports for any month of the year on a single worksheet. Step-by-step, David explains how to use Excel functions, including VLOOKUP, OFFSET, and SUM, to quickly create accounting reports that allow you to switch to any reporting period with just two mouse clicks. He also illustrates how to export data from your accounting package, improve the integrity of your spreadsheets, incorporate Check Figures and Alarms into you work, and more.
 
David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in Excel 2016. He draws your attention to any differences in Excel 2013, 2010, or 2007 during the presentation as well as in his detailed handouts. David also provides an Excel workbook that includes most of the examples he uses during the webcast.



Topics Covered

• Modifying queries that return data from other sources into an Excel worksheet.
• Exploring the nuances of data exported from accounting programs, such as extraneous worksheets, blank columns, and extraneous rows.
• Using Conditional Formatting to draw attention to reports that don’t balance to the source data.
• Returning data to an Excel spreadsheet from Microsoft Query.
• Using the Query Wizard within Microsoft Query to choose the relevant columns from your accounting report export.
• Creating an in-cell list by way of Excel’s Data Validation feature.
• Adding a refinement to the SUMIFS function to allow the report to consolidate numbers or display departmental values.
• Building and exporting a 12-month Profit & Loss report from QuickBooks Online and QuickBooks Desktop as a model for what to look for when exporting from other applications.
• Updating the financial report to use data for a new calendar or fiscal year.
• Extracting data dynamically for a given month or year to date by way of the OFFSET function.
• Using the SUMIFS function to sum values for a single account or a range of account numbers.
• Seeing how to use the Trusted Document feature in Excel 2010 and later to suppress the Data Connection security prompt.

Learning Objectives:

• Identify a variety of Excel functions—all focused on helping you use Excel far more effectively.
• Define the best format for getting accounting data to Excel from an accounting package.
• Recall how to use the OFFSET function to return data for a specific month within a range of 12 columns.

Course Number: 
DR1934
NASBA Field of Study: 
Computer Software and Applications
Level: 
Intermediate
Presenter:
David Ringstrom
Prerequisites: 
Experience Working in Excel
Advanced Preparation: 
None