ThisCalculator

Everyday

Protect Cells by Criteria: What It Means

Updated · Standard formula, shown below

Average pace (min/km) 5.00 min/km
Average speed (km/h)
12.00 km/h
Result
Time (min)
50 min
You entered
Distance (km)
10 km
You entered

This article explains what it means to protect cells by criteria in spreadsheet programs, covering how conditional protection works, how to set it up, and common pitfalls. You will learn the logic behind protecting only certain cells based on their values, and how to apply it in Excel and Google Sheets.

What Does “Protect Cells by Criteria” Mean?

In spreadsheet applications like Microsoft Excel and Google Sheets, “protecting cells” usually means locking them so that other users cannot edit them. By default, all cells are locked, but the lock only takes effect when you turn on sheet protection. “Protect cells by criteria” means you want to lock only certain cells—for example, cells that contain formulas, or cells whose value meets a specific condition—while leaving other cells editable. This is not a built-in single command; it requires combining cell locking with conditional formatting or custom formulas.

Background: Why Protect Cells Conditionally?

In a shared workbook, you often want to allow users to enter data in some areas but prevent them from changing formulas or critical values. For instance, a budget sheet might have input cells for expenses (editable) and a total cell with a SUM formula (protected). But what if you want to protect a cell only when it shows a certain value, like “Final” or a number above a threshold? That is conditional protection. It is useful for things like preventing edits to cells that are marked as “approved” or “locked” based on a status column.

How Conditional Protection Works

The core mechanism is the same in Excel and Google Sheets: every cell has a “locked” property that is ignored until you protect the sheet. To protect based on criteria, you need to set the locked property for cells that match your condition. Since you cannot directly set “locked” via a formula, you use conditional formatting to identify those cells, then manually lock them, or you use a script (like Google Apps Script) to automate it. In Excel, you can also use the “Allow Users to Edit Ranges” feature with a formula, but it is limited.

How to Set Up Conditional Protection in Excel

  1. Select the range where you want conditional protection.
  2. Go to the Home tab, click Conditional Formatting > New Rule.
  3. Choose “Use a formula to determine which cells to format.”
  4. Enter a formula that returns TRUE for cells you want to protect. For example, to protect cells in column C that are greater than 100, use =C1>100 (adjust relative reference).
  5. Click Format, go to the Protection tab, and check Locked. (Note: In some Excel versions, the Protection tab is only available after you enable protection? Actually, it is always there.)
  6. Click OK, then go to Review > Protect Sheet and set a password if desired.

This method works because conditional formatting can change the locked state, but note: it only works if you apply the formatting before protecting the sheet. Also, the lock applies to the entire cell, not just the value.

How to Set Up Conditional Protection in Google Sheets

Google Sheets does not have a direct “conditional lock” feature. Instead, you can use a script (Google Apps Script) to protect ranges based on criteria. For example, you can write a script that checks each cell in a range and if the value meets a condition, it adds that range to a protected range list. Alternatively, you can use the built-in “Protect ranges” feature manually, but it does not update automatically. For a dynamic approach, a script is the only way. Here is a simple concept:

  • Use onEdit trigger to run a function after edits.
  • The function checks the edited cell’s value against your criteria.
  • If it matches, it adds a protection rule for that cell.
  • If it no longer matches, it removes the protection.

This requires some coding knowledge, but it is the most flexible.

Common Problems and Misconceptions

A frequent misconception is that conditional formatting’s “Locked” option actually prevents editing. It does not—it only marks the cell as locked, which takes effect once the sheet is protected. If you forget to protect the sheet, your conditional locks do nothing. Another issue: in Excel, if you apply conditional formatting to change the locked state, it may not work as expected because the lock is a property of the cell, not a format. Actually, in Excel, the Protection tab is part of the Format Cells dialog, and conditional formatting can change it, but it is not officially documented and may be unreliable. A safer approach is to use a macro or VBA to set the locked property based on criteria. For Google Sheets, scripts are the standard.

How to Choose the Right Method

Consider your needs:

  • Static criteria (e.g., always protect column B): just lock the cells manually before protecting the sheet.
  • Dynamic criteria based on values (e.g., protect cells that are >100): use a script (Google Sheets) or VBA (Excel) for reliability.
  • User-friendly without coding: use conditional formatting to highlight cells, then manually lock them, but this is not automatic.
  • Collaborative real-time: Google Sheets scripts are better because they run on edit.

For most users, the simplest is to protect entire ranges that contain formulas or critical data, and leave input cells unprotected. Only use conditional protection when you have a clear need.

Safety and Best Practices

When protecting cells, always consider data integrity. If you protect cells based on criteria, ensure the criteria are correct and do not lock cells that need editing. Also, remember that sheet protection is not security—anyone with the password can unprotect it. For sensitive data, use proper access controls. Additionally, if you use scripts, test them thoroughly to avoid accidentally locking everything. Always keep a backup of your spreadsheet before applying complex protection.

Frequently Asked Questions

Can I protect cells based on a cell’s value in Excel without VBA?

Yes, but it is limited. You can use conditional formatting to change the locked state, but it is not officially supported and may not work consistently. The reliable way is to use VBA to loop through cells and set the Locked property based on a condition. For example, you can write a macro that runs when the workbook is saved.

Does protecting a cell prevent formatting changes?

No, protecting a cell only prevents editing the cell’s content. Formatting changes (like font color or number format) are controlled by the “Format cells” option in the Protect Sheet dialog. You can uncheck that to allow formatting even on protected cells.

Why is my conditional formatting lock not working?

Most likely because the sheet is not protected. Remember, the locked property only takes effect after you protect the sheet. Also, ensure that the conditional formatting rule is applied to the correct range and that the formula is relative correctly.

Can I protect cells in Google Sheets without a script?

You can protect ranges manually, but there is no built-in way to make it conditional. You would have to manually update the protected ranges whenever the criteria change. For automatic protection, a script is required.

Is there a way to protect cells based on a dropdown selection?

Yes, you can use a script that triggers on edit. When a dropdown value changes, the script can protect or unprotect certain cells. For example, if a cell says “Locked”, then protect the adjacent cells. This is common in approval workflows.

Related

Formula used
in t | Time (min) | min | 50 | 0
in d | Distance (km) | km | 10 | 0
out pace | Average pace (min/km) | min/km | t / d | 2
out speed | Average speed (km/h) | km/h | d / (t / 60) | 2

Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.