Microsoft Advance Excel 2013/2016/2019

What you will learn:
 Create flexible data aggregations using pivot tables
 Represent data visually using pivot charts
 Calculate margins and other common ratios using calculation on pivot table
 Filter data using slicers in multiple pivot tables
 Create aggregate reports using formula based techniques
Pre-requisites:
 Formatted a worksheet using colors and fonts before
 Worked with general formulas and used basic worksheet functions like
 VLOOKUP, IF, MAX, MIN, COUNTIF…
 Have used basic of conditional formatting
 Built a table before and performed drag and fill operations on formulas (little
 black dot bottom right hand-side of a cell)
 Used logic before in general and know the relational operators <, >, <=, >=,
 <>, =
 Used basic Excel features like Sort, AutoFilter and have built Basic charts.

Program Contents:
Module 1: Advance Functions
 Statistical Functions
 Database Functions
 Lookup and references Functions
 Database Functions
 Date Functions
 Text Functions
 Logical Functions

Module 2: Working with Lookup functions
 Limitation of VLookup/Hlookup functions and alternatives for same.
 Match function.
 Index function.

Module 3: Sorting and Filtering Data and Subtotal
 Sorting and filtering lists
 Sorting data according to color
 Using multiple-level sorting
 Subtotal and Multi level Subtotal.
 Filters and its Drawback
 Advance Filter.

Module 4: What-If Analysis with Solver
 Using goal seek
 Using data tables
 Creating and editing scenarios
 Solver
Module 5: Formula Auditing
 Evaluating Formula
 Tracing Precedents
 Tracing Dependents
 Error Checking

Module 6: Conditional Formatting
 Apply Data bars, Icons and Shapes.
 Highlighting values based on various criteria.
 Highlighting values based Formula.

Module 7: Working with External Data
 Importing files from different sources
 Refreshing data

Module 8: Working with Pivot Reports/Charts/ Data Visualization
 Simple Report.
 Cross tab Report.
 Changing Data Range
 Formatting and making dynamic Report
 Use of Slicers
 Power Pivot

Module 9: Data Validation
 Data Validation for Invalid Values
 Data Validation for List/Dynamic List with named range
 Data Validation for Protecting Formula

Module 10: Text To Column/External Data
 Convert data from text to tabular format.
 Getting data from external sources to Excel

Module 11: Data Analysis Tool Pack
 Descriptive Statistics
 Annova
 Regression

Module 12: Security and Protection
 Protection Workbook
 Protection Worksheet
 Hiding Formula etc