Google Sheets Scenario Filter: How to Rebuild It

Google Sheets does not have a built-in feature called Scenario Filter. To rebuild one, either create a filter view for manually showing particular scenario rows or add a scenario dropdown and use the FILTER function to display matching rows automatically.

What a scenario filter does in Sheets

A scenario filter is usually a way to show the records associated with one case, such as Base, Best case, or Downside, while hiding the others. It can make a planning sheet easier to review, but it does not itself calculate what would happen under each scenario. The calculations still need to be in your data or formulas.

Google Sheets has ordinary filters, filter views, dropdowns, and functions such as FILTER, but it does not provide an Excel-style Scenario Manager with a dedicated control for saving and switching between sets of input values. People may use “scenario filter” informally to mean a filter for a Scenario column, or a selector that changes a report. The right rebuild depends on which behavior you need.

For example, a project table might have columns for Work item, Scenario, Cost, and Days. A filter can show only rows tagged Base. A dropdown-driven report can do the same while leaving the original table visible and unchanged. If you want to switch assumptions and recalculate totals rather than show rows, you will need formulas linked to the selected scenario, not just a filter.

Choose between a filter view and a dropdown

Use a filter view when you want to inspect or share a saved view of existing rows. It is quick to set up and does not require a formula. Use a dropdown with a formula when you want a visible selector that changes a separate report area as the selected scenario changes.

Filter view: best for sorting and temporarily hiding rows in the source table. It preserves the underlying records.
Dropdown plus FILTER: best for a compact report that updates when someone selects a scenario.
Scenario calculations: best handled with formulas that refer to scenario-specific inputs, perhaps alongside a dropdown. Filtering rows alone does not change assumptions or recalculate a model.

Consider how other people will use the sheet. A filter view can be less disruptive when several collaborators are looking at the same data, because it gives a user a separate view rather than changing the shared default display. A formula-driven report is easier to present, but its output area must be kept clear so the results can expand.

Rebuild a filter view for scenario rows

First check that your table has one header row and a consistent Scenario column. Avoid blank rows inside the table; they can make it harder for Sheets to recognize the full range. Then use the Google Sheets menu path Data > Filter views > Create new filter view. A dark border indicates the filter view is active.

Open the filter control in the Scenario column.
Clear the selection for all values, then select the scenario you want to see, such as Base.
Sort or adjust other column filters if useful.
Click the filter view name at the top left to give it a meaningful name, such as Base scenario.
Close the view when finished. To use it again, go to Data > Filter views and select its name.

To make a basic filter that changes the shared sheet view rather than creating a separate view, select a cell in the table and choose Data > Create a filter. Use the filter icon in the Scenario header to select the values to show. Use Data > Remove filter when you want to clear the basic filter. Menu labels can change slightly as Google updates its interface, but these are the standard Sheets menu locations.

Build a dropdown-driven scenario report

This approach keeps your source table intact and puts filtered results elsewhere. Suppose A1:D5 contains a header row and four records: Work item, Scenario, Cost, and Days. The scenario labels are in B2:B5. Put a selector in F1 and reserve a blank area below it, such as F3:I, for the report. The output area must not contain other values, because the results need room to expand.

Select F1 and choose Insert > Dropdown.
Add the scenario names that appear in the Scenario column. Include All if you want an option to show every record.
Set the initial choice, for example Base, and click Done.
Enter the report formula in F3. Keep the cells where results will spill into empty.

With headers in A1:D1, records in A2:D5, scenario labels in B2:B5, and the dropdown in F1, enter this formula in F3:

=IF($F$1="All",A2:D5,IFNA(FILTER(A2:D5,B2:B5=$F$1),"No matching rows"))

If F1 is Base and the sample rows are Site rent (Base, 1200, 3), Extra staff (High demand, 800, 5), Freight (Base, 250, 2), and Rush shipping (Disruption, 500, 1), the formula returns the Site rent and Freight rows. If F1 is All, it returns all four records. If a selected value has no matching row, it displays No matching rows. The formula returns rows; it does not add the Cost values or calculate a scenario total.

The FILTER function needs its data range and condition range to line up by row. In this example, A2:D5 and B2:B5 both cover four rows. If you expand your source table, expand both ranges accordingly or choose a suitably sized range. Dropdown entries also need to match the scenario labels exactly; extra spaces or inconsistent spelling can cause a selection to return no matches.

Make the scenario data dependable

A filter is only as reliable as the labels and layout behind it. Use one consistent label for each case throughout the Scenario column. For example, Base, Best case, and Downside are clearer than a mix of Base, base, and Normal. Dropdowns help reduce accidental spelling differences when people enter new records.

Keep scenario names in a small, clearly maintained list if they are used in several places. When a new scenario is added, update both the dropdown choices and any formulas or summaries that depend on the list. Avoid placing explanatory notes inside the data range: put notes above or beside the table so that they are not treated as records.

If the data is imported or frequently edited, check for leading or trailing spaces and blank scenario cells. A value that looks like Base may not exactly match Base if it contains an invisible space. Also consider whether rows with an empty Scenario should be excluded, given their own label, or included in an All report. Decide this deliberately rather than letting missing labels go unnoticed.

Filtering rows is not the same as changing a model

A common misunderstanding is that selecting a scenario in a filter changes the assumptions used in a forecast. A filter only controls which rows are displayed. It does not change a cost, growth rate, date, or other input unless a formula specifically refers to the selected scenario.

For instance, a table may store one set of costs for each scenario. Filtering to Downside reveals those stored rows, but a total elsewhere will not necessarily update unless its formula is designed to use the selected scenario or the filtered results. If you need a changing total, build the total from the displayed results or use a calculation that explicitly tests the selector. Check the output against a small hand-calculated example before relying on it.

Google Sheets also does not have Excel’s Scenario Manager feature under that name. If you are recreating a workbook that used saved sets of input values, a scenario dropdown connected to lookup formulas or separate assumption tables may be a better match than a row filter. The structure depends on whether each scenario is a set of records, a set of model inputs, or both.

Common problems and practical fixes

The formula says no matches: compare the dropdown value with the Scenario column, including spaces and punctuation. Confirm the condition range covers the same rows as the data range.
The formula cannot expand: clear any values or merged cells in the destination area. A dynamic result cannot spill over occupied cells.
New rows are missing: extend the source and condition ranges, or use a wider range that includes future records. Keep both ranges aligned.
The filter icon is missing: confirm that a basic filter or filter view is active. For a filter view, open it from Data > Filter views.
Collaborators see unexpected rows: check whether you are using a basic filter that changes the shared view or a filter view intended to be separate.

When rebuilding a lost setup, inspect the source data before recreating controls. Look for a Scenario column, any existing dropdown cells, and report formulas that may already refer to a selector. If the old filter view was deleted, Sheets may not have a one-click way to restore it; recreate the view and its criteria from the data rather than assuming that a deleted view is still available.

Sharing, maintenance, and alternatives

Before sharing the sheet, label the selector and report area clearly. A note such as “Choose a scenario” beside the dropdown and “Filtered records” above the output makes the interaction understandable without instructions. Protect formula cells if collaborators should change only the selector and source data. In Google Sheets, access controls and protected ranges can reduce accidental edits, but they do not replace a backup or a review of important calculations.

For a one-time review, a basic filter may be enough. For several reusable views, filter views are a good fit. For a dashboard-style presentation, a dropdown and FILTER formula are usually more direct. A pivot table can summarize scenario data by categories and values, while charts can visualize the resulting summaries; neither is a substitute for a scenario selector unless it is connected to a suitable data structure.

If a workbook must also work in Microsoft Excel, check compatibility before relying on a Google Sheets-specific layout or menu workflow. Excel has its own filter and dropdown tools, and its scenario-analysis features differ. The data and formulas may transfer, but a Sheets filter view itself is not an Excel feature that can be used in the same way.

Frequently Asked Questions

Does Google Sheets have a Scenario Filter?
No. Sheets has filters and filter views, but no dedicated tool with that name. You can recreate the behavior with a filter view or a dropdown linked to a formula.

How do I filter a Google Sheet by scenario?
Choose Data > Create a filter, then use the filter control in the Scenario column to select the scenario. For a reusable separate view, use Data > Filter views > Create new filter view.

Can a dropdown automatically show only one scenario?
Yes. Put a dropdown in a selector cell and use FILTER in a separate, empty output area. The formula needs to compare the selector with the Scenario column and use matching row ranges.

Why does my scenario filter show no results?
Check for mismatched labels, extra spaces, blank cells, and ranges that do not cover the same rows. If you use a formula, make sure its spill area is empty.

Does filtering a scenario update totals?
Not automatically in every setup. A filter hides or shows rows; totals update only if their formulas calculate from the relevant records or otherwise respond to the selected scenario.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.