Course Details
Course Code: MDVR; Instructor-led
Audience
- Sales Executive / Managers
- Business Analysist
- Resource Planning Team
- Project Managers
- Purchasing Manager
- Financial Executive / Managers
Prerequisites
Basic knowledge of Windows is essential with the following pre-requisites:
- Able to switch between task applications
- Able to create a spreadsheet with simple formatting
- Able to use Auto Filter command
- Able to use basic functions – AutoSum, Count, Max, Min and Average functions
Methodology
This program will be conducted with interactive lectures, PowerPoint presentation, discussion, and practical exercise.
Course Objectives
Upon completion of this program, participants should be able to:
- Getting Started with Microsoft Excel 2019, Identify the Elements, and Interface of Excel
- Performing Calculation, Basic Formula and Functions
- Modify worksheet & Formatting Worksheet
- Printing and managing large workbook
Outlines
Module 1: Creating Pivot Table
- Getting ready source data
- Creating Your First Pivot Table
- Simplifying summary organizing field
Module 2: Quick Formatting
- Configure Pivot Table Layout
- Setting Pivot Table Style
- Changing Number Format
- Turn On summary with Subtotal and Grandtotal
- Navigating report using Expanding and Collapsing
Module 3: Extracting Meaningful Data
- Rearranging Top and Bottom value
- Enabling Multiple Filer
- Label Filter
- Value Filter
Module 4: Categorizing Large Set of Data
- Grouping Date
- Grouping Your Own Selection
Module 5: Analyzing Pivot Table
- Changing Summary Value with different formula
- Measuring Value as Percentage
- Preparing Accumulated Value
- Comparing value against previous and next category
- Adding calculation for multiple value fields
MODULE 6: Getting Latest Data into Pivot Table Report
- Refreshing Pivot Table for additional data
- Changing Data Source
MODULE 7: Sporting trends with Conditional Formatting
- Highlighting the Top 10 analysis
- Spotting larger or smaller numbers in a range
- Applying indicator icon to visualize range of value
MODULE 8: Showcase Meaningful data as Pivot Chart
- Getting the right chart to visualize the data
- Customizing Pivot Chart Layouts and style
- Joining two type of chart to represent 2 corresponding value
MODULE 9: Mini charts for series of data with Sparkline
- Show trends in a series of values with Sparkline – Line
- Highlight maximum or minimum values with Sparkline – Column
- Quick formatting multiple sparkline with ONE click
MODULE 10: Sketch Dashboard Layout
- Compiling all visual representation into one page
- Create best layout by position all items
- Standardize the Formatting of the page
MODULE 11: Create and manage cell / shape link
- Linking important value from pivot table into Dashboard
MODULE 12: Interactive Selection with Slicer and Timeline
- Dynamic filter to update entire dashboard with slicer
- Focusing on a selected time period with Timeline
Module 13: Exporting Excel Dashboard into PowerPoint
- Linking Excel Dashboard in Power Point
- Setting interaction with OLE Action Verb
- Setting Up Presenter View
- Presenting Dashboard
