Data Analysis with Microsoft Excel

What is Data Analysis?

Data Analysis is the collection , transformation of Raw Data (which can be a Structured or Unstructured Data) to Make Prediction , or Create Insight and adding value to Decision Making Process.

What is Microsoft Excel?

Microsoft Excel (Popularly Known as MS Excel) is a Spreadsheet developed by Microsoft. It have the following features;

• It organize Data and Numbers
• It is use for Calculation
• It has a designing tools for Visualization
• It is deigned for various Operating System OS (which include Windows, MacOS and many more...)

How to Open Microsoft Excel Document on your Computer

• Click on Start button on the desktop
• Search for Microsoft Excel
• Open the Microsoft Excel your Searched and click on blank workbook
• And your Microsoft Excel document will display

How to perform Data Analysis with Microsoft Excel 

There are basically four stage to perform Data Analysis, they include:

• Data Exploration Stage
• Data Preparation Stage
• Data Cleaning Stage
• Data Visualization Stage

Data Exploration Stage

It basically deal with Knowing the data very well, getting familiar with the data

Asking the question.. How many rows those the data set have?, How many columns does the data set have?, What is the total sales(Revenue, Price etc.)?, What is the mean, median and mode? and many more....

Data Preparation Stage

It basically deals with Knowing more about the data very well, getting very familiar with the data

Thinking about the various details that we need in the data to make a meaningful insight

Data Cleaning Stage

It basically deals with Analyzing of the data

Data Analysis begin here, Cleaning the unnecessary row or column, breaking the data set into something meaningful

Data Visualization Stage

It basically deals the visual display of the most important information we need or we got from our Data Cleaning Stage

The Data Visualization Stage uses Pivot chart, Recommended Chart (Column Chart, Pie Chart, Pie Chart and so on)

Starting my Analysis

Stage 1: From my given Dataset, I first convert my dataset into a table before I can begin anything. Converting the dataset into a table, I click on any cell in the data set and from my keyboard I press Ctrl + T, Microsoft Excel automatically highlight the whole dataset and a dialogue box pop up. Click on the OK button and name your table.

Stage 2: Our Dataset has been converted into a table, Delete the unnecessary columns and also delete the unnecessary rows, Start by Exploring the Dataset. Knowing how many rows and columns it contains using the function [=COUNTA() or =COUNT()], also knowing the maximum or minimum revenue or sales or profit etc using the function [=MAX() for maximum and = MIN() for minimum] and many more.

Stage 3: Preparing the Dataset comes up; Knowing the most important and useful Columns or Rows in the dataset

Stage 4: Then Cleaning of the Dataset Comes up. Pivot table is mostly use in this stage to analyses your dataset very well. For example, I need a summarize table for SALES REP AND SALES…Pivot table is needed in this stage

• It follows the procedure by Pressing Alt + N + V on your keyboard and a dialogue box pop up

•          Select Your table or Range (Select your table you named from Stage 1

•          Choose where you want your Summarize Pivot table to display whether (New Worksheet or Existing Worksheet)

•          If you want a New Worksheet, Click on New Worksheet and click on OK

•         If you want the Existing Worksheet, click on it and choose your Desired Location and click on OK

•       After Clicking on OK from both New Workbook or Existing Workbook, a Pivot table Field pop up with various fields (The column headers)

•          Drag Your SALES REP in the Column and Drag SALES in the values and a summarize Pivot Table pop up (You Can always drag base on what you want)

Stage 5: Visualizing the Dataset comes up; we want to visualize our summarized Dataset between (SALES REP and SALES)

•          Click on any cell from the above summarize dataset

•          Click on the Insert tab

•          Click On recommended Chart

•          Select your best Visuals

•          And Your Visuals Pop Up (Editing is allowed).

TAKE NOTE:

Data Visualization-Choosing Track

Trend                                       Line Chart, Area Chart

Comparison                             Bar chat, Column Chart

Relationship                            Bubble Chart, Bar chart, Column Chart

Distribution                             Scatterplot, Histogram

Proportion                               Pie chart, Donut Chart, Tree map

Tracking Changes                     Waterfall Chart

Tracking Process                       Funnel Chart