Data Validation Manager
Free Google Sheets Add-on

Twelve tabs, one status list, one new option to add. Here is how to change it once instead of twelve times, and how to stop the tabs drifting apart.

How to Update a Dropdown List Used on Many Tabs in One Go

The pattern is familiar: a workbook with a tab per month, team or project, and the same dropdown on every one of them. Then the business adds a value. "On hold" has to join "Open, Won, Lost", and the dropdown has to be edited on every tab. Do it by hand and you will miss one, type one differently, or forget the chip colors on tab nine. This guide shows how to update a dropdown list used on many tabs so every tab ends up with the same rule: the setup that avoids the problem, the manual and scripted routes, and a sidebar that propagates one edited rule everywhere.

Why one edit does not reach the other tabs

Every tab in Google Sheets keeps its own data validation rules. Data > Data validation lists the rules of the current tab only, and a rule edited there changes nothing on the next tab. A fixed-list dropdown stores its items inside the rule, so a status dropdown on twelve tabs is twelve separate copies of the list, each with its own chip colors, help text and warning-or-reject setting. Nothing links them, which is also why they drift: a tab duplicated in March carries the list as it was in March.

Method 1: Point every tab at one list range

The structural fix is to stop storing the items in the rule. Put the options in a cell range, and make every dropdown read from it.

  1. Add a tab named Lists and type the options in a column, for example A2:A20, one per cell, with a heading in A1.
  2. On each tab, open Data > Data validation, click the status rule, switch the criteria to Dropdown (from a range) and enter Lists!A2:A20. Press Done.
  3. From now on, adding, renaming or removing an option is a change to the Lists tab. Every dropdown that reads the range follows immediately.

Make the range longer than the list. Blank cells in the range do not appear as options, so A2:A50 leaves room to add items without touching any rule again. Two cautions: the conversion itself is still one visit per tab, and a dropdown from a range only works inside the file that holds the range. If the rule is pasted into another file, the dropdown breaks, as explained in how to copy a data validation rule to another spreadsheet without breaking it.

Method 2: Edit the rule on every tab

If the dropdowns stay as fixed lists, the plain method is a round of edits:

  1. Write the final list down once, exactly as it should read, including capitalisation.
  2. On the first tab, open Data > Data validation, click the rule, change the items, check the chip colors and whether multiple selections are allowed, press Done.
  3. Repeat on every other tab, ticking them off a list. Include hidden tabs: unhide them first (View > Hidden sheets), because hidden tabs keep their rules and their dropdowns.

On four tabs this is the fastest route. On twenty it is error-prone, and the errors are quiet: "On Hold" on one tab and "On hold" on another, a color missed, a tab skipped. No built-in view shows the twelve rules side by side for comparison.

Method 3: Edit once, paste the rule onto the other tabs

A better manual route edits the dropdown on one tab and copies the finished rule rather than re-typing it:

  1. Edit the rule on the first tab until it is right.
  2. Select one cell of that range and copy it.
  3. On the next tab, select the same range (the whole column of statuses, not just one cell) and choose Edit > Paste special > Data validation only. The copied rule replaces the old one on the selected cells; values and formatting are untouched.
  4. Repeat per tab.

Because this is Google's own copy of the rule, the chip colors and the multiple-selection option come along, and all tabs end up with an identical rule. The remaining risks are the selection (paste onto C2:C500 on one tab and C2:C200 on another and the tabs differ again) and the fact that you still visit every tab.

Method 4: Set the rule everywhere with Apps Script

If the rule sits on the same cells on every tab, a few lines of Apps Script set it in one run. Open Extensions > Apps Script, paste this, adjust the list and the range, and run it:

function setStatusDropdownOnEveryTab() {
  var rule = SpreadsheetApp.newDataValidation()
    .requireValueInList(['Open', 'Won', 'Lost', 'On hold'], true)
    .setAllowInvalid(false)
    .build();
  SpreadsheetApp.getActiveSpreadsheet().getSheets().forEach(function (sheet) {
    if (sheet.getName() === 'Lists') return;
    sheet.getRange('C2:C500').setDataValidation(rule);
  });
}

Unlike the methods above, this script writes: it replaces whatever rule was on C2:C500 of every tab except the one you exclude, so check the tab names and the range first, and remember it will happily add the dropdown to tabs that never had one. The first run asks for authorization, which is normal for your own scripts. The script sets only the list and the reject option; open one of the rules in Data > Data validation afterwards if you also want chip colors or multiple selection.

Or propagate the edited rule from a sidebar

Data Validation Manager, a free Google Sheets add-on, is built for exactly this case. Open Extensions > Data Validation Manager > Launch Data Validation Manager. The sidebar lists every rule on every tab as cards, each with a plain-English line such as "Dropdown: Open, Won, Lost" and range chips that jump to the cells, so the twelve copies of the status rule are finally visible together.

To update them, edit one cell's dropdown in the sheet first, in Data > Data validation as usual: add "On hold", set its chip color, allow multiple selections if you want. Then open the menu on the card of the old rule and choose Propagate from a cell…, select the edited cell in the sheet, click Use selected cell and confirm Propagate to N tabs. The add-on replaces the old rule with that cell's rule on every tab that carries the old rule; items, chip colors and multiple selection all carry over. To put a rule on the same cell addresses of tabs you choose, Copy to tabs… does that, keeping colors and multiple selection the way Sheets does. Every change is undone with Ctrl/Cmd+Z in the sheet, followed by Refresh in the sidebar. The add-on never edits cell values and has no rule editor of its own; rules are still made in Data > Data validation.

After the update: cells that hold a removed option

Whatever route you take, changing a list does nothing to the values already in the cells. Rename "Lost" to "Closed lost" and every cell that says "Lost" is now invalid, with a red triangle, on every tab. Fix the values with Edit > Find and replace, with "All sheets" selected so one pass covers the workbook, then confirm nothing is left: finding all invalid entries at once covers the ways to get that list across tabs.

Frequently asked questions

If I add an option to the Lists range, do I have to touch the rules?

No, as long as the new item sits inside the range the rules point at. A rule on Lists!A2:A50 picks up anything typed into those cells, and blank cells are simply not shown. If the list outgrows the range, extend the range in each rule once and leave more room.

Can I rename an option without breaking the cells that use it?

Not by editing the rule alone. The rule says what is allowed; it does not rewrite the cells. Rename the item in the rule or the list range, then use Edit > Find and replace across all sheets to change the old spelling to the new one. Until you do, those cells are invalid.

What happens to the old rule when I propagate the new one?

It is replaced, on every tab where it was found, by the rule of the cell you picked. Cell values are not touched. If the result is wrong, Ctrl/Cmd+Z in the sheet restores the previous rules, and Refresh in the sidebar shows them again.

Back to all guides