Training

Since the inception of GabrAria, professional Excel training has always been a part of my business strategy.  In 2009 I successfully completed Fred Pryor Seminars' Trainer Certification course.  I conducted a fair number of public Excel seminars for Fred Pryor in 2010.   However, as the application development business picked up, I had less time to devote to teaching Excel seminars with Fred Pryor.  Recently I have developed a suite of five full-day Excel training courses that I have taught in many cities around the United States.  Additionally, I have worked with clients to create customized training agendas that pull specific topics of interest to create a training program tailored to their organization's needs.   Since founding GabrAria I have taught Excel seminars in the following locations (some of them several times):

  • Atlanta
  • Boston
  • Chicago
  • Clearwater
  • Dallas
  • Des Moines
  • Jackson
  • Kansas City
  • Lancaster (CA)
  • Las Vegas
  • Long Beach
  • Midland/Odessa
  • New York
  • Oxnard
  • The Quad Cities
  • San Francisco
  • Shreveport
  • St. Louis
  • Washington DC

Target User Level

I do not offer any type of introductory Excel training.  If you or your organization needs basic, entry-level training there are many organizations that offer this, including Fred Pryor Seminars.  The training I offer is targeted towards finance, marketing, logistics and other professions that use Excel intensely.  The training is designed to take users that already have a good grasp of Excel's fundamentals, and have at least tried out some of the more advanced features, and move these users up to the next level of Excel ability.  For recommendations regarding prerequisites to a GabrAria training course please refer to the information further down this page regarding the specific course or courses you are interested in.

Target User Levels for GabrAria Excel Training
Target User Levels for GabrAria Excel Training


Areas of Training Available

To be considered truly proficient in Excel, a user should be skilled in the six areas of the application shown in the diagram below and GabrAria has full-day training progams ready to go for each topic with the exception of Analytical Tools, and Charting.  Those two topics combine to fill one full day of training.   Click on any circle in the diagram below to jump to more details about the items included in that topic's training.  In addition to the five full-day courses shown below, I also have a half-day training curriculum that focuses specifically on dashboarding in Excel.

Diagram of Training Areas productivity analytics charting onecourse formulas pivottables vba
Click on a circle to jump to information on that topic.

Productivity and Efficiency Booster Course

Recommended Prerequisites
  • Should be able to write basic formulas
  • Should be able to do basic formatting like changing fonts, fills, borders, alignment.
  • Should at least know VLookup() exists, even if they aren't a master at it.
  • Should have some familiarity with number formatting
  • Should know how to do basic sorting and filters.
Topics Included in the Course
  • Power User Tips & Tricks
    • Rules for Tables & Formatting Strategies
    • Quick Movement
    • Quick Selection
    • Preselecting Before Entering Data
    • Arranging your work area
    • Autofill & Formatting Tricks
    • Comments
    • Theme Formatting
    • Fonts / Wrapping / Centering
    • Number Formatting
    • Copying Down into Empty Cells
  • Customizing Your Excel Work Area
    • The Quick Access Toolbar (QAT)
    • The Ribbon
    • The Status Bar
  • Auditing Tools & Tricks
    • Reconciling
    • Error Tracing
    • Speak Cells
  • Named Ranges
    • Creating
    • Using
    • Autocreating
  • Formulas
    • Order of Operations
    • Creating 3-D Formulas
    • Solving Common Problems with VLookup()
    • Logical Breaks
    • Tricks with Dates
  • Power Editing
    • Hot Keys
    • Drag & Drop
    • Find & Replace
    • Go To Special...
    • Paste Special…
    • Show Formulas
    • Fill Across Sheets
    • Floating Minibar
    • Moving & Copying Sheets
  • Arranging Data
    • Grouping & Ungrouping
    • Non-standard Sorting
    • Non-standard Filtering
    • Custom Views
  • Data Validation
    • Types
    • Levels of Warning
    • Messages
  • Instructor's Favorite Shortcuts & Formatting Tips
  • Printing
  • Headers & Footers
    • The three views at the bottom right
    • Controlling Page Breaks
    • Gridlines (debugging aid)
    • Comments
  • File Operations
    • New versus New
    • Templates
    • Saving a Workspace
    • Opening a Workbook at Startup
    • Changing # of Recent Files Shown
    • Changing # of Sheets in New WB
  • Protection
    • Worksheet Protections
    • Workbook Protections
  • Shapes
    • How to Change
    • Make a Hyperlink
    • Z-Order
    • Changing the Shape of a Comment
    • Grouping / Ungrouping

Analytics + Effective Charting & Data Visualization Course

Recommended Prerequisites
  • Should be able to create the basic chart types: column, bar, line, pie
  • Should know how to alter various chart properties like fill colors, axis labeling, legend position, etc.
  • Should be able to create embedded charts (charts that sit on a worksheet) and chart sheets (charts in a sheet by themselves).
  • Should have used conditional formatting, or at least played with it a bit.
  • Should know what a histogram is, even if it's been a while since they last created one.
  • Helpful if have used named ranges and table objects.
Topics Included in the Course
  • Dynamic Charts
    • List or Table
    • Dynamic Named Ranges
  • Interactive Charts
    • Combobox Control
    • Listbox Control
    • Option Buttons
    • Checkboxes
    • Data Validation
    • Scroll Bars
  • Data Tables
  • Scenario Manager
  • Goal Seek
  • Solver
  • Histogram
  • Good Charting & Charting Concepts
    • Why Chart
    • Data Layout
    • Effective Charts
  • Formatting
    • XY versus Category
    • Blank Cells
    • Formatting Guidelines
    • Chart Types
      • Good
      • Okay
      • Poor
    • Stacked vs. Clustered
    • Bar versus Column
    • Combo Charts
    • Dual-Level Axis Charts
  • Non-chart Data Visualization Techniques
    • In-Cell Bar Charts Using Text
    • Floating (scatter) Using Text
    • Wingdings et al
  • Comparing Sparklines and Tiny Charts
  • New Conditional Formatting Tools
  • How to Create Some Common Advanced Charts
    • Waterfall Chart
    • Gantt I
    • Gantt II
  • Dashboards
    • Design Precepts
    • Static Versus Interactive
    • Construction Process
    • Examples

Advanced Formulas Course

Recommended Prerequisites
  • Should know of named ranges and hopefully have used some.
  • Should know the difference between absolute references versus relative references ($A$1 versus $A1 versus A$1 versus A1).
  • Should have used VLookup() in a variety of workbooks and have encountered some of the more common VLookup() problems.
  • Should be comfortable with all the basic functions like SUM(), AVERAGE(), MIN(), MAX()
  • Should have written formulas that resulted in errors.  (Not intentionally, mind you; but if you haven't made some mistakes, you're not trying hard enough.)
  • Helpful if have used some other functions like If(), SumIf(), AverageIf(), Count(), CountA(), Date(), NetWorkDays()
  • Helpful if have at least once accidentally created a circular reference.
Topics Included in the Course
  • R1C1 notation versus A1 notation
    • Review / Explanation
    • How to Turn On / Off
    • Using when Inserting Rows
    • Using to Edit Non-Uniform Formulas
  • Review of Index() & Match
    • Index()
    • Match()
  • Advanced Applications for Index(), Match(), Large() & Small()
    • Wildcarding
    • % of Group's Subtotal
    • Creating a Filtered List
    • Fetching Top/Bottom N Values
  • Power Tricks with Lookup() & Match()
    • Last Value / Last Position
    • Fetching the Second from the Last Value
  • A Deeper Look at the SubTotal() Function
  • Text / String Functions
  • Named Ranges / Named Formulae
    • A Quick Review of Abs & Rel, Naming Constants & Naming Formulas
    • Understanding Scope
    • Referencing Options
    • Common problems or issues when naming ranges/formulae
    • A suggested naming protocol to use when defining Names
  • A Review of Rounding Options
    • Round() vs. RoundUp() vs. RoundDown() vs. MRound()
    • Ceiling() vs. ISO.Ceiling() vs. Floor()
    • Int() vs. Trunc()
    • Odd() & Even() and DollarDe() and DollarFr()
    • Understanding the impact of the Precision As Displayed setting
    • A quick word on FPE's
      • How to Round Times
  • Understanding Structured References in Tables
    • Columns / Fields
    • The # qualifiers
    • Combining [[#],[C]]
    • Copying or Moving Worksheets with Tables
  • Advanced Data Validation
    • Cascading
    • Formulas
  • Comparing new IFs() functions and SumProduct for Multiple-Criteria calculations.
    • Overview of Boolean Arithmetic
    • SumIf(), AverageIf(), CountIf()
    • SumIfs(), AverageIfs(), CountIfs()
    • SumProduct() & Boolean math & coercers
  • Array Formulas
    • Overview of Arrays in General
    • Naming Array Constants
    • Transpose() & Index()
    • Ctrl + /
    • Why {...Row(Indirect("1:n"))…}
    • Examples of Useful Array formulae
  • Using Circular References on Purpose
  • Creating Mega-Formulas
  • Debugging - how to trace errors and other formula debugging techniques.
    • Dependents & Precedents
    • Error Tracing
    • Evaluating Formulas
    • Watches
    • Additional Tools in the Marketplace
      • Name Manager
      • Fast Excel
      • RefTree

Pivot Tables & Data Crunching Course

Recommended Prerequisites
  • Should have done basic pivot tables a fair number of times.
  • Should know how to summarize data by more than just SUM().
  • Should have played with the various Pivot Table formatting tools.
  • Helpful if have imported data into Excel from some other source - Text/CSV files, Access, other formats…
  • Should know macros exist and maybe even have recorded a couple.
Topics Included in the Course
  • A review of the Summarization Tools Possible
    • Sum, Count, Avg, Max, Min, StdDev
    • % of Col Total
    • % of Item
    • % Diff from Item
    • Diff from Item
    • Running Total
    • % Running Total
    • Rank
  • A review of the Formatting Options Available
    • Number Formatting
    • Table Formatting Options
    • Options on the Design Tab
    • Naming table, fields (Options tab)
  • Customizing Pivots
    • Empties
    • Autofit Columns
    • The VALUES summary in the Field List Pane
    • Fields Over/Down
    • Printing Options
  • Dynamic Named Ranges versus Tables
  • Sorting & Filtering
    • Sorting
    • Filtering
  • Grouping & PivotCaches
  • Calculated Fields & Calculated Items
    • Creating and Using Calculated Fields
    • Drilldown & Limitations
    • Calculated Items
    • Limitations on Calc'd Items
  • Pivot Charts
    • Formatting / Turning Filters On/Off
  • Page Filters versus Slicers
  • Synchronizing Pivot Tables
  • Consolidating Data
  • Connecting to External Data Sources
    • From Microsoft Access
    • From the Internet
  • Sharing via SkyDrive
  • PowerPivot
    • Locating & Installing the Add-In
    • The PowerPivot TAB in the Ribbon
    • The PowerPivot WINDOW
  • Using VBA to Enhance the Pivots You Share
    • Automating a Top/Bottom N filter
    • Toggling Between Values & Percents
    • Refreshing
    • Resetting
  • Walking Through an Example Month-End
    • Importing a new month's data
    • Fixing problems in the new month's data
    • Building a sample pivot from scratch
    • Appending the data and looking at how we defined the source
  • Making the Process Quicker & More Robust Using VBA
    • Splitting the Table for each Item off to a new workbook
    • Creating an E-mail for Each Split-Out

Automating Excel using VBA and Macros

Recommended Prerequisites
  • Should have recorded macros.
  • Should have tried to edit macros by hand.
  • Should have run into at least one error in execution where Excel threw up the "Error/Debug" dialog box.
  • Helpful if have used the MessageBox() and InputBox() functions
  • Helpful if know what an Event Handler is [like Workbook_Open() or Worksheet_Change()].
Topics Included in the Course
  • Object Oriented Programming Overview
    • The Excel Object Model
    • Methods versus Properties
    • Subroutines versus Functions
  • Macro Recording
    • Absolute versus Relative Referencing
  • Calling your macro
    • Button in Ribbon
    • Button on QAT
    • Button / Shape on WS
  • VBE environ
    • Code Panes
    • Immediate Window
    • Project Explorer
    • Object Browser
    • Properties Pane
    • Key Options
  • Macro Protection
    • Saving as XLSM
    • Trusted Locations
    • Signing Your Macros
  • Variables
    • Types
    • Scope
    • Arrays & Collections
    • Naming Conventions
  • The Range Object
    • Formulas
    • Changing Properties
    • Methods
  • Branching
    • If / Then / Else
    • Select Case
  • Looping
    • For / Next
    • Do While / Until
    • For Each
  • Debugging
    • Stepping Code
    • Setting Watches
    • Setting Breakpoints
  • Robust Programming
    • Never Select
    • Never assume default objects
    • Declare Library
    • Clear the Clipboard
    • Name Ranges when trapping events
    • Documenting
    • Avoiding Infinite Loops
    • Check Selection before using
    • Avoid "magic numbers" and "magic names/addresses"
    • The 64K soft limit
    • R1C1, not A1
    • Don't assume status when you "restore" a setting
  • Error Handling
    • On Error GoTo
    • The Error Object
    • On Error Resume Next
    • Conditional Continuation
  • UDF's
    • Volatile
    • Can't Change Cell Values
  • User Input & Output
    • Message Box
    • Input Box
    • Userforms
  • Event Programming
    • Explain top of code pane
    • Worksheet Events
    • Workbook Events
    • Application Events
    • Toggle Events / Avoiding Infinite Loops
  • As You Develop as a Developer
    • Personal Macro Workbook
    • Libraries

Training Philosophy & Structure

When mastering a new skill, there just isn't any substitute for reinforcement and practice.  If you have ever learned to play a musical instrument or to speak a foreign language, you know that it looks very simple and straightforward when the instructor is showing you how to play a piece of music or how to say a phrase in a foreign language.  It becomes a very different experience when suddenly it's the student's turn to exhibit the skills the instructor just demonstrated.  It's simply not enough to have an instructor up at the front of a classroom showing you Excel skill after Excel skill.  It is critical that those attending the class go through the exercises for themselves.

In the field of professional training for the workplace there are basically two teaching models: pay in the classroom or pay after the class is over.   The tradeoff is this - if you pay in the classroom you are going to reduce the amount of material you cover by 35% to 50% depending on the complexity of the topics being covered.   But when you leave the classroom there is a much greater chance you will be able to apply what was taught after you return to the office.  After all, you already did it once or maybe even twice during class!  Plus (and this is a very big "plus"!) if you go through the skill in class, should you happen to struggle a bit, you have the instructor right there to help you. 

The other training model says to cover as much material as possible in the time allotted.  Obviously this means you get exposed to anywhere from fifty percent more to almost double the amount of material.  However it is then incumbent upon each student to make time in his or her schedule to review and invent his own drills on an entire day's material to make sure he or she retains the information. The standard solution for those that use the "pay later" training model is to ask attendees spend additional funds to buy some sort of a learning tool that will serve as a guide for the review process.  

I am an advocate of the first school of thought when it comes to Excel training.  It has been my observation that after spending one or more days away from their desks taking Excel training, people return to the office and find an inbox overflowing with e-mails.  Plus there are usually dozens of other issues that came up while they were out which need to be handled. Scheduling time to review all of the concepts that they learned in Excel training gets pushed down the list.   A week or two flies by before they finally get around to trying to review what they learned in class.  By now they've forgotten much of what was said.  They try to practice the skill, but they're not quite sure what they wrote in their notes and they're pretty sure instructor said to do it such-and-such a way.  But when they try to do it the way they think the instructor said, it's not working.  And now there's nobody to ask for help.  Sound familiar?   It's not that I think it's terrible to recommend that attendees buy additional materials to help them truly master the skills learned at the seminar.  It's just that most people won't spend their own money to buy these resources and so the training ends up being a poor investment.  If attendees do not apply the skills while still in the classroom or - at the latest - one to three days after the class, then by the time one or two months has elapsed they have retained only a very small portion of skills that the instructor showed them in class.

Since I believe in the "pay in the classroom" school of training, all of my training materials have two key components which help you have a good grasp of the skills by the time you walk out the door at the end of the day.  First, I provide you with sample files of every concept we cover.  No paper workbooks - Excel workbooks.  This allows you to open the spreadsheets in Excel and examine the formulas or step through the VBA.   The entire day's material is indexed in the form of an agenda.  The agenda contains links to each Excel workbook used throughout the day.  And each workbook has a table-of-contents to allow you to quickly locate the examples you're seeking when you get back to the office and try to apply the skills you used in class. 

Second, each day's training contains two or three review sessions that force attendees to "circle back" and recall and re-apply the concepts that were covered earlier.  It is truly astounding how many students I have seen struggle to remember how to apply a concept just an hour or an hour and a half after first doing the exercise together in class.  If such a high percentage (easily 80%) of attendees struggle to recall all the information just 90 minutes after having already applied it once, what hope do those who attend the "practice later" seminars have?  Not only did they never actually do the skill in class, but they have to try and recall it days or weeks later using naught but a paper workbook.   I do not want GabrAria training customers to experience that degree of frustration!  I want them to be able to solve their problems, be more efficient and maybe even dazzle their coworkers after taking training from GabrAria.

So -- are you ready for some Excel training?!   If yes, use the Quote Request page to contact me and we'll see what we can do to help your organization climb up that Excel abilities scale!


Top