How do I Stop Users Breaking my Excel Spreadsheet?

At Excel4Business, our experts have learned a number of tricks to ensure users can collaborate in Excel without having to worry about the file’s outputs being compromised through user error. A summary of the questions answered in this article appears below:

How do I stop people changing my Excel workbook?

If the file does not require further editing, you can save it as read-only. If it should only be edited by certain users, you can set the file to read-only recommended, requiring users to enter a password if they wish to edit. Using Sharepoint, you can circumvent the password requirement by editing user permissions. If your workbook is some sort of blank file for users to complete, such as a quote form, then you can save it as a template file (XLTX or XLTM format). It is always good practice to maintain periodic backups of files so, if everything else fails, you can retrieve a good version of your spreadsheet.

None of these techniques restrict how users can interact with your file on their own machine. When opening a monthly report, users might double-click on pivot tables to retrieve interesting subsets of data. This creates an extra worksheet in the Excel file that other users don’t need to see. The reason to make files read-only, or similar, is to give people freedoms that might be lost if you started locking down cells and worksheets.

You can make a file read-only by editing its properties externally through Windows Explorer or Sharepoint. If you want to restrict a file to users in possession of a password, you can do this through the small Tools sub-menu that appears next to the Save button in the Save As dialog box. After locating the menu, select General Options and enter a Password to Modify. You will have to confirm your password then click OK/Save on the various windows now open to create a file that is read-only by default. Note you can check the box ‘Read-only Recommended’ from the same menu as an alternative to entering a password if you can trust your fellow users to identify whether they should be tampering with the file or not!

The same menu also includes the option to ‘Always create backup’. This will save a single backup file to be updated each time your workbook is saved so can not prevent other users making changes to your spreadsheet.

If you don’t want people populating a blank template file then the template formats (XLTX or XLTM) will default to opening a copy of the file. It may not be advisable if you are worried about overwriting your own template because whenever you access it through the Recent list in your copy of Excel, you will open an editable version.  However, it will not show up in another user’s Recent list because they have never edited the file. It is also possible for users to edit the file by right-clicking->Open in Windows (instead of double-clicking), but that is unlikely to occur. You can always add a password to modify your template as an extra layer of security.

How do I stop users adding, deleting or hiding sheets in my Excel workbook?

You can click Protect Workbook on the Review ribbon in Microsoft Excel. If you then add a password, users without the password will be unable to remove the protection and make changes to the workbook’s structure. It means the arrangement, the colors and the names of the tabs at the bottom of the Excel window cannot be changed.

Perhaps the best argument for protecting a workbook is that it’s quick to do and, if you’ve optimised the arrangement of the tabs, you probably don’t want them to be moved. The biggest argument against is that you will have to remember the workbook’s password and, within a company setting, you will need to ensure others know the password if you are no longer available. If you would have to share the password with those you intend to lock out, then workbook protection is not a good solution.

Assuming you are trying to prevent accidental damage to your workbook, our consultants find the main benefit is that you can hide lists and tables of information from users that you don’t want them to edit. Most tracking spreadsheets contain status columns. If you want to create performance reports, you need to know how to interpret the different statuses in your outputs. This relies on forcing users to pick between pre-determined statuses. If you’re using data validation, these statuses will be stored in their own list or table. To prevent users adding new statuses, you should hide the source list and protect the workbook.

The fact protection prevents users from re-naming sheets can be helpful but well-conceived macros and formulas should not fail when this occurs. Workbooks are often in greater danger if users inadvertently duplicate sheets containing code. Instead of having to structure code to guard against this and other remote possibilities, it is simpler just to lock down a workbook’s structure.

How do I stop users overwriting cells in my Excel workbook?

Lock any cells you do not want to be edited, then protect the worksheet by clicking Protect Sheet from the Review ribbon. In practice, all cells are locked by default so, if you have not unchecked the Locked option on the Protection tab of the Format Cells menu, you can assume a cell is locked. Once you have protected a sheet, you can always try selecting and typing in the cell to confirm it is locked.

When protecting a worksheet, you have the option of adding a password. That prevents users circumventing the protection by selecting Unprotect Sheet from the Review ribbon. When protecting a sheet, you can allow various behaviours such as deleting rows. Those options only apply to unlocked cells as a cell’s locked status takes precedence.

This can complicate matters. If you want to stop a user overwriting a specific cell but want them to be able to sort the table in which the cell lives, you cannot use permanent worksheet protection. Our Excel experts can write macros to detect which cells have been changed and undo any unwanted changes. If it’s just an issue with sorting a known range, you can write a Visual Basic routine to unprotect the sheet, display the sort dialog box, and then protect the sheet once the sort has been applied.

Worksheet protection is often used by people wishing to prevent damage to their formulas. Although locking a formula cell prevents direct damage, a user can destroy a simple formula like ‘=A1+B1’ by inadvertently dragging cell A1 over the top of B1. This simulates a cut and paste, and will see the formula re-written as ‘=B1+#REF!’. As formulas often refer to input cells, worksheet protection may be insufficient to protect worksheets where large user bases making frequent edits.

How do I prevent users inputting invalid data?

The textbook answer is Data Validation. This option from the Data Ribbon lets you specify that users should enter data from a list, a date, a positive number, and more besides. Although dropdown lists can dynamically update through use of formulas, other forms of Data Validation cannot. If you have specified a cell can contain a decimal number greater than zero, you can’t extend that logic with a condition like “except if this other cell is blank, in which case no number should be input”. This sort of logic can be added through Worksheet_Change macro.

Even simple data validation can fail to prevent users entering invalid data. Any cell requiring a user input has to be unlocked and selectable. This means users can copy and paste over the contents of the cell, removing data validation in the process. To prevent this occurring requires background code. One approach is to frustrate a user’s attempts to copy and paste into the file by clearing the clipboard using Application.CutCopyMode = False whenever a cell with validation is selected. This Worksheet_SelectionChange solution would not prevent a user pre-selecting a cell but has the benefit of being quick to implement.

Assuming the data is being entered in a table, a more comprehensive approach is to keep a record of each column’s data validation requirements in a hidden row elsewhere on the worksheet. You can then test if entered is compliant, clear the cell if it is not and restore the original formatting. If such corrective coding is necessary, it can be more efficient to shift data inputs away from the worksheet and into user-forms.

If your data validation needs to be contingent on the entry in another cell, this can be achieved using the Worksheet_Change event in VBA. You can change a cell’s validation using the .Validation property but the most common scenario is that you want to lock the cell completely e.g. you can’t enter a product’s price until you’ve selected a product. If it would be invalid to enter data at all, you can lock the cell using code.

A non-coded option would be to use conditional formatting in the price column to indicate the cell should be left blank until a product is selected. If you match the background color to the font color then, although an invalid entry might be possible, it would remain invisible. If using this technique, you need to make sure any formulas ignore any invisible, invalid entries. For that reason, our experts would prefer to use code.

How do I prevent users breaking my sheet by copying and pasting?

The simplest way to stop users pasting data into cells is to lock the cells, protect the worksheet, and stop them entering data directly into the cells. For input cells, you will need to provide an alternative space into which users can enter data. A Visual Basic form is perfect because even if users copy and paste information into your user-form, it will not affect any formatting in your spreadsheet and the entered value can be validated before being transferred to the spreadsheet.

At Excel4Business, the most frequent problem caused by a copy/paste is that users have copied a locked cell into an unlocked cell in a protected sheet and find they are unable to make further changes to the data. Loss of data validation, damage to cell formatting or accidental cut and pastes across sheets destroying formulas are other risks.

The most primitive method through which users can enter data is to display an InputBox every time they click in a cell using the Worksheet_SelectionChange event. This box can be pre-filled with the cell’s existing content but cannot guide users with dropdown lists, or provide checkboxes to allow for quick yes/no responses. From a user-experience perspective, this forces users to click in a cell, enter text and find an OK button each time a user changes a cell. This is inferior to the normal experience of clicking in a cell, typing and hitting Enter or even navigating to the next cell with a tab or arrow. Another issue is that you cannot leave the edited cell selected because then users cannot re-enter using the Worksheet_SelectionChange event.

One alternative is to correct any breakages using Worksheet_Change events. This is laborious from a development perspective.

If users are editing a table of data, the best solution is to let users edit every cell in a row through a single VBA form. This form will require separate input boxes for every field but can enhance a user’s experience because, instead of having to scroll across a sheet, a user can edit everything in one place. The same user-form can even offer a better space in which to browse data. Visual Basic forms require significant coding so, if you are unsure where to start, our experts can assist.

How do I stop users causing breakages when trying to develop my sheet further?

If your spreadsheet does not do everything that users require, some will be tempted to develop the sheet themselves. Although setting a file to read-only recommended or hiding row/column headings can discourage users from making changes, a determined user will work around such constraints. The only solutions are to protect worksheets, workbooks, and VBA code with passwords.

Assuming you do not want to lock the file down fully, one option is to push users to make changes in a different file. If they need to perform further analysis on the output data, then letting them export unprotected copies of the worksheets from your file may be sufficient. They can then build their own file that relies on pasted extracts from your file.

To stop them wanting to change anything else, there are two approaches that should be adopted when building the file. The first is to make sure it does everything that might be required of it. The second is to ensure it’s not susceptible to breakages. These often run together. Imagine an invoicing sheet from which users build invoices for customers. They will want a shortcut to retrieve details of existing customers. A beginner user might try to copy a row in the spreadsheet. An intermediate user might try to add lookup formulas so they can key in client names and return address details. Both can be satisfied by a button saying ‘Create New Invoice’ that lets you select an existing customer.

Once you’ve added the button, the sheet’s risk of breakage is a function of whether users can find the button when they need it. they have to discover a special ribbon or enter a keyboard shortcut, then they might not know it exists. For a small userbase of a sheet with limited functions, training might be an option. However, an intuitive design will always win.

The biggest challenge then is if requirements change over time. A user is then faced with the choice of trying to accommodate the new requirement within the existing framework, developing the file further, or going back to talk to the original developer. The biggest influence on a user’s decision is whether they think they can match the original developers Excel skills, so producing a file that is aesthetically pleasing and doesn’t feel like Excel can serve as good protection against damage.

By Ed Bolton, founder of Excel4Business Ltd. Last reviewed October 2026.