Application of Excel in Statistical Analysis: Excel is one of the most commonly used Microsoft’s popular spreadsheet software tools for data analysis. Most data professionals and statisticians prefer MS Excel for data analysis because of simplicity, less cost, convenience of use, easy for manipulating data and drawing graphs, and a lot of control and flexibility. Excel is an ideal tool for learning statistical concepts and performing some basic statistical analysis but it is often to use SPSS, SAS, or MINITAB for more advanced statistical analysis.
Spreadsheet software is used to store information in columns and rows and is designed to work with numbers but often include text. Excel mainly organizes work into workbooks and each workbook contains many worksheets and charts. Worksheets are used to list and analyze data. A Pivot Table is an excel tool for summarizing a list into a simple format. It may be used as an interactive data summarization tool to condense large datasets into a separate, concise table.
Most of Excel’s statistical procedures are part of the data analysis tool pack, which is in the tools menu. It includes t-tests, correlations, descriptive statistics, one or two-way analysis of variance, regression, etc. Microsoft Excel 2000 (version 9) provides a set of data analysis tools (Analysis ToolPak), which can be used for the development of complex statistical analysis. The Data Analysis TookPak has a descriptive statistics tool that provides summary statistics for a set of sample data. Summary statistics include mean, mode, median, minimum, maximum, standard error, standard deviation, variance, skewness, range, etc. Descriptive analysis is observed by going to Excel Data → Data Analysis → Descriptive statistics.
Analysis of Variance (ANOVA) is a statistical technique that is used to check the impact of one or more factors by comparing the means of different samples. The ANOVA method in Excel shows whether the mean of two or more data sets is significantly different from each other or not. ANOVA can find in Excel as ANOVA single factor, ANOVA two factor with replication, and ANOVA two factor without replication. If the P-value in the ANOVA summary table is greater than 0.05, then, there is a significant difference between the groups.
The rank and percentile are the Excel feature mainly used for finding the rank of all the values in a list. Percentile shows the category in which the data belongs, such as the top 60%, top 40%, top 20%, etc.
Regression is widely used for using Excel for statistical data analysis and to establish a relationship between dependent variables and independent variables.
Sampling is one of the most preferred Excel tools for creating samples from a huge population. Periodic and random sampling methods are used in Excel 2010 for identifying items in the data set.
Make sure you also check our other amazing Article on : Design of Experiments in Biostatistics