How to Do Sensitivity Analysis in Google Sheets

Ever find yourself staring at a forecast, wondering how much a small change could either blow up your plans or rocket you to success? This "what-if" game is a constant reality for anyone managing a budget, setting prices, or planning a marketing campaign. Instead of just guessing, you can use a technique called sensitivity analysis to map out these potential outcomes. This article will show you exactly how to perform a sensitivity analysis right within Google Sheets, helping you turn uncertainty into a strategic advantage.

So, What Is Sensitivity Analysis, Anyway?

In simple terms, sensitivity analysis is a way to see how sensitive your output (like profit, revenue, or conversion rate) is to changes in your inputs (like ad spend, price per product, or website traffic).

Think of it like adjusting the knobs on a sound system. If you turn the bass dial a little bit, does the sound change dramatically or just a tiny bit? Sensitivity analysis does the same thing for your business model. It answers questions like:

"If my ad click-through rate drops by 10%, how badly does that hurt my profit?"
"How much can I increase the price of my product before I start losing money, assuming sales drop slightly with each price hike?"
"What’s the minimum conversion rate I need on my landing page to make this campaign profitable?"

By exploring these scenarios, you can identify the most critical levers in your business. You’ll learn which variables have the biggest impact and where you should focus your attention, helping you make smarter, more resilient plans.

Getting Started: Setting Up Your Model in Google Sheets

Before you can analyze anything, you need a basic financial model. Don't worry, this doesn't need to be complicated. We're going to create a simple profit-and-loss model for an e-commerce store that sells a single product. This will be our foundation.

Open a new Google Sheet and set it up like this:

Step 1: Define Your Inputs and AssumptionsThese are the variables you'll be testing. In one section of your sheet, list them out clearly. Let’s say we’re selling a fancy water bottle.

A1: Inputs / Assumptions
A2: Sale Price per Unit
A3: Cost per Unit (COGS)
A4: Monthly Units Sold

Now, fill in some baseline values in the next column:

B2: $50
B3: $15
B4: 400

Step 2: Define Your OutputsThese are the results you care about, calculated based on your inputs. A few rows below your inputs, create your output calculations.

A7: Model Outputs
A8: Total Revenue
A9: Total Cost
A10: Monthly Profit

Step 3: Add the FormulasThis is where you connect your inputs to your outputs.

In cell B8 (Total Revenue), type the formula:=B2*B4This multiplies the Sale Price per Unit by the Monthly Units Sold.
In cell B9 (Total Cost), type the formula:=B3*B4This multiplies the Cost per Unit by the Monthly Units Sold.
And finally, in cell B10 (Monthly Profit), type the formula:=B8-B9This calculates your total profit.

Based on our numbers, you should see a profit of $14,000. Now this simple model is ready for analysis.

Method 1: Manual One-Variable Sensitivity Analysis

Unlike Excel, Google Sheets doesn't have a built-in "Data Table" feature. But we can build one ourselves quite easily. Let’s start by testing how our profit changes when we adjust just one variable: the Sale Price per Unit.

Our goal is to create a table that shows profit levels at different price points.

Step 1: Set Up the Analysis Table

Find some empty space in your sheet. A few columns over or a few rows down from your model is perfect.

In one column, list out the different price points you want to test. For example, in cells D2 through D8, enter: $40, $45, $50, $55, $60, $65, $70.
Now, here’s the key part. In the column header directly to the right of your variable list (in this case, E1), you need to reference your final output cell from the model. Simply type =B10 into cell E1. This tells Google Sheets that you want to calculate profit in this column. Your setup should look something like this:
- D1: Sale Price
- D2: $40
- D3: $45
- and so on
- E1: $14,000 (because it's just a reference to your profit in B10 for now).

Step 2: Write the Dynamic Formula

Now, we'll write a single formula in cell E2 that we can drag down to fill the rest of the table. This formula will re-calculate the profit for each new price point we’ve listed.

For this to work, we need to use absolute references (using the $ sign) for the inputs that are staying the same, and a relative reference for the input that is changing (our price).

In cell E2, type the following formula:

Let’s break that down:

D2 is the new sale price we are testing ($40). Since this is a relative reference, when we drag the formula down, it will become D3, D4, etc.
$B$4 is the absolute reference to our static Monthly Units Sold. The dollar signs lock this reference, so it doesn't change when we copy the formula.
$B$3 is the absolute reference to our static Cost per Unit.

Press enter. You should see the calculated profit for a $40 sale price ($10,000).

Now, click on cell E2, grab the small blue square in the bottom-right corner (the fill handle), and drag it down to the last row of your table (E8). Voila! The formula populates the entire column, showing you the projected profit for each different sale price.

Method 2: Manual Two-Variable Sensitivity Analysis

What if you want to test two variables at once? For instance, how does profit change when both the Sale Price and Monthly Units Sold change? This helps you understand compound effects, like whether a lower price can be offset by higher sales volume.

Step 1: Set Up the 2D Table

This layout is slightly different. You'll put one variable's values in a column and the other variable's values in a row.

In a column (e.g., D11:D17), list your range of Sale Prices: $40, $45, $50, etc.
Spanning across a row (e.g., E10:I10), list your range of Monthly Units Sold: 200, 300, 400, 500, 600.
Important: Leave the top-left cell of your table empty (D10). This corner is where our formula will live later. At least for setup. You could put the profit formula reference here, B10, but it's not technically required for the manual table method. Let's build it without for now. It's clearer.

Step 2: Write the "Mixed" Reference Formula

Our goal is to write one formula in the top-left corner of the results area (E11) that we can drag across the entire table. This requires using "mixed" absolute and relative references.

In cell E11, type this formula:

Let's look at the "mixed" references:

$D11: The $ before the D locks the column. So, when you drag the formula sideways, it will always look for the price in column D. The row number 11 remains relative so it changes when you drag down.
E$10: The $ before the 10 locks the row. When you drag the formula down, it will always look for units sold in row 10. The column letter E remains relative so it changes when you drag sideways.
$B$3: This is our fixed Cost per Unit, so we lock both the row and column.

Press enter. Now, drag that formula from E11 across to I11. Then, with that whole row selected, drag it down to row 17. Your entire table will now be filled with profit calculations for every combination of sale price and units sold.

Step 3: Visualize the Data with Conditional Formatting

A big grid of numbers can be hard to read. Use conditional formatting to bring it to life.

Select your entire results grid (E11:I17).
Go to the menu and click Format > Conditional formatting.
A sidebar will appear. Go to the "Color scale" tab.
By default, it often sets a green-yellow-red scale, where higher values are greener. This is perfect! You can now instantly see the "hot zones" where profit is highest and the "cold zones" where you might be losing money.

A Quicker Way: Using Google Sheets Add-ons

Building tables manually is empowering, but it can be time-consuming if your model is complex. While Google Sheets lacks a native Solver or Data Table feature, the Google Workspace Marketplace has add-ons that can fill the gap.

Go to Extensions > Add-ons > Get add-ons and search for "sensitivity analysis" or "goal seek." You'll find tools designed to simplify scenario planning. These tools provide a user interface for selecting your variables and output cells, automating the table creation process for you. While configurations vary, they can be a great time-saver for regular analysis.

Final Thoughts

Performing a sensitivity analysis in Google Sheets might require a few manual steps, but it’s an incredibly powerful skill. By creating one and two-variable data tables, you move beyond static forecasts and start understanding the dynamic relationships within your business model, preparing you for a much wider range of potential futures.

Manually connecting data and building models in spreadsheets is a great way to grasp the fundamentals. But as your business grows, the data you need for accurate analysis is rarely in one place. We built Graphed to solve this problem. After a simple, one-click connection to your data sources like Google Analytics, Shopify, or Facebook Ads, you can just ask questions in plain English — like "create a chart comparing Facebook Ads spend vs. revenue by campaign for last month." We instantly build a live, updating dashboard for you, saving you from the slow, manual process of exporting CSVs and wrestling with spreadsheet formulas.