top of page
Top

Microsoft Excel Classes

​

Microsoft Excel: Getting Started with Microsoft Excel Formulas & Functions

3 Hours

​

  • Understanding the user interface

  • Customizing the user interface

  • Navigating a spreadsheet quickly

  • Entering and editing text

  • Using Backspace vs. Delete while editing

  • Deleting data

  • Modifying cell alignment

  • Moving and copying data

  • Using Undo and Redo

  • Using the Autofill handle to copy or extend a series

  • Separating and formatting text with FlashFill

  • Creating formulas

    • Adding, subtracting, multiplying and dividing

    • Using cell references and range references

  • Creating Basic Functions

    • SUM

    • MAX

    • MIN

    • AVERAGE

    • COUNT

    • COUNTA

  • Using AutoFill to copy formulas

  • Understanding the Order of Operations

  • Finding and replacing information on a spreadsheet

  • Understanding relative and absolute references

  • Displaying formulas on a spreadsheet

  • Formatting a spreadsheet

    • Changing fonts

    • Formatting numbers as currency

    • Centering text across columns

    • Adding headers and footers

    • Changing the orientation of the page

    • Applying borders

    • Scaling a spreadsheet to fit on a page

    • Centering a spreadsheet on a page

  • Printing a spreadsheet

​

Top

​

Microsoft Excel: Advanced Functions

3 Hours

It is highly recommended you take Microsoft Excel: Basic Formulas & Functions or have equivalent training or experience with Microsoft Excel.

​

  • Learn to use the following functions:

    • IF

    • HLOOKUP, VLOOKUP, XLOOKUP

    • SUMIF

    • COUNTIF

    • INDEX

    • MATCH

    • SUMPRODUCT

    • PMT

    • OFFSET

    • ROUND

    • UPPER, LOWER, PROPER

    • ISERROR

    • ISTEXT

    • ISNUMBER

    • STDEV

    • TRIM

    • AVERAGE, MEDIAN, MODE.MULT, MODE.SNGL

    • RANK.AVG, RANK.EQ

    • LARGE, SMALL

  • Separating text into multiple columns

 

Top

​

Microsoft Excel: Useful Features in Microsoft Excel

3 Hours

It is highly recommended you take Microsoft Excel: Basic Formulas & Functions or have equivalent training or experience with Microsoft Excel.

 

  • Applying a password to open or modify a spreadsheet

  • Allowing users to edit ranges with passwords

  • Using named ranges for navigation purposes and creating formulas

  • Creating custom lists

  • Creating custom views

  • Creating scenarios

  • Using the Format Painter

  • Applying conditional formatting to one or more cells

  • Creating and using styles

  • Using Paste Special

  • Printing titles

  • Validating data with data validation

  • Using Goal Seek

  • Using Solver to optimize a result

  • Creating one and two way Data Tables

​

Top

​

Microsoft Excel: Charting, Linking, Embedding and Working with Multiple Sheets

3 Hours

It is helpful to have taken Microsoft Excel: Basic Formulas & Functions or have a basic familiarity with Excel.

​

  • Creating Charts

    • Selecting the data to include in a chart

    • Creating a chart with a single keystroke

    • Moving a chart

    • Resizing a chart

    • Modifying characteristics of a chart

      • Changing bar colors

      • Changing the color of the background

      • Deleting gridlines

      • Formatting gridlines

      • Formatting text

      • Formatting values

      • Adding text to a chart

    • Creating a chart template​

    • Using new chart types - Treemap, Sunburst, Histogram, Box and Whisker, Waterfall, Funnel, People Graph

  • Linking

    • Creating a link using an equation

    • Creating a link using Paste Special

    • Linking from Microsoft Excel to Microsoft Word

  • Embedding

    • Embedding an Excel spreadsheet inside a Word document

  • Working with Multiple Sheets

    • Renaming sheets

    • Changing the color of tabs

    • Adding new sheets

    • Deleting sheets

    • Setting the default number of sheets in a new workbook

    • Changing the order of sheets

    • Using Group Mode to modify several sheets at the same time

    • Creating equations using data from different sheets

  • Creating and using hyperlinks

 

Top

​

Microsoft Excel: Working with Microsoft Excel Databases

3 Hours

It is helpful to have taken Microsoft Excel: Basic Formulas & Functions or have a basic familiarity with Excel.

 

  • Splitting windows

  • Freezing panes

  • Navigation techniques

  • Sorting a database

  • AutoFiltering a database

  • Working with tables

    • Creating tables

    • Formatting tables

    • Creating total rows

    • Adding calculated columns

    • Using Slicers

    • Adding new fields

  • Using advanced filters

  • Subtotaling a list

  • Creating database functions - DSUM, DAVERAGE, DCOUNT, DCOUNTA, DMAX, DMIN, DGET

  • Creating PivotTables and PivotCharts

    • Rules for setting up a database

    • Modifying the format of Calculated Fields

    • Creating Calculated Items

    • Creating user-defined Calculated Fields

    • Using Slicers and Timelines

    • Creating Dashboards

    • Connecting Slicers to multiple PivotTables

    • Creating PivotCharts

    • Automatically adding new records to the source of data for the PivotTable

    • Refreshing PivotTables manually, upon file open and upon activating a sheet

 

Top

​

Microsoft Excel: Recording Macros and Creating Forms

3 Hours

It is helpful to have taken Microsoft Excel: Basic Formulas & Functions or have a basic familiarity with Excel.

 

  • Recording Macros

    • Recording a macro

    • Using absolute and relative references when recording a macro

    • Running macros from the macro dialog box

    • Running macros using a button created by the user

    • Running macros using shortcut keys

    • Creating a macro button on the Quick Access Toolbar

    • Creating a custom menu item on the toolbar

    • Viewing the VBA code

    • Making minor modifications to the VBA code

    • Assigning shortcut keys to existing macros

  • Creating Forms

    • Adding and moving labels

    • Turning off gridlines

    • Adding combo boxes

    • Adding option buttons

    • Adding check boxes

    • Adding list boxes

    • Adding scroll bars

    • Adding spinners

    • Creating a report with the results

​​

Top

​

Microsoft Excel: Microsoft Excel Shortcuts, Tips, and Tricks

3 Hours

This course is most useful for people who already use Excel but feel that there must be a faster way to get things done.

​

  • Using AutoFill to extend a series

  • Using Flash Fill to separate, join or format text and numbers

  • Entering the current date and time with the keyboard

  • Entering information into several cells at the same time

  • Selecting an entire column

  • Selecting an entire row

  • Selecting the entire sheet

  • Selecting a block of information

  • AutoFit a column

  • AutoFitting an entire spreadsheet

  • The Mini Toolbar

  • Entering formulas into multiple cells at the same time

  • Finding formulas and finding constants

  • Displaying all the formulas in a spreadsheet at the same time

  • Quickly navigate to different sheets

  • Quickly navigate to a specific sheet

  • Comparing values in a row or column to the first value in the row or column

  • Transposing data

  • Erasing cell contents with the mouse

  • Changing cell orientation

  • ®, ™ and © symbols

  • Putting frames on pictures

  • Copying equations down as far as there is data in the column next to it

  • Copying down and copying right

  • Moving down one screen or one screen to the right

  • Creating a new worksheet

  • Scrolling quickly

  • Navigating to a specific cell quickly

  • Changing the default number of worksheets in a new file

  • Copying formatting from one location to one or more locations

  • Entering fractions

  • Adding and removing borders with the keyboard

  • Drawing perfect objects

  • Navigating and selecting a block of data

  • Freezing the first row or column of a list

​

Top

 

Microsoft Excel: Introduction to Microsoft Excel

6 Hours

​

  • Understanding the user interface

  • Customizing the user interface

  • Navigating a spreadsheet quickly

  • Entering and editing text

  • Backspace vs. delete

  • Deleting data

  • Modifying alignment

  • Moving and copying data

  • Undo and Redo

  • Using the Autofill handle

  • Creating formulas

    • Adding, subtracting, multiplying and dividing

    • Using cell references and range references

  • Creating Basic Functions

    • SUM

    • MAX

    • MIN

    • AVERAGE

    • COUNT

    • COUNTA

  • Using AutoFill to copy formulas or extend a series

  • The Order of Operations

  • Finding and replacing information on the spreadsheet

  • Understanding relative and absolute references

  • Displaying formulas on a worksheet

  • Formatting a spreadsheet

    • Changing fonts

    • Formatting numbers as currency

    • Centering text across columns

    • Adding headers and footers

    • Changing the orientation of the page

    • Applying borders

  • Printing a spreadsheet

 

Top

​

Microsoft Excel: Intermediate Microsoft Excel

6 Hours

 

  • Working with named ranges

  • Inserting and formatting graphics

  • Creating charts

    • Modifying charts

    • Formatting charts

    • Changing chart types

  • Creating calculations across sheets

  • Using more advanced functions

  • Splitting windows

  • Freezing panes

  • Working with tables

    • Creating tables

    • Formatting tables

    • Creating total rows

    • Adding calculated columns

    • Using Slicers

    • ​Adding new fields

  • Creating PivotTables and PivotCharts

    • Rules for setting up a database

    • Modifying the format of Calculated Fields

    • Creating Calculated Items

    • Creating user-defined Calculated Fields

    • Using Slicers

    • Creating Dashboards

    • Connecting Slicers to multiple PivotTables

    • Creating PivotCharts

    • Automatically adding new records to the source of data for the PivotTable

    • Refreshing PivotTables

  • Working with Themes

    • Applying Themes

    • Creating your own Theme

  • Working with Templates

  • Understanding Excel options

 

Top

 

Microsoft Excel: Advanced Microsoft Excel

6 Hours

 

  • Applying a password to open or modify a spreadsheet

  • Allowing users to edit ranges with passwords

  • Applying conditional formatting

  • Using data validation to restrict data entry

  • Creating drop-down lists

  • Auditing a spreadsheet

    • Displaying formulas

    • Tracing precedents and dependents

    • Creating a Watch Window

    • Evaluating formulas

    • Using automatic and manual calculation options

  • Creating sparklines

  • Using the Scenario Manager

  • Using Goal Seek

  • Creating custom lists

  • Creating custom views

  • Creating one way and two way Data Tables

  • Removing duplicates

  • Using the Analysis ToolPak

  • Linking cells (DDE)

    • Creating a link using an equation

    • Creating a link using paste special

    • Linking from Microsoft Excel to Microsoft Word

  • Embedding programs (OLE)

    • Embedding an Excel spreadsheet inside a Word document

 

Top

​

Microsoft Excel: Excel Visual Basic for Applications (VBA)

12 Hours over 2 Days

This class is a programming class. Students should be proficient in using Microsoft Excel before taking this class.

 

  • Chapter 1 - Getting started

    • Introducing Excel VBA

    • Macros

    • The Developer Ribbon

    • Recording a macro using Absolute and Relative references

    • Recording an Absolute Macro

    • Running a macro using the ribbon

    • Running a macro using a shortcut key

    • Creating an icon on the Quick Access Toolbar to run a macro

    • Adding a command button to run a macro

    • Recording a Relative Macro

    • Viewing the Visual Basic for Applications (VBA) code

    • Editing a macro in the VBA Editor     

    • Understanding the development environment

    • The Object Browser   

    • Using Visual Basic Help

    • Closing the Visual Basic Editor

  • Chapter 2 - Working with procedures and functions

    • Understanding modules

    • Creating a Standard Module

    • Understanding procedures    

    • Creating a Sub Procedure

    • Calling Procedures from inside other procedures

    • Using the Immediate Window

    • Creating a Function Procedure

    • Naming procedures   

    • Working with the Code Editor

    • Colors used in coding

    • Using capitalization while coding     

    • Setting Code Editor Options

    • Code settings

    • Window settings

    • Guidelines for editing code

    • Commenting code

    • Finding code 

    • Complete Word feature

Chapter 3 - Understanding objects

  • Understanding objects

    • Navigating the Excel Object Hierarchy

    • Understanding collections

    • Using the Object Browser

    • Working with properties

    • Using the With Statement

    • Working with methods

    • Creating an Event Procedure

  • Chapter 4 - Using expressions, variables, and intrinsic functions

    • Understanding expressions and statements

    • Declaring variables

    • Explicit vs. implicit variable declaration

    • Working with Declaration Statements

    • Working with variable scope

    • Naming variables

    • Understanding Data Types    

    • Creating an Assignment Statement

    • Using intrinsic functions

    • Understanding constants

    • Using intrinsic constants

    • Using Message Boxes

    • Using Input Boxes

    • Declaring and using Object Variables

  • Chapter 5 – Controlling program execution

    • Understanding control-of-flow structures

    • Working with Boolean expressions

    • Comparison Operators and Logical Operators (Keyword Operators)           

    • Using the If…End If decision structures

    • If…Then Statements

    • If…Then…Else Statements

    • Using the Select Case…End Select structure

    • Using the Do…Loop structure

    • Using the For…To…Next structure

    • For…Next Statements

    • Using the For Each…Next structure

    • Guidelines for use of control-of-flow structures

  • Chapter 6 - Working with forms and controls

    • Understanding UserForms

    • Using the Toolbox

    • Working with UserForm properties, events and methods

    • Understanding controls

    • Setting control properties in the Properties Window

    • Working with the Label Control

    • Working with the Text Box Control

    • Working with the Command Button Control

    • Working with the Combo Box Control

    • Populating a control

    • Working with the Frame Control

    • Working with Option Button Controls

    • Working with control appearance

    • Setting the tab order

    • Adding code to controls

    • Launching a form in code

  • Chapter 7 - Working with the PivotTable object

    • Understanding PivotTables

    • Creating a PivotTable

    • Working with the PivotTable Wizard method

    • Working with PivotFields

    • Assigning a procedure to a custom toolbar

  • Chapter 8 - Debugging Code

    • Understanding errors

    • Using debugging tools

    • Setting Breakpoints

    • Stepping through code

    • Using Break Mode during Run Mode

    • Determining the value of expressions

  • Chapter 9 - Handling errors

    • Understanding error handling

    • Understanding VBA’s error trapping options

    • Trapping errors with the On Error statement

    • Understanding the Err object

    • Writing an error-handling routine

    • Working with inline error handling

 

Top

Microsoft Excel Outines Top
Microsoft Excel Advanced Functions
Microsoft Excel: Useful Features in Microsoft Excel
Microsoft Excel: Charting, Linking, Embedding and Working with Multiple Sheets
Microsoft Excel: Working with Microsoft Excel Databases
Microsoft Excel: Recording Macros and Creating Forms
Microsoft Excel: Microsoft Excel Shortcuts, Tips, and Tricks
Microsoft Excel: Introduction to Microsoft Excel
Microsoft Excel: Intermediate Microsoft Excel
Microsoft Excel: Advanced Microsoft Excel
Microsoft Excel: Excel Visual Basic for Applications (VBA)
bottom of page