How to Perform Logistic Regression in Excel

This guide will explain how to perform logistic regression in Excel.

We’ll explain how to use the maximum likelihood estimation method and the Solver add-in to find the coefficients of our regression model.

Logistic regression is a type of predictive analysis that explains the relationship between a dependent binary variable and one or more independent variables.

With logistic regression, we can create a working regression model in an Excel spreadsheet.

Let’s take a look at a simple example of a situation where we can perform logistic regression in Excel.

Suppose you want to predict whether a customer will purchase a particular product. 

The dependent variable in this scenario is either 1 or 0. Either the customer will purchase a product (1), or they will not order anything (0). The independent variables in this scenario could be the potential customer’s age, gender, or even the time and date they visited the store.

Given the training data of 1000 customers, how can we perform logistic regression in Excel?

To create the regression model for this scenario, we’ll need to find the regression coefficients. In this guide, we’ll use the maximum likelihood estimation method to estimate these coefficients.

Now that we know when to perform logistic regression, let’s learn how to set it up on an actual sample spreadsheet.



A Real Example of Performing Logistic Regression

The following section provides an example of how to perform logistic regression in Excel. We will also explain the formulas and tools used in these examples.

First, let’s take a look at our sample data. Our table includes customer information such as their age, how many days since they first visited the store website, and the number of items in their cart. The first column is the dependent variable that indicates whether the customer purchased on their latest visit.

sample dataset for logistic regression


We can perform logistic regression by using the maximum likelihood estimation method.

logistic regression in Excel


To get the coefficients seen in the bottom-left table, we used the Solver tool to maximize the value in cell H17 by changing the four coefficients.

Do you want to take a closer look at our examples? You can make your own copy of the spreadsheet above using the link attached below. 

If you’re ready to try performing logistic regression, head over to the next section to read our step-by-step breakdown on how to do it!



How to Perform Logistic Regression in Excel

This section will guide you through each step needed to perform logistic regression in Excel. You’ll learn how to find the regression coefficients using the Solver tool.

Follow these steps to perform logistic regression in Excel:

  1. First, create a new table with entries for each decision variable. Each variable will have a placeholder coefficient.
    create placeholder decision variables in a new tableIn this example, we’ll start with a placeholder value of 0.001.
  2. Next, we’ll create a new column labeled ‘logit’.
    create logit column
  3. We’ll use the Fill Handle tool to fill out the rest of the logit column.
    use fill handle tool
  4. Next, we’ll create another column to compute for e^logit.
    compute for e^logit
  5. We’ll use the Fill Handle again to fill out the rest of the column.
    get e^logit
  6. Next, we’ll use the formula =F2/(1+F2) to find the probability of each data point.
    compute for probability
  7. We’ll use an Excel formula to calculate the log-likelihood of each probability.
    calculate log-likelihood
  8. We’ll use the SUM function to find the total of all log-likelihood values.
    find sum of log likelihood
  9. Next, we’ll click on the File tab.
    select file
  10. In the File menu, select the Options button.
    select options
  11. In the Excel Options dialog box, click on the Add-ins tab. Select the Go.. option.
    Select Add-in
  12. Ensure that the Solver Add-in option is checked. Click on OK to proceed.
    activate solver add-in for logistic regression in Excel
  13. In the Data tab, click on the Data Analysis option. In the Set Objective textbox, input the cell that computes the total of all log-likelihood values. Our goal is to maximize this value.
    set sum of log likelihood values as the objective to maximize
  14. Next, select the placeholder values as the range to change.
    select placeholder values
  15. Ensure that the ‘Make Unconstrained Variables Non-Negative’ is unselected. Select the GRG Nonlinear option as the solving method. Click on Solve to proceed.
    edit options for Solver add-in
  16. The Solver add-in should now compute for the regression coefficients to use for logistic regression.
    use Solver add-in to perform logistic regression in Excel

These are all the steps needed to perform logistic regression in Excel.



Frequently Asked Questions (FAQ)

Here are some frequently asked questions about performing logistic regression in Excel.

  1. What is the log-likelihood value?
    The log-likelihood value in a regression model measures the goodness of fit of a model. A higher log-likelihood value indicates a better fit. We compute for the log-likelihood by finding the natural logarithm of the computed probability given certain parameters.



This step-by-step guide should provide you with all the information you need to begin performing logistic regression in Excel.

We’ve touched on how the maximum likelihood estimation is used to find the regression coefficients for our model. Our guide also showed how to use the Solver add-in to find the optimal values for these coefficients.

Logistic regression is just one example of the many Excel functions you can use in your spreadsheets. Our website offers hundreds of other functions and methods to help you get more out of Microsoft Excel.

With so many other Excel functions available, you can find one appropriate for your use case.

Don’t miss out on our team’s new spreadsheet tips, tricks, and best practices. Subscribe to our newsletter to stay updated on the latest guides from us!

Get emails from us about Excel.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.


Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like