Excel Advanced & Dashboard Designing

Course Duration & Payment Details

Day: Sundays Only
Timing: 11:00am to 01:00pm
Duration: 16 Hours (8 weeks)
Starting from: Sunday 9th October 2022
Last date of registration: Sunday 2nd October 2022

Actual Fee: 18000/-  | Early bird discount: 9000/-
How to Pay: Online Payment
Bank: Habib Bank Limited
Title: Irfan
Account No: 0011557900292501 | PK02HABB0011557900292501

Training Outline

  • Data Analysis Basics
    • Analyzing & Extracting Data with Filters & Advance Filter (Splitting data into Multiple Sheets)
    • Sort / Advance Sort – (Dynamic Sorting)
    • Finding and Removing Duplicates
  • Analyzing Data within Lists & Tables
  • Learn Core Data Analysis Functions
    • Mathematical Functions (SUM, MAX, MIN, COUNT, COUNTA, AVERAGE)
    • Understanding Logical Functions (IF)
    • Lookup & Reference Functions Revisited and made subsets of data
      • A step ahead with VLOOKUP; Simple VLOOKUP, Multi-worksheet, Multi-list, Nested VLOOKUP
      • Understanding HLOOKUP
      • MATCH / INDEX as a universal replacement to VLOOKUP
  • Working with Date & Time Functions
  • Working with Multiple Worksheets  &  Workbooks (Links)
  • Applying Security to Files, Workbooks & Worksheets
  • Working with Name Manager
  • Data Analysis and PivotTable Reporting (20 Useful Daily routine Tasks)
    • Creating a Pivot Table
    • Formatting a Pivot Table
    • Sorting Filtering Pivot Tables
    • Adding Removing Fields from Pivot Tables
    • Applying different styles to Pivot Table Fields
    • Creating Pivot Charts
    • Analyzing Data with PivotTable
  • Working with Using Slicers
  • Visual Analysis of Data using Charts
  • Creating Advanced Charts
  • Dynamic Charts with Form Controls
  • Creating Dynamic & Advanced Dashboard
    • Business Reporting where Dashboard fits in it
    • Understanding Different Types of Dashboard Reports
    • Typical Business Dashboard Components
    • Representing your data on Dashboard
  • Conditional Formatting
    • How to use conditional formatting to highlight the top and bottom values
    • How to use conditional formatting to mark values that are above or below average.
    • How to use conditional formatting to create in-cell data bars.
    • How to use color scales with conditional formatting.
    • How to apply conditional formatting with icons.
    • How to highlight specific & duplicate values with conditional formatting.
  • Update and Refresh Connection
  • Excel Macros
    • Recording Macros to automatic repetitive tasks
    • Editing Recorded Macros
  • Excel’s Built-In Data Analysis Tools
    • Intro to What if? Analysis
    • Data Tables
    • Scenarios
    • Goal Seek
  • Using Sparklines
  • Introduction to Power Query
    • Extracting, transforming, and loading data
    • Converting data formats using Power Query steps
  • Common Data Import Sources
  • Working with CSV; Text; Excel Files
  • Importing multiple files
  • Working with Folders and Multiple Files
  • Combining Data from Multiple Files
  • Loading Data into a Worksheet
  • Loading Data into the Excel Data Model
  • Power Query and Table Relationships
Click on the image & download the sample file