How to build an Ultimate Interactive Dashboard using Excel

Design and development Approach:

1. Prepare Dashboard Base
2. Create Structures
3. Add Outlines
4. Create Pivot Tables
5. Add Metrics
6. Create Charts
7. Customize
8. Create Slicers

In this tutorial, we will go through how to build an interactive and dynamic excel dashboard from scratch using the sample dataset provided here by clicking this link to download the data.

Requirement Gathering:

Here is some useful information you may want to gather before designing your dashboard.

1. What info would the audiences need to see on this dashboard?
2. How can it be more user friendly?
3. What type of data would your audience want to be available?
4. What type of charts would your audience need to be added onto the dashboard?

Here are the following steps:

1. Prepare the canvas (base) for the dashboard by removing the gridlines on the sheet, making sure no cell has any color/value yet.
2. Create the overall structure of our dashboard using the shapes which are readily available on Excel.
3. Add outlines, labels and shapes in the dashboard.
4. Add titles, slicers/filters, metrics and charts in the dashboard.
5. Now, you can start working with the data to create pivot tables.
6. Then, connect the metrics to the pivot tables.
7. Create charts base on your audience in the dashboard.
8. Now it’s time to customize the dashboard to make it look more polished and professional.
9. Finally, add the slicers.

Procedures:

• Remove grid lines on dashboard sheet by clicking view on the top tool panel and uncheck gridlines. This makes the base (canvas) more polished.

• Insert shapes to display the metrics/data on the top of the dashboard. On the LHS of the canvas (base) add panel to insert the slicers (filters).

• Add narrow rectangle text boxes to include labels. Below these labels, add large rectangle boxes to insert charts.

Tips:

You can format multiple shapes at once by selecting multiple shapes while holding down the CRTL key and clicking to all the shapes necessary to format.

Also, you do not have to create a Pivot Table again using the raw data. Just copy an existing Pivot Table and adjust the VALUE FIELD SETTINGS as needed.

• Insert charts from Pivot Tables sheet into the large rectangle boxes.
• Create 3 slicers to filter the dashboard per YEAR, JOB TITLE and MONTH.

Note:   To ensure that the data on the dashboard is always updated and accurate, make sure to connect the slicers to all necessary Pivot Tables.

Conclusion:

Congratulation! We just completed “how to create an ultimate Interactive Dashboard using sample Social Media Dataset.” We linked to the dataset, study and review the data. We also went through sample questions you may have for the end users. We discussed the necessary steps to design and develop the dashboard. We then follow all the steps and procedures to complete our dashboard.