How to lock, unlock, pin and protect cells and formulas in Microsoft Excel? step by step guide

When Microsoft users work in Excel, at least once it has happened to them that they put the titles that they are going to use and when they go down the cell, they lose everything and they don’t even remember what 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 resource valid 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 spreadsheet, it is quite advantageous when you are working on a large document or, to work much more comfortably without worries. In addition, there is the possibility to protect 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 these are locked and password protected, can be easily unlocked at any timeby the same user who blocked them in the first instance.

Thus, when you work impetuously in the creation of your reports in Excel, you will have the full certainty that no one else will be able to change the column headings, formatting, or formulas you fix once. That is why we will provide you with the most appropriate information so that you can learn to block and protect rows, columns and cells; as well as to ensure the formulas and prevent them from being deleted due to an error.

What is it and what is it for locking and pinning rows, columns and cells in Microsoft Excel spreadsheet?

Perhaps you may have already seen 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 row or first rows (in some cases they can be the columns); remain visible even if you scroll down or up the file.

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

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

• The owners or administrators of the sheet 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, are the administrators or the owner.
• Editors can see locked cells but they cannot edit the data added to them. The same goes for anyone working from a Posted Sheet or a Update Request.

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

Thanks to this function, it will be less tedious to work in Excel with large volumes of data that on several occasions, it is complex to analyze or compare the information housed in said document. Integrating in this way, greater ease and comfort, in addition to preventing that this can completely harm your task.

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

Here we will show you the simple steps to achieve immobilize 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 note that this process was made in the 2013 version.

Freeze Rows

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

• then you must select the row directly below the row(s) you want to freeze.In the example that we show you now, we want the row 1 is blocked And because of that, we click on row 3.

• Then you should go to the option “View” at the top of the window.

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

• Seeing 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 freedom to the other cells to be moved.

• With this, we can see that by scrolling down the file, row 1 indicating the data: Name, surname Y age; is immobilized or frozen, meanwhile.

Freeze Columns

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

• First of all you must select the column to the right of the column to lock. In this case, we add more columns to better observe the process. And we proceed to select column Bgiven that let’s freeze column A.

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

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

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

There are several Methods to achieve protect formulas in Excel and thereby avoid inconvenience when deleting them by mistake. Well, we’ll let you know two of them so you can use the one that seems quickest and easiest to you.

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” located at the end of the bar Startand then click on “formulas”.

• Then 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 it to you:

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

This method has an excellent advantage, which is that you don’t need to protect the workbook with passwords and you can save the formulas very easily. As for the disadvantages, it is that the elimination of the formula is allowed when pressing the key “delete“, namely, suppress.

Protecting the spreadsheet

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

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

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

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

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

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

With this, your formulas will already be protected against any vulnerability or editing, while the other boxes can still be edited. The advantage of this tool is that the formulas are protected against any change or deletion, since not even the key “delete” can delete the formulas. The only disadvantage is that you must keep the password very wellsince if you forget or lose it, you will not be able to unprotect the formulas in any way.