Statistical Analysis with Excel: Assistance Prof. Dr.
Salema Sultan Salman 1
Introduction to data analysis software:
Data analysis software is a type of computer program designed to help users collect, manage,
analyze, and visualize large sets of data. These programs are used in a wide range of industries,
including finance, healthcare, marketing, and scientific research, among others. Some popular
data analysis software includes Microsoft Excel, R, Python, SAS, SPSS, and Tableau.
Microsoft Excel is a spreadsheet program that allows users to organize and analyze data using
various functions and tools. It is commonly used in business and finance to perform financial
analysis, create budgets, and track expenses.
R and Python are programming languages commonly used in data analysis and statistical
modeling. They offer a wide range of statistical tools, data visualization libraries, and machine
learning algorithms. These languages are popular among researchers and data scientists for
their flexibility and ability to handle large datasets.
SAS and SPSS are statistical analysis software programs used in a variety of industries, including
healthcare, government, and finance. They provide advanced statistical analysis tools, data visualization
options, and reporting capabilities.
Tableau is a business intelligence and data visualization software used to create interactive
visualizations, dashboards, and reports. It allows users to connect to various data sources and
create compelling data stories.
Advantage of using Excel as a statistical tool:
Microsoft Excel is a widely used spreadsheet software that provides several advantages as a
data analysis tool. Some of the advantages are:
1. Customization: Excel provides a lot of customization options for data visualization,
including formatting and design of charts, graphs, and tables. Users can create their own
templates and formatting styles to meet their specific needs.
2. Easy to Share: Excel spreadsheets can be easily shared with others through email or cloud-
based platforms, allowing multiple users to collaborate and work on the same file.
3. Integration: Excel integrates with other Microsoft Office applications, such as Word and
PowerPoint, allowing users to easily copy and paste data or visuals across different documents.
4. Macros and Add-ons: Excel provides functionality for creating macros and add-ons, which
allow users to automate tasks and perform complex analysis using third-party software.
5. Cost-effective: Excel is relatively inexpensive compared to other data analysis software,
making it an affordable option for small businesses or individuals.
Preparing Excel for data analysis:
Enabling Excel Analysis tool pack:
In order to perform complex statistical analysis the user can save time by using the Analysis
Tool pack. This is not enabled in Excel as default. The user needs to install this tool pack.
Statistical Analysis with Excel: Assistance Prof. Dr. Salema Sultan Salman 2
As a first step File tab is clicked and the sub menu Options is [Link]-Ins category is
selected. In the Manage box, Excel Add-ins is selected and the Go button is clicked.
This opens up the Add-Ins box where the Analysis ToolPack radio button is selected and OK
button is clicked. This will automatically enable this plugin.
Image showing Options submenu listed under File Menu
Image showing Add in’s submenu
Statistical Analysis with Excel: Assistance Prof. Dr. Salema Sultan Salman 3
Image showing Excel Add in selected before clicking on GO button
Image showing the Add ins that needs to be enabled selected by checking on
their respective boxes. On clicking the OK button the Add Ins get enabled.
Statistical Analysis with Excel 14
Image showing Data Analysis plug in enabled
Components of data analysis plugin:
The components of a data analysis plugin in Excel can vary depending on the specific plugin
being
used. However, here are some common components:
1. Data import and management: This component allows you to import data from various
sources,
clean and prepare the data for analysis, and manage it in Excel.
2. Descriptive statistics: This component provides a range of descriptive statistics, such as
mean, median, mode, variance, standard deviation, and correlation coefficients.
3. Inferential statistics: This component allows you to perform hypothesis testing, including t-
tests,ANOVA, and regression analysis.
4. Data visualization: This component allows you to create various charts and graphs, such as
histograms, scatterplots, and box plots, to help you visualize your data.
5. Predictive analytics: This component uses machine learning algorithms to make predictions
based
on historical data. This can include forecasting, clustering, and classification.
6. What-if analysis: This component allows you to perform scenario analysis, sensitivity
analysis, and goal seeking to help you understand how different variables might impact your
analysis.
7. Optimization: This component allows you to optimize your data analysis by finding the best
solution to a problem using mathematical models and algorithms.
These components can be used individually or in combination to help you perform various
types of data analysis tasks within Excel.
The Figure 1- " illustrates a cyclic relationship between the sample and the population in
statistics.
Statistical Analysis with Excel 15
Figure 1-The relationship between populations and samples, and between parameters
and statistics
The relationship involves two main steps:
1. From Population to Sample: Individuals are selected from the Population to
create the Sample.
2. From Sample to Population: Inferences are made about the Population
(specifically its Parameters) based on the data (Statistics) collected from the
Sample.
Essentially, the sample is a subset of the population used to gather data, and the information
derived from the sample is then used to draw conclusions about the larger population.
Types of variables
two main types of variables in any study or experiment:
• Independent Variable
• Dependent Variable
1. Independent Variable (The Cause): The New Teaching Method. The researcher controls
which students receive the traditional method, and which receive the new method. This is
the variable being manipulated.
2. Dependent Variable (The Effect): The Reading Speed (measured in words per minute). The
researcher measures this after the methods are implemented to see if the speed depends on
the method used.
How can check the data normal distribution in excel
Checking for normal distribution (or Gaussian distribution) in Excel is crucial because many
statistical tests (like t-tests and ANOVA) assume your data follows this pattern. While Excel
doesn't have a single "Normality Test" button like dedicated statistical software (SPSS, R, etc.),
you can use a combination of visual and statistical methods.
Here are the best ways to check for normal distribution using only Excel:
Statistical Analysis with Excel 16
1. Statistical Check: Skewness
The fastest and most direct way to assess normality is by calculating two key descriptive
statistics: Skewness.
A. Skewness (Measures Symmetry)
What it is: Skewness measures how symmetrical your data distribution is. A perfectly normal
distribution is perfectly symmetrical.
Excel Function: =SKEW(data_ range)
Interpretation:
0 (Zero): Perfectly symmetrical (ideal).
Between -1.0 and +1.0: Generally considered acceptable for most research purposes.
Outside this range: Suggests significant skewness, meaning the data is not normally distributed.
Statistical Analysis with Excel 17