Excel Statistics: Short Video Tutorials in Easy Steps

Excel Statistics

This collection of articles will guide you through the most common Excel Statistics functions. For nearly all Excel stats operations, you’ll have to load the Data Analysis ToolPak first. One advantage of using Excel 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.

Contents:

Problems, Tips and Warnings
Basics:
Microsoft Excel Statistics 2007 Articles & Videos
Microsoft Excel Statistics 2013 Articles & Videos

Excel Statistics: Problems, Tips and Warnings

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.

Excel for statistics. — The Excel Rank function.

Specific Issues with Excel 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 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.

Basics

How to Sum in Excel

Watch the video or read the steps below to learn how to sum in Excel 2013:

How to Sum in Excel 2013: Overview

If you want to sum in Excel, you have plenty of choices. You can use:

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 “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.”

Check out our YouTube channel for more stats help and tips!
Back to Top

Excel Pivot Tables

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

Watch the video for the steps:

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

Microsoft Excel Statistics Topics (2007-2010)

Microsoft Excel Statistics: Excel 2016 & 2013

Subscribe to our YouTube channel for more Excel Statistics help and tips!