Creating an Excel Dashboard

For this project, I was really hoping to accomplish two things. First, I wanted to re-familiarize myself with a Windows platform through using a Virtual Machine I set up using VMWare Fusion Player (you can see how I did that by viewing THIS POST). The second was to build upon my Excel Skills by creating a simple dashboard using Pivot Charts.

THE PROJECT

The Data

For this project, I used a Video Game Sales dataset from Kaggle. The data set was a single CSV file with sales data from over 16 thousand games. Each game for each gaming platform was on a different row; in other words, a game like Minecraft would be on there multiple times for the XBox, Playstation, and DS. The table also included information like global sales, North American sales, Japanese sales, European sales, platform, genre, and publisher. The games were also ranked in terms of overall sales.

The Ask

After doing a cursory examination of the data and what it included, I decided to come up with some business questions that I hoped the data could answer. I identified three:

1. Which video game franchise is responsible for the most global sales?
2. Which platform's games historically has the best sales?
3. Which genre of games has been the most popular?

These seemed like reasonable questions to ask if I were a new company trying to compete against a brand like Game Stop to stock both new and used games from a variety of devices.

Cleaning and Manipulating the Data

The data wasn't terribly messy, but there were some major issues with it. Here's a quick summary of the adjustments I made:

• Since the data was in CSV format, any letters with diacritical marks (a term I just learned, which refers to elements like accents and umlauts that are over letters) were incorrect. Game names like "Pokémon" were instead listed as "Pokémon." There were about 35 game titles that had this error.
• A significant number of games (271, to be exact) did not have a date attached. I ended up researching the date for each one to fill that in. I did NOT do this for the missing Publishers, however.
• I created a column for Franchise. The most effective way I found to do this was by creating a nested IF query. There was a limitation with this, however, since you can only have up to 64 nested queries. Since I only wanted to find the top 10 franchises, however, this was not a major issue.
• I created a column for decades, again using a nested query. Completing the date column from before helped with this.
• I added two columns for the platforms themselves. One with the full name of the device, since many of the platforms were shortened, and one with the platform company. These were both filled in using VLookups.

You can see the full range of changes I made to the data by looking at the document on my GitHub. I opted to make multiple sheets for each distinct purpose, a technique I adopted from Alex Freberg .

Analysis and Visualizations

I used Pivot Tables to address the questions I laid out at the start, and then made corresponding Pivot Charts to go with them:

1. Franchise Sales - This Pivot Chart revealed that Mario titles had the most global sales, with Pokemon coming in second, and Call of Duty coming in third.
2. Platform Sales - The PS2 had the most sales from their games, although it also had the second-most games produced. The Nintendo DS had the most games produced, but games from that platform were 5th in sales.
3. Popular Genre - Action games racked up the most in sales, while sports games came in second in sales.

When I added these Pivot Charts to the dashboard and added a slicer for decade, it revealed some additional insights:

• There's been an uptick in shooter game sales in the most recent decade, especially in North America. When you look just at the decade of 2011-2020, Call of Duty is the franchise with the most sales.
• Mario might be number one in Franchise sales because of its dominance in the decade of 1981-1990 and 2001-2010.
• The Pokemon franchise had its peak in 1991-2000.
• With my own regional bias with Madden, I did not expect FIFA to have the highest sales for sports franchise, even in our current decade.

When I was putting together my visualizations, I was surprised to find that it did not seem possible to create a tree map off of a Pivot Table, even when the data SHOULD translate to that format. I followed THIS GUIDE to create a tree map using the Platform company, platform name, and games produced that I added to the dashboard. Unfortunately, though, because the tree map is not connected to a Pivot Table, the slicers do not affect it.

You can access the final Dashboard by clicking below (some of the formatting is weird when I put it into OneDrive, but you can get the idea):

Recommendations

When looking at the data, I would make the following recommendations to a company looking to sell video games:

• The latest platforms will bring in the most sales. Going through the decades, you can see how quickly gaming systems become obsolete. In addition
• Playstation, Xbox, and Nintendo are the big three. Almost all games that come out now fall under devices by those names. However, don't sleep on PC, which saw an increase in sales the last two decades.
• Franchises are king. Promoting major franchise releases will be beneficial to any video game business plan.

MY EXPERIENCE

This was my first time using a Virtual Machine, and the first time I operated on a Windows device in a WHILE. The main concern I had with operating on the VM was that it was SLOW. However, it will be nice to have the option to practice some of the Excel features that are only available on Windows devices, like Power Pivot.

In addition, the Virtual Machine uses up a ton of memory. I had to clear out quite a bit from my device to use it.

As for the project itself, I was able to practice some Excel Skills like VLookup, the IF function, and Pivot Tables. I wish I had found a data set that had multiple tables, so I could have practiced relational in Excel, but that will have to wait for a future project. I also found that it was easier creating a dashboard in Excel, but from a visualization standpoint, I greatly prefer the look and customization options available on Tableau.

As always, please feel free to leave a comment, and offer any feedback!