Excel for Data Analysis: Pre-processing, Evaluating and Visualizing Data

This article discusses the use of Excel for pre-processing, evaluating and visualizing data. It covers various functions and tools available in Excel for data analysis. The article also explains data mining methods used in business such as anomaly analysis, neural network and decision tree. Additionally, it discusses the advantages and disadvantages of SPSS over Excel. The subject of the article is business intelligence and it does not mention any specific course code, course name or college/university.

BUSINESS INTELLIGENCE

TABLE OF CONTENTS
PART 1............................................................................................................................................3
Analysing use of excel for pre-processing, evaluating and visualizing data.............................3
PART 2............................................................................................................................................8
2.2 Explaining the data mining methods used in business........................................................14
2.3 Explaining the advantages & disadvantages of SPSS over excel........................................15
REFERENCES................................................................................................................................1

PART 1
Analysing use of excel for pre-processing, evaluating and visualizing data
Excel is one of the important tool that play crucial role in analysing data in significant
manner. There are several functions that allow organization to get convenience and efficiency in
accomplishing objectives. In the current time, data analysis plays essential role in decision
making, it allows the users to get proper ability to store, analyse and monitor distinct level of
statistical data in effectual manner.
There are few in built function which gives ability to match right formula with
appropriate type of data analysis. It includes concatenate, count, if, average, sum, v lookup, if
error, min ifs, etc. in order to make proper data analysis organization can use excel for having
better functioning. Excel is one of the convenient tool that can be used by organization to get
the proper functioning and processing in turn better action from the available relevant
information can be taken. There are various kinds of functions available in excel which can
provide guidance to employees or user to accomplish their task in effectual and proper
organized manner. In excel V & H lookup is concerned with vertical and horizontal for
searching values for meeting the particular purpose.
There are various type of tools available that can be used to evaluate data by using
certain specialized features available in the market. By using excel there are different kinds of
feature that can be useful by various people to meet their distinct ways of requirements. In
order to be prompt and effective via using distinct ways of graphs, charts, etc. to visualize large
complicated data set in easy & understandable manner (Salkind and Frey, 2021). Storing,
analysing and transforming data to create monthly, quarterly and yearly financial & non
monetary reports. It can be aid in maintaining proper recording of vast business transaction that
can be useful for comparing previous year performance with current period. This can be
crucially utilized to draw conclusion regarding prevailing changes so that required course of
action to implement modifications can be obtained.
Excel in present time has involved different kinds of advanced functions which can be
exerted by every scale of business to analyse its performance trend. These mentioned actions
can help in getting higher capability of obtaining desire level of data to make strategic decision.
In addition to this, pivot table can be implemented to have make large complex data in in easiest
manner. Identifying prevailing errors to have proper information of existing errors can be done

easily. It provides assistance in having proper information regarding company's lacking areas in
turn higher consideration can be given to improve prevailing situations. In addition to this, it
becomes possible for company to utilize available features for managing its functional areas in
effective way.
It permits the organization to be quick in continuing repetitive work to done effectual. If
and conditional formatting can be used by users to get ability to highlight the data that according
to requirement to have proper functioning. Scheduling, basic accounting, tracking sales return
on investment, data assessment can become possible by implementing excel into organisational
processing. in addition to this, variety of formulas can be used to make procedure efficiently.
Structuring worksheet to have to relocate and understand specific categories of information can
become possible by executing pivot table. Visualization can present and reflect large source of
data in ease and understandable pattern (Elliott, 2018). It can be said that organization can gain
convenience in attaining its objectives by focusing on having strategic decision via analysing
data with the help of data analysis features availed in excel. Excel can be beneficial for all scale
operating frim as It gives assistance to in utilizing different kinds of features offered by excel in
advanced manner through updating versions to meet changing requirement of users.
There are variety of reasons which can be helpful for the company to make significant
implementation to contribute in gaining competitive advantages. With help of pivot table
company can get ability to complete of tasks in better sustainable manner by taking decisions
effectively. The large vast data can be easily summarized for creating graphical presentations
to make strategic conclusion in turn higher capability of taking right decision can be done. It
can be created by following certain steps systematically to derive correct and accurate manner
presenting table in turn better approach for establishing relationship can be done. This actions
can be achieved by paying attention on proper implementation of flowing steps.
 In the initial step the source of data is selected for creating the pivot table. In addition
to this, on ribbon there is insert tab which is required to select for proceeding this.
 Afters selecting pivot table excel will ask to choose data that user want to analyse and
place on worksheet where it wants to placed pivot table. Field pane will appear to to
get data by setting relationship through dragging particular shown information.
By following these steps pivot table can be formulated. In addition to this, sales and
profitability information are reflected as follows. The filter option has been selected to

organized data into scatter information by arranging them in quarters to summarize in suitable
manner. After formulation of pivot table chart has been presented to visualize data in better way.
Column Labels
Row Labels <1/1/2009 Qtr1 Qtr2 Qtr3 Qtr4
<1/1/2009
Sum of Sales
Sum of Profit
2009
Sum of Sales 384000.468 583155.794
5
369589.
014
417315.
919
Sum of Profit 18509.72 47701.7 63265.3
6
22776.2
1
2010
Sum of Sales 338003.962
5
260170.541 337843.
7215
382849.
19
Sum of Profit 28030.26 17129.61 40106.6
4
46888.3
9
2011
Sum of Sales 366284.752 434479.037 291268.
3345
381322.
467
Sum of Profit 36982.13 61150.01 26314.7
7
36967.2
2
2012
Sum of Sales 472812.385
5
356108.784
5
363187.
272
409443.
6835
Sum of Profit 42316.3 36221.4 36743.1
2
15686.1
5
Total Sum of Sales 1561101.57 1633914.15
7
136188
8.342
1590931
.26
Total Sum of Profit 125838.41 162202.72 166429. 122317.

89 97
From the above data it can be articulated that sales and profitability information of
specified organization has been summarized in four quarters for each year. On the basis of
above table it can be interpreted that company's sales has been increased in second quarter then
declined. On the basis of this, it can be said that its in fluctuating rend which is indicating
organization sales has inclined in quarter four as compared to first. In the profitability segment
of organization the trend of growth is as well fluctuating. It can be stated that growth is seen in
upward direction in initial part then decreased which is reflecting that firm's performance keeps
on changing. From yearly perspective of organisational growth it can be said that firm requires
improvement as downward sloping performance is seen.
It can be justified by supporting the this statement with the opinion of Ma and et.al.,
(2017) there are various types of elements that largely influence the growth of organization by
declining sales & profitability. It can be said that the most important reason which can affect
the processing of firm is associated with its internal actions. It includes employees skills,
attributes, etc. internal processing play significant role in achieving competitiveness so that
capability of beating competition can be gained. Improper implementation of strategy can highly
impact the organizational plan regarding probability and sales in effectual manner. In addition to
this, these are controllable actions which decreases the capabilities and efficiency of company
so giving focus on it is important for the company . On the other side, Munch, (2017) said that
external factor are more important for company as it involves all aspects like political, social,
technological, legal, economical and environmental. This cannot be controlled as it highly
influence the ability of making business environment inefficient. These factors play important
role in affecting the progress of company. In against of this Davis (2021) depicted that internal
and external both factors largely contribute in affecting organization’s revenue and sustainability.
Firm required to pay attention on all the segment to have relevant revenue and growth.
On the basis of above report it can be identified that there are several distinct factors
which largely affect the functioning of organization that leads to poor performance due to decline
trend of profitability (Anderson and et.al., 2020). It can be interpreted that decreased profitability
due to less organizations capability to generate revenue. This can majorly affect the superstore
capability of maintaining competitive advantages. It is crucial for superstore to be analyse the

prevailing situation in turn better approach for making changes can have identified in order to get
leading position in industry. Having significant processing and proper course of actions frim can
improve the existing action I n better manner for deriving ability to uplift adverse situations.
In addition to this, it can be articulated that another reason that are declining its
performance is providing discounts which are decreasing profitability. Discounts has been
provided by frim to have ability to influence the processing by making customer motivated and
encouraged to select the offerings of superstore. It may contribute in achieving success &
sustainability in industry.

PART 2
2.1
K Means

Initial Cluster Centers
Cluster
1 2

Gender 1 2
Age 13 26
Rice 1 0
From the assessment of above table it can be said that in cluster 2 two of initial centers
age and gender is higher. I n category 1 the outcome derived in case of rice consumption is
greater.
Iteration Historya
Iteration Change in Cluster
Centers
1 2
1 4.450 2.659
2 .241 .420
3 .000 .000
a. Convergence achieved due to no
or small change in cluster centers.
The maximum absolute coordinate
change for any center is .000. The
current iteration is 3. The
minimum distance between initial
centers is 13.077.
Final Cluster Centers
Cluster
1 2
Gender 1 2
Age 18 24
Rice 1 1
In final cluster centers gender & age in category 2 is more as compared to 1 and equal figures
are seen in rice consumption record.
Number of Cases in each
Cluster
Cluster 1 54.000
2 41.000

Valid 95.000
Missing .000
The number of cases in each cluster is found to be valid by 95 by combining outcome of
cluster 1 and 2.

Frequencies
Age
Frequency Percent Valid Percent Cumulative
Percent
Valid
13.00 5 5.0 5.0 5.0
15.00 5 5.0 5.0 10.0
17.00 8 8.0 8.0 18.0
18.00 13 13.0 13.0 31.0
19.00 20 20.0 20.0 51.0
20.00 5 5.0 5.0 56.0
22.00 21 21.0 21.0 77.0
23.00 1 1.0 1.0 78.0
25.00 12 12.0 12.0 90.0
26.00 10 10.0 10.0 100.0
Total 100 100.0 100.0
The above chart can be taken into consideration for having larger emphasis about age factor.
Descriptive

Gender Age Rice
N Valid 95 95 95
Missing 0 0 0
Mean 1.53 20.33 .59
Median 2.00 19.00 1.00
Mode 2 19a 1
Sum 145 1931 56
a. Multiple modes exist. The smallest value is shown
O n the basis of above table it can be said that sum of mean, mode, medium is greater in
age category as compare to gender and rice.
2.2 Explaining the data mining methods used in business
There are different kinds of data mining method which are used by companies fro the purpose of
attaining ability to make decision on the basis of sufficient and reliable data. Different form of
data mining techniques comprises association, classification, prediction, sequential pattern,
neural network, decision tree, outlier evaluation, etc.
 Anomaly analysis is concerned with making evaluation of those information
that doe not comply with expected behaviour ( Liu, Wu and Lin, 2018,). It is
widely taken into practice by those organization that are associated with making
evaluation of frauds, intrusion detection, et which as well known as outlier
mining method.
 Neural network is based on biological neural which is collection of processing
units with weighted relationship between them. It is utilized for regression
analysis, classification, data processing, etc. this particular technique of data
mining is associated with three pillars that helps in reaching the capability to get
accuracy which comprises model, learning algorithm and activation function.
 Decision tree is structure based approach that is utilized for attaining proper
clarity and easiness. It includes internal, branches terminal , topmost nodes, etc.
that are concerned with particular subject matter. For example- in case of

imposing any law on companies its ability and non capability are distinct via
formulating t the specified manner of structure.
 Sequential pattens is one of the data mining method that is concerned with
drawing conclusions on the basis of certain forms of actions that are occurring.
For instance- This is widely used by firms to identify boom level of growth
(Kadaru and UmaMaheswararao, 2017). In fashion industry higher sells are are
found at the time of special occasions like cultural festivals, etc. so marketing
& sales manager identifies this by looking at continuous occurring trend.
 Predication data mining method is used by firms to have future based evaluation
on the basis of past and present based data that contributes in making appropriate
decision (Davari, Noursalehi and Keramati, 2019). In addition to this, it is
combination of the methods like classification, pattern matching, trend analysis
and relation.
 Cluster analysis is basically a classification method that is exerted by considering
similarities in order to formulate decision. This is done by taking methods such as
hierarchical , grid, partitioning, model and density based approaches for mining
data. These are the actions that are taken into consideration for having ability to
obtain sufficient level of data.
2.3 Explaining the advantages & disadvantages of SPSS over excel
There are various advantages of using the SPSS method which provide assistance in
gaining objectives of user. SPSS is the one of the most efficient statistical tool helps in
analysing data in accurate and advanced manner as compared to SPSS. The statistical
calculation are exerted to easily access to built in function for having data analysis. The main
tool that is found excellent in SPSS over excel is pivot table which as well permit the suer to
be get detailed charts and graphs. (SPSS vs EXCEL, 2020) In SPSS the detailed and quality
improved manner for the purpose fro presenting the data in most organized manner. It is easier
to convert the codes into values in SPSS which is not possible in excel. It allows the user to
work on large statistical data that is not possible in excel. Creation of table is easier which
largely contributes in having efficiency in generating reports. SPSS is one of the statistical
software that gives b ability to built feature for complex test. There is higher possibilities of

preventing errors in SPSS which is not possible in the excel that is one of the major aspect
need to be highlighted over excel to get deeper information.
On the other side there are certain disadvantages as well that need to be taken into
process fro shading light on its negative factor. It is expensive as compared to excel in terms of
purchasing fro students. Excel is comparatively user friendly which is lacking in SPSS. In order
to use the SPSS there is requirement of qualified training for understanding its each feature. In
excel most of the feature can be used freely which is not possible in SPSS as there is requirement
of buying those features. Reduction in data redundancy is one of the major benefit that excel
user can have over the SPSS. These are some of advantages that user can obtain from excel
over SPSS.

REFERENCES
Books and journals
Anderson, D.R. and et.al., 2020. Modern business statistics with Microsoft Excel. Cengage
Learning.
Davari, M., Noursalehi, P. and Keramati, A., 2019. Data mining approach to professional
education market segmentation: a case study. Journal of Marketing for Higher
Education, 29(1), pp.45-66.
Davis, R. A., 2021, June. Simplify Uncertainty Analysis Using Excel Macros. In 2011 North
Midwest Section.
Elliott, V., 2018. Thinking about the coding process in qualitative data analysis. The Qualitative
Report. 23(11). pp.2850-2861.
Kadaru, B. B. and UmaMaheswararao, M., 2017. An overview of general data mining
tools. International Research Journal of Engineering and Technology, 4(9), pp.930-936.
Liu, C.H., Wu, Z.Y. and Lin, C.P., 2018, April. A study of intention and behavior of using
mobile communication software: The case of seniors. In 2018 IEEE International
Conference on Applied System Invention (ICASI) (pp. 1006-1008). IEEE.
Ma, Z and et.al., 2017. The role of data analysis in the development of intelligent energy
networks. IEEE Network. 31(5). pp.88-95.
Munch, E., 2017. A user’s guide to topological data analysis. Journal of Learning Analytics.
4(2). pp.47-61.
Salkind, N.J. and Frey, B.B., 2021. Statistics for people who (think they) hate statistics: Using
Microsoft Excel. Sage publications.
Online
SPSS vs EXCEL. 2020. [Online]. Available through <https://www.educba.com/spss-vs-excel/>
1