Action eBiz Inc. | Microsoft Office Training| Toronto Mississauga |

Microsoft Excel Training 2016, 2013, 2010, 2007

Toronto, Mississauga, GTA, SW Ontario

excellogo

 

With customized MS Excel training learn how to create formulas and work with data more efficiently. Your training is “hands on” where you create actual formulas & test them for errors (Not just listen to the theory) Work with your own data tables and organize them for easy sorting, filtering etc. Analyze data quickly and easily with pivot tables and much more. By working with your own data (or similar data) you will create formulas you can use to save time & money.

Below is a suggested list of topics but these can be modified to suit your needs.

Get information or a quote on Excel Training
Send us an email Call 905.271.1119

 

AGENDA

Go to Excel training level 1
Go to Excel training Level 2
Go To Excel training Level 3

Microsoft Excel Training Level 1 – Introduction

Topic 1: Microsoft Excel training basics

  • The Excel 2010, 2013 & 2016 Ribbon customizable interface
  • New features in Excel 2013, “Sparklines””Flash fill”
  • New features in Excel 2016, Sales Funnel Charts, Waterfall charts and more.
  • New Charts suggestions, Pivot Timelines, Apps for Excel and more

Topic 2: Formatting a worksheet in Microsoft Excel

  • Default General, Text, Number, Currency, Percentage formats etc.
  • Copying and pasting formats using the format painter (a very useful tool)
  • Conditional Formatting (New Features/Options in 2016 and 2013)
  • Adding comments & text boxes to worksheets

Topic 3: Entering data and navigating in a worksheet

  • Inputting numbers & Text
  • Editing cell contents after input
  • Quickly input a Date series
  • Navigation short cut keys (e.g. Control End: goes to last cell)

Topic 4: Creating Formulas & Functions in Microsoft Excel

  • Creating Excel formulas (SUM, AVERAGE, MAX, MIN etc)
  • New functions such as MAXIFS (Max with multiple criteria) are now possible
  • Editing & checking formulas & functions (New functions in 2013)
  • Using Excel Absolute references and when are they needed
  • Creating, defining named ranges (Defined Names) & why use them

Topic 5: Copy and Move Cells

  • Inserting/deleting entire rows and columns versus cells
  • (Copy cells or the entire worksheet)
  • Moving data Copying data (shortcut keys, Control + C etc.)
  • Using the Autofill feature to list days of the week
  • Adding Custom Lists

Topic 6: Printing worksheets

  • Spell checking in MS Excel
  • Print Preview and Page breaks
  • Examining print options

Topic 7: Multiple worksheet workbook in Excel

  • Creating a multiple-sheet workbook
  • Hiding worksheets

Click to go to MS Excel Training Formulas Level 1

Microsoft Excel Training Level 2 – Intermediate

Topic 1: Creating Excel Charts

  • Creating a new chart in MS Excel (labels & data)
  • Selecting Row or Column orientation
  • New “Sparkline Charts in Excel 2010, 2013 & 2016)

Topic 2: Modifying charts in Microsoft Excel

  • Selecting Chart types (Column, Bar, Line Charts etc.)
  • Modifying charts elements
  • Adding, modifying and/or deleting chart items
  • Moving and sizing chart items

Topic 3: Formatting Elements of a chart

  • Formatting chart text in MS Excel
  • Formatting the chart regions

Topic 4: Using graphic objects

  • Inserting graphic objects & AutoShapes
  • Modifying graphic objects
  • Using graphic objects to enhance worksheets and charts

Topic 5: Sorting data in Excel

  • Single-level sorting in MS Excel
  • Adding sorting levels
  • New sorting options: cell colour, font colour or icon
  • Sorting options for Custom Sort (a non-alphabetic sort)
  • Design considerations

Topic 6: Filtering data in Microsoft Excel

  • Creating a Filtered list
  • Add Custom criteria
  • Create multiple criteria
  • New filtering options: cell colour, font colour or icon
  • Managing a filtered list

Topic 7: Useful Summary Functions to save time

  • SUMIF to sum by label criteria (a legacy function)
  • SUMIFS Excel 2010 & 2013 sum by multiple criteria
  • The SUBTOTAL function creates formulas that you specify

Click to go to MS Excel Training Formulas Level 1

Microsoft Excel Training Level 3 – Advanced

Topic 1: Customizing your Work Area

  • Creating and saving Custom Excel Views
  • Saving and using Templates
  • Protecting Worksheets from unauthorized changes

Topic 2: Named Ranges in Formula Construction

  • Creating Names for cells (named ranges) – how they work in formulas in MS Excel
  • Best practices with named ranges
  • Saving time & reducing errors with named ranges

Topic 3: Pivot Tables  for Data Analysis

  • Creating Pivot Tables for summary information & data analysis
  • Modifying an existing Pivot Table
  • Subtotals in an Excel Pivot Table
  • Adding Interactive Pivot Charts

Topic 4: The Powerful IF Statement

  • Creating an IF Statement (True or False output)
  • Create multiple IFs when there are a range of outputs needed
  • Add nesting IF statement with OR & AND functions included

Topic 5: VLOOKUP & HLOOKUP for Data Automation

  • Creating a VLOOKUP Function to return a value
  • VLOOKUPs Exact or Approximate match
  • How the VLOOKUP can work across files
  • Using the HLOOKUP Function

Topic 6: IS function for checking formulas

  • The IS Functions to check formulas
  • Auditing functions ISERROR to see errors
  • IFERROR can be used to reduce clutter

Topic 7: Linking Multiple Worksheets

  • Problems with working with many workbooks
  • Open a second window on a workbook
  • Linking Cells in Different Worksheets

Topic 8: Consolidating Data

  • Consolidating Data from many worksheets
  • Links to summarize data
  • Using CONSOLIDATE functions
  • Create a 3D formula in Excel

Topic 9: Goal Seek & Solver for finding solutions

  • Create a Goal Seek to give a solution
  • Using Solver Utilities in MS Excel
  • Using Scenario Manager to View a Worksheet with Different Input Values

Topic 10: Using the Scenario Manager

  • Scenario Manager with different inputs
  • Creating a summary report from Scenario manager

Topic 11: Introduction to power of Macros in Microsoft Excel

  • Running existing Excel Macros
  • Create a Macro without knowing VBA
  • Recording a new Macro for repetitive actions
  • Viewing and editing the Macro

Some Excel features

Pivot Table

A pivot table is an Excel tool that enables you to summarize and explore data interactively. Once a pivot table is created it can modified to produce specialized reports rapidly.

 

Formula Auditing

The Formulas Auditing tool traces the relationships between the formulas and helps you spots errors

 

New Sort by Colour

Excel now allows you to sort by the cell or font colour. Filter by colour is also available

 

 

 

Excel tips

 

Add Custom Lists

If you have a list you use frequently it can be added to Excel. Select the list and go to the File > Options > where you will find custom lists. Once added all you need to do is type the first item then AutoFill the cells

Auto Fill Formulas Feature

Once a formula is in first cell then selecting the + at the bottom right corner of the cell allows you to copy the formula down or across –  a time saver.

Format Painter

If a cell has a specific format (font colour, size etc.) and this format has to be applied to other cells it can be time consuming. But you do not have to note all the specifications just use the “Format Painter” to copy the format.

 

Do Not leave a blank row

Do Not leave a blank row between the table values & table labels as Excel will not connect the two – it will be two tables.

Use Short Cut Keys for Tables

Use the shortcut ket Control + Shift + * to select an entire table – no need to mouse over 50,000 rows this key will select all the data.

Custom Sort

It is possible to sort a table is a non-alphabetic way ( if  A to A or Z to A does not work for you). Just add a custom list and then sort by this list.

 

Named Ranges in Formulas

For complex formulas with many arguments it can be confusing and difficult to use the cell coordinates. To solve this specific cells can be named, so instead of K56 the call could be call “tax” for example.

Find Working Days

Excel has a special function that can calculate the number of working days. It omits the week-end and any holidays you list.

Calculate Percent of Total

The Pivot table display can be modified to display the percent of total (or row or column). Right and Show Data as Percent of column. It does all the calculating for you.

 

About our Training

 

Delivery

We provide onsite fully customized training targeting your specific needs – not just a pre-set fixed agenda. The training can be with your own data and this means you are productive even on the training day (you can use the formulas created immediately with your data). We work with you and build your training agenda based on your training objectives and goals. In Excel there are hundreds of functions and while most people will use the SUM and AVERAGE functions the less common functions may be very useful for your data. These formulas and functions can also be combined so there are thousands of combinations.

Location

We provide onsite Excel training in the Toronto, GTA, Mississauga, Oakville, Burlington, Hamilton, Niagara, Kitchener, Waterloo, Guelph, Concord, Markham, Pickering, areas. We come to your location so you and your team do not have to drive across town – no wasted time. We offer customized MS Excel training for Employees, Managers, Executives involved in Financial, Accounting, Marketing, Sales etc. in Toronto, Mississauga, Brampton, Ontario, Kitchener, Waterloo, Hamilton, and Cambridge, Canada. For large groups it is also possible to rent hotel meeting rooms for onsite Excel training. If there is a sales meeting then Excel onsite training could be added.

Courses

We provide courses on Microsoft Excel, Excel PowerPivot Training Toronto & Ontario, Access, Microsoft Project training courses, Office 365 & SharePoint End-User. In MS Excel courses the trainer covers MS Excel data analysis, VLOOKUP, pivot tables, etc. PowerPivot training  DAX formulas. MS PowerPoint covers creating and organizing slides. Microsoft Project training covers Gantt charts & Scheduling. Training on SharePoint End-User Toronto covers Lists & libraries. Office 365 covers OneDrive, Sites, Outlook, Calendar We also offer onsite training in other Microsoft Office software: PowerPivot, MS Project, MS PowerPoint, MS Word, MS Outlook, MS Access training Toronto, Office 365 and SharePoint end-user training Toronto Mississauga Ontario.

Copyright © 1999 - 2016 Action eBiz Inc. |

Send us an email   Call 905.271.1119