A Simple Data Analysis on Excel

First let us have a look at the raw data.

It is an excel sheet with 264 rows.

Step 1 : Transforming the Data

• First we turned the sheet into an Excel Table using ctrl+T shortcut.
• If you notice, the manager column is not in a proper format. We can use the trim() function to remove the unwanted space. However before deleting the old column, paste the new column as values to avoid the reference error as shown below.

• Now the Quantity Column is given in decimal values which is not meaningful. So we turn the values in this column into whole numbers using roundup() function.
• Now we remove the duplicate values from the table.

Step 2 : Descriptive Statistics

• We are going to get the summary statistics on numeric columns. We can easily get this from the 'Data Analysis' option of the Data Tab. If you do not find data analysis option in your Excel, you can add in the Data Analysis Toolkit from the Excel Options.

Finally we got the summary statistics for price and quantity column as follows

• Checking for Outliers : If we look at the Mean and Median of the Price, we may suspect some outliers in the price feature. Let us visualize them using a Box and Whisker Plot. We can create a chart from insert ribbon. Following is the Box Plot for Price Column for each manager.

It is evident that the outliers in the price are from the manager Joao Silva.

Step 3 : Data Analysis

Let us answer following questions;

What is the best selling product?

What is the total revenue?

What is the revenue breakdown by payment method?

First we add a new column called 'Revenue' in out table simply by multiplying price and quantity.

All the above questions are answered using Pivot Table and it is shown below;

Following is an interactive analysis for the above dataset;

Here I have created a drop down list for all the managers using 'Data Validation' and a city field which automatically selects the city using 'XLOOK UP'. Then the revenue for each product for selected manager is calculated using 'SUMIFS()'. Also a 'Conditional Formatting' is added on the revenue to show the variation.

Thank you for taking the time to check this article. I am currently looking for opportunities as a Data Analyst, so please feel free to connect with me.