Excel Training
Excel Training
EXCEL Level 1 (Basic) – EXCEL 2013, 2016, 2019, 365
Target Student:
This course is for those who are new to Microsoft Excel and want to gain confidence navigating the interface, using functions and formulas, and formatting their work to present it clearly.
Course Length: 6 hours (1 day)
Learning Objectives:
In this course, you will create, edit, display, and print Excel workbooks using new features and custom interface tools.
• Enter, edit, and format data to create workbooks
• Enter data using time-saving tools like Flash Fill and Autocomplete
• Use functions and formulas to calculate data
• Understand Relative and Absolute Cell References
• Format worksheets and cells
• Manage larger workbooks
• Print workbooks
• Customize the interface by changing ribbon display options and customizing the Quick Access Toolbar
Course Outline:
Section 1: Exploring the User Interface
• Backstage View
• Ribbon Overview
• Quick Access Toolbar
• Status Bar
• Shortcut Key Tips
• Contextual Tabs
Section 2: Creating, Opening and Saving Workbooks
• What is Excel?
• Create a New Blank Workbook
• Open Excel Files
• Save Workbooks
• Navigate a Workbook
• Selections
• Help
Section 3: Working with Data
• Basic Data Types
• Enter, Edit and Delete Data
• Use Autocomplete and Autofill
• Resize Columns and Rows
• Insert and Delete Columns, Rows, Cells
• Hide and Unhide Columns and Rows
• Use Undo, Redo and Repeat
• Spellcheck and AutoCorrect
• Use Find, Replace and Go To
• Cut, Copy, Paste and Paste Special
• Use the Fill Handle
Section 4: Performing Calculations
• Use Basic Formulas
• Use Basic Functions
• Understand Absolute and Relative Cell References Section
5: Formatting A Workbook
• Work with Fonts and Font Attributes
• Apply Borders and Shading
• Use the Format Painter
• Change Text Alignment
• Wrap Text • Apply Manual Line Breaks
• Rotate Text
• Merge and Centre Text
• Create and Modify Styles
• Apply Number Formats
• Clear Formatting Section
6: Managing Large Workbooks
• Rename Worksheets
• Colour-code Worksheet Tab Colours
• Copy, Move, Delete, and Group Worksheets Work with Freeze Panes Section
7: Printing and Viewing a Workbook
• Work with Print Preview and Page Layout View
• Change Margins and Page Orientation
• Scale to Fit Worksheets
• Insert Headers and Footers
• Print Titles Across All Pages
• Work with Page Breaks
• Review Print Option
EXCEL Level 2 (Intermediate) - EXCEL 2013, 2016, 2019, 365
Target Student:
This course is for individuals who are familiar with the basics of Excel and who wish to learn additional functions and tools to organize and analyze their data more fully.
Course Length: 6 hours (1 day)
Learning Objectives:
In this course, you will create advanced formulas, utilize and maintain database records, utilize Named Ranges in your formulas and visualize your data in Charts.
• Navigate efficiently around your worksheets
• Work with Named Ranges
• Use, date, text functions and IF, VLOOKUP and XLOOKUP Functions
• Display relationships between formulas and cells using formula auditing tools
• Organize worksheet data with tables
• Sort and filter data
• Visualize data with charts
Course Outline:
Section 1: Working with Range Names
• What are Range Names?
• Apply Range Names using the Name Box
• Editing Range Names
• Using Range Names in Formulas
Section 2: Using Specialized Functions
• Function Categories
• The Excel Function Reference
• Function Syntax
• Function Entry Dialog Boxes
• Using Nested Functions
• Automatic Workbook Calculations
Date, Time and Text functions
Section 3: Analyzing Data with Logical and Lookup Functions
• The IF Function
• The LOOKUP Function
• The VLOOKUP and HLOOKUP Function
• The XLOOKUP Function
Section 4: Auditing Worksheets
• The Trace Cells Feature
• Show and Hide Formulas
• Resolve Errors in Formulas
• Using the Watch Window
Section 5: Organizing Worksheet Data with Tables
• What Are Tables?
• The Create Table Dialog Box
• The Table Tools – Design Contextual Tab
• Table Styles
• Table Modification Options
Section 6: Sorting and Filtering Data
• The Difference Between Sorting and Filtering
• Sorting Data
• Filtering Data with Autofilters
• Creating Advanced Filters
• Filter Operators
• Removing Duplicate Values
Section 7: Using Subtotal and Summary Functions
• SUBTOTAL Functions
• The Subtotal Dialog Box
• Summary Functions
Section 8: Visualizing Data with Charts
• Chart Types
• Create Charts
• Resizing and Moving the Chart
• Switching Between Rows and Columns
• Chart Elements
• Format the Chart with a Style
• Create Custom Chart Templates
EXCEL Level 3 (Advanced) - EXCEL 2013, 2016, 2019, 365
Target Student:
This course is for users who are familiar with Excel and who wish to expand their knowledge and use of advanced data analysis and time-saving features.
Course Length: 6 hours (1 day)
Learning Objectives:
In this course you will combine, analyze, and display data using Excel’s powerful features, and learn the basics of how to use macros to further extend Excel’s capabilities
• Use Data Validation to restrict data entry and create dropdowns
• Save documents as a template to make sure they always look and work the same way
• Use outlining tools to group and subtotal your data
• Import and export data
• Perform a What-If analysis by creating scenarios and using data analysis tools such as Goal Seek and Solver
• Record and manage macros
• Edit basic macros using the Visual Basic Editor
• Link and consolidate data
• Analyze data with Pivot Tables and visualize with PivotCharts
• Visualize data with Sparklines and Conditional Formatting
Course Outline:
Section 1: Enhancing Workbooks
• Comments
• Hyperlinks
• Watermarks
• Background Pictures
• Updating Workbook Properties
• Preparing a Workbook for Multiple Audiences
• Add Alternative Text to Objects
• Modify Worksheets Using the Accessibility
Checker Manage Fonts
• Managing Themes
• About Themes
• Customize Themes
• Creating and Using Templates
• Create a Template
• Modify a Template
• Adding Data Validation Criteria
• Data Validation
• The Data Validation Dialog Box
Section 2: Working with Multiple Workbooks Linking Cells in Workbooks
• Understand External References
• Link Cells Link between worksheets and
workbooks
Consolidating Data
• Data Consolidation
• The Consolidate Dialog Box
Section 6: Analyzing Data with Pivot Tables, Slicers, and Pivot Charts
• Creating a Pivot Table
• Pivot Tables
• The Create Pivot Table Dialog Box
• The Pivot Table Fields Pane
• Summarize Data in a Pivot Table
• The “Show Values As” Functionality of a Pivot Table
• Create a Calculated Field
• Refresh the Pivot Table Data
• Add Style to a Pivot Table
• Work with Subtotals and Grand Totals
Introduction to PowerPivot
• Filtering Pivot Table Data
• Use the Field Headers
• Use the Pivot Table Dialog Box
• Use Slicers
• Analyzing Data with Pivot Charts
• Create Pivot Charts
• Filter with Pivot Charts
Section 3: Analyzing Data Create Scenarios
• What is a Scenario?
• The Scenario Manager Dialog Box
• Perform A What-if Analysis
• Add-in Types
• Goal Seek Feature
• The Solver Tool
• Perform A Statistical Analysis with the Analysis Toolpack
• Add and Review the Analysis Toolpack
Section 4: Importing and Exporting Excel Data
• The Export Process
• The Import Process
• The Get External Data Group
• Delimited Text Files
• Methods of Importing Text Files
• Publish as Web Page
Section 5: Automating Worksheet Functionality Creating and Editing a Macro
• What are Macros?
• The Record Macro Dialog Box
• Name Macros
• Visual Basic for Application
• Copying Macros Between Workbooks
• Macro Security Settings
• Difference between a Relative and Absolute Macro
Section 7: Presenting Data Visually
• Conditional Formatting
• What is Conditional Formatting?
• The Conditional Formatting Rules Manager
Dialog Box
• Using formulas in conditional formatting
• Using Sparklines
• What is a Sparkline?
• Types of Sparklines
• The Sparkline Tools – Design Tab