Instructor Led, Office Productivity, Practical Knowledge for the Workplace
Practical Excel 2016 for the Workplace - Intermediate and Advanced (ILT)
0( 0 REVIEWS )
0 STUDENTS
Instructors
Practical Excel 2016 for the Workplace – Intermediate and Advanced (ILT)
0 STUDENTS ENROLLED
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”
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 |
Course Reviews
No Reviews found for this course.