Excel Advanced & Dashboard Designing

Course Duration & Payment Details

Days: Pending
Timing: Pending
Duration: Pending
Starting from: Pending
Last date of registration: Pending

Actual Fee: 18000/-  | Early bird discount: 9000/-
How to Pay: Online Payment
Bank: Habib Bank Limited (26 street, Badar Commercial, DHA Karachi)
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)
    • Clean up Data using Text Functions ( Left, Right, Mid, Clean, Search)
    • Summarize Raw Data – Conditional Summary Calculations using (SUMIFS, COUNTIFS, AVERAGEIFS)
    • 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 making 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/HLOOKUP Lookup / Reference Functions
      • CHOOSE, OFFSET & INDIRECT Functions
  • 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
    • Inserting Form Controls
    • Check box
    • Radio button
    • Scroll bar
  • 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
  • Understanding Power Pivot
  • Importing Data into Power Pivot
  • Creating the Data Model
    • What is a Data Model?
    • Creating Relationships between Tables in the Model
    • Managing Relationships
  • Using Calculations in Power Pivot
    • Types of Calculations in Power Pivot
    • Creating a Calculated Column
    • Creating a Calculated Field (Measures)
    • Working with DAX Functions
Click on the image & download the sample file