Data Analysis Using Excel

In this repo, I will show my some Data Analysis Task Using Excel.

Problem statement

I am given sales data of Plexus Biker’s Hub [Sales Data.xlsx]. Analyze the data so that they can get some valuable insights that will help them to maximize revenue.

Task 1 - Dataset Preparation

• Sales data from 2011 to 2016 are given in ‘Sales Data’ file.

• The G Column - ‘Age Group’ is empty. Use formula to derive the age group from the Customer Age column. Let's group the customers based on the following criteria:

  • Youth (<25)
  • Young Adults (25-34)
  • Adults (35-64)
  • Seniors (>64)

• Find out ‘Customer Gender’ using Index Match (Not Vlookup).

• Use formula to derive the month of sales (i.e. January, February) from the Date column.

• Add any other column that might be necessary for the analysis.

Task 2 - Pivot table

Use pivot tables to summarize the data. Do not make more than five tables.

Task 3 - Dashboard

Make an interactive dashboard containing at least 5 to 8 charts.

Task 4 - Insights

Share five insights you’ve found from the analysis. Write them down in the excel sheet.

Solution

All Problem statement's solution can be found in Result of Sales Data.xlsx

Solution of Task 1 - Dataset Preparation

Use formula to derive the age group from the Customer Age column.
Find out ‘Customer Gender’ using Index Match (Not Vlookup)


Use formula to derive the month of sales (i.e. January, February) from the Date column.

Solution of Task 2 - Pivot table

Use pivot tables to summarize the data. Do not make more than five tables.
Pivot-1 Image

Pivot-2 Image

Pivot-3 Image

Pivot-4 Image

Pivot-5 Image

Solution of Task 3 - Dashboard

Make an interactive dashboard containing at least 5 to 8 charts.

Solution of Task 4 - Insights

Share five insights you’ve found from the analysis. Write them down in the excel sheet.