Excel's Analysis Toolpak is an add-on that provides analysis capabilities found in dedicated statistical software (e.g. Single-Factor ANOVA, Two-Factor ANOVA, t-test, Regression ). In this video, Joe shows you how to install and use the Toolpak so that you can effectively utilize the full range of its capabilities in your work.
- [Voiceover] Now I'll tell you about an extremely useful Excel add-in, the Analysis ToolPak. This contains a wide variety of statistical tools that appear in dedicated statistical software packages. And those packages typically cost quite a bit. But the ToolPak doesn't come installed in Excel. You have to add it. Here's how. On the File tab, click Options. Then, on the dialog box, click Add-Ins. Then, click Analysis ToolPak.
Down in the Manage box, be sure that Excel Add-Ins is showing, and click Go. In the Add-Ins box, check the box next to Excel ToolPak, and click OK. On the Data tab, all the way in the Analysis area, you should see a button labeled Data Analysis. Before we use a data analysis tool, I want to point something out about the two data arrays. I defined Model_Year as the name for the values in Column A and Mpg as the name for the values in Column B.
The underscore in Model_Year should tell you that Excel doesn't like spaces in array names. On the Formulas tab, I'll open the Name Manager. You can see that each array begins in the first row and thus includes the name. Data analysis tools give you the option of incorporating the names, also called labels, as part of the information you give to the tool. Why does this matter? Because if you give the names to the tool, the names appear in the output. And that's a good thing, because it clarifies the output.
Now, onto an analysis tool. Click the Data Analysis button. And in the dialog box, select Regression. In the Input Range box, I'll type mpg. And in the Input X Range box, I'll type model_year. And because of the way I defined these ranges, I can check the box next to Labels. In the box next to New Worksheet Ply, the default selection for where the output goes, you can supply a name for the new tab that will hold the results.
Type Regression ANOVA and click OK. And here it is. I always like to expand all the columns at once. On the Home tab, in the Cells area, under Format, I AutoFit Column Width. That opens up all the columns. Down in the ANOVA table, you see Model_Year in cell A:18, and that's because I was able to check that Labels box when I was setting up this analysis. For now, you've learned how to add the Analysis ToolPak to Excel and how to use an analysis tool.
And now you're ready to do a huge amount of statistical analysis.
He explains how to organize and present data and how to draw conclusions using Excel's functions, charts, and 3D maps and the Solver and Analysis ToolPak add-ons. Learn to calculate mean, variance, standard deviation, and correlation; visualize sampling distributions; and test differences with analysis of variance (ANOVA). Then find out how to use linear, multiple, and nonlinear regression testing to analyze relationships between variables and make predictions. Joseph also shows how to perform advanced correlations, variable frequency testing, and simulations.
By the end of this course, you should have the foundational knowledge you need to take other statistics-related courses and perform basic analysis in the workplace.
- Using Excel's statistical functions and 3D charts
- Visualizing sampling distributions
- Performing comparisons with ANOVA
- Performing two-way analysis with ANOVA
- Analyzing linear regression
- Performing multiple regression and nonlinear regression analysis
- Making advanced correlations
- Testing variable frequencies
- Running simulations