OVERVIEW
LEARNING OUTCOME
OVERVIEW
The main focus of the training is to analyse data and summarize data. Participants will learn how to perform advanced lookup to summarise data, consolidate and subtotal data, filter data and validate data. Finally, what if analysis will be performed on data.
The results will then be used to visualize the data.
LEARNING OUTCOME
After completing this course, students will be able to:
- Perform advanced lookup
- Perform advanced filtering
- Consolidate and summarise data
- Perform what if analysis
- Analyses data using Pivot Table
- Visualize data
MICROSOFT EXCEL (ADVANCED)
Who Should Attend?
This course is suitable for users who are required to analyze data or generate reports using Microsoft Excel.
Prerequisites
Completion of Microsoft Excel Intermediate or equivalent knowledge.
Course Outline
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
- 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
- Working with the scenario manager’s panel.
- Creating scenarios.
- What is goal seek?
- When to use Goal seek
- Solving problems with goal seek
- 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