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.
- Open the document where you want to lock a certain row or cell.
- 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.
- Later you must go to the option “View”Located at the top of the window.
- 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.
- 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 indicates the data: Name, last name Y age; is immobilized or frozen, meanwhile.
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.
- 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.
- 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 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”.
- 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 ability to write on the formulas.
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.
- 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“.
- To proceed to block the cells that have formulas, enter the section “Cell format“.
- 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.
- 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.
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! 😉