Course ID: EXCLBOOT

Excel Boot Camp for CPAs – Comprehensive Learning

About & Customize

Popular Options

  • In-Person
  • Live Webcast

  • Experience hands-on learning and gain a thorough understanding of how to: Protect your work in Excel
  • Troubleshoot for worksheet calculation errors
  • Apply a wide variety of data analysis tools and features significant to CPAs
  • Compare and contrast which data analysis tool applies best to a particular set of data
  • Incorporate techniques to consolidate and link worksheets
  • Effectively use business graphics to present data
  • Use a wide variety of Excel functions for financial and other calculations, test data for accuracy, use IF and VLOOKUP variations
  • Effectively link Excel data to Word
  • Use macros to automate repetitive steps for data cleanup and reporting

  • Day 1 Course Highlights:
  • Learn proven multiple worksheet budget design techniques
  • Create a variety of formulas and functions and use new structured referencing and formula autocomplete
  • Use the time-saving ribbon tools
  • Add tools not on the Ribbon and customize the quick access toolbar
  • Use time-saving keyboard shortcuts
  • Change the appearance of worksheet data by using business formatting techniques
  • Save repetitive steps with the Format Painter and also the repeat key
  • Learn to quickly select and copy only visible cells
  • Use border line combinations and features for separating lines for financial reports
  • Create new tools and macros for format combinations
  • Group worksheets to take action on more than one worksheet at the same time such as restructuring worksheets or adding page setup dialog box settings
  • Evaluate the advantages and disadvantages of paste, paste as picture, embedded objects and paste link Excel data to Word or PowerPoint
  • Link a range from Excel to Word that expands/contracts as rows and columns are added/removed from the source Excel range
  • Learn when embedded Excel objects could cause a data breach if emailing a Pivot Table
  • Learn how to break links
  • Use the Excel camera tool to create interactive updating linked pictures within Excel
  • Understand list design rules to take advantage of the many time-saving data analysis features
  • Use the new Table features for manipulating lists and running automatic subtotals
  • Compare and contrast the use of the Table range vs. a cell range when building a Pivot Table
  • Incorporate worksheet data into bar, pie and SparkLine charts using new features
  • Incorporate quality graphics following new business graphic guidelines
  • Save a graph as a reusable template for future graphs
  • Learn when to add a secondary axis for presenting dissimilar types of data such as revenue and profit % in the same graph
  • Explore the chart variations of Sparklines, waterfall, panel, heat, and bullet
  • Use the shortcut tips to duplicate and connect shapes for flowcharts
  • Create a digital dashboard with conditional formatting graphics and clickable objects
  • Use many important advanced functions for IF testing, nested functions, decision making functions and VLOOKUP
  • Use Conditional Formatting to identify data patterns in color
  • Sort or filter by background color of cells based on conditional formatting
  • Quickly find duplicates in data
  • Test data for accuracy and cleanup data using the many functions in the categories of: Information, Date and Text
  • Use Text to Columns to import text or .csv files
  • Learn tips to compare two Excel files for changes/differences

    Day 2 Course Highlights:

  • Protect worksheet cells, sheet tabs and files
  • Use cell Range Naming techniques and Range Name references
  • Use the New Name Manager dialog box for quick cell/range identification and formula building
  • Use new multiple criteria functions: SUMIFS, AVERAGE IFS, COUNTIFS
  • Suppress the display of Excel error messages in cells with IFERROR or AGGREGATE
  • Use Formula Auditing tools to trace formula dependencies across single worksheets, multiple worksheets and multiple files
  • Use the Watch Window to continuously view calculations on multiple worksheets and linked files
  • Use the Data Validation feature to control user input for values in min/max ranges, percentages or selections from lists
  • Group and Outline according to formula structure or worksheet structure
  • Create and repair external formula links between files
  • Control calculation of external links with new user prompt controls
  • Move linked files and keep the links working
  • Use 3-dimensional Sum, Average, Count functions across sheets
  • Consolidate across dissimilar worksheets with the Pivot Table multiple consolidated ranges feature
  • Add hyperlink range and file references to cells
  • Use Pivot Tables to analyze data by category with subtotals
  • Learn Pivot Table tips for recalculation and duplicating Pivot Tables according to specific categories
  • Create a Pivot Table report of unique values, codes or categories from data
  • Create a calculated field in a Pivot Table
  • Create percentages or running totals in a Pivot Table
  • Use the drill down feature to explore underlying sets of data
  • Turn off the drill down and pivot cache features to avoid a data breach
  • Understand the pivot cache and how to protect your data
  • Use Excel commands to create multiple Pivot Tables automatically according to filters
  • Use Pivot Table options for automatic recalculation
  • Create a Pivot Table of multiple consolidated ranges using ranges from separate worksheets
  • Use the new Pivot Table slicers to filter results easily
  • Understand what an ODBC connection is to an external database to run queries and Pivot Tables from Excel to a database
  • Experience loading a Power Pivot data model
  • Learn automation in Excel by recording your manual steps into macro examples for printing, formatting and data cleanup
  • Understand the variety of ways to run macros
  • Create new Quick Access Toolbar tools for macros
  • Create a Personal Macro Workbook of favorite macros to run on any worksheet
  • Automate creating pdf files from Excel
  • Work through examples including cleaning up QuickBooks or other reports
  • Learn how to tweak/edit the Visual Basic code for macro automation steps to repeat
Designed For

CPAs and Professionals at all levels of an organization needing: 1) a comprehensive working knowledge of Microsoft Excel at an intermediate level, and 2) a hands-on class that is paced to their needs.

Field of Study
Computer Software & Applications
Prerequisites

Fundamental knowledge of Excel

Course Producer
Business Learning Institute
CPE Credits
12
Level
Intermediate
Available Formats
In-Person, Live Webcast
Types
Seminars

This course, like all courses in the BLI catalog, is customizable for your organization. Format, length, and content customizations are available.

To get started, contact BLI:
(888) 777-7077
team@blionline.org