Microsoft Excel 2019, 2016 Training
Toronto, Mississauga, GTA, SW Ontario
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 2019 or Office 365 Excel there are many new functions such as IFS to easily input a multiple IF function. Also new charts in Excel 2019 or Office 365 Excel new charts 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 Office 365 or Excel 2019, 2016 Ribbon custom interface
- New features in Excel 2019 “Sparklines””Flash fill” (to clean up data)
- New Charts in Excel 2016,Forecast Sheet, Sales Funnel Charts, Tree 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)
- Add a Bar chart right in your data
- 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 (Autofill)
- 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 2019 & 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)
- NEW the XLOOKUP function
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)
Use 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, Brampton, 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 or traffic. 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.