Netflix Userbase Excel Dashboard

twxmargerate
Jul 13, 2023
3 min read

This project is to create a dashboard in Excel to analyse Netflix Userbase. In this blog post, I'll show you how I did it. This is the dashboard that I have created in Excel as shown below:

The dataset I used is from Kaggle: https://www.kaggle.com/datasets/arnavsmayan/netflix-userbase-dataset

It's important to read the dataset description to understand what's the data is about so that it will be easier when cleaning and analysing the dataset.

Step 1 : Data Cleaning

When cleaning data, first of all, what I did is to find if there's any duplicate values, in the description of the dataset in Kaggle, it mentioned that the user is identified by their User ID, so if there's no duplicate in the User ID column then it means there's no duplicates.

How I find duplicate:

Highlight or select the User ID column.
Then go Home tab.
Find Conditional Formatting --> Highlight Cell Rule --> Duplicate Values to highlight all duplicate cells.
After that, go to User ID column to filter all the duplicate values.

In this dataset, there's no duplicate in User ID, so there's no need to remove any duplicate.

Step 2: Pivot Table and Chart

Open a new sheet to put the Pivot Table and chart so that it won't get too messy if everything is crowded in one sheet. When creating Pivot Table and chart, it's widely depend on what you want to analyse and what data you want to interpret, so this part is actually quite free and easy. You can use Pivot table to study your data then lastly decide which are the ones you want to put in your dashboard.

For me, I created user count in each country, user count in using each device, percentage of male and female user, user count in each age group, monthly revenue of each subscription type and lastly a sum of monthly revenue.

I'll deep dive into explaining how I created the user count in each country table chart and user count in each age group Pivot Table, and for others they are just regular Pivot Table so I won't deep dive explaining them.

User Count in Each Country

Firstly, create a normal Pivot Table which include the Country field and Count of User ID.

Because it's not allowed to create a Map using Pivot Table, so we need to copy the created pivot table and paste as value.

After pasting the table, we need to convert the data type to geography data. The geography data may not be available in all version of Excel, as for me, I'm using Microsoft 365 MSO Version 2306. You should be able to find this in your Data tab. If you're using Microsoft 365 and can't find Geography in your Data tab, please kindly update the latest version and restart your Excel.

Here's how you can convert the data type of the country into geography data type:
- Go to Data tab
- Select the data you want to convert
- Then Click on the Geography in Data Styles
- Wait for it to convert, then it's done!

After that, we go to Insert and click on Charts to insert Filled Map.
And now we've got our Map, we can customise however we like it.

User Count in Each Age Group

Create a Pivot Table which include Age and User ID count.

Select all values in Age column, right-click and click on Group

Window called Grouping should appear, starting at and ending at is the entire range, and by is how many numbers to group together, here I put 5 so my grouping will be 26-30, 31-35, 36-40, 41-45, 46-51.

And that's how we get the pivot table with Age Group.

After that, you can create other Pivot Tables and charts which you want to show in your dashboard.

Step 3: Creating Dashboard

Open a new sheet for the dashboard. It's pretty simple to create dashboard in Excel. We just have to put the charts and Pivot table together.

For me, I just copy and paste the following charts/tables:

Sum of monthly revenue Pivot Table,
Subscription type monthly revenue pie chart created from subscription type monthly revenue Pivot table,
User count using different devices bar chart created from its Pivot table,
The filled map created,
User count in each country Pivot table,
User count in each age group Pivot table.

After that, I add gender, age, country slicers which can apply on all pivot tables and charts in this dashboard. Note that the filled map chart we created is not created from Pivot table, therefore the slicers can't apply to the filled map, so when we're using the slicer for filtering, the map will not have any changes.