Let’s first understand what is a Dashboard????
The term dashboard originates from the automobile dashboard
where drivers monitor the major functions at a glance via the instrument cluster.
In the same manner, if we create a Dashboard from a set of
Data which is relevant to a particular objective or business process, we will
get the view of the KPIs at a glance. Now a days lot of programmes have
dashboards. The same can be created in excel to a very professional level. This
post is just a glimpse of a basic yet a powerful dashboard. In the coming posts
I’ll try to introduce much some more complex and amazing dashboards which will
surely gets you an edge over others at your workplace J
Before we look into the steps to prepare a dashboard, I have
created a small piece of visual presentation without an audio. Just go through
with this and you will get a fair idea where we are heading….
Hope you got it….
Now before we move forward just
go at the end of this post and download the sample file, so that you can follow the below steps practically.
go at the end of this post and download the sample file, so that you can follow the below steps practically.
There are three worksheets in the Exercise file you have
just downloaded. We have to use “Data” and “Data for Drop Down List” worksheets.
Now you can insert a new worksheet in this workbook.
Now we will create a drop down list of State's. For this just
select cell B3. Then go to Data tab à
Data Validation.
Note :- If you want to know the uses and procedure to apply Data
validation in detail. Read this post.
A dialog box will appear, choose “List” from the drop down
and click inside the source box.
Then go to “Data for drop down list” sheet and select all
the given states i.e. “A2:A32”. And then press OK.
Now you will see a drop down is coming in B3 which shows the
list of all the States.
Come a bit below in Excel worksheet and create a horizontal
list of months from April to March for Financial year 2013-14 e.g. I have
created this list starting from cell C21. And in Cell B21 to B24 write down the
particulars. As shown below:
Now to get the relevant data in the above table on the basis
of the State’s drop down list we will use SUMIFS. We have chosen this formula
because in this table we need the sum of the concerned values on the basis of
two criteria i.e. Months & State. So first we will put a formula in C22,
which will do a sum of TOTAL CALLS ATTENDED by taking APRIL-13 and STATE (Cell
B3) into consideration. So the formula will be…
=SUMIFS(Data!$C$3:$C$405,Data!$A$3:$A$405,Sheet4!C21,Data!$B$3:$B$405,Sheet4!$B$3)
Note:- I have used $ sign as I need to freeze the ranges because I need
to copy this formula and paste it further for other months. If you still
uncomfortable in using $ sign or you don’t have any idea about it. Read this post.
Same formula will be
applied for TOTAL CALLS REJECTED except some minor changes…
=SUMIFS(Data!$D$3:$D$405,Data!$A$3:$A$405,Sheet4!C21,Data!$B$3:$B$405,Sheet4!$B$3)
And for TOTAL CALLS RECEIVED also….
=SUMIFS(Data!$E$3:$E$405,Data!$A$3:$A$405,Sheet4!C21,Data!$B$3:$B$405,Sheet4!$B$3)
If you have chosen any state from that drop down list, your
table must be looking like this.
Now select whole table.
Then go to Insert tab àColumnàCluster Column Chart
It will look like this.
Now just right click on any of the column in the chart which
is representing TOTAL CALLS RECEIVED data. In my case it’s a green color
column. So I am selecting it by right clicking on any one of them. An then
select “Change series Chart type”.
From “Change Chart Type” box just choose LINE chart. And
then press Ok. Here you will see that only TOTAL CALLS RECEIVED data will be
shown as line and other data will still be in column form.
Repeat the same procedure again for either TOTAL CALLS
REJECTED or TOTAL CALLS ATTENDED but this time choose “Stacked Column” Chart as
shown in below screen shot.
Now it will look like this.
Now we need to make some cosmetic changes which is entirely
up to you. Just click on the chart so it will be selected then go to layout tabàGridlinesàPrimary Horizontal
Gridlinesà
Choose None.
Also remove legends (again Optional). Go to Layout tabàLegendàNone
Now we will insert three “Check Boxes”. Go to developer tabàInsertàForms ControlàChoose Check Box. Now
drag it and place it inside the Chart at the top and now right click and edit
text to “Total Calls Received” and again right click and click on “Format
Control”àControlàClick in “Checked”
option box and in Cell link: box just click inside it and then click on cell
A24. It will look like the below image, where you can see in cell A24 its
written as “True” because the check box is in checked mode.
Note: If you are not getting Developer tab then you can Read this Post.
Now follow the same procedure for TOTAL CALLS ATTEDNED and
TOTAL CALLS REJECTED and link them to cells A22 and A23 respectively. It will
something look like this.
Also put a Chart title. This will show currently selected
State from the drop down list. For this fist click on the chart and go to
Layout tabà
Chart Titleà
Above Chart. Now just without clicking anywhere just click into the formula bar
and put = sign and click on B3 which is in our case a drop down list.
Now just last but not the least thing we need to do. We have
to edit the formula , so that it will be linked with the results of check
boxes. Click on C22 just edit the formula and change it like below. Copy and
paste the formula further to all the months.
=IF($A$22,SUMIFS(Data!$C$3:$C$405,Data!$A$3:$A$405,Sheet4!C21,Data!$B$3:$B$405,Sheet4!$B$3),"")
Formula for C23 will be the below one. Copy and paste it
through all the months too.
=IF($A$23,SUMIFS(Data!$D$3:$D$405,Data!$A$3:$A$405,Sheet4!C21,Data!$B$3:$B$405,Sheet4!$B$3),"")
For formula C24 will be.
=IF($A$24,SUMIFS(Data!$E$3:$E$405,Data!$A$3:$A$405,Sheet4!C21,Data!$B$3:$B$405,Sheet4!$B$3),"")
Now just try to play with your Dashboard and check whether
it is working as we desired. Choose different State’s, uncheck different check
boxes etc.
Hope your hard work finally brought a good result…. J
Now I know one thing which you and I can see that those TRUE
& FALSE are still visible on the dashboard but were not in my video. So we
have to give one last final touch. Just select the range A22:A24 and change the
font color to white to make it in visible. We can also hide the GRIDLINES of
the entire sheet so it will look more professional. To do this go to View tabà Gridlinesà just uncheck it.
Thant’s it, now just enjoy it and share your knowledge with
you friends…. J