Excel Dashboard Crash Course
Created by Yodalearning, Last Updated 25-Oct-2019, Language:English
Excel Dashboard Crash Course
Steps to Create Advanced Excel Dashboard for Data Analytics with Visual using Excel Tools, Functions and Excel Macros
Created by Yodalearning, Last Updated 25-Oct-2019, Language:English
What Will I Get ?
- Analyze data and create reports in a fraction of the time of spreadsheets
- Create new powerful calculations, KPIs and Key Metrics
- Plot data on Map in Advanced Excel Dashboard
- Learn 3 types of Dashboards in this course
- Learn to make Excel Dashboard using Pivot tables
- Learn to create Excel Dashboard using Functions
- Learn to create Excel Dashboard using Macros and Functions
- Get Conclusion of all 3 Excel dashboards
Requirements
- Course works on all Microsoft Excel versions 2003, 2007, 2010, 2013, 2016. Of course the newer the better, but some features works same in all the versions.
- Basic understanding of Excel because We'll take you to the advanced level in this Excel Dashboard crash CourseCourse
Description
Dashboard help user visualizes complex scenarios and problem better. Manager with the help of Advanced Excel dashboard can make better business decisions.
Our course is meant for professional working at different levels of careers, i.e., From MIS executive to Data Analyst.
MS Excel made it easy to make Dashboards for any Analytics. Business user can easily analyze data to create powerful reports and dashboards in a fraction of the time of traditional spreadsheets.
In this course, you'll learn to make dashboard in Excel in different ways:
Dashboard using Pivot Table like Multiple Pivot, Interlinked Pivot, Slicer buttons to control charts, Advanced Charts, etc.
- Text Function in Excel
- Naming the dynamic data and the using it in function
- Create Chart for comparison from the Pivot table
- Create Trend line from the Pivot table
- Creating Pie Chart from Slab Report from Pivot table
- Inserting Slicer using Chart & Customize Slicer Setting
- more..
Dashboard using Excel Functions:
- Importing Data from the Internet
- Converting Line Item into a Currency Table
- Calculation of Currency using INDEX and MATCH
- AverageIF for calculating Country Wise Salary
- Dynamic Dropdown
- List longitude and latitude with plotting data on dashboard
- Using REPT Function based on the percentage calculation
- Advanced INDEX and MATCH with multiple references
- Insert World Map into the chart
- Plotting Data on Map Chart and Controlling Map Preview from drop-down
- Preparing the base by removing duplicate for Dashboard
- more..
Dashboard using Excel Macros & Functions
- Implementation of OFFSET for dynamic data
- Using macro to update pivot table based on the Job Type
- Preparing data for Spiderweb chart and change the graph setting
- Use of Array functions like MAXIF and MINIF with single and multiple parameters
- Creating Name Range using shortcut
- more..
This course is for all business users who want to learn how to create management reports, analyze data and create interactive dashboards using Excel 2010 and later versions.
Benefits Of Excel Dashboard Crash Course
- It Provides an Overview OF your Problem
- You get to avail total Visibility into your Business
- Excel Dashboards provide extreme flexibility
- Saves more time at Work
- Increased Profits
- You’ll get Improved Results with interactive Dashboards
If you have read this far, next action is JOINING this course. Invest 4 hrs. for the benefit of lifetime Ninja status on Advanced Excel Dashboard Crash Course.
Who this course is for:
- People who analyze data and create new insight
- People who do reporting and create dashboards
- Professionals working with large amount of Data
Course Content
-
Introduction
2 Lectures 00:04:14-
Introduction to Dashboard Course
Preview00:03:08 -
Introduction to 1st Dashboard
Preview00:01:06
-
-
Working Files
1 Lectures-
Downloadable Content For Practice
-
-
1st Dashboard using Pivot
12 Lectures 00:53:58-
Understanding the Data
Preview00:01:41 -
Text Function
Preview00:06:30 -
Dynamic Data Create Table
Preview00:07:16 -
Naming the dynamic data and using it in the function
00:04:21 -
Preparing 1st Pivot & updating data
00:05:28 -
Classic View & Filling blank with zero in Pivot
00:03:46 -
Create Chart for comparison from the Pivot
00:01:46 -
Create Trend Line from the Pivot
00:08:18 -
Creating Pie Chart from Slab Report from Pivot
00:04:50 -
Preparing Framework for Dashboard
00:02:14 -
Inserting Slicer using Chart
00:03:18 -
Customize Slicer Settings
00:04:30
-
-
2nd Dashboard using Functions
34 Lectures 02:03:30-
Brief on 2nd Dashboard
00:01:36 -
Importing Data from the Internet
00:04:39 -
Converting Line Item into a Currency Table
00:05:35 -
Calculation of Currency using INDEX & MATCH
00:03:24 -
Creating Slabs using Vlookup True Parameter
00:04:58 -
Creating Qtrs based on Financial or Calendar Selection
00:04:28 -
Preparing the base by removing duplicates for Dashboard
00:02:36 -
AverageIF for calculating Country Wise Salary
00:02:23 -
Substituting ERROR with zero using IFERROR
00:02:16 -
Introduction to Function INDIRECT Part 1 & 2
00:10:13 -
Using Function INDIRECT to Retrieve AverageIF from relevant sheet
00:04:03 -
Using Function CountIF with INDIRECT to calculate No. of Emp
00:04:18 -
Calculating Contribution Country wise No Of Emp
00:02:29 -
Using NameRange to simplify calculation
00:02:48 -
Using NameRange with formula in 2nd Dashboard
00:05:18 -
Introduction to Offset Function
00:02:57 -
Offset Function in Excel
00:04:01 -
Using Offset Function in Dashboard
00:03:58 -
Use of MINIF in Dashboard
00:03:14 -
Use of MAXIF in Dashboard
00:02:41 -
Using Average to get Designation Wise Experience result
00:03:45 -
Using MAXIF with multiple conditions
00:03:26 -
Using MINIF with multiple conditions
00:01:28 -
Dynamic Dropdown List
00:02:06 -
Longitude and latitude with plotting data on dashboard
00:03:08 -
Using REPT Function based on the percentage calculation
00:02:41 -
Activation of Developer Tab and creating Scroll Bar
00:04:04 -
Inserting Trend chart based on data
00:04:29 -
Advance INDEX and MATCH with multiple references
00:03:47 -
Inserting Spark Line
00:02:26 -
Insert World Map into the chart
00:01:58 -
Plotting Data on Map Chart
00:05:09 -
Controlling Map Preview from dropdown
00:03:39 -
Summarizing 2nd Dashboard
00:03:29
-
-
3rd Dashboard using Functions $ Macros
13 Lectures 00:49:09-
Brief on 3rd Dashboards
00:01:24 -
Implementation of OFFSET for dynamic data
00:03:45 -
Creating Trend Chart from 2 Pivot Table
00:03:46 -
Modifying the Chart Settings for better view
00:02:10 -
Using Macro recording for Trend Chart 01
00:03:03 -
Using macro to update pivot based on the Job Type
00:07:52 -
Triggering macro with any change in sheets
00:02:42 -
Creating Name Range using shortcut
00:03:28 -
Create chart to represent multiple parameters effectively 01
00:02:53 -
Create chart to represent multiple parameters effectively 02
00:03:41 -
Preparing data for Spiderweb chart and change the graph setting
00:04:39 -
Using macro to Hide and Unhide working sheets
00:06:31 -
Inserting Trendline and formatting for better view
00:03:15
-
-
Conclusion
1 Lectures 00:03:25-
Conclusion of all 3 dashboard
00:03:25
-