Free Solver template for Google Sheets

What’s inside

Plus a Solver menu at the top, next to “Help”, with four options: Solve this tab, Goal Seek…, How to use and Restore examples…, in case you change an example tab and want it back. The Spanish version includes four more worked examples: product mix, transportation, staff scheduling and capital budgeting.

How it works

1. Make a copy. Click “Make a copy of the template” and then Google’s “Make a copy” button. It’s saved to your Google Drive. Open it on a computer: the Solver menu is designed for Google Sheets in a web browser.
2. Pick a tab. Adapt the example or use “My model”.
3. Type in your numbers. One column per variable, with its name, its type and what it adds to the objective. One row per constraint, with how much each variable uses, the sign and the limit.
4. Solve. From the menu, SolverSolve this tab. The first time, Google will ask for permission: see just below.
5. Read the result. The solution appears in the green “Solution” row, the value in “Objective” and the status in “Status”. The “Slack” column shows how much of each resource is left; when it’s 0 it turns green: that’s what holds you back.

The table rules

• Don’t change the labels in column A (“Objective”, “Variables”, “Type”, “Min”, “Max”, “Objective coefficients”, “Solution”, “Constraints”). That’s how the script finds each part.
• To add variables, insert columns before the “Used” column and name them in the “Variables” row.
• To add constraints, write new rows below the existing ones, with no blank rows in between. The “Used” and “Slack” formulas are rebuilt automatically when you solve.
• Type: “Continuous” allows decimals; “Integer” only 0, 1, 2…; “Binary” only 0 or 1 (yes or no).
• Empty Min means 0 (it can’t be negative). Empty Max means no limit.
• If something is missing or mistyped, the script tells you which cell to check and takes you there.

The first time: Google’s permission

The template runs on an Apps Script script, Google’s system for automating its spreadsheets. The first time you use the Solver menu in your copy, Google asks for permission to run it:

1. “Authorization required”. Click to continue and choose your Google account.
2. “Google hasn’t verified this app”. That’s normal: the script lives in your own copy and nobody has submitted it for review. Click “Advanced”, at the bottom left, and then “Go to Solver template for Google Sheets (unsafe)”.
3. Review the permissions it asks for and accept them. They’re only the two in the table below; if there are checkboxes, tick them.

The permissions are the minimum possible:

You can read the whole code before copying, with the button on Google’s copy page that opens the Apps Script file, or in your copy, under ExtensionsApps Script. It’s open source, under the MIT license.

Goal Seek

Under SolverGoal Seek… a panel opens on the right with three fields: the formula cell, the target value and the cell to change. “Use selected cell” copies whatever cell you have selected. When you click “Find”, the cell changes until the formula shows your value; if you don’t like it, “Undo” puts it back. There are worked examples in the Goal Seek guide.

Limits

• Linear problems only. If your model multiplies variables together, uses powers or IF formulas on the variables, it won’t work. Here’s how to tell.
• Time. The template waits up to 60 seconds. Problems with tens or a few hundred variables solve in seconds; with many integer variables time may run out, and it warns you the solution may not be the best one.
• The model is a table. The script reads the numbers in the table, not your formulas. If your model is spread across the sheet as formulas, it has to be moved into the table.
• One tab at a time. It solves the tab you have open.

Make a copy of the template