Excel Dashboard 

Dashboard fundamentals

Introduction to Dashboard – Why, When and How

Design Tips and Ideal Data Formats

Drawing Excel Charts

Building Up and Down Indicators on Charts

Formatting cells and tables in Excel

Trend Sparkline with variations

Bar Sparkline an Win and Loss Sparkline

Pie chart and formatting tips

Two-axis charts

Thermometer chart

Stacked Chart

Spider chart

Bubble chart

Bullet chart

Showing forecasting with line charts

Waterfall – New Charts in 2016

Format Specific Period, Data Point, Marker, XY, Positive and Negative

Conditional Formatting in Graphs

Histogram Chart

VLOOKUP and HLOOKUP

MATCH and CHOOSE

INDEX Function

Indirect Function

Pivot tables basics

Pivot charts basics

Form Control with example

Check box controlled graph

List controlled graph

Chart with interactive legend

Graph using Macro

Overview of Dashboard

Identifying of Unique items for a dashboard

How to make lists in a dashboard

Building dashboard basic layout and formatting tips

Linking values in dashboard

Using Dynamic functions

Calculating values based on criteria

Download Practice FIles

 

Single sheet tracker 

MRTS Sales Dashboard Question

MRTS Sales Dashboard Solution 01

MRTS Sales Dashboard Solution 02

MRTS Sales Dashboard Solution 03

MRTS Sales Dashboard Solution 04

MRTS Sales Dashboard Solution 05

MRTS Sales Dashboard Solution 06

Power Generation Tracker Question

Power Generation Tracker Solution

Download Practice Files for SIngle Sheet Tracker

 

Pivot Table Dashboard

Introduction to Dashboard Course

Introduction to 1st Dashboard

Understanding the data

Text Function

Dynamic data Create Table

Naming the dynamic data and the using it in function

Preparing 1st Pivot and updating data

Classic view and Filling blank with zero in Pivot

Create Chart for comparison from the Pivot

Create Trend line from the Pivot

Creating Pie Chart from Slab Report from Pivot

Preparing Framework for DashBoard

Inserting Slicer using Chart

Customize Slicer Setting

 

Map chart dashboard using Advanced Excel functions

Brief on 2nd Dashboards

Importing Data from the Internet

Converting Line Item into a Currency Table

Calculation of Currency using INDEX and MATCH

Creating Slabs using Vlookup True Parameter

Creating Qtrs based on Financial or Calendar Selection

Preparing the base by removing duplicate for Dashboard

AverageIF for calculating Country Wise Salary

Substituting ERROR with zero using IFERROR

Introduction to Function INDIRECT Part 1 and 2

Using Function INDIRECT to Retrieve AverageIF from relevant sheet

Using Function CountIf with INDIRECT to calculate No OF Emp

Calculating Contribution Country wise No Of Emp

Using NameRange to simplify calculation

Using NameRange with formula in 2nd Dashboard

Introduction to Offset Function

Offset Function in Excel

Using Offset Function in Dashboard

Use of MINIF in Dashboard

Use of MAXIF in Dashboard

Using Average to get Designation Wise Experience result

Using MAXIF with multiple conditions

Using MINIF with multiple conditions

Dynamic Dropdown List

longitude and latitude with plotti a g data on dashboard

Using REPT Function based on the percentage calculation

Activation of Developer Tab and creating Scroll Bar

Inserting Trend chart based on data

Advance INDEX and MATCH with multiple references

Inserting Spark Line

Insert World Map into the chart

Plotting Data on Map Chart

Controlling Map Preview from dropdown

Summarising 2nd Dashboard

 

Dynamic Dashboards using Excel Macros 

Brief understanding on Dynamic Dashboards using Excel Macros

Implementation of OFFSET for dynamic data

Creating Trend Chart from 2 Pivot Table

Modifying the Chart Settings for better view

Using Macro recording for Trend Chart 01

Using macro to update pivot based on the Job Type

Triggering macro with any change in sheets

Creating Name Range using shortcut

Use of MAXIF and MINIF with single and multiple parameters

Create chart to represent multiple parameters effectively 01

Create chart to represent multiple parameters effectively 02

Preparing data for Spiderweb chart and change the graph setting

Using macro to Hide and Unhide working sheets

Inserting Trendline and formatting for better view

Conclusion of all 3 dashboard