Workplace Productivity - Microsoft 365 Excel Fundamentals, Efficiency, Charts and Pivot Tables
Certification

The Great Canadian Training Workplace Productivity - Microsoft 365 Excel Fundamentals, Efficiency, Charts and Pivot Tables Certification is an immersive, hands-on program designed to elevate your Excel skills and boost your career. Through 27 hours of live instructor-led training, activities, and exercises, you'll master everything from essential formulas and data visualization to advanced Pivot Table techniques and dynamic dashboards.

What sets our certification apart is our Capstone Certification Project. In your profession your boss will never hand you a multiple choice questionnaire about Excel or lead you in a step-by-step quiz on how to do something in Excel. In the real world you will be given data or ideas that you then need to generate something useful out of. That’s what our Capstone Certification Project is designed to test. You’ll be given relevant data and details and be asked to apply what you’ve learned in this course to turn it into something beneficial. You’ll draw on all the skills you’ve learned, from the modest ability to format cells to the ability to dynamically pivot data and create beautiful visualizations. Your completed project will be submitted to the trainer for review and if you’ve successfully applied what you’ve learned you’ll receive your certification.

DAY 1 (6 hours) Instructor-Led  

This is a comprehensive module designed to equip participants with essential Excel skills, covering key areas such as customizing the Quick Access Toolbar, creating and managing formulas, visualizing data with charts, and performing advanced data management and analysis using Pivot Tables. Participants will gain practical experience in organizing lists, using functions, and enhancing data presentation through various Excel features and tools. 

Unit 1– Excel Essentials

Customizing the Quick Access Toolbar 

Understanding Rows, Columns and Cells 

Properly Clearing Cell Data 

Using Excel’s Fill Handle

Unit 2– Formulas and Functions

Creating a Basic Formula 

Understanding Order of Operations 

Working with Absolute and Relative Cell References 

Creating and Editing Named Ranges 

Introduction to Basic Functions (SUM, AVERAGE, SUMIF, COUNTIF) 

Using the AUTOSUM Feature 

Using the Function Library 

Working with Advanced Functions (IF, XLOOKUP) 

Working with Date Functions (TODAY, NOW)

Unit 3– Visualizing Data with Charts 

Building Charts 

Understanding Chart Terminology and Concepts 

Modifying and Formatting Charts 

Editing Chart Source Data 

Working with Chart Elements 

Changing Chart Types 

Using the Switch Row/Column Feature 

Adding Graphics to Charts 

Unit 4– Data Management and Analysis 

Organizing Lists with Excel Data Tables 

Analyzing Data with Pivot Tables

Working with Data Validation

Activities and Exercises: 

Build a data table from scratch, apply validation rules, and add a dropdown list

Create formulas using SUMIF and XLOOKUP, then use formula auditing to trace errors

Analyze a dataset using Pivot Tables and convert it into a Pivot Chart

DAY 2 (6 hours) Instructor-Led  

Microsoft Excel includes lots of fabulous time-saving techniques that can increase productivity, efficiency and comfort as you work on your spreadsheets. Day 2 and 3 will focus on improving your efficiency using these methods. In Day 2 you will become more comfortable with the interface and navigate and modify your spreadsheets with simple clicks on the keyboard or handy mouse techniques. Make full use of Excel’s powerful Fill Handle, insert multiple rows at once and insert rows and columns with one tap of the keyboard. Set your list back to its original sort order, duplicate your worksheet with a simple drag-and-drop, update values on the spot with Paste Special and much more.

Unit 5– Using Excel Efficiently - Part 1

Update Values On the Spot Using Paste Special

Find and Highlight All of Your Formulas & Functions In An Instant

Become More Efficient By Using Excel's Fill Handle to the Fullest Extent

Convert Your Formulas to Values with a Quick Drag-and-Drop

10 Magical Ways to Use Flash Fill

Hide Cells in Plain Sight!

Insert Screenshots into Excel

Quickly Transpose Blocks of Data

Make Excel Talk to You

Insert Multiple Rows All At Once

Insert Rows and Columns with a Click of the Keyboard

Organize Your Workbook By Colour-Coding Your Sheet Tabs

Efficiently Create Column Headings

Look Like a Math Expert By Using Excel's AutoCalculate Feature

Add the Excel Calculator to Your Quick Access Toolbar

Set Your List Back to its Original Sort Order

Input Values That Start with Leading Zeros

Copy Multiple Items to the Office Clipboard

Bullet Points in Excel? Yes, You Can!

Duplicate Your Worksheets with a Simple Drag-and-Drop

Great Keyboard Shortcuts!

Activities and Exercises: 

Use Flash Fill to extract names and patterns from messy data

Duplicate worksheets and reset sorted lists to their original order

Update values using Paste Special with real-time scenarios

DAY 3 (6 hours) Instructor-Led  

Building on the techniques learned in Day 2, Day 3 finds you learning some of the more advanced tips, including how to create multi-tiered data validation drop-down lists and how to increase the power of your IF statements with wildcards. This module also covers how to make your conditional formatting more flexible with check boxes and drop-down lists and how to add creativity and readability by including pictures and smoothing out lines. You will even learn how to put together a Gantt chart in Excel!

Unit 7– Using Excel Efficiently - Part 2

Create Multi-Tiered Data Validation Lists

Make Your Conditional Formatting More Flexible with Checkboxes and Drop-Down Lists

Use Conditional Formatting to Compare Lists

Use Wildcards in Your IF Statements

Now That You Know How to Use CountIF, Let's Try SUMIF!

Create Charts with a Keystroke

Get Creative – Add Pictures to Your Charts

Gantt Charts in Excel!

Smooth Those Lines in Your Line Charts!

10 Useful Pivot Table Tricks

Insert and Work with a Spreadsheet Inside Your Word Document

Copy Only Visible Cells from Your Subtotaled Data

Enter Data into Visible Cells Only in a Filtered List

Generate a Unique List Using Advanced Filters

Become More Efficient by Creating Autofill Lists

Create Custom Date Formats

Activities and Exercises: 

Create a Gantt chart from a task list using conditional formatting

Build a two-level drop-down list and use checkboxes to control formatting

Practice 10 advanced Pivot Table tricks, including filtering and layout hacks

DAY 4 (6 hours) Instructor-Led  

Excel offers a large number of charts and graphing tools that complement its data analysis toolset. This module will introduce you to the key principles of data visualizations as well as explore Excel’s custom tools like formatting options and custom templates. From there we’ll review over 20 different chart types like Pies, Columns, and Line Charts as well as more advanced options like Histograms, Waterfalls, and even Geospatial Heat Maps.

Unit 8– Mastering Charts and Graphs

Understand the Key Principles of Data Visualizations

Learn Data Visualization Best Practices

Customize Charts in Excel

Chart Elements, Layouts, Colours, and Styles

Chart Formatting Options

Change Chart Types

Adjust Axis Options

Explore the Differences in the Effectiveness of Excel Chart Types

Create, Modify, and Apply Chart Templates

Master Basic Charts and Graphs

Bar and Column Charts

Line Charts and Trendlines

Combo Charts

Pie and Donut Charts

Histograms and Pareto Charts

Area Charts

Scatter Plots

Bubble Charts

Box and Whisker Charts

Tree Maps and Sunburst Charts

Waterfall Charts

Radar Charts

Funnel Charts

Stock Charts

Heat Maps

Surface and Contour Charts

Geospatial Maps (Power Map Introduction)

Sparklines

Activities and Exercises: 

Create and format 6+ types of charts (bar, pie, waterfall, heat map, etc.)

Adjust axes, apply custom layouts, and test various data visualization styles

Build a mini dashboard using sparklines and templates

DAY 5 (3 hours) Instructor-Led  

Excel is a powerful program, beautifully designed so that you can simplify your use and analysis of large amounts of data. This module is designed to unlock the full power of Excel's Pivot Table capabilities, guiding you from foundational concepts to advanced analytical techniques. You'll gain a strong understanding of how to organize data properly, build dynamic Pivot Tables and Charts, and apply tools like XLOOKUP, calculated fields, and slicers to analyze and visualize complex information with clarity and precision. Through hands-on activities, you'll develop practical skills that make your data work smarter—streamlining decision-making and uncovering deeper insights.

Unit 9– Introduction to Pivot Tables

What is a pivot table?

Benefits of using pivot tables

Understanding proper data structure

Overview of the pivot table interface

Understanding pivot table terminology

Unit 10– Pivot Table Layout and Design

Choosing the right fields for your pivot table

Understanding pivot table layouts

Rearranging and reorganizing the pivot table

Formatting a pivot table

Customizing pivot table design

Unit 11– Basic Pivot Table Functionality

Sorting and filtering data

Grouping data

Drilling down into data

Refreshing a pivot table

Unit 12– Advanced Pivot Table Operations

Combining data from multiple sources using vlookup/xlookup

Linking data from different sources using relationships

Using conditional formatting in a pivot table

Creating and editing calculated fields

Pivot table options and field settings

Unit 13– Working with Slicers and Pivot Charts

Creating different types of pivot charts

Modifying pivot charts to suit your needs

Using slicers to interact with a pivot table

Creating and customizing slicers

Using slicers to dynamically filter pivot table data

Activities and Exercises: 

Create a Pivot Table and use slicers to filter data dynamically

Add a calculated field to show YoY growth

Merge external data sources using XLOOKUP and analyze results via Pivot Chart