Probability and Statistics > Microsoft Excel for Statistics
Microsoft Excel for Statistics
This collection of articles will guide you through the most common functions in Microsoft Excel for statistics. For nearly all Excel stats operations, you’ll have to load the Data Analysis ToolPak first. One advantage of using Microsoft Excel for statistics is that you can enter your data and manipulate it. For example, you can sort from A to Z or high values to low. You can also create several graphs next to your data.
- Problems, Tips and Warnings
- More Microsoft Excel for Statistics 2007 Articles & Videos
- More Microsoft Excel for Statistics 2013 Articles & Videos
If you’re using Microsoft Excel in your stat class, you may be surprised to learn that Excel is actually a very poor choice. Excel is a great spreadsheet package, but it was never intended to be used for statistical analysis. The Data Analysis package is an optional add-in. That should give you a clue that data analysis isn’t Excel’s strong point. You can easily make simple graphs and calculations. But you’ll run into some serious issues with more complex tasks: Including missing data.
Specific Issues with Excel for Statistics
According to the University of Massachusetts, Excel has several issues with statistical calculations. These include:
- Excel can only perform certain tests on one column at a time. In the “real world,” it’s unlikely that you’ll have a single column of variables; You’re much more likely to have a mix over several columns. This can cause issues with running t-tests and even simple frequency counts
- Excel doesn’t log what you are doing. So if you forget what you’ve done, or simply want to repeat a task, that may not be possible without starting from scratch
- If you want to perform a series of statistical tests on your data, you’re likely going to be doing a lot of copying, pasting, sorting, and rearranging. That’s because each test in Excel requires the data to be arranged in a certain way. For example, the One-Way ANOVA requires that data be arranged in separate and adjacent columns or rows for each group. The two-factor ANOVA with replicates requires a rectangular region with columns representing one factor and rows representing the other. With a statistical package such as SPSS, you won’t have to rearrange anything.
These are just a few of the pitfalls with Excel for statistics. Of course, statistical packages like SPSS are likely out of your budget; They cost thousands of dollars which is why the casual user chooses Excel. However, if your school or work offers a statistical package, you may want to learn it. In the long run your computations will be easier, less time-consuming and more accurate.
Watch the video or read the steps below to learn how to sum in Excel 2013:
How to Sum in Excel 2013: Overview
- The SUM function, which you can type into a cell.
- The autosum feature. You can find Σ on the toolbar.
- The sum feature in the Data Analysis Toolpak. You must load the Data Analysis Toolpak before you can use this feature.
Sum in Excel 2013: The SUM function
Step 1: Type your data set into one column, without gaps.
Step 2: Click in any empty cell on the worksheet.
Step 3: Type “=SUM(Z1:Z99)” where “Z1:Z99” is the location of your cells (Z1:Z99 tells Excel that your data is in column Z, cells 1 to 99).
Sum in Excel 2013: Autosum
Step 1: Type your data set into one column without gaps.
Step 2: Click an empty cell immediately underneath your data.
Step 3: Click the autosum button on the “Home” tab. The autosum button (Σ) is on the right of the toolbar.
Sum in Excel 2013: Data analysis Toolpak
Step 1: Click the “Data” tab and then click “Data Analysis.”
Step 2: Click “Descriptive Statistics” and then click “OK.”
Step 3: Click the Input Range box and then type the location for your data. For example, if you typed your data into cells A1 to A10, type “A1:A10” into that box
Step 4: Click the radio button for Rows or Columns, depending on how your data is laid out. In general, you’ll want to input your data in columns so click the “columns” radio button.
Step 5: Click the “Labels in first row” box if your data has column headers.
Step 6: Click the “Descriptive Statistics” check box.
Step 7: Select a location for your output. For example, click the “New Worksheet” radio button.
Step 8: Click “OK.”
A pivot table is way to summarize data. It’s called a “pivot” table because you can pivot the table. In other words, you can switch rows and columns. These charts are commonly used in businesses to compare data. They’re a staple in any business stats course and are often avoided because of the misconception that they are hard to make. However, they’re actually just as easy as an other graph in Excel and they may even in fact be easier because Excel will auto-fill in your input data as long as your data is properly formatted. What is properly formatted data for a Pivot Table? Data that’s ordered in columns and rows with correct column and row headers.
How to make an Excel Pivot Table
Making the table:
Step 1: Enter your data into columns. For example, you could have sales in column A, gross profit in column B and net profit in column C.
Step 2: Click a cell anywhere in the dataset.
Step 3: Click the insert tab and then click “Pivot Table.”
Step 4: Check that the data range is ok. Click a radio button for the location of the table. For example, click “New Worksheet.”
Step 5: Click “OK.”
Step 6: Click and drag which fields you would like in the rows of the table from the options on the right hand column. Drag to the “rows” area at the bottom. Note: in order for this to work properly, you have to plan out your table beforehand. Check the image showing to the left of the worksheet for what an Excel pivot table looks like.
Step 7: Repeat Step 6 for the columns you would like in your chart.
Step 8: Repeat Step 6 for the central part of the table. For example, if you want “sales” in the middle, drag that down to the values area.
Once you have made the table, then comes proper formatting. This is mostly a matter of choice. None of the formatting steps are completely necessary although you’ll have a much more professional (and in some cases, more understandable) pivot table if you make a few tweaks to your basic table. For example, when you make an Excel pivot table, the default cell number format is plain “Number.” If you’re dealing with dollar figures, it makes sense to add “Currency” as an option on your table. Other topics covered in this short video include:
- Choosing a style.
- Highlighting rows (or columns).
- Omitting categories using the slicer tool.
How to format an Excel Pivot Table
Tip: You must be able to choose the right categories for rows and columns when you create your chart. Make yourself familiar with how a standard pivot table looks before making your table. The videos above will give you an idea of what a pivot table looks like as well as how to choose the categories.
Back to Top
- How to find a 5 Number Summary in Excel.
- how to make a Box and Whiskers Chart in Excel.
- How to Make a Column Chart in Excel
- How to find a Z-Score in Excel.
- The Mode in Microsoft Excel.
- An Interquartile Range Excel 2007.
- The Standard Deviation in Excel.
- The Mean in Excel 2007 & 2010.
- How to run a Hypothesis Test in Excel.
- How to find a Confidence Interval for the Mean: Excel.
- How to find a chi-square p-value in Excel
- The Median in Excel.
- How to find a Normal Distribution Probability in Excel.
- Excel Descriptive Statistics.
- Excel Data Analysis Toolpak.
- How to Create a Histogram in Excel 2007.
- The Normal Distribution Excel 2007.
- An Excel beta distribution.
- The Correlation Coefficient Excel 2007.
- How to make a Scatter Plot in Excel 2007.
- A Linear Regression Equation in Microsoft Excel.
- Chebyschev’s Theorem in Excel.
- A Frequency Distribution in Excel.
- A Pie Chart in Excel.
- The Random Number Generator in Excel.
- How to find Pearson’s Coefficient of Skewness in Excel.
- Excel Multiple Regression (Polynomial Regression).
- T Test in Excel.
- Make a Scatter Plot in Excel 2013.
- How to Make a Bar Graph in Excel 2013.
- A Histogram in Excel 2013.
- An ANOVA Excel 2013.
- A Two Way ANOVA in Excel with replication & without replication.
- Find Covariance in Excel 2013.
- Find Correlation in Excel.
- Find Sample Variance Excel.
- Kurtosis Excel in 2013.
- Find Skewness in Excel 2013.
- Find Standard Deviation in Excel.
- The Mode in Excel 2013.
- The Median in Excel 2013.
- The Standard Error in Excel 2013.
- The Mean in Excel 2013.
- Load the Excel Data analysis Toolpak.
- Skewness in Excel 2013.
- The Range in Excel 2013.
- Sample Variance in Excel 2013.
- Exponential Smoothing in Excel 2013.
- F test two sample for Variance.
- Fourier Analysis in Excel 2013.
- Moving Average in Excel 2013.
- Excel Sampling: Find a Sample.
- Run the Excel 2013 Regression analysis.
- Excel Regression Analysis Output Explained.
- Use the Excel PERCENTRANK function and Excel PERCENTILE function.
- Use the Excel RANK function (Excel 2013).
- T-Test in Excel 2013.
- Two Sample z test in Excel 2013.
Subscribe to our YouTube channel for more Microsoft Excel for Statistics help and tips!
If you prefer an online interactive environment to learn R and statistics, this free R Tutorial by Datacamp is a great way to get started. If you're are somewhat comfortable with R and are interested in going deeper into Statistics, try this Statistics with R track.Comments are now closed for this post. Need help or want to post a correction? Please post a comment on our Facebook page and I'll do my best to help!