top of page
Excel.png
Lesson 4: Formatting a Worksheet

 Topic A: Apply Text Formats

 Topic B: Apply Number Formats

 Topic C: Align Cell Contents

 Topic D: Apply Styles and Themes

 Topic E: Apply Basic Conditional Formatting

 Topic F: Create and Use Templates

Lesson 5: Printing Workbooks

 Topic A: Preview and Print a Workbook

 Topic B: Set Up the Page Layout

 Topic C: Configure Headers and Footers

Lesson 6: Managing Workbooks

 Topic A: Manage Worksheets

 Topic B: Manage Workbook and Worksheet Views

 Topic C: Manage Workbook Properties

 Microsoft Excel 2016 Basics (Level I)

 

Microsoft Excel is a tool that helps you analyze, share, and manage information more effectively.  It can be used for financial analysis, listing large amounts of data for manipulation or to do a variety of calculations on any type of numerical data.  There are many ways to work in Microsoft Excel, many of which go un-noticed.  This course will help you learn the basics of setting up a spreadsheet, different ways to layout your data, and navigate through it.  Discover many of the techniques you can use with Microsoft Excel.

​

Course Content – Part I                                                                   

 

Lesson 1: Getting Started with Microsoft Office Excel 2016

 Topic A: Navigate the Excel User Interface

 Topic B: Use Excel Commands

 Topic C: Create and Save a Basic Workbook

 Topic D: Enter Cell Data

 Topic E: Use Excel Help

Lesson 2: Performing Calculations

 Topic A: Create Worksheet Formulas

 Topic B: Insert Functions

 Topic C: Reuse Formulas and Functions

Lesson 3: Modifying a Worksheet

 Topic A: Insert, Delete, and Adjust Cells, Columns, and Rows

 Topic B: Search for and Replace Data

 Topic C: Use Proofing and Research Tools

​

Course Content – Part II
Excel 1
Microsoft Excel 2016 Intermediate (Level 2)

 

You already know how to get Excel to perform simple calculations and how to modify your workbooks and worksheets to make them easier to read, interpret, and present to others. But, Excel is capable of doing so much more. To gain a truly competitive edge, you need to be able to extract actionable organizational intelligence from your raw data. In other words, when you have questions about your data, you need to know how to get Excel to provide the answers for you. And that's exactly what this course aims to help you do.

 

Course Content – Part I                                                              

​

Lesson 1: Working with Functions

 Topic A: Work with Ranges

 Topic B: Use Specialized Functions

 Topic C: Work with Logical Functions

 Topic D: Work with Date & Time Functions

 Topic E: Work with New and Legacy Text Functions

Lesson 2: Working with Lists

 Topic A: Sort Data

 Topic B: Filter Data

 Topic C: Query Data with Database Functions

 Topic D: Outline and Subtotal Data

Lesson 3: Analyzing Data

 Topic A: Create and Modify Tables

 Topic B: Apply Intermediate Conditional Formatting

 Topic C: Apply Advanced Conditional Formatting

​

Lesson 4: Visualizing Data with Charts

 Topic A: Create Charts

 Topic B: Modify and Format Charts

 Topic C: Use Advanced Chart Features

Lesson 5: Using PivotTables and PivotCharts

 Topic A: Create a PivotTable

 Topic B: Analyze PivotTable Data

 Topic C: Present Data with PivotCharts

 Topic D: Filter Data by Using Timelines and Slicer

Excel 2
Course Content – Part II
Microsoft Excel 2016 Advanced (Level 3)

​

Clearly, you use Excel a lot in your role. Otherwise, you wouldn't be taking this course. By now, you're already familiar with Excel 2013, its functions and formulas, a lot of its features and functionality, and its powerful data analysis tools. You are likely called upon to analyze and report on data frequently, work in collaboration with others to deliver actionable organizational intelligence, and keep and maintain workbooks for all manner of purposes. The ability to collaborate with colleagues, automate complex or repetitive tasks, and use conditional logic to construct and apply elaborate formulas and functions will put the full power of Excel right at your fingertips. The more you learn about how to get Excel to do the hard work for you, the more you'll be able to focus on getting the answers you need from the vast amounts of data your organization generates.

​

Course Content – Part I                                                           

 

Lesson 1: Working with Multiple Worksheets and Workbooks

  Topic A: Use Links and External References

  Topic B: Use 3-D References

  Topic C: Consolidate Data

Lesson 2: Using Lookup Functions and Formula Auditing

  Topic A: Use Lookup Functions

  Topic B: Trace Cells

  Topic C: Watch and Evaluate Formulas

Lesson 3: Sharing and Protecting Workbooks

  Topic A: Collaborate on a Workbook

  Topic B: Protect Worksheets and Workbooks

Lesson 4: Automating Workbook Functionality

  Topic A: Apply Data Validation

  Topic B: Search for Invalid Data and Formulas with Errors

  Topic C: Work with Macros

​
Lesson 5: Creating Sparklines and Mapping Data

  Topic A: Create Sparklines

  Topic B: Map Data

Lesson 6: Forecasting Data

  Topic A: Determine Potential Outcomes Using Data Tables

  Topic B: Determine Potential Outcomes Using Scenarios

  Topic C: Use the Goal Seek Feature

  Topic D: Forecasting Data Trends

Lesson 7: Appendix Topics (Time Permitting)

  Using PowerPivot

  Working with Forms and Controls

Excel 3
Course Content – Part II
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.

​

Course Content – Part I                                                            

 

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

Course Content – Part II
VBA
bottom of page