-
Course Code
MRSF-012
Microsoft Excel 2016 Advanced Level
- You have been using Excel 2016 / 365 for a while and either in training or through self-development you have a solid foundation in the basic and intermediate skills for working in Excel.
- You have used Excel to perform tasks such as running calculations on data and sorting and filtering numeric data. In this course, you will extend your knowledge into some of the more specialized and advanced capabilities of Excel by automating some common tasks, applying advanced analysis techniques to more complex data sets such as PivotTables, collaborating on worksheets with others, importing and exporting data.
Learning Outcomes
- Configure Power Pivot and import data into a Microsoft Excel 2016 Workbook
- Create a pivotable using Power Pivot in Microsoft Excel 2016
- Create and populate a pivotable using Power Pivot in Microsoft Excel 2016
- Create a custom number format in Microsoft Excel 2016
- Create a custom date and time format in Microsoft Excel 2016
- Create a custom accounting format in Microsoft Excel 2016
- Create a custom fill series in Microsoft Excel 2016
- Create custom conditional formatting rules in Microsoft Excel 2016
- Modify custom conditional formatting rules in Microsoft Excel 2016
- Apply complex filtering criteria to data in Microsoft Excel 2016
- Use custom formats, filters, and fill series when working with data in Microsoft Excel 2016
Course Contents
- Creating a Macro
- Comparing Relative and Absolute Macros
- Running a Macro
- Editing a Macro
- Assigning Shortcuts to Macros
- Macro Security
- Enabling Macros in the Backstage View
- Copying a Macro between Workbooks
- Document Properties and the Document Inspector
- Protection
- Track Changes
- Workbook Merging
- Creating a PivotTable
- Amending the Fields in a PivotTable
- Updating the PivotTable
- Adding Calculations to a PivotTable
- Add Grouping to a PivotTable
- Creating a PivotChart
- PivotTable Timelines
- Creating PivotTables from Tables and Related Tables
- An Array Formula
- Array Functions
- FREQUENCY
- TRANSPOSE
- Single Cell or Multiple Cell Arrays
- Single Cell Array Functions
- Multiple Cell Array Functions
- GETPIVOTDATA
- MATCH
- VLOOKUP
- ROW / COLUMN
- INDEX
- OFFSET
- INDIRECT
- LOOKUP (Array format)
- LOOKUP (Vector format)
- Creating a Single Input Data Table
- Create a Two-Variable Data Table
- Creating Scenarios
- What-If Analysis Using Goal Seek
- Consolidating Data
- Linking to External Workbooks
- Number Validation
- Data List Validation
- Message Prompts and Alerts
- Conditional Data Validation
- Data Validation Errors
- Exporting Excel Data
- Import Delimited Text by Connecting to It
- Importing a Web Query
Module 1 - Creating Macros
Module 2 - Collaborating with Others
Module 3 - Creating PivotTables, Pivot Charts and Slicers
Module 4 - Array Functions
Module 5 - Advanced Lookup and Reference Functions
Module 6 - Analyzing Data
Module 7 - Data Validation
Module 8 - Import/Export Data
Our Methodology
- Make coaching and monitoring innovative and using modern
- Media training also using on the go training by using interactive means and focusing on
- The exercises, practical applications and real situations study
- Live delivery method, instructor-led training
- Experienced consultant, trainers, and professional
- Qualified trainer with high-level experience
Attendance Reports
- Send daily attendance reports to training departments
- Send full attendance report to training dep. by the end of the course
- Attend 100 % from the course days also provide daily
- Issue attendance certificate for participant who attend minimum 80% from the course duration
Pre/Post Reports
- Pre- assessment before starting training
- Post assessment after finish training
- Full report for the deferent between Pre-& Post assessment
Who Should Attend
- End users at all levels, including those who have little or no familiarity with Microsoft Excel; and more experienced Excel users who want to learn about the new interface and features in Microsoft Excel 2016
- Users of Microsoft Excel 2016 or 365 subscription editions
- Personnel at all levels of the enterprise
- Users seeking to attain competency in Microsoft Excel 2016
- Users seeking to obtain Microsoft Office Specialist (MOS) certification at Core level in the use of Microsoft Excel 2016
- Users seeking a basic IT certification
