Course ID: EXCLBOOT

Excel 2-Day Boot Camp for CPAs


CPAs who work with Excel every day will benefit from these topics and tips which include: protecting features, range naming, decision-making IF and VLOOKUP functions, financial functions, Pivot Table reporting, controlling calculation inputs, securing files, formula auditing, external formula linking, business graphics, linking Excel data and graphs to Word, many multiple worksheet and consolidation tips, time-saving usage of the macro recorder and Visual Basic tips. Even if you thought you knew it all in Excel, this course will advance your knowledge. Many immediately applicable tips are incorporated into this 2-day course. Participants will receive take-aways for after class use. Topics covered apply to Excel 2016, 2013, and 2010. (Format: “hands-on, instructor-led group training”)


LEARNING OBJECTIVES
  • 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

MAJOR TOPICS

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
Fundamental knowledge of Excel

PREREQUISITES
Fundamental knowledge of Excel

COURSE PRODUCER
Business Learning Institute

CPE CREDITS
16.0

LEVEL
Intermediate

This course is available to be scheduled as:

Group Live
 

Let's Roll!

To learn more or customize this course for your group, complete this form and a BLI team member will get back with you shortly.


Or, contact BLI: 888-481-3500 or learn@blionline.org