LOCK Cells and Formulas in Excel  Step by Step Guide ▷ 2020

When Microsoft users work in Excel, at least once it has happened to them that they place the titles they are going to use and when they lower the cell, they lose everything and do not even remember that they had written there. But luckily, this is very easy to avoid, since all you need is fix some cells. Taking into account that it is a valid resource for all versionsThat is, for Excel 2016, Excel 2013, Excel 2007, and so on.

For its part, lock and pin rows, columns, and cells in a worksheet, It is quite advantageous when you are working on a large document or, to work much more comfortable without worries. In addition, there is the possibility of protecting an entire spreadsheet, so that none of the cells can be edited. Ultimately, this can help you avoid changes in the information and even in the formulas. Specifying that once they are locked and password protected, can be easily unlocked at any time, by the same user who blocked them in the first instance.

So when you work hard on creating your reports in Excel, you can be sure that no one else will be able to modify the column titles, formats or formulas that you once fix. That is why we will give you the most appropriate information so that you learn to block and protect rows, columns and cells; as well as securing the formulas and preventing them from being erased by some mistake.

What is it and what is the purpose of locking and fixing rows, columns and cells in Microsoft Excel spreadsheet?

Perhaps you may have already noticed this option activated in a previously created spreadsheet. So, it can be said that locking and fixing rows, columns and cells consists of the first or first rows (in some cases it can be the columns); they remain visible even if you scroll down or up the file.

Indeed, the utility of this function lies in keep seeing the title that lets you know what information you are observing in each of those rows or columns. In Excel, this is known as “Immobilize”; a section found in the View tab.

For its part, there are three rules for the blocking function, and they are:

  • Sheet owners or managers They are the only ones that can lock or unlock cells on a sheet.
  • The only ones allowed to continue editing or deleting locked columns and rows, they are the administrators or the owner.
  • Editors can observe locked cells but they cannot edit the data added to them. The same is also the case with anyone who works from a Published Sheet or a Update Request.

It should be noted that the columns of attachments and discussions, cannot be blocked. Also limiting that once you have frozen a row or column, a lock icon will appear in the header of the same to indicate said action.

Thanks to this function, it will already be less tedious working in Excel with large volumes of data that on various occasions, it is complex to analyze or compare the information contained in said document. Thus integrating greater ease and comfort, in addition to preventing that this could completely harm your task.

Steps to lock or protect cells in Excel from being modified on Windows and Mac

Next we will give you the simple steps to manage to freeze and protect the cells of your document. As mentioned, it is a valid function for multiple versions of Excel but in the same way, we stand out that this process was made in the 2013 version.

Freeze Rows

  • Open the document where you want to lock a certain row or cell.

Steps to lock or protect cells in Excel from being modified on Windows and Mac

  • Then you must select the row just below the row or rows you want to freezeIn the example that we show you now, we want the row 1 is blocked And because of that, we click on row 3.

Steps to lock or protect cells in Excel from being modified on Windows and Mac

  • Later you must go to the option “View”Located at the top of the window.

Steps to lock or protect cells in Excel from being modified on Windows and Mac

  • Once you are in “View“, Open the section that says”Immobilize”And that shows you three options: Freeze panels, freeze top row and immobilize first column.

Steps to lock or protect cells in Excel from being modified on Windows and Mac

  • When you see that these three options are displayed, as we have selected the second row, you must choose the first section “Freeze panels“, Which is what facilitates the possibility of blocking the rows and columns, giving the other cells the freedom to be moved.

Steps to lock or protect cells in Excel from being modified on Windows and Mac

  • With this, we can see that when we scroll down the file, row 1 indicates the data: Name, last name Y age; is immobilized or frozen, meanwhile.

Steps to lock or protect cells in Excel from being modified on Windows and Mac

Freeze Columns

This was the process to lock a certain row. However, to do it with columns, the following procedure will be carried out:

  • First of all you must select the column to the right of the column to block. In this case, we add more columns to better observe the process. And we proceed to select column B, considering that we are going to immobilize column A.

Steps to lock or protect cells in Excel from being modified on Windows and Mac

  • Again we go to the tab View and there we select the option Immobilize, and then choose “Freeze Panels”. Just like we did with the process to freeze rows.

Steps to lock or protect cells in Excel from being modified on Windows and Mac

  • Once you do this, you will notice that when you move the spreadsheet to the right, the column “Name” it remains always first.

Steps to lock or protect cells in Excel from being modified on Windows and Mac

How to protect formulas in excel and prevent them from being deleted by mistake?

There are several methods to protect formulas in Excel and thus avoid inconveniences when deleting them by mistake. Well, we will let you know two of them so that you can use the one that seems faster and easier.

Using data validations

  • To start, select all the cells that contain formulas and that you want to protect. You do this by clicking on “Search and Select”Which is located at the end of the bar Start, and then click on “Formulas”.

Using data validations

Using data validations

  • Later in the section “Data”, click on “Data validation”And write the data and the formula ==” “(equal sign and a pair of double quotes). Here we show you:

Using data validations

Using data validations

  • Later in the tab “Error message”, it is possible to enter a personalized message that indicates that you have disabled the ability to write on the formulas.

Using data validations

This method has an excellent advantage and is that you do not need to protect the book with passwords and you can protect the formulas very easily. As for the disadvantages, is that the elimination of the formula is allowed by pressing the key “Delete“, that is to say, suppress.

Protecting the spreadsheet

Perhaps you have already used this alternative to protect Excel sheets or workbooks, but in this post we will let you know how to protect only the formulas, attached to the ability to edit all other cells. The process is the following:

  • Choose all cells by pressing this part of the upper left corner of the boxes.

Protecting the spreadsheet

  • Then press the keys Ctrl + 1 and this will show you the window “Format”(Also found in the Start section), where you should go to the”Protect sheet ” and then verify that the option “Protect sheet and contents of locked cells“.

Protecting the spreadsheet

  • To proceed to block the cells that have formulas, enter the section “Cell format“.

Protecting the spreadsheet

  • Then you can select all the formulas. And so press again Ctrl + 1, but this time it is necessary to leave the check of Blocked up in section To protect. This is unique and especially for lock cells containing formulas.

Protecting the spreadsheet

  • Now you must look for the tape Check and click on “Protect sheet” O well, “Protect book”. There, in the window that appears, put a password for later “To accept”And confirm the same password again.

Protecting the spreadsheet

With this, your formulas will already be protected against any vulnerability or edition, while the other boxes can continue to be edited. The advantage of this tool is that the formulas are protected against any change or deletion, since not even the “Delete”You can delete the formulas. The only downside is that you must keep the password very well, since in case of forgetting or misplacing it, you will not be able to unprotect the formulas in any way.

If you have any questions, leave them in the comments, we will answer you as soon as possible, and it will be of great help to more members of the community. Thank you! 😉

You may be interested:

Rate this post

Leave a Comment