Microsoft Office Webinar Package

Select Format

From: $299.00

Session 1 – Mastering Excel Formulas and Functions
Training Highlights:

Gain productivity tips, shortcuts and accelerator tools for Microsoft Excel
Learn about Excel’s Pivot Table feature, and how to use it
Learn shortcuts for building formulas, and how to use formula-auditing tools
Learn how to use time-saving functions such as IF, V LOOKUP, MATCH, and INDEX
Learn about slicers and how to use them
Learn how to use Text functions to extract and manipulate data (character or numerical) for sorting or analyzing
Learn how to tabulate data tools from multiple worksheets using 3D formulas
Learn how to use multiple functions in a nested fashion
Learn how to use the COUNTIF family of functions. Also learn about newer functions such as AGGREGATE, ARABIC, ISFORMULA, and FORMULATEXT

Session 2 – Excel PivotTables: Summarize and Analyze Data in a flash with Pivot Tables
Training Highlights:

Compare two or more fields in different layout styles
Sort and filter results
Group information
Drill down to see the details behind the summary
Use Slicers instead of filters
Categorize data and time data in multiple levels
Add calculated fields to perform additional analyses
Hide or reveal information with one click
Create a Pivot Chart in sync with the PivotTable
Deal with dynamic source data
Learn about “refresh”
Create a PivotTable based on data from multiple worksheets

Session 3 – Excel: How to Build an Effective Dashboard
Training Highlights:

Creating summaries using Pivot Tables
Working with data sources
Visual communication with the help of charts
Displaying the “Top x”
Using infographics in your dashboard
Creating KPI summaries using formulas
Adding user interaction to your dashboard

Session 4 – Excel – Advanced Reporting with Power Pivot and Power Query
Training Highlights:

Learn how to install Power Pivot to your devices
Learn about Data Models
Find out how to create relationships in Power Pivot
Find out how to import data into excel from external sources
Learn about DAX formulas
Learn about Power Query’s data sources
Learn how to clean, transform, merge and unpivot data in Excel

Session 5 – Excel Competency: Get Acquainted with Pivot Tables
Training Highlights:

Find out how to get past the “Pivot Table field name already exists” prompt
Learn the requirements of ideal data sets, for analysis within Pivot Tables
Learn how to expand and collapse Pivot Table elements, to avoid information overload
Understand the nuances of formatting numbers within Pivot Tables
Learn how to add fields to a table
Understand the nuances of sorting Pivot Tables, and subtotaling data within a Pivot Table

Session 6 – Mastering Excel Formulas and Functions
Training Highlights:

Gain productivity tips, shortcuts and accelerator tools for Microsoft Excel
Learn about Excel’s Pivot Table feature, and how to use it
Learn shortcuts for building formulas, and how to use formula-auditing tools
Learn how to use time-saving functions such as IF, V LOOKUP, MATCH, and INDEX
Learn about slicers and how to use them
Learn how to use Text functions to extract and manipulate data (character or numerical) for sorting or analyzing
Learn how to tabulate data tools from multiple worksheets using 3D formulas
Learn how to use multiple functions in a nested fashion
Learn how to use the COUNTIF family of functions. Also learn about newer functions such as AGGREGATE, ARABIC, ISFORMULA, and FORMULATEXT

Session 7 – Creating Data Visualization and Infographics in Excel`
Session Highlights:

Creating Dashboards with Pivot Tables and summaries
On-the-fly filtering for compelling storytelling
Advanced charting techniques
Color-coding charts dynamically
Combination charts – line and bar in a single chart
Dynamic charts – automate adding new data to a chart
Build waffles, maps, and other compelling visuals
Creating infographics with Excel
Using Conditional Formatting to present data visually
Using Excel add-ins to create infographics

Session 8 – Beyond VLOOKUP: Mastering Advanced Excel Functions
Learning Objective Points:

Restricting users to enter dates within a given range or before/after a given date.
Learning about the MINIFS function available in certain versions of Excel 2016.
Using the SUMIF function to summarize data based on a single criterion.
Discovering the capabilities of the SUMPRODUCT function.
Saving time when aggregating data from multiple worksheets with Excel’s INDIRECT function.
Learning how VLOOKUP stops looking after it finds an initial match within a list.
Learning how the HLOOKUP function enables you to perform horizontal matches.
Using Excel’s OFFSET function to dynamically reference data from one or more accounting periods.
Learning why the INDEX and MATCH combination often is superior to VLOOKUP or HLOOKUP

Session 9 – Excel Competency: Establishing Spreadsheet Internal Controls
Session Highlights:

Using a custom number format to hide zero amounts within a specific area of a spreadsheet.
Improving the integrity of spreadsheets with Excel’s VLOOKUP function.
Using Conditional Formatting to identify unlocked cells into which data can be entered.
Toggling the Locked status of a worksheet cell on or off by way of a custom shortcut.
Utilizing Data Validation to limit percentages entered in a cell to a specific range of values.
Future-proofing VLOOKUP by using Excel’s Table feature versus referencing static ranges.
Using Excel’s IFERROR function to mask # sign errors like #N/A.
Using Conditional Formatting to color-code your data, identify duplicates, and apply icons.
Creating resilient SUM functions that won’t break when users insert additional rows.
Overcoming VLOOKUP’s quirks by using SUMIF to look up numeric values.
Ensuring proper VLOOKUP integrity by using Data Validation to create an in-cell drop-down list.

Session 10 – Microsoft Excel Tips and Shortcuts: Tips for Every Skill Level
Session Highlights:

Learn how to copy or move data by dragging, instead of using multi-step command sequences
Discover how to navigate smoothly through worksheets and workbooks using keyboard and mouse shortcuts
Find out keyboard shortcuts for different number formats
Find out how you can display or hide all formulas instantly, and select all formula cells with two clicks
Learn how to create charts effortlessly and instantly, and how to manipulate them easily
Discover how to create lists of dates, times or values without using commands
Learn how to create formulas faster with entire column references

Session 11 – Mastering Excel Pivot Tables
Session Highlights:

Comparing two or more fields in different styles
Filtering and sorting results
Conducting ad-hoc grouping of information
Identifying the kinds of field elements displayed using slicers than filters
Making use of timeline for dynamic time-based displays
Digging deep to know the details behind the summary
Categorizing of time/date data in various levels
Creating of a Pivot Chart in sync with a PivotTable
Putting in calculated fields in order to do additional analysis
Ways to reveal detail/hide/summary information/ with a click
Ways to deal with the “refresh” concept and the dynamic source data
Ways to create a PivotTable using data from various worksheets
Ways to provide Conditional Formatting to PivotTable Data
Generating PivotTables from various input sources (different workbooks and worksheets)
Using Slicers to accentuate field elements that are being shown at present (and which ones are not)
Making use of the new Timeline feature
Using Conditional Formatting with PivotTable data
Exploring the best techniques for updating PivotTables
Creating ad hoc and date-based groupings within a PivotTable

Session 12- Compelling Presentations with Excel and PowerPoint
Session Highlights:

Pivot Tables and Charts
Logic-Based Summary Functions
Presentation Design Concepts
Conditionally-Formatted Charts
Building Office Themes
Link an Excel Chart to PowerPoint in Seconds
Storytelling with Data
Audience Analysis
Restoring missing data
Copying, pasting, linking, and embedding data
Presenting dense

Session 13 – File Triage: Work Smarter in Microsoft Word, Excel, and PowerPoint
Training Highlights:

Learn how to remove extra line breaks from Microsoft Word documents
Find out how you can update data throughout Microsoft Excel spreadsheets
Discover how you can fix slides without slide numbers in your presentations
Learn how to create fillable forms using Microsoft Word
Learn how you can build Interactive Presentations using Microsoft PowerPoint
Find out how you can automate your inbox in Outlook
Use the calendar feature in Microsoft Outlook to maximize efficiency
Learn all about OneNote and its useful features
Also learn about the useful features in Visio, SharePoint, Evernote, and Acrobat Pro

About Our Speaker

Dennis Taylor is an author, speaker, seminar leader and facilitator. With over 25 years’ experience, he is well-versed in Microsoft Excel and has conducted nearly 600 webinars on the subject, throughout the United States and Canada. In addition, he has also authored and presented numerous courses on Excel, and authored (and co-authored) multiple books on spreadsheet software.

Mike Thomas has extensive experience of working in the IT training business, where he’s been actively involved for over 27 years. He is an expert in several subjects, including in Microsoft Office and Apple Mac.

Mike is the founder of The Excel Trainer, which provides training for Microsoft Excel. Here, he has produced nearly 200 written and video tutorials, which have helped people over the world increase their efficiency. He has also recorded training courses for pluralsight.com. In addition, he has conducted hundreds of webinars on a wide variety of topics, related to technology.

Neil Malek – Microsoft Certified Trainer, CompTIA CTT+, and Adobe Certified Expert – is founder and principal at Knack Training, a Southern California education company. He has over 15 years of experience teaching software and professional development skills, and has worked with individuals, non-profits, small businesses, and Fortune 100 companies to identify and address training needs.

David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. David’s mantra is “Either you work Excel, or it works you,” so he focuses on what he sees users don’t, but should, know about Microsoft Excel. His goal is to empower you to use Excel more effectively. To learn more about David, you can view his LinkedIn profile and follow him on Facebook or Twitter (@excelwriter).