The “X Range” is the independent variable, the one you will be using to make the predictions. The “Y Range” is your dependent variable, the one you want to make predictions about. That will take us to a menu where we’ll select Regression and then push OK. So, the first thing we would do is click Data Analysis as we talked about above. If we wanted to find an equation that could predict an estimate of Y based on X, we would need to do some regression analysis. Let’s say we had some data that looked like this:

So, how best to use the tool now that you have it? That all depends on the data and your goals with it, but since most people think of fitting a line to a bunch of points when they think of data analysis, we’ll start with this one: Doing Regression Analysis in Excel At the far right end of the banner, you’ll see a new Analysis section, where you can click on Data Analysis. To get to the Analysis ToolPak, click the Data tab.

Check the Analysis ToolPak option, then hit OK. Now hit Go at the bottom of the window ( not the OK button), and a box will pop up. In the pop-up, click Add-Ins in the left sidebar, then highlight “Analysis ToolPak” in the menu next to it. Click File in the top left corner, then go to Options. The process for Excel 20 is essentially the same (although all screen shots come from Excel 2010). To do any real data analysis in Excel you’ll want to activate the Analysis ToolPak. (Take a look at Part 3 for more basic but less useful statistical tools, as well as miscellaneous tools like Fourier Analysis, moving averages, exponential smoothing, random number generation, sampling, and rank and percentile.) How to Get Data Analysis in Excel 2010 or 2013 (If you’re looking for info on statistical tests for Excel, click here to check out Part 2.) This whole post should take 20 to 40 minutes. Next, we’ll talk about regression analysis ( the real thing, including multiple variables, not just fitting a line to a graph).

In part 1 of this series on data analysis in Excel, I’m going to tell you how to do that. Statistical analysis in Excel is a huge pain unless you know how to enable the Analysis Toolpak.

Regression data analysis tool in excel