【 LOCK Cells and Formulas in Excel 】 Guide ▷ 2022

When Microsoft users work in Excel, at least once it has happened to them that they put the titles they are going to use and when they go down the cell, they lose everything and do not 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 others.

For his part, lock and pin rows, columns, and cells in a spreadsheetIt 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 of protecting an entire spreadsheet, so that none of the cells can be edited. In short, 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 titles, formats, 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 secure the formulas and prevent them from being deleted due to an error.

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

You may have already seen this option turned on in a previously created spreadsheet. So, it can be said that blocking and fixing rows, columns and cells consists of the first or first rows (in some cases they can be the columns); they 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 blocking function, and they are:

The owners or administrators of the sheet They are the only ones which can lock or unlock cells on a sheet.
The only ones allowed to continue editing or deleting locked columns and rows, Are they the administrators or the owner?.
Editors can see locked cells but they cannot edit the data added to them. The same is also true for anyone working from a Published 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 immobilized a row or a column, a lock icon will be displayed in its header to indicate said action.

Thanks to this function, it will already be less tedious to work 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 can completely harm your task.

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

Next we will show 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 note that this process was made in version 2013.

Freeze Rows

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

then you must select the row located just below the row or rows you want to freeze.In the example that we show you now, we want the row 1 be locked And because of that, we click on row 3.

Subsequently, you must 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, immobilize top row and immobilize first column.

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.

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

Immobilize 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 that is located 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 Bin view of the fact that We are going to immobilize column A.

Again we go to the tab View and there we select the option Immobilizeand then 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 in the first place.

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 thus avoid inconveniences when deleting them due to a mistake. Well then, we will let you know two of them so that you use the one that seems fastest 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” which is located at the end of the bar Startand then click on “formulas”.

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:

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 and that is that You don’t need to protect the book with passwords and you can protect the formulas very easily.. As for the disadvantages, it is that the deletion of the formula is supported by pressing the “ key.del“, namely, 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 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 cells.

Then press the keys Ctrl+1 and this will show you the window “Format” (it is 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“.

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

Then you can select all formulas. And so press again Ctrl+1but this time it is necessary to mark 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 edition, 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 “del” 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.