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.
Given with two sets of data like below, and asked to compare them graphically, what charts will come to your mind.
Today, I will show you to compare two sets of values using scatter plot chart. something like this
the data points above the (x=y) slanted dotted line show increase in value vs first value and vice-versa for points below the line. Nifty right! It visually shows at once the points have higher values than previous ones.
Just select the data >> goto insert chart >> select scatter plot chart, all your data points are plotted. Now for an x=y slanted dotted line, the plot below as another series
0 0 or lower than your lowest data point
100 100 or higher than your highest data point
select the chart first, goto chart tools >> design >> select data
select the red dots, right click and add the trendline.
format the dots to become invisible, format the line to a different color and dotted.
format the x and y axis to numbers hide the highest number of slanted (x=y) line.
In my case, I have axis formatted from 10 to 90.
Make chart say more with XY chart labeler add-in and other formulas, look like below
Recently, I was given sales data to be analyzed by date. It was suspected that more sales is done towards the end of month to meet the month end target. Essentially they wanted to know the sales pattern in months across the years to be divided between dates 1-20, last 3 days and remaining days. Different ending date of months made the problem difficult.
The function EOMONTH was there for rescue. It returns the last day of the month that is the indicated number of months before or after start date. It is extensively used in financial world.
I have created dummy data to show the analysis. First I created helper columns to breakdown the problem. Columns were created for first date of the month, 20th date, 3 days before last date and last date.
By giving -1 as months after start date and then adding 1 to it gives me first date of the month. The last date of the month was EOMONTH function with 0 month after start date. Finding 20th date of the month and 3 days before the last date is matter of simple adding and subtracting number.
After making helper columns using simple IF function did the job for me. Then I collated the functions in helper column to make a single cell formula. It did the job for me. Later I used pivot table for further analysis.