Microsoft Excel Classes
-
Microsoft Excel: Getting Started with Excel Formulas & Functions
-
Microsoft Excel: Charting, Linking, Embedding and Working with Multiple Sheets
-
Microsoft Excel: Microsoft Excel Shortcuts, Tips, and Tricks
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
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
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
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
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
-
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
-
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
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
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
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
-
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
-