Plug & Play Excel Dashboard

Make your custom "EXCEL DASHBOARD" in simple steps with VBA for FREE

Description and Details

Make your Excel Dashboard by choosing charts (Eight different types of Charts) from the chart templates

There are eight different types of charts which you can use for any kind of data analysis and graphical representation

All charts are made from shapes which are freely available in Excel

This concept is developed as Excel has some limitations like the values and texts in excel graph are not clearly visible if there are many

datapoints.

The quality and clarity of the graph matches to what you see in DOT NET application and other professional charting software

Excel dashboard :

for Educational purpose:- This Excel App can be utilised to learn about different shapes that can be used for making professional looking

graphs in Excel with the help of formulas, pivot tables and visual basic for application

for Programmers:- This concept is made possible only with the help of visual basic for application as the shapes have sized properly,

positioned correctly, etc for the purpose of building graphs

for Corporates Employees:- This Excel App can be used for business reporting purpose, for operational metrices like SLA, TAT, Productivity,

for analytical purpose, for use by Quality Management team etc

Moreover, this is a good startup product for developing your skills in terms of preparing excel charts with Visual Basic for Application

Instructions

You can create your own custom dashboard by following the below steps

On an average there can be a maximum of 50 datapoints fit in one screen which calculates upto 10 to 12 charts if the average

datapoints is set to 5. however if you need more number of charts to be fit in a single page, then you can always adjust the width

and height of the chart to accomodate more than 12 charts in a single page

Pre-Requisite

Things that you require before you start working on your custom dashboard are mentioned below

Firstly you will need properly filled data table with several columns (atleast data with 10 categories, 4 categories for filtering) to completely

populate the dashboard. If there are less number of data, the dashboard would look empty. Data can be of any kind, for example operations

data like turn around time (TAT), productivity, service level agreement (SLA) and accuracy. can be human resource data, business analysis

data or statistical data

Remember that this charting software is only for calculated datasets, i.e. data values should be calculated already like sum, count,

sumproduct, average, median, mean and mode. This charting software will only dispaly the chart with the corresponding data values. These

types of dashboard can be used for presentations, slide shows etc.

After your data is ready, the data has to be pasted in the data sheet of this dashboard. post that you have to analyse which type of chart

is suitable for each dataset

Column / Bar / Stack graphs are used when there are more than 5 datapoints. so use these graphs when you have to dispaly chart by month

(12 datapoints), These charts also make efficient use of the space

Other graphs like Donut / Pie / Speedometer / Thermometer is used when there are less than 5 datapoints. so use these graphs when you

have to dispaly chart by Qtr (3 or 4 datapoints)

Step by Step Instructions to create Excel Dashboard (One-time Setup)

1) activate 'charts' worksheet in this workbook

2) enter datapoints in your chart named range in column 'F". for example, if your chart named range has 5 entries, enter 5 in column 'F'

3) create chart range in the 'reports' worksheet. for example, if your chart range has 5 datapoints and if the chart range starts from A2,

then create namedrange from A2 to A6 and name the named range. enter the named range in column 'H' of the 'charts' worksheet

4) select add from the column named 'charts' to add charts to the corresponding templates

5) click on 'Generate Charts' and your chart template is ready for use

6) you will find the template you have just created in the template worksheets (there are 9 template worksheets named from 'template1'

to 'template9')

7) cut & paste the chart templates to the 'dashboard' worksheet. the chart templates are named appropriately, however make sure

there are no duplicate chart names in your chart template collections

8) now register the chart templates in the 'settings' tab (shown below)

8a) the table starts from column 'N' and ends in column 'W' and is coloured in yellow

8b) populate the settings column completely for proper functioning of the charts

8c) refer to the table below for entries in the settings tab corresponding to template names

Bar

range09vc

dashboard

Chart_Bar

_stick_bar

_stick_text

_stick_value

0_Chart_Bar

n/a

template1

Column 1

range07v

dashboard

Chart_Column1

_stick_bar

_stick_text

_stick_value

0_Chart_Column1

n/a

template2

Donut

range09vd

dashboard

Chart_Donut

_Donut_chart

n/a

0_Chart_Donut

n/a

template3

TherMo

range09vf

dashboard

TherMo_chart_group

TM_bar

n/a

0_TherMo_chart_group

n/a

template4

Pie

range09vd

dashboard

Chart_Pie

_Pie_chart

n/a

0_Chart_Pie

n/a

template5

SpeedO

range09ve

dashboard

speedo_chart_group

_Speedo_chart_

n/a

0_speedo_chart_group

n/a

template6

Column 2

range06v

dashboard

Chart_Column2

_stick_bar

_stick_text

_stick_value

1_Chart_Column2

n/a

template7

Stack

range03v

dashboard

Chart_Stack

_stick_bar

_stick_text

_stick_value

0_Chart_Stack

n/a

template9

9) dashboard has four levels of data drill-down (filtering) from macro level to micro level

10) your dashboard is ready for use

Input Data

step by step guide to input data in the 'data' worksheet of this workbook

1) select 'settings' worksheet to enter your data headings

1a) date column

1b) filter columns (where you would like to see four levels of data drill-down)

1c) value columns (can be count / sum / product or any other value)

1d) chart columns (data for the charts)

2) go to 'data' worksheet and paste your data under respective data headings

note: use copy paste (NOT cut paste) method while copying the data to protect named range cells

Secure your copy of Plug & Play Excel Dashboard for FREE

DOWNLOAD

For any query, issues or feedback regarding this Excel App please contact me @ nirmaludupa@yahoo.in