ADVANCED LOOKUP

Lookup using Index and Match

• Match function’s syntax
• The return value of Match function
• Index function’s syntax
• The return value of Index function
• Combining Index and Match to create lookup operations

Double lookup

• Applying Index and Match to create double lookup operations

Lookup using the Choose function

• Choose function’s syntax
• Applying choose to create data table lookup
• Creating dynamic chart with choose

CONSOLIDATING AND SUMMARIZING DATA

Subtotaling

• Using the subtotal command to summarise pivoted data
• Working with the standard calculation when subtotaling
• Working with the navigation panel

Consolidating

• Using the consolidate command to consolidate data from multiple sheets

FILTERING DATA

Auto-filter

• Auto-filtering data using the Filter command.
• Applying the SUBTOTAL function to auto-filtered data to create data summary.

Advanced filtering

• Using the advanced filter with criteria.
• Filtering in place.
• Filtering into another location.
• Using conditional equations when filtering.

WHAT IF ANALYSIS

Input tables

• Single input table
• Double input table

Scenario manager

• Working with the scenario manager’s panel.
• Creating scenarios.

Goal Seek

• What is goal seek?
• When to use Goal seek
• Solving problems with goal seek

Solver

• Setting up the Worksheet
• Running Solver
• Generating Reports and Scenarios with Solver
• Modifying Constraints
• Setting Solver Options

MICROSOFT EXCEL PIVOT TABLES

• Creating and using a pivot table
• Filtering and sorting a Pivot Table
• Selecting, drilling down and refreshing a Pivot Table
• Drilling down and creating report
• Applying standard calculation
• Applying preset calculation
• Creating calculated items
• Creating calculated fields
• Using slicers to filter Pivot Table
• Creating Pivot chart
• Building dashboard.

DATA VISUALIZATION

Advanced Conditional Formatting

• Creating New Rules
• Modifying New Rules
• Manipulating icon sets

Sparklines

• Creating sparklines
• Setting markers
• Working with missing data
• Working with data axis
• Inserting text in sparklines

Advanced charting: Components That Group Data

• Listing Top and Bottom Values
• Organizing source data
• Using pivot tables to get top and bottom views
• Using Histograms to Track Relationships and Frequency
• Adding formulas to group data
• Adding a cumulative percent
• Using a pivot table to create a histogram
• Emphasizing Top Values in Charts

Advanced charting: Components That Show Performance Against a Target

• Showing Performance with Variances
• Showing Performance Against Organizational Trends
• Using a Thermometer-Style Chart
• Using a Bullet Graph
• Showing Performance Against a Target Range