HOW TO CREATE AN INTERACTIVE DASHBOARD ON EXCEL- PART ONE

Creating an interactive dashboard in excel can appear to be a daunting or even impossible task…but I’m here with good news, It’s not just possible but fun too hurrayyy!!!

LET’S GET STARTED!

DATA SET

Extract and clean your data set; ready for use. The table below is an example of a cleaned data set ready for use!

PIVOT TABLE

Then create a new sheet on the same workbook for the pivot table. The pivot table, just as the name dictates is a set of tables extracted from the data set in order to help compare the various entities from the data set. You can see it as a table for graphs like in mathematics...lol.

The table helps in making analysis very easy and you can “draw” different graphs from the table. That in essence is what actually produces the beautiful dashboards we see in data analysis.

CREATING THE PIVOT TABLE

Pivot tables are very easy to create. The basic things to know are the entities one intends to compare; more like the “x” and “y” in a table.

· You can rename the new sheet created for your Pivot table. Here, it’s “Pivot New”.

· Then go to “insert” on the home tab and click on “Pivot Table”

· A dialogue box will pop up as shown in the diagram above

· You’ll notice that the “Existing Worksheet” column is already checked and the cursor is blinking on the Table Range column.

· Go ahead then and click on the sheet with the data set. Here, it’s the “Employee Data”

· Then select the range of tables clicking on the first cell in the column and then pressing down the shift+ctrl keys, use the down and right arrows to select the whole table that contains the data.

· When done, the name of the sheet will appear on the column.

· Then click “Ok”

· You should have something like the figure below:

It gets more interesting here!

This is the phase where you get to select the parameters you prefer to compare on your table.

For instance, to know the number of employees according to the age descriptions given in the data set;

· Go to the “Pivot Table Fields” column showing on the left,

· Then drag the “Age Description” to the “Rows” column below

· Next, drag the “Employee ID” to the “Values” column.

The “∑” just before “Values” indicates summation. In this case, we need to “count” the number of employees in each category not the sum.

Thus, we click on the dropdown button just beside the “Sum of Employees ID” and select “Value Field Settings”

· Then select “Count” as indicated in the diagram below:

· Click on OK and then you have something similar to the figure below:

You can as well edit the title from “Count of Employee ID” to “No of employees”.

To create more tables, you can go through the same process or simply do a copy and paste of the previous table and then select the parameters you want to compare.

For instance, to compare Department with the No of Employees;

· Just copy and paste the previous table as shown in the figure below:

· Then uncheck the boxes,

· Drag the Department to the Rows column

· Drag the Employee ID to the Values column and change to Count as explained in the first Pivot table.

· You should have something similar to this:

· Continue the process for as many Pivot tables as you desire for your graph but ensure there are spaces in between each table.

CREATING DASHBOARD

This is getting hotter…lol

· Start by creating a new sheet named Dashboard.

· Go to “view” and uncheck the “Gridlines”. This removes the gridlines from the sheet and makes it appear neater.

· You should have something similar to the figure below:

· Then select all the cells,

· Go to the “Home” tab and click on “Fill color”

· Select any color of your choice.

· You should have something similar with the figure below:

In subsequent articles, we shall continue from here and look into creating or beautifying(lol) your dashboard and most importantly making it interactive!!