How to use Solver in Google Sheets and Excel guide

1. Manual ways to use Solver in Google Sheets and Excel

Before you automate anything, you need to understand the traditional workflow. Think of this as the playbook you’ll later hand to your AI agent.

A. Manual Solver in Google Sheets (via add-on like OpenSolver)

1. Open your Google Sheet with the model you want to optimize.
2. Install a Solver-style add-on:
   • Go to Extensions > Add-ons > Get add-ons.
   • Search for 'OpenSolver' or 'Solver'.
   • Click into the listing and press 'Install', then grant permissions.
   • See Google’s add-on help: https://support.google.com/docs/answer/9363973
3. Set up your model:
   • Choose an objective cell (for example, total profit).
   • Mark decision variable cells (like quantity to produce, budget per channel).
   • Add formulas that calculate constraints (inventory, budget, minimum ROAS, etc.).
4. Launch the add-on from Extensions > [Your Solver Add-on] > Start.
5. In the sidebar, select:
   • Objective cell.
   • Maximize / Minimize / Value.
   • Decision variable range.
   • Constraints (e.g. B2:B5 >= 0, C10 <= 100000).
6. Click 'Solve' and wait for the add-on to change your decision cells.
7. Inspect the result, accept or revert, then manually note assumptions and outcomes on another tab.

B. Manual Solver in Excel (built-in add-in)

1. Make sure Solver is enabled:
   • Go to File > Options > Add-ins.
   • At the bottom, choose 'Excel Add-ins' and click 'Go'.
   • Check 'Solver Add-in' and click 'OK'.
   • Official guide: https://support.microsoft.com/office/load-the-solver-add-in-in-excel-612926fc-d53b-46b4-872c-e24772f078ca
2. Build your model:
   • One objective cell with a formula (e.g. total margin).
   • Decision variables: cells Solver can change.
   • Constraint cells: formulas that link to limits.
3. Open Solver: Data > Solver.
4. Configure:
   • Set Objective: select the objective cell.
   • To: Max, Min, or Value Of.
   • By Changing Variable Cells: select the variable range.
   • Add constraints one by one (e.g. D2:D10 <= F2:F10, B2:B10 >= 0, integer constraints where required).
5. Choose solving method (GRG Nonlinear, Simplex LP, Evolutionary) based on whether your relationships are linear.
6. Click 'Solve', then choose whether to keep Solver solution.
7. Save a Scenario so you can compare multiple runs: in Solver, click 'Load/Save' or use 'Scenario Manager'.

This manual approach is powerful but fragile: one mistaken range or forgotten constraint, and a busy marketer or founder has to re-run everything.

‍

2. No-code automation for Solver workflows

Once you know the clicks, you can layer light automation on top. This doesn’t remove Solver’s dialogs, but it reduces all the surrounding busywork: data prep, logging, and reporting.

A. Triggered Solver models around your data refresh (Google Sheets)

1. Store raw data (ad spend, leads, inventory) on a 'Data' tab, fed by tools like Google Analytics, ad platform exports, or no-code connectors.
2. Keep your optimization model on a separate 'Model' tab that references the Data tab.
3. Use Apps Script (no-code-ish) or a marketplace connector to refresh data on a schedule.
   • Apps Script overview: https://developers.google.com/apps-script/guides/sheets
4. Use your Solver add-on manually, but:
   • Create a 'Run Log' sheet with columns: Date, Scenario, Key inputs, Result.
   • After each Solver run, use a small Apps Script custom menu to automatically copy current inputs and outputs into the log.

You’re still clicking 'Solve', but the logging is automated and repeatable, making it much easier for an AI agent to pick up later.

B. Excel macros to semi-automate Solver runs

1. Turn on the Developer tab: see https://support.microsoft.com/office/show-the-developer-tab-247c9caa-6168-456b-82c3-48c0e22f0e87
2. Record a macro that:
   • Opens Solver.
   • Loads a specific Solver model configuration.
   • Clicks 'Solve' and then 'Keep Solver Solution'.
3. Save this macro in your workbook as 'RunCampaignSolver'.
4. Add a button on the sheet linked to the macro.

Now a team member only clicks a button instead of configuring Solver from scratch.

C. Scenario templates for non-technical teammates

1. In either Sheets or Excel, create a 'Scenario' input area with clearly labeled cells: target CPA, minimum margin, max inventory, etc.
2. Use formulas to connect this section to the model tab that Solver will reference.
3. Document in a top-left note: '1) Edit scenario cells. 2) Press Run Solver button. 3) Read results on Summary tab.'

You’ve now turned Solver into a productized template. This makes it dramatically easier to hand off to an AI agent later, because the structure is consistent.

‍

3. Running Solver at scale with an AI agent

Here’s where a Simular AI computer agent changes the game for operators, agencies, and sales teams.

‍

A. AI agent as your Solver operator Imagine you run weekly pricing optimization across 40 product groups in both Google Sheets and Excel models. Instead of an analyst babysitting each file:

1. You document the workflow once: open file, refresh data, run Solver, log result, export summary.
2. In Simular Pro, you configure an agent to:
   • Launch the required apps (browser for Google Sheets, desktop for Excel).
   • Navigate to each spreadsheet or workbook.
   • Detect the 'Run Solver' macro button in Excel, or the Solver add-on menu in Sheets.
   • Execute the steps exactly as a human would: select ranges, click 'Solve', wait, store outputs.
3. Connect the agent to a webhook so, after all runs, it posts a dashboard summary to your reporting tool or Slack channel.

Pros:

• Eliminates repetitive, error-prone clicking.
• Works across desktop Excel, browser-based Google Sheets, and even other tools in the same run.
• Transparent execution: you can replay every step the agent took.

Cons:

• You must design stable templates: consistent sheet names and ranges.
• Initial onboarding effort to teach the agent the exact flow.

‍

B. Multi-scenario Solver simulations overnight For agencies and revenue teams, the real power is scenario volume. With an AI agent:

1. Create a table of scenarios in Google Sheets or Excel (different budgets, prices, lead-cost assumptions).
2. Script your model so it reads one scenario at a time into the Solver inputs.
3. In Simular, instruct your agent to:
   • Loop through each scenario row.
   • Update the input cells accordingly.
   • Run Solver.
   • Append the result to a 'Scenario Results' sheet.
4. Schedule this as a nightly or weekly job.

You wake up to a scenario matrix ranked by objective (profit, revenue, ROAS) instead of spending hours experimenting.

Pros:

• Turns Solver from a one-off tool into a decision pipeline.
• Allows non-technical founders and marketers to benefit from optimization at scale.

Cons:

• More moving parts: your scenarios table, Solver model, and agent loop must stay in sync.
• Requires some governance so someone owns verifying that the model’s still valid as the business changes.