Microsoft Office Excel Advanced
This Microsoft Excel advanced course will extend your knowledge into some of the more specialized and advanced capabilities of Excel by using the data analysis and business intelligence features. You will learn how to use the newer functions and features including PivotTables and Power Pivots for analysis of large data sets. Using Get and Transform functions are always popular sections on the course, as well as a first look at creating Macros using the powerful point and click features available in Microsoft Excel.
PrerequisitesExperienced MS Excel users who have completed an intermediate course or have a similar level of knowledge.
Learning OutcomesHaving completed the training, the attendees will be able to:
Use the new features in MS Excel
Create Dashboards using Camera Tool.
Understand and use new Functions in Excel
Create Tables, use PivotTable, PivotCharts & Slicers.
Use PowerPivot to analyse large data sets
Understand Data analysis and Business intelligence features in Excel
Use advanced Data Analysis functions in DAX
Automate some Tasks using Macros/VBA
What’s new in ExcelNamed Ranges, Copy&paste options,Camera Tool, Notes,Comments
Creating a MS Excel Dashboard, KPI example
Using Quick Analysis, Summations, Data bars, Rules, Charts, Tables
New functions in ExcelNew functions in Excel - Ifs, Switch, MaxIfs, MinIfs, Countifs, SumIfs, Iferror
Excel functions – TextJoin, Concat, vLookup / xlookup examples
Analysing Data using PivotTablesChanging the layout and format of the PivotTable
Calculations and analysis in Pivot Tables
Using Slicers to filter the PivotTable data
Using Pivot Charts
Data Analysis and BIIntroduction to Power Pivot
Using the Power Pivot for Business Analysis and reporting
Using Get and Transform
Power Query and data models
Business Intelligence, DAX for Business Analysis and reporting
Visual Basic for ApplicationsVisual Basic Editor
Creating user functions using Visual Basic for Applications
Advanced ExcelMicrosoft ExcelMS ExcelPivot TablesExcel 365Dynamic ArraysOffice 365Excel 2021LAMBDA