When Microsoft users work in Excel, at least once it has happened to them that they place the titles that 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 to fix some cells . Taking into account that this is a valid resource for all versions , that is, for Excel 2016, Excel 2013, Excel 2007 and so on.
For its part, blocking and fixing rows, columns and cells in a spreadsheet is quite advantageous when you are working on a large document or to work much more comfortably without worries . There is also the possibility of protecting a complete 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. By specifying that once these are locked and password protected, can be easily unlocked at any time , by the same user who blocked them in the first instance.
Thus, when you work hard to create your reports in Excel, you will have the full certainty that no other person will be able to modify the column titles, the formats or the formulas that you once fix . That is why, we will let you know the most timely information so that you learn to block and protect rows, columns and cells; as well as securing the formulas and preventing them from being deleted due to an error.
Index:
What is it and what are the columns and what is it and what are the columns in the Microsoft Excel spreadsheet?
Perhaps you may have already observed this option activated in a previously created spreadsheet. Then, it can be said that blocking and fixing rows, columns and cells is that the first or first rows (in some cases they may be the columns); remain visible even if you scroll down or up the file.
Indeed, the usefulness of this function lies in continue to see the title that lets you know what information you are observing in each of those rows or columns . In Excel, this is known as “ Freeze “; a section found in the View tab .
On the other hand, there are three rules for the blocking function, and they are:
- The owners or administrators of the sheet are the only ones who can lock or unlock the cells in a sheet.
- The only ones allowed to continue editing or deleting columns and blocked rows, are the administrators or the owner .
- Publishers can observe the locked cells but cannot edit the data added to them . The same applies to 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 immobilized a row or a column, a lock icon will be displayed in the header of it to indicate that action.
Thanks to this function, it will be less tedious to work in Excel with large volumes of data than on several occasions, it is complex to analyze or compare the information hosted in that document. Integrating this way, greater ease and comfort, as well as ensuring that this can completely harm your task.
Steps to block or protect cells in Excel so they are not modified in Windows and Mac
Next we will show you the simple steps to immobilize and protect the cells of your document . As mentioned, it is a valid function for multiple versions of Excel but similarly, we emphasize that this process was performed in version 2013 .
Freeze Rows
- Open the document where you want to block a certain row or cell.
- Then you must select the row located just below the row or the rows you want to freeze . In the example we show you now, we want the row 1 to be blocked and therefore, we click on row 3 .
- You should then go to the option “ Vista ” at the top of the window.
- Once you are in “ Vista ”, open the section that says “ Freeze ” and shows you three options: Freeze panels , freeze upper row and freeze 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 facilitates the possibility of blocking the rows and columns, giving freedom to other cells to be displaced.
- With this, we can see that by scrolling down in the file, row 1 indicating the data: Name , surname and age ; is frozen or frozen, meanwhile.
Freeze Columns
This was the process to block a certain row. Now, 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 be blocked . In this case, we add more columns to better observe the process. And we proceed to select column B , since we will freeze column A.
- Again we go to the Vista tab and there we select the Freeze option, then choose “ Freeze Panels ”. As we did with the process to freeze rows.
- Once you do this, you will realize that by moving the spreadsheet to the right, the column “ Name ” remains always first in the first place >.
How to protect formulas in excel and prevent them from being deleted by mistake?
There are several methods to protect the formulas in Excel and thereby avoid inconvenience by deleting them by mistake. Well, we will let you know two of them so you can use the one that seems quicker and easier.
Using data validation
- To start, select all cells that contain formulas and that you want to protect. This is done by clicking on “ Search and Select ” located at the end of the Start bar, 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 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 it is that you don’t need to protect the book with passwords and you can easily protect the formulas . As for the disadvantages, the elimination of the formula is supported by pressing the “ Delete ” key, that is, delete .
Protecting the spreadsheet h3>
Maybe 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 formulas , attached to the possibility of editing all the other cells . The process is as follows:
- Choose all the cells by pressing this part of the upper left corner of the boxes.
- Then, press the Ctrl + 1 keys and this will show you the “ Format ” window (also found in the Start section), where you must go to the “ Protect sheet” tab and then verify that the “ Protect sheet and locked cell contents ” option is unchecked.
- To proceed to block cells that have formulas, go to the section “ Format of cells “.
- Then you can select all the formulas. And then press Ctrl + 1 again, but this time it is necessary to leave the Blocked check in the Protect section. This is done only and especially to block cells that contain formulas .
- Now you should look for the tape Review and click on “ Protect sheet ” or, “ Protect book ”. There, in the window that appears, enter a password for “ 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 continue to be edited . The advantage of this tool is that the formulas are protected against any change or deletion, since even the “ Delete ” key cannot erase the formulas. The only disadvantage is that you must keep the password very well , since if you forget it or lose it, you will not be able to unprotect the formulas in any way.