In this post, I have tried to make an excel based dashboard taking data from past questions asked in Finals of 2017 Modeloff, Section 4 – Data Diving. The question gave temperature and rainfall data of 18 states of the USA for the year 2010. The task was to bring the data to life, show pattern and information hiding in it.
A map was also given and asked to use it optionally or we could use Excel’s newer map tools. However, I have not used the map or any other map tools for the dashboard.
The time given in test was 60 min, but I have taken way more time making it.
What interested me to make a dashboard on data was because a lot of Bigdata analysis is done on temperature and rainfall. I thought it would a learning for me as well to make such a dashboard in excel.
The dashboard gives a standalone and relative position of the city selected in terms of highest/lowest/average temperature and rainfall.
There are two drop downs, one for a city other for a month. The three tables give yearly, monthly, and overall data. The last table giving overall data also highlights selected city and maximum/minimum data.
Two line and bar combination charts are given which talks about the temperature and rainfall for the year and month in detail. With chart at the top, one can find hottest months in the year and most/least rainy months. The chart at the bottom can help you to analyze the specific month.
Two scatter plots are also given to put the data of city for the month you choose in perspective. Here you can find cities which more or less hotter/rainy than the city and month of your choice. Please take care of the axis name while you read the charts. The red dot is your city.
link to Dashboard
Hope you find the the dashboard useful. Any comments on further improvements are welcome.