Microsoft® Excel 2016/365 Formulas, Functions & PivotTables for the Advanced Excel User

Open Enrollment Course Dates: 12/21

Click here to pre-register for this course now!

Course Length: 1 day
Delivery: Instructor Led/Group Live
PDUs: 7.5
PMI Activity ID# 000355
Recommended CPE:  8 hours
Prerequisites/Advanced Preparation: Excel Introduction or equivalent knowledge
Course Level: Advanced
Course Fee: $395/single participant (call for group and/or on-site pricing)
Course Hours: 8:30 a.m. – 4:15 p.m.


Target Student: Take this course if you want to learn useful tools, tricks and tips for utilizing functions and formulas available in Microsoft® Excel 2013. Learn which are best suited from over 300 available built-in functions. If you need to analyze large amounts of data quickly and easily, this class covers PivotTables – Excel’s best resource for doing so.

Course Content:

Lesson 1: The Interface
Quick Access Toolbar
Home Ribbon
Insert Ribbon
Page Layout Ribbon
Formulas Ribbon
Data Ribbon
Review Ribbon

Lesson 2: Fundamentals Review
Overview of Formulas and Using AutoSum
AutoFill and AutoComplete
Moving Data
Editing Tools
Conditional Formatting
Cell Merging and AutoFit
Find and Replace
Working with Worksheet Appearance

Lesson 3: Working with Formulas
Relative and Absolute Cell References
Mathematical Operators
Formulas with Multiple Cell References
Formula Auditing Buttons
Fixing Formula Errors
Displaying and Printing Formulas
Displaying and Tracing Formulas

Lesson 4: Working with Functions
Inserting and Editing a Function
Defining Names
Using and Managing Defined Names
Understanding Formula ErrorsUsing Logical Functions (IF)
Using Financial Functions (PMT)
Using Database Functions (DSUM)
Using Lookup Functions (VLOOKUP)
Financial Functions
Date & Time Functions
Math & Trig Functions
Statistical Functions
Lookup & Reference Functions
Database Functions
Text Functions
Logical Functions

Lesson 5: Working with Web and External Data
Inserting a Hyperlink
Creating a Web Page from a Workbook
Importing Data from an Access Database or Text File
Importing Data from the Web or Other Sources
Working With Existing Data Connections

Lesson 6: Working with PivotTables
Creating a PivotTable
Specifying PivotTable Data
Changing a PivotTable’s Calculation
Filtering and Sorting a PivotTable
Working with PivotTable Layout
Grouping PivotTable Items
Updating a PivotTable
Formatting a PivotTable
Creating a PivotChart

Lesson 7: What-If Tools for Analyzing Data
Working with Data Tables
Creating Scenarios
Creating a Scenario Report
Using Goal Seek
Using Solver

Cancellation Policy: 5 working days for full refund. Cancellations after that time are charged full tuition for the course.

For more information regarding refund, concerns, and/or program cancellation policies please contact our offices at 919-878-7100 ext. 22

Knowledge Source Inc. is registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors. State boards of accountancy have final authority on the acceptance of individual courses for CPE credit. Concerns regarding registered sponsors may be submitted to the National Registry of CPE Sponsors through its website:www.nasbaregistry.org.