Library

Course: Learn How to Create Amazing Excel Dashboards with Pivot Table and Formulas

Learn How to Create Amazing Excel Dashboards with Pivot Table and Formulas

  • Life Time Access
  • Certificate on Completion
  • Access on Android and iOS App
  • Self-Paced
About this Course

Excel Dynamic Dashboard is mostly one pager, that helps management tracking important metrics and take a decision based on it. Data can be presented in forms of Charts that are supported by data from same or other file.

Basic knowledge
  • Participants should have basic knowledge of Excel
What you will learn

In this course I will teach you from Basic to Advanced Level, till creation of Scroll bar, Radio button & List box to create Interactive Dashboard.

You will also learn Formulas (CountIF, SumIF, CountIFs & SumIFs) which is used to create summary reports and is used to create the dashboard with multiple criteria.

I have divided this course in 4 Parts.

Part 1 Charts

  • Dynamic Dashboard Course Introduction
  • Column Chart
  • Line Chart
  • Pie Chart
  • Creating Combination
  • Creating Secondary Axis Chart
  • Creating Thermometer Chart
  • Creating Dynamic Chart

Part 2 Pivot Table

  • Understanding Pivot & Getting Sum, Count & %
  • Refreshing Pivot Table
  • Creating Yearly, Quarterly and Monthly Reports
  • Using Interactive Slicer and Timeline
  • Comparing Data of different years with Pivot
  • Creating Dynamic Dashboard with Pivot Table

Part 3 Formulas

  • CountIF in Same Sheet
  • CountIFs in Same Sheet
  • SUMIF Formula in Same Sheet
  • SUMIFs Formula in Same Sheet
  • CountIF and SumIF from other Sheet
  • IF Condition Formula
  • Advanced IF Condition
  • Concatenate Formula
  • Vlookup
  • Adding Dollar Sign in Vlookup
  • Vlookup with IFError
  • Vlookup with Match

Part 4 Interactive Controls for Dashboard

  • Using Dropdown to creating simple Interactive Chart
  • Project 2  Another Example of Dropdown
  • Creating Radio Button & understanding its concept
  • Creating Combo box
  • Creating List box
  • Scroll Bar Horizontal Example 1
  • Scroll Bar Horizontal Example 2
  • Scroll Bar Horizontal Example 3
  • 5 Years Comparison Chart
  • Creating CheckBox

Happy Learning !

Curriculum
Number of Lectures: 39
Total Duration: 05:00:56
Learn to Create Charts for Dashboard
  • Creating Column Chart  
  • Creating Line Chart  
  • Creating Pie Chart  
  • Creating Combination Chart (Column and Line both Together)  
  • Creating Secondary Axis Chart (Primary and Secondary Axis)  
  • Creating Thermometer Chart  
  • Creating Dynamic Chart (When data Increases, chart also changes its range)  
  • Practice Files (It's in Zip format, you have to Unzip it)  

    Download this Practice File folder on your Desktop, right click and Extract it)

Learn Pivot Table and created Dashboard with Pivot table using Slicer & Timeline
  • Understanding Pivot Table (Basic Level)  
  • Refresh Pivot Table  

    When you make any changes in Master Data, you should Right Click on Pivot and then Click on Refresh.

  • Creating Yearly, Quarterly and Monthly Report  

    In this you are going to learn (Grouping) and interesting way of creating Yearly, Quarterly and Monthly Reports.


    You will love this feature.

  • Using Slicer and Timeline with Pivot Table  

    An interactive way of creating Multiple Reports with Slicer and Pivot Table.

  • Comparing Yearly data with Pivot table  
  • Final Steps to Create Dashboard with Pivot table using Slicer and Timeline  

    In this lengthy video, you are going to learn interesting steps to create all the summary reports on one sheet and present it in form of Dashboard using Slicer and Timeline.

  • Practice Files Charts (It's in Zip format, you have to Unzip it)  

    Download this Practice File folder on your Desktop, right click and Extract it)

Learning Interesting Formulas to create Summary Reports
  • Learn CountIF Formula  
  • Learn CountIFs Formula (Use for Multiple Criteria)  
  • SumIF Formula (To add all the transaction based on Single Condition)  
  • SumIFs Formula (To add all the sales amount on Multiple Criteria)  
  • Learn CountIF and SumIF formula which pulls data from other sheet  
  • Learn Advanced IF Condition (Multiple Level)  
  • Concatenate Formula (Use to combine data from 2 or more cells)  
  • IF Condition (Revision)  
  • Vlookup Formula (Basic Level) Vertical Lookup - Pulling data from other sheet  
  • Adding $ Sign in Vlookup (Small Automation) (Intermediate Level)  
  • Vlookup with IFError Formula  
  • Fully Automate Your Vlookup (Advanced Level)  
  • Practice Files of Formulas (It's in Zip format, you have to Unzip it)  

    Download this Practice File folder on your Desktop, right click and Extract it)

Interactive Controls for Dashboard original files
  • Project 1 (Creating Dropdown) for Interactive Chart  
  • Project 2 Another Example of Creating Dropdown for better Understanding  
  • Project 3 Radio Button Creation with Chart for Dashboard Presentation  
  • Project 4 Combo box Creation so that user can select from List  
  • Project 5 List Box creation  
  • Project 6 Scroll Bar Horizontal Example 1  
  • Project 7 Scroll Bar Horizontal Example 2  
  • Project 8 Scroll Bar Horizontal Example 3 (For better Understanding)  
  • Project 9 5 Years Comparison Chart  
  • Project 10 Creating CheckBox (User can select Multiple Criteria)  
  • Practice Files of Interactive options (It's in Zip format, you have to Unzip it)  

    Download this Practice File folder on your Desktop, right click and Extract it)

Reviews (0)