How to Do Statistical Analysis in Excel

Performing statistical analysis doesn’t have to feel like a high-level math class you weren't prepared for. Excel is packed with powerful tools that can help you transform rows of data into meaningful insights. This guide will walk you through the essential techniques, from foundational descriptive statistics to uncovering relationships in your data with regression analysis, all within your spreadsheet.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

The Essential First Step: Enabling the Analysis ToolPak

Before you get into the more powerful statistical functions, you need to activate a free, built-in Excel feature called the Analysis ToolPak. Think of it as unlocking a suite of advanced analytical tools that aren't visible by default. While you can perform many basic calculations with standard Excel functions, the ToolPak automates complex analyses and saves you a ton of time.

Here’s how to turn it on:

For Windows Users

• Go to File in the top-left corner, and then click on Options at the bottom of the left-hand menu.
• In the Excel Options window, select Add-ins from the side panel.
• Near the bottom of the window, you'll see a dropdown menu next to "Manage." Make sure it’s set to Excel Add-ins and click the Go... button.
• A small Add-ins window will pop up. Check the box next to Analysis ToolPak and click OK.

You'll now find a new "Data Analysis" button under the Data tab in your Excel ribbon, usually on the far right. This is your gateway to advanced statistical functions.

For Mac Users

• Click on Tools in the top menu bar.
• Select Excel Add-ins... from the dropdown menu.
• Check the box next to Analysis ToolPak and click OK.

Just like on Windows, you will now see the "Data Analysis" button under the Data tab.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

The Foundation of Analysis: Descriptive Statistics

Descriptive statistics are exactly what they sound like - they describe or summarize your data. It's the first step in any analysis, helping you get a high-level overview of a dataset. With descriptive stats, you can quickly understand things like the average value (mean), the middle value (median), the most frequent value (mode), and how spread out your data is (standard deviation). Let's say you have a list of monthly sales figures, descriptive statistics give you a snapshot of your typical performance.

Using Individual Functions (The Manual Way)

You can calculate the most common descriptive statistics one by one using simple formulas. Let's assume your data is in cells A2 through A31.

• Mean (Average): This gives you the average value.
• Median: This finds the middle number in your dataset, which is helpful because it's less affected by outlier high or low values.
• Mode: This tells you the number that appears most often.
• Standard Deviation: This measures how spread out your numbers are from the average. A low standard deviation means your data points are clustered closely together, while a high one means they are spread far apart. Use STDEV.S for a sample of a population (most common use case).
• Minimum & Maximum: These functions find the smallest and largest values in your dataset.
• Count: This simply counts how many cells in your range contain numbers.

Using the Analysis ToolPak (The Fast Way)

Calculating each metric individually is fine for a quick look, but the Analysis ToolPak can generate a comprehensive summary in just a few clicks.

1. Navigate to the Data tab and click on Data Analysis.
2. From the list, select Descriptive Statistics and click OK.
3. A dialog box will appear. Here's how to fill it out:
4. Click OK.

Excel will instantly generate a clear, organized table with the mean, median, mode, standard deviation, count, min, max, and more. This gives you a complete statistical overview without writing a single formula.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Finding Relationships: An Introduction to Regression Analysis in Excel

While descriptive statistics summarize one set of data, regression analysis helps you understand the relationship between two or more variables. It's a powerful tool for answering business questions like: "Does increasing my ad spend lead to more sales?" or "How does website traffic affect the number of free trial sign-ups?"

In this analysis, you have an independent variable (the thing you control, like 'Ad Spend') and a dependent variable (the outcome you are measuring, like 'Sales'). Let’s walk through an example where a company wants to see if their spending on Facebook Ads correlates with website sessions.

Step 1: Set Up Your Data

Your data needs to be organized in two separate columns. For our example, Column A would be "Ad Spend ($)" and Column B would be "Website Sessions." Each row represents a specific time period, such as a day or a week.

Step 2: Run the Regression Analysis Tool

1. Go to the Data tab and click Data Analysis.
2. Scroll down and select Regression, then click OK.
3. In the Regression dialog box:
4. Click OK.

Step 3: Interpreting the Most Important Parts of the Summary Output

The regression summary output can look intimidating, but you only need to focus on a few key numbers to get started.

Regression Statistics Table

• R Square: This is a very important metric. It tells you what percentage of the variation in your dependent variable (Website Sessions) can be explained by your independent variable (Ad Spend). It’s shown as a decimal, so an R Square value of 0.81 means that 81% of the changes in website sessions can be directly attributed to fluctuations in your ad spend. A higher R Square indicates a stronger relationship.

Coefficients Table

• Intercept Coefficient: This is your baseline. It tells you what your website sessions would be if your ad spend was $0.
• Ad Spend ($) Coefficient: This is the most actionable insight. It shows you how much the dependent variable changes for every one-unit increase in the independent variable. If the coefficient here is 2.5, it means that for every additional dollar you spend on ads, you can expect to get 2.5 additional website sessions on average.
• P-value: This tells you if your findings are statistically significant. A common rule of thumb is that if the P-value for a coefficient is less than 0.05, you can be fairly confident that the relationship isn't a random fluke. If you see a high P-value (e.g., 0.60), it suggests there's no real statistical relationship between the variables.

Visualizing Your Analysis for Clearer Insights

Numbers and tables are great, but a simple chart often tells the story much more effectively. For regression analysis, the best visualization is a scatter plot. It helps you see the relationship between your two variables at a glance.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Creating a Scatter Plot with a Trendline

1. Select your two columns of data (both Ad Spend and Website Sessions).
2. Go to the Insert tab and find the Charts section.
3. Click on the Scatter chart icon (the one with the dots) and select the first option. A chart showing your data points will appear.
4. Now, right-click on any of the data points in the chart and select Add Trendline... from the menu.

Excel will automatically draw a line of best fit through your data points. This line visually represents the relationship the regression analysis uncovered. If the line slopes upward, it indicates a positive correlation (as ad spend goes up, so do sessions). In the "Format Trendline" panel that appears, you can also check the box for Display R-squared value on a chart to add that key metric directly to your visual.

Final Thoughts

You’ve now learned how to unlock Excel's Analysis ToolPak, generate descriptive statistics to summarize your data, and run a regression analysis to find meaningful relationships between variables. These fundamental tools are a fantastic starting point for doing more sophisticated data analysis directly within Excel, transforming your raw spreadsheets into powerful business insights.

We’ve walked through the key manual steps in Excel, which is perfect for understanding the concepts, but it can also involve time wrangling cell ranges and interpreting static summary reports. For our daily reporting, we streamline this process with tools like Graphed. It allows us to connect our data sources directly, then ask questions in plain English like, “Show me the correlation between Facebook Ads spend and new customer sign-ups last quarter.” It builds the dashboard instantly, saving us the time of manual report creation and letting us get to the insights faster.