It is one of the most powerful and versatile programs that exist, taking years to master and get to know it in depth. Microsoft Excel is software designed to develop spreadsheets that can do, in seconds, tasks that would otherwise take hours. We are going to explain how you can lock cells in Excel to prevent written formulas from being altered by mistake.
I must say that I have been using Excel for many years and I have created some quite complex sheets, especially for calculating photovoltaic installations. Despite this, I have barely scratched the surface and many of their formulas I don’t know what they are for.
A competition has even been developed on this software where complex tests must be solved in the shortest amount of time possible. This tool, used by millions of people every day around the world, has reached such an extreme.
The application allows us to lock all the cells of a spreadsheet, not just the formulas contained therein. This is a security measure, above all, to prevent third parties from modifying cells that they should not touch. We are going to explain how to protect the cells to prevent anyone from editing the formulas, only entering values.
Protect a formula in Excel
If you already have your sheet finished with all the formulas entered and everything works perfectly, it is time to protect the cells. You can prevent modification of the entire sheet or the cells you want. Surely, if it is a complex sheet for installation calculations, you want to leave some cells open to enter values.
You have the ability to protect one or more Excel cells as follows:
- Open the spreadsheet you want to protect
- Select all cells by pressing the combination Ctrl+E.
- Right click on any of the cells.
- Within the drop-down menu, click on “Cell Format”.
- You will see how the “Format Cells” window opens
- One of the tabs should say “Protection.”
- Next, we must uncheck the “Blocked” box and then click “Accept”. We close the window.
- Now, let’s go to the “Home” tab on the top bar and go to the bottom bar. Within the “Editing” section we must click on the “Search” magnifying glass and then “Go to Special”.
- A new window opens and we select the “Formulas” option and click on accept.
- Excel highlights all the cells that have formulas and we right-click on them.
- Again, in the context menu, click on “Format cells”.
- Again we go to the “Protection” tab and click on the “Blocked” and “Accept” option box.
- To prevent them from being modified, we have to go to the top bar and go to “Review”.
- Within this option we have to click on “Protect sheet”.
- The next thing is in the open window, set a password to protect the sheet and then click “Accept”.
- It asks us to “Confirm password” to verify that there are no errors in it.
Now, all cells with formulas become protected and can only be altered by whoever has the password. We know it is a long and tedious process, but it can be crucial to prevent someone from screwing you up.
