Microsoft Excel – Data Analysis and Dashboard Reporting

  • 17-04-2023 - 18-04-2023
  • Virtual Classroom

INTRODUCTION

 Microsoft Excel is one of the most powerful and popular data analysis desktop application on the market today. By participating in this Microsoft Excel Data Analysis and Dashboard Reporting course you’ll gain the widely sought after skills necessary to effectively analyze large sets of data. Once the data has been analyzed, clean and prepared for presentation, you will learn how to present the data in an interactive dashboard report.

The Excel Analysis and Dashboard Reporting course covers some of the most popular data analysis Excel functions and Dashboard tools, including;

  • XLOOKUP() Excel 365 Function for Looking up Information
  • SWITCH, FILTER, SORT, UNIQUE functions in Excel 365
  • TEXTJOIN, IFS Functions

 

OBJECTIVE

  • Understand and Identify the principles of data analysis
  • Effectively summarize and present data clearly
  • Use effective design principles when creating and presenting data
  • Take full advantage of the built in tools Microsoft Excel offers in order to analyze and build dashboard reports
  • Build interactive dashboard reports
  • Working with Power Tools in Excel – Power Query and Power Pivots

 

TRAINING METHODOLOGY

Remote Learning, Lecture style, with hands-on exercises

 

REQUIREMENTS

Experience working with Microsoft Excel on a Windows system

 

WHO SHOULD ATTEND?

Experienced Microsoft Excel users who are looking to efficiently analyze large data sets and create dynamic dashboard reports

 

OUTLINES

  1. Managing Raw Data
    • Excel Versions and features
    • Working with Raw Data
    • Data Transformation in Excel Power Query
    • Import and Connection Data
    • Text Files , CSV , Workbooks , PDF , Web , Folders
    • Text to Column Tricks
    • Troubleshooting merged data
    • Working with Unnecessary Spacings
    • Troubleshooting Wrong Date and Time
  1. Combine Query in Excel
    • Demo 1 : Combine & Append Queries using Folder Datasets
    • Demo 2 : Combine & Append Queries using Multiple Worksheet Datasets
    • Demo 3 : Combine & Append Queries using PDF Pages
  1. Working with Combined Query in Excel
    • Combine & Merge Query in Excel
    • Merging Files from same filetypes
    • Merging Files from different filetypes
    • Demo : Lookup Vs Combine & Merge Query
  1. Working with a Single Dataset
    • Transform Manual Database to Smart Datasets
    • Smart Table Magics
    • Smart Table Conditions
    • Working with Data Sources
  1. Working with Data Analysis
    • Preparing Functions
      1. Statistical Functions
    • Quick Report with Pivot Tables
    • Pivot Table Conditions
    • Pivot Table Connection
    • Handling Queries
    • Working with Simplified Reports 
  1. Working with Multiple Datasets
    • Excel 365 New Functions
    • SWITCH , FILTER , SORT, UNIQUE functions in Excel 365
    • TEXTJOIN , IFS Functions
    • Consolidating Data
    • Data Modelling using Power Pivot in Excel
    • XLOOKUP Functions
  1. Working with Data Reporting
    • Pivot Table Data Sources
    • Pivot Tables Queries and Solutions
    • Pivot Table Data Grouping
    • Pivot Table Subtotals
    • Pivot Tables – Format Number
    • Pivot Table Functions
    • Pivot Table – Show Page Report Filter
    • Pivot Table – Data Modelling
    • Pivot Table – Row , Columns , Values and Filter Section
    • Pivot Table – Rank and Comparison
    • Pivot Table – Designs and Themes
    • Pivot Tables – Complex Queries
    • Pivot Tables – Options and Advance Format
  1. Preparing Dashboard
    • Pivot Tables Queries
    • Pivot Chart Queries
    • Slicers
    • Preparing Interactive Dashboard
    • Adding Other Objects
    • Working with 1-screen Excel Dashboard View
    • Link and Add Slicers and Timeline
    • Working With Sparklines
    • Working with Conditional Formatting
  1. Prepare and Distribute Reports 
  1. Preparing Interactive Dashboard Visualization in Excel 365

  

PRE-REQUISITE

  • Good Internet / wifi connectivity
  • PC / Laptop equipped with Microsoft 2013 or above version
  • Good audio visual
  • Virtual platform – ZOOM

Register Here
Download Brochure

Organizer:

Shabeenah Bee bt Mohd

  • Time : 9:00 am - 5:00 pm (Asia/Kuala_Lumpur)

Registration Deadline Expired!!

Related Events