Course Description
COURSE DESCRIPTION
This course takes up where the Excel 2016 Fundamentals course leaves off. It covers a wide variety of Excel topics, ranging from intermediate to advanced level. You will learn a wide variety of Excel functions, tips for creating readable and correct formulas, a number of useful data analysis tools, how to import external data into Excel, and tools for making your spreadsheets more professional.
Category: Office Productivity/Microsoft
Duration: 1-2 Days
What are the requirements?
Microsoft Office 2016
What am I going to get from this course?
How to use intermediate to advanced Excel functions from various categories (counting/summing/averaging, math, text, date/time, statistical, financial, reference, and logical) to solve practical problems
More advanced tips for working with formulas, including formula auditing and formula evaluation
How to use Excel’s many powerful tools for data analysis, including tables, pivot tables, data tables, Goal Seek, and Solver
How to use Excel’s tools for importing external data from databases, text files, and the Web
How to use a number of intermediate to advanced tools for adding professional touches to your spreadsheets, including data validation, workbook/worksheet protection, scenarios, form controls, and recording macros
An introduction to several tools new to Excel 2016, including Flash Fill for text pattern recognition and several data analysis tools (Quick Analysis, the Excel Data Model, and the PowerPivot and Power View add-ins)
What is the target audience?
Any competent Excel users who aspire to becoming Excel “power users” Agenda
Lesson Plan
Introduction to Course
Welcome to Intermediate and Advanced Course
Summarizing Functions
More Summarizing Functions
COUNTIF, SUMIF, AVERAGEIF
COUNTIFS, SUMIFS, AVERAGEIFS
Math Functions
Intro to Math Functions
INT, ROUND, CEILING, FLOOR
ABS, SQRT, SUMSQ
LN, EXP
RAND, RANDBETWEEN
Text Functions
Intro to Text Functions
LOWER, UPPER, PROPER
TRIM, VALUE
Concatenating
Parsing with Text to Columns
Parsing with Text Functions
Flash Fill
Date and Time Functions
Intro to Dates and Times in Excel
Y2K
TODAY, NOW
YEAR, MONTH, DAY, WEEKDAY
DATEDIF
DATE, DATEVALUE
NETWORKDAYS, WORKDAY
Statistical Functions
Intro to Statistical Functions
MIN, MAX
MEDIAN, QUARTILE, PERCENTILE
STDEV, VAR
CORREL, COVAR
RANK, LARGE, SMALL
New Statistical Functions
Financial Functions
Intro to Financial Functions
PMT
NPV, XNPV
IRR, XIRR
Reference Functions
Intro to Reference Functions
INDEX
MATCH
OFFSET
INDIRECT
Logical Functions
Intro to Logical Functions
IS… Functions
IF… Functions
Advanced Formula Tools
Range Names 2
R1C1 Notation
Auditing Formulas
Evaluating a Formula
External Formula References
Array Formulas
Importing External Data
Intro to Importing External Data
Importing Data from a Database
Importing Data from a Text File
Importing Data from the Web
Importing Data from Saved Connections
Data Analysis Tools
Intro to Data Analysis Tools
Quick Analysis
Tables
Pivot Tables 1
Pivot Tables 2
Slicers
Consolidating
Subtotals
Data Tables
Forecasting
Goal Seek
Solver
Power BI
Intro to Power BI
Data Model
Power Query
Power Pivot
Power View
Power Map
Adding Professional Touches
Intro to Adding Professional Touches
Data Validation
Protecting Worksheets and Workbooks
Custom Views
Scenarios
Outlining
Developer Ribbon
Using Form Controls
Recording a Macro
Conclusion to Course
Conclusion to Intermediate and Advanced Course