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

Microsoft Excel 2016, 2013, 2010, 2007 Training

Toronto, Mississauga, GTA, SW Ontario

excellogo

 

With customized Microsoft Excel training learn how to create formulas, use functions and work with your 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 data tables and organize them for easy sorting, filtering etc. Analyze data quickly and easily with pivot tables and much more.  You will create formulas you can use to save time & money.

If you have Excel 2016 or Office 365 Excel there are new functions such as IFS to easily input a multiple IF function. Also new charts in Excel 2016 such as Waterfall & Hierarchy & Sunburst Charts – these will enhance your reports.

Below is a suggested list of topics but these can be modified to suit your needs (you can choose a custom list of topics if you are already using Excel)

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 2016, 2013 & 2010 Ribbon custom interface
  • New features in Excel 2016/2013 “Sparklines””Flash fill” (to clean up data)
  • New Charts in Excel 2016,Forecast Sheet, Sales Funnel Charts, Waterfall charts and more.
  • New sample Charts suggestions, Pivot Timelines, Apps for Excel and more
  • Excel Online for co authoring, 2 or more people can work on the same file.

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 such as “Above Average”  in 2016 and 2013)
  • Adding comments & text boxes to worksheets

Topic 3: Entering data and navigating in a worksheet

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

Topic 4: Creating Formulas & Functions in Microsoft Excel

  • Creating basic Excel formulas (SUM, AVERAGE, MAX, MIN, COUNT etc)
  • New functions such as MAXIFS (Max with multiple criteria) are now possible
  • Editing & checking formulas & functions (New functions in 2016 & 2013)
  • Using Excel Absolute references (with the $ sign) and when you should use them.
  • 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 how to copy the entire worksheet)
  • Moving data Copying data (shortcut keys, Control + C etc.)
  • Using the Autofill feature to list days of the week or list dates
  • Adding Custom Lists (of locations for example) for rapid entry

Topic 6: Printing worksheets

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

Topic 7: Multiple worksheet workbook in Excel

  • Creating a multiple-sheet workbook
  • Hiding worksheets when not needed

Click to go to MS Excel Training Formulas Level 1

Microsoft Excel Training Level 2 – Intermediate

Topic 1: Creating Excel Charts

  • Selecting data & 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 different Chart types (Column, Bar, Line Charts etc.)
  • Modifying charts elements (titles, labels etc.)
  • Adding, modifying and/or deleting chart items
  • Moving and sizing chart items
  • Moving a chart to it’s own sheet

Topic 3: Formatting Elements of a chart

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

Topic 4: Using graphic objects

  • Adding graphic objects & AutoShapes
  • Modifying existing graphic objects
  • How graphic objects can enhance worksheets and charts

Topic 5: Sorting data in Excel

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

Topic 6: Filtering data in Microsoft Excel

  • Creating a Filtered list or table
  • Adding Custom criteria
  • Creating multiple criteria for many columns
  • 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 but still widely used)
  • SUMIFS Excel 2016 & 2013 sum by multiple criteria (127 are possible)
  • The SUBTOTAL function creates formulas quickly

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 for naming 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
  • Adding Subtotals in an Excel Pivot Table
  • Creating Interactive Pivot Charts

Topic 4: The Powerful IF Statement

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

Topic 5: VLOOKUP & HLOOKUP for Data Automation

  • Creating a VLOOKUP Function to return a value from another data table
  • VLOOKUPs Exact or Approximate match
  • How the VLOOKUP can work across worksheets or files
  • Using the HLOOKUP Function (for horizontal data)

Topic 6: IS function for checking formulas

  • Using  IS Functions to check formulas
  • Auditing.checking 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 the same workbook
  • Linking Cells in Different Worksheets or workbooks

Topic 8: Consolidating Data

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

Topic 9: Goal Seek & Solver for finding solutions

  • How Goal Seek to gives 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

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

Topic 11: Introduction to power of Macros in Microsoft Excel

  • Running Excel Macros already in the file
  • Creating a Macro without knowing VBA
  • Recording a new Macro for repetitive actions
  • Viewing and editing the Macro

Some Useful Excel features

Pivot Table

A pivot table is a very useful 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 (the pivot table has embedded formulas)

 

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 (such as locations or employees)  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 use 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 – click on the cell & then the new cells.

 

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 to the system.

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 in the table.

Custom Sort & Flash Fill

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. Use Flash Fill if you need to edit data, so if full name is in one column just type the First Name in the next column to right and Flash Fill will do the rest (once it sees a pattern)

 

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. So =B24*tax is easy to understand.

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 in a second.

 

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 2016, 2013, 2010 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