Rediscovering Excel 4/N: Small Dashboard Project

This series is about restarting my Excel journey for the Data Analytics path. It involves revising old concepts and learning new ones to comprehensively understand one of the most common tools in this field.

Gathering public data to work on

There are many websites to obtain public datasets without payments or licensing. They can either be websites like Kaggle or government websites.

For this project, I took data from: https://actuariesclimateindex.org/data

Understanding the dataset

I went through the data and tried to understand what it was supposed to mean and also how data had been arranged.

There were 3 worksheets for every component (Sea level, CDD etc).

The first one is monthly data from Jan 1961 to May 2022.

The second one is seasonal data from Winter 1961 to Spring 2022

Dec-Feb was winter, Mar-May was Spring, Jun-Aug was Summer and Sep-Nov was Fall

Winter, Spring, Summer and Fall had been numbered 1 to 4 respectively while months were numbered 1–12.

The third worksheet was unstandardised data.

Data Cleaning

The data was made for display which meant there were some formatting and styling done to the table which is not required for this project.

Clearing all formats

Removing colours, borders and font formats

Adding gridlines

Before

After

Removing data not required for the project

Unsmoothed variants of the table removed

Tables which were removed

Tables kept

Worksheets containing unstandardised data removed

I wouldn't be working on unstandardised data for the project and so removed those worksheets.

Combined Monthly and seasonal data into single worksheets

Considering that there were still 2 worksheets for every component (Monthly and Seasonal), it was difficult to navigate and it also seemed reasonable to put monthly and seasonal data in a single worksheet for a given component.

Removed NULL values/columns

A few components did not have data for certain stations. For example, no sea level data for CAR and MID while no CDD data for CAR.

Checked for other issues

Checked for any NULL cells

Checked for typing errors

Checked for missing columns or rows

Data Processing

Now that the data is cleaned up we can start working on the data. I created a separate worksheet “Working1” and pasted the sea level monthly and seasonal data. However, I also made sure to paste transposed version. That is the station names are now the column names and it is easier to look at all the axes. In the original layout, the table was a horizontal one.

New format: Monthly (left) and Seasonal (right)

Renaming columns/rows

In the original tables, there were no row/column labels for Year and Month/Seasonal. Moreover, the months and the seasons were numbered which may not be obvious in charts at first glance.

I labelled the year and month/season columns and converted the numbers into Jan-Dec and Winter-Fall.

Monthly table

Seasonal table

Pivot tables

I created pivot tables out of the monthly and seasonal data separately. This will also help to create dynamic pivot charts for the dashboard.

Moreover, as shown above, the tables contain data spanning over several years and often individual data for a year are not very useful, especially the older ones. However, they have an important role to set trends and compare the present to the past.

Hence, I created a pivot table to take month-wise average sea levels for every station.

The average sea level of a station in a month over the years

Data Visualisation

I selected the pivot table above and created the following graph to compare various stations and their average monthly sea levels.

Monthly sea level over the years

I noticed how the minimum was that of ALA and the maximum was that of CEA.

I create pivot tables for those 2 stations and plotted corresponding charts.

ALA (above), CEA (below)

I repeated the above for seasonal data to obtain the following.

Seasonal sea level over the years

ALA (above), CEA (below)

Now with these visualisations, I can create a dashboard for sea levels over the years.

Dashboarding

I used the 3 charts for monthly and 3 for seasonal data to create the dashboard.

Monthly data

Seasonal data

I also added slicers for dynamic filtering on the dashboards.

A slicer for monthly data

Slicer used to only show 2022 data

Monthly and seasonal charts put together with respective slicers

Other design elements

I also added a title and a description to explain the dashboard along with some more chart labels.

Overall dashboard

Using the slicers

Observing 2017’s monthly and seasonal data (not average over the years)

Conclusion

For this project, I gathered actuary data from government public datasets, and performed data cleaning and pre-processing. Following this, I created visualisations to understand trends and created a dashboard to consolidate these visualisations. I also added dynamic features for easier filtering by the observer without requiring modifications to source tables.

Files are uploaded on GitHub:

EDA-Projects/Excel at master · binayakbishnu/EDA-Projects

Next up:

Extending this project for other components and making a cumulative dashboard

Portfolio | Github | LinkedIn | Twitter