Data Analysis & Visualization in Excel: Pre-processing, Analysis, and Visualization Techniques

A2 Data Analysis &
Visualisation

Table of Contents
PART 1............................................................................................................................................3
Evaluation of data pre-processing, data analysis and data visualization in excel........................3
PART 2..........................................................................................................................................13
2.1 Number of students like vanilla flavour of ice crème..........................................................13
2.2 Number of students are male and female............................................................................16
2.3 Mean and Median of participants like chocolate and strawberry ice crème flavour using
SPSS software............................................................................................................................18
2.4 Steps to follow to compute K-mean clustering in SPSS......................................................23
2.5..............................................................................................................................................30
2.6 The advantages and disadvantages of using SPSS and Excel along with practical examples
...................................................................................................................................................30
REFERENCES................................................................................................................................1

PART 1
Evaluation of data pre-processing, data analysis and data visualization in excel
Data pre-processing, analysis of data and virtualization of data is important for gathering
the useful information from the raw data.
Step 1: Pre-processing of the data:
Data processing is the first step and a most significant component of data preparation.
The data processing describes the type of processing which is basically performed on raw data in
order to convert the data into a structured format so that data mining and other data science tasks
are easily get performed (Matheus, Oestreicher and Bergsten, 2020). There are basically various
data pre-processing tools and techniques through which the raw data can be processed.
For example, in the case of superstore case, the filter has been used to analyse the number
of years have been taken for the purpose of determining and analysing the decline in the sales of
superstore from the period 2009 to 2012. The filter allows the users to better analyse the data
because it display the rows that meet the filter criteria and hide the unnecessary and unrequired
rows.
In the present case of superstore example, it is identified that the sum of sales and profits over
the years are as follows:
Year Sales Profit

2009 1754061 152252
2010 1318867 132154.9
2011 1473355 161414.1
2012 1601552 130967
For data pre-processing, the use of filter option in excel helps in copying, formatting and
printing the overall dataset without sorting and moving it to other place. The data pre-processing
is highly important for data analysis with the help of which most reliable, precise and robust
results for enterprise can be collected.
Beside using the filter option for data pre-processing, there are various other essential
functions in excel which is used for data pre-processing. These are as follows:
SUMIFS function: This is one of the most basic excel function used for data pre-
processing which sum the specified value in a range of cells using SUMIFS function
respectively. In the present case, with the help of SUMIFS function the data pre-processing can
be done easily (Lee and et.al., 2022). To determine the yearly sales and profits of superstore over
the years the SUMIFS function of excel is used.
Formula = SUMIFS (Sales rows, date rows, “>=” &DATE (year, month, date), Date rows, “<=”
&DATE (year, month, date)
In next step, to determine the decline in sales and profit the following formula is used:
= (current year sales – previous year sales)/ current year sales * 100

By using the SUMIFS excel function, the total sales and profit over the years has been
determined and further using the decline formula the decline the sales and profit are determined.
This are as follows:
Year Sales Profit
Change in
sales
Change in
profit
2009 1754061 152253
2010 1318867 132154.9 -24.8106384 -13.20045669
2011 1473355 161414.1 11.71362441 22.14010226
2012 1601552 130967 8.70106462 -18.86276003
Pivot table: A pivot table is a powerful tool or excel function with the help of which users
can calculate, summarise and analyse the data based on comparison, patterns and trends. A pivot
table is one of the interactive way with the help of which users can summarise the large amount
of data more quickly (Carvalho, Moreira and Torres, 2021). It is also one of the most significant
way through which sales and profit of superstore based on product category, years are easily
determined and compared.

After using the pivot table function of excel, the sales of each product category has been
clearly identified which are as follows:
Table 1: Total sales and Total profits based on products category
Order Date (All)
Row Labels Sum of Sales
Sum of
Profit
Furniture 5178590.542 117433.03
Office Supplies 3752762.1 518021.43
Technology 5984248.182 886313.52
Grand Total 14915600.82 1521767.98
The use of Pivot table is best for data pre-processing as it helps them comparing the total
sales and profits generated by each products of superstore. In simple term, it can be said that A
Pivot table is especially designed by the company for querying large amount of data into many
user-friendly ways (Majeed and et.al., 2020).

Further, the sum of sales and sum of profits of each product category of superstore in the year
2009, 2010, 2011 and 2012 which are determined through Pivot table are as follows:
Table 2: Sum of sales and sum of profit of different product category in the year 2009
Order Date (Multiple Items)
Row Labels Sum of Sales
Sum of
Profit
Furniture 1472671.724 61804.53
Office Supplies 1035399.64 177646.27
Technology 1701825.482 194645.22
Grand Total 4209896.846 434096.02
Table 3:Total sales and total profit of each product category in the year 2010
Order Date (Multiple Items)
Row Labels Sum of Sales
Sum of
Profit
Furniture 1252518.416 9397.4
Office Supplies 910359.95 118143.24
Technology 1397208.679 237376.69
Grand Total 3560087.045 364917.33
Table 4: Sum of sales and profit of different products of superstore in the year 2011
Order Date (Multiple Items)
Row Labels Sum of Sales
Sum of
Profit
Furniture 1268656.078 50422.45
Office Supplies 796383.79 86960.01
Technology 1364905.113 242928.04
Grand Total 3429944.981 380310.5
Table 5: Total sales and total profit of different products of superstore in the year 2012
Order Date (Multiple Items)
Row Labels Sum of Sales
Sum of
Profit

Furniture 1184744.324 -4191.35
Office Supplies 1010618.72 135271.91
Technology 1520308.909 211363.57
Grand Total 3715671.953 342444.13
Step 2: Analysing the data results:
This is the second step of data processing where the converted raw data is analysed. With
the help of data pre-processing using the excel functions and options such as filter, SUMIFS and
Pivot Table, the users can further analyse the findings. On the basis of first stage of data
processing that is data pre-processing, the findings related to yearly sales, yearly profit, change
in sales, change in profit and products wise sales as well as profits has been determined
(Baviskar and et.al., 2021). The analysis of findings or data are as follows:
Interpretation: On the basis of the above table, it is identified that the total sales and total
profit of all products category in the year 2009 is 1754061 and 152252 respectively. Further, it is
also analysed that the sales and profit in the year 2010 is 1318867 and 132154.9 respectively. In
the year 2011 the sales have further increased to 1473355 and decreased in the year 2012 to
1601552 (Sakurai and et.al., 2021). However, on the other hand, the profit of superstore in the
year 2011 has increased to 161414.1 and in the year 2012 it has reduced to 130967. This result is
actually arisen with the use of filter option as mentioned above.
Analysis of SUMIFS and decline in sales & profit result: