How to Use Solver in Google Sheets

Key takeaways

A solver is a Google Sheets tool that helps find optimal solutions for complex tasks, such as financial modeling and resource allocation.

• In Google Sheets, a solver is an add-on you can access from the menu bar.

• A solver interface allows you to define a problem using the following parameters: objective, variable cells, and constraints.

• You can use a solver to help optimize formulas, support large-scale business decisions, allocate resources efficiently, and more.

Discover types of Google Sheets solvers, how they differ from Goal Seek, their practical applications, and popular alternatives. Afterward, enroll in Google’s Data Analytics Professional Certificate to learn how to clean and organize data for analysis and visualize data findings.  

What is a Google Sheets solver?

A solver is a dynamic tool in spreadsheet programs, such as Google Sheets, for analyzing complex scenarios and determining the best possible outcome. You can use solver for tasks like financial modeling, scheduling, and resource allocation. Using a solver add-on, you can identify the best possible solutions under constraints, whether maximizing profits, minimizing costs, or reaching a specific goal.

Does Google Sheets have a built-in solver?

No, Google Sheets doesn’t have a built-in solver. In Google Sheets, a solver is an add-on that you can access from the menu bar. Since solvers are extensions to Google Sheets, you can choose from a couple of different options, such as OpenSolver or Frontline Systems’ Solver.

How to install a solver in Google Sheets

You can install an open-source solver add-on through the Google Workspace Marketplace by searching for “solver,” choosing the product you want to use, and clicking “Install.” Alternatively, you can open Google Sheets, click “Extensions,” select “Add-ons,” → “Get add-ons,” then search “solver,” and click install on your solver extension of choice.

Google Sheets solver options

A popular choice for a solver in Google Sheets is OpenSolver. There are also other solver options, such as Numerous.ai, which uses AI to analyze your Google Sheets projects.

OpenSolver

OpenSolver is a free solver add-on for Google Sheets that’s available in the Google Workspace Marketplace. It also works with Microsoft Excel. Like a typical solver tool, OpenSolver supports objectives, variable cells, and constraint parameters [1].

Numerous.ai

Numerous.ai uses ChatGPT to summarize, categorize, and extract content from Google Sheets and Excel. You can also use Numerous.ai to generate topics, then fill them with content, all in one prompt [2].

Number Engine 

Number Engine’s solver is a paid tool that solves constrained optimization problems directly within Google Sheets spreadsheet models. You can use it to solve both linear and nonlinear models. Optimization Solver performs its computations on Google’s servers, meaning you do not need a high-end device to use it effectively [3].

Gemini

You can use Google’s AI assistant Gemini for quick data generation, summaries, and insights in Google Sheets. Based on your prompts, Gemini can also identify trends and outliers within your data [4]. 

Formula Bot

Formula Bot is a free Google Sheets AI formula generator that converts your text into a Google Sheets formula for you to use. You can also feed existing formulas into Formula Bot to get an explanation. Formula Bot can help you learn more about formulas through seeing examples and building intuition [5]. 

How to use a solver in Google Sheets

After installing a solver add-on, you can access it through the "Extensions" menu and select it by name to open its interface. When you open OpenSolver, for instance, a sidebar will pop up where you can enter your optimization model. The following sections outline how to configure OpenSolver and interpret its results.

Setting up your model

The solver interface lets you define the problem, including variables and constraints, through several primary parameters:

• Objective cell: Your optimization goal, set by selecting a formula cell and choosing to maximize or minimize its value.

• Variable cells: The cells the solver adjusts to reach your goal.

• Constraints: The limits for the variable cells, such as a maximum budget or minimum hours. Constraints set the boundaries that the solver must follow.

It is important to be precise when entering solver parameters, as it will help guarantee accurate results.

Running solver and interpreting results

After entering all information, click “Solve” to find the optimal solution. The solver will show the results in a new sheet and highlight the cells with optimal values. By default, the solver will attempt to find a solution that meets all constraints. If successful, it shows the objective cell’s optimal value and the decision variable values. If not, it displays an error message. 

How is a solver different from Goal Seek?

A solver helps you find the optimal (maximum or minimum) value for a formula in a specific cell by adjusting a set of cells used in calculating the formulas in both the objective and constraint cells, while Microsoft’s Goal Seek is a simpler tool used to determine the input value necessary to achieve a certain result from a formula. You can access each tool as an add-on in Google Sheets.

Types of problems you can solve with a solver

You can use solvers to optimize formulas, make business decisions at a larger scale, and effectively allocate resources. A solver can remedy a large variety of problems in a plethora of fields:

Optimization tasks

A solver’s primary use is formula optimization to discover the best available answer to a problem. Solvers also provide consistency to this automation process, offering predictability and recreation of decisions given the same input.

Multi-variable equations and constraint-based models

Solvers adjust cell values based on your rules and are especially useful for problems with multiple variables and constraints. Constraints are a key component of solvers and are helpful for solving problems where explicit boundaries are present.

Resource allocation and scheduling scenarios

You can use solvers for budget planning by adjusting how you allocate your spending across categories to maximize savings and stay within budget. Additionally, you can use a solver for project scheduling, such as distributing resources efficiently to finish a project on time and within budget.

Business use cases

You can use a solver for a variety of business applications, including faster business decisions. For example, when orders arrive rapidly, solvers quickly assign deliveries, staff, and inventory, freeing employees for strategic tasks and improving customer satisfaction.

Tips for getting more accurate solver results

If the solver’s solution seems off, check your formulas and make sure the objective and constraints are set correctly. Remember, entering solver parameters accurately is necessary for ensuring correct results. The solver may not always find the overall optimal solution and can sometimes settle on a local one, so it’s best to try different starting values and run it multiple times to find the best result.

Read more: What Is Excel? Formulas, Functions, and More

Explore our free resources for data analysts

Stay informed with the newest industry trends, career advice, and skill-building insights by subscribing to our weekly Career Chat newsletter on LinkedIn. Then, check out our curated set of free resources for data analysts:

• Watch on YouTube: 4 Essential Tools Every Data Analyst Must Know | Google Certificate Guide

• Explore career paths: Data Analysis Career Progression: Job Levels & Skills to Advance

• Practice with a tutorial: How to Use Data Validation in Google Sheets

With Coursera Plus, you can learn and earn credentials at your own pace from over 350 leading companies and universities. With a monthly or annual subscription, you’ll gain access to over 10,000 programs. Just check the course page to confirm your selection is included.  

Build job-ready skills with Coursera Plus

Start 7-day free trial

• 
• 
• 
• 

Start 7-day free trial