Course Details
Course Code: 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 create a spreadsheet with simple formatting
- Able to use Auto Filter command
- Able to use basic functions – AutoSum, Count, Max, Min and Average functions
- Able to switch between task applications
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: Starting with PowerPivot
- Add-ins Power Pivot from Options
- Launch the PowerPivot window
- A Tour to PowerPivot window
- Switching Normal mode and Advanced Mode
- Exploring the PowerPivot tab
- Changing between Data & Diagram view
Module 2:Adding data to PowerPivot
- Get Data from database
- Get data from Excel files
- Get data from Text or CSV files sources and types supported in PowerPivot workbooks
- Add Data by using Excel Linked Tables
- Load from Power Query
Module 3: Preparing data for analysis
- Working with Tables
- Managing Columns
- Set Data Type and Format
- Filtering and Sorting Data
Module 4: Relationships & Refresh
- Understanding Types of Relationships
- Create a Relationship Between Tables
- View and Edit Relationships
- Delete Relationships
Module 5: Manage Connection
- Refresh with latest data
- Editing connection
- Deleting connection
MODULE 6: Calculated Column
- Building Calculated Columns within a Table
- Building calculate Column across multiple table
MODULE 7: Measures
- Creating a New Measure
Formatting a Measure
- Creating Measure across multiple Tables
- Useful DAX Functions
- FILTER
- ALL
- RELATED
- TOTALYTD
- TOTALQTD
- TOTALMTD
- SAMEPERIODLASTYEAR
- CALCULATE
- Modify and Delete Measures
MODULE 8: Key Performance Indicators (KPI's)
- Create New KPI
- Manage KPI
MODULE 9: Date Table
- Mark as Date Table
- Create a Date Table
MODULE 10: Getting data from multiple of sources
- Import data from database
- Import data from Text or Text Files
- Import data from folder
MODULE 11: Managing Query
- Duplicate Query
- Rename Query
- Switching Between Power Query Editor and Excel Screen
- Viewing Applied Steps in Query Settings pane
- Renaming and Deleting Applied Steps
MODULE 12: Performing Simple Operation
- Identify Column Header via Promote and Demote
- Removing Repeated Headers from Subsequent Files
- Removing Duplicate Records
- Changing Column Headers
- Changing Data Type
- Removing, Moving, Renaming Columns
- Reorganize data with Sorting
- Get relevant data with Filter
MODULE 13: Joining multiple tables
- Joining multiple tables into master table via Append
- Merging Queries on Single or Multiple Matching Columns
- Returning Referenced Details on Those Matched
- Returning Results on Those Not Matched
MODULE 14: Getting Results
- Close and Load data into Excel from Power Query
- Refresh query to get latest data from source
MODULE 15: Data Reshaping
- Unpivoting Columns
- Pivoting Columns
- Splitting Column into Multiple Columns
- Merging Columns
- Fill empty rows with data above and below
MODULE 16: Output Different Result
- Manage number column with some calculation
- Converting Text to Uppercase, Lowercase
- Extracting Part of Text
- Cleaning up non-printable characters
- Showing Date Time as Date or Time
- Combining Date Column and Time Column as One Column
- Format or Extract date based on date value day, month and year
- Summing Values based on Grouping
- Replace value
MODULE 17: Managing Additional Column
- Create Custom Columns
- Create Conditional Column
- Indexing Column
MODULE 18: Managing Data Source
- Data Source Settings
- Change Source Location
- Change to Different Source
