✦Template Printable Ideas Insights

"How to Use Excel Solver in Google Sheets"

By

Excel'sSolver feature, long a staple in data modeling and optimization, is now available in Google Sheets. Here's a comprehensive guide on how to use Excel's Solver in Google Sheets to perform what-if analyses and find optimal solutions for decision-making scenarios.

Before we dive in, ensure you have the Solver tool installed in your Google Sheets. It's available as an add-on, and you can install it from the Add-ons menu in your Google Sheets interface.

Setting Up Your Data in Google Sheets

Solver works best with structured data. Start by organizing your data, ensuring you have three main parts: decision variables, objective function, and constraints.

How to use an Excel spreadsheet in Google Sheets (access from any device, anytime) or Numbers for Ma
How to use an Excel spreadsheet in Google Sheets (access from any device, anytime) or Numbers for Ma
Why You Should Switch From Excel to Google Sheets
Why You Should Switch From Excel to Google Sheets
How to add a Calendar into a cell in Google Sheets #googlesheets #tutorial #spreadsheet
How to add a Calendar into a cell in Google Sheets #googlesheets #tutorial #spreadsheet

For instance, you might be managing a bakery and want to find the optimal mix of products (cakes and bread) to maximize your profit, given the production time and capacity constraints.

Entering Your Data

Suppose you have the following data:

How to find duplicates in Google Sheets
How to find duplicates in Google Sheets
Basic Formatting in Google Sheets
Basic Formatting in Google Sheets
The Ultimate Google Sheets Keyboard Shortcut Cheat Sheet for Beginners & Pros
The Ultimate Google Sheets Keyboard Shortcut Cheat Sheet for Beginners & Pros
Product$Profit per unitHours to produceHours available
Cakes$50240
Bread$301Unlimited

Enter your data in Google Sheets, setting up separate columns for the profit per unit, hours to produce, and hours available for each product.

Setting Up a Sample Scenario

Now, let's set up a simple scenario. We'll aim to maximize profit while ensuring we don't exceed the available hours for production.

Create a separate sheet and title it "Solver". Here, you'll set up your decision variables (number of cakes and bread to produce), as well as formulas for your objective function (total profit) and constraints (total hours).

How to use Google Sheets: A beginner's guide | Zapier
How to use Google Sheets: A beginner's guide | Zapier
6 Crazy Google Sheets Formulas You Should Know
6 Crazy Google Sheets Formulas You Should Know
How to Use Filters in Google Sheets
How to Use Filters in Google Sheets
the google sheets are in green and white
the google sheets are in green and white
Follow for more Google Sheets tips
Follow for more Google Sheets tips
How to use VLOOKUP in Google Sheets: A complete guide
How to use VLOOKUP in Google Sheets: A complete guide

Using Solver in Google Sheets

Now that your data is organized, it's time to open the Solver dialog box in Google Sheets.

Go to Add-ons >Solver >Start. The Solver Parameters window will open.

Setting Up the Objective

In the 'Set objective' field, enter the cell containing your total profit formula (e.g., =A2*B2+A3*B3, if you've entered your profit formulas in cells A2, B2, A3, and B3).

9 Google Sheets Apps to Boost Your Productivity
9 Google Sheets Apps to Boost Your Productivity
Free Spreadsheet Templates for Excel & Google Sheets
Free Spreadsheet Templates for Excel & Google Sheets
How To Make A Budget On Google Sheets in 2026 (+ Examples)
How To Make A Budget On Google Sheets in 2026 (+ Examples)

Choose 'Max' from the dropdown menu, as we want to maximize profit.

Setting Up Constraints

Set up your constraints in the 'Make unitary comparable' and 'Subject to the constraints' fields. Click 'Add' to enter each constraint, then click 'OK' when you're done. In our example, add the constraint =B2+C2<=40 to ensure we don't exceed the available hours.

Running the Solver

Click 'Solve' to run the Solver. Once it's complete, you'll see the optimal number of cakes and bread to produce to maximize profit, along with the new profit value and a 'Range of variable values' report.

Validating and Adjusting Your Results

Ensure your results make sense and align with your expectations. If not, review your data, formulas, or constraints. Make adjustments as necessary and re-run the Solver.

Understanding the Sensitivity Report

The sensitivity report shows how changes in your data (right-hand side values) affect your solution. Review this report to understand the impact of different scenarios on your optimal solution.

Using Excel's Solver in Google Sheets can significantly enhance your data analysis and decision-making capabilities. With a little practice, you'll be optimizing stock portfolios, budget allocations, and more, all within the convenience of Google Sheets. Happy solving!