5 ways to block and unlock the formulas in Excel

Protecting formulas in Excel is essential to maintain the integrity of the spreadsheets. This article explores five different methods to block and unlock the formulas, from simple protection of the sheet to the use of advanced scripts. You will learn to use the review form, file menu, the context menu, the VBA code and the Office Scripts to manage the safety of your formulas.

The protection of formulas in MicrosoftExcelIt is a crucial aspect to guarantee the accuracy and integrity of the spreadsheets. When working onprojectsImportant or financial reports, it is essential to prevent accidental or unauthorized changes to the formulas that make up the heart of the calculations. Fortunately, Excel offers several options to block formulas and protect the worksheets. In this article, we will explore five effective methods to block and unlock the formulas in Excel. We will start from the simplest techniques, such as the use of the review form, and progress towards more advanced solutions, such as the use of Code VBA and Office Scripts. Each method has its advantages and adapts to different needs and levels of competence.

Whether you are an occasional Excel user or a professional who works daily with complex calculation sheets, you will surely find a solution suitable for your needs. You will learn not only to protect your formulas, but also how to manage the safety of your work sheets in a flexible way, allowing selective changes when necessary.

Method 1: block the formulas from the revision tab

The review card is the most common and accessible starting point to protect the formulas in Excel. This method is ideal for users who want a quick and integrated solution directly into the Excel interface.

Preparation of the worksheet

Before applying protection, it is important to prepare the worksheet:

1. Select all the cells of the sheet by pressingCtrl + Aor by clicking on the upper left corner of the sheet.
2. Open the cell format menu withCtrl + 1or by clicking right and selecting "Celle format".
3. In the "Protection" tab, deselect the "Blocked" option. This unlocks all the cells.
4. Confirm with “OK”.

Now all the cells are unlocked, allowing us to select only those with formulas to be protected.

Selection of cells with formulas

To quickly select all the cells containing formulas:

1. Go to the “Home” tab.
2. Click on "Find and select" in the "Edit" group.
3. Choose “Go to Special.”
4. In the window that opens, select "formulas" and confirms with "OK".

Now you have selected all the cells containing formulas in the sheet.

Locking selected cells

With cells containing selected formulas:

1. Open the cell format menu again.
2. In the "Protection" tab, select the "Blocked" option.
3. Confirm with “OK”.

This passage sets the selected cells as blocked, but the protection is not yet active.

Activation of the protection of the sheet

To actually activate protection:

1. Go to the “Review” tab.
2. Click on “Protect Sheet”.
3. In the window that opens, you can set a password (optional but recommended for greater safety).
4. Select the actions you want to allow users (for example, "select blocked cells" and "select unlocked cells").
5. Confirm with “OK”.

At this point, your formulas are protected. Users will be able to view the results of the formulas but will not be able to modify them directly.

Advantages and limitations

This method offers several advantages:

• It is easily accessible from the Excel user interface.
• Allows selective formulas protection.
• It offers flexibility in allowing certain actions to users.

However, it also presents some limitations:

• It must be applied separately for each sheet of work.
• The protection password, if forgotten, can be difficult to recover.
• It is not suitable for the automatic protection of many sheets simultaneously.

Protection via the revision card is an excellent starting point for most Excel users. In the next methods, we will explore more advanced options for more complex situations.

Method 2: block formulas from the file menu

The File Menu offers an interesting alternative to protect the formulas in Excel. This method is particularly useful when you want to have an overview of the safety of your document and apply protection from a single centralized position.

Access the file menu

To get started:

1. Open the Excel sheet you want to protect.
2. Click on the "File" tab in the upper left corner of the Excel window.
3. Select "information" from the side menu.

Here you will find an overview of your document security settings.

Cell preparation

Before applying protection, you need to prepare the cells as in the previous method:

1. Return to the worksheet.
2. Select all the cells and unlock from the Celle format menu.
3. Select the cells with formulas using "Go to special".
4. Block only the selected cells.

These steps are identical to those described in method 1.

Application of protection

Now, to protect the sheet:

1. Back to the "File" menu> "Information".
2. Click on "Protects Folder".
3. Select "Current sheet protects".
4. In the window that opens, set a password (optional) and select the desired authorizations.
5. Confirm with “OK”.

Protection management for multiple sheets

An advantage of this method is the possibility of managing the protection of multiple sheets at the same time:

1. In the "File" menu> "Information", you will see a list of all the protected sheets in the working folder.
2. You can quickly remove the protection by clicking on "Remove protection" next to the name of the sheet.
3. To protect multiple sheets, repeat the process for each desired sheet.

Advantages of the File menu method

This approach offers some unique advantages:

• Provides an overview of the safety of the entire document.
• It allows you to manage the protection of multiple sheets from a single position.
• It is useful for documents with many sheets of work.

Limitations to consider

There are also some limitations:

• It requires multiple steps than the revision card method.
• It does not offer more advanced protection options than the standard method.
• It can be less intuitive for users accustomed to working mainly in the main Excel interface.

The file menu method is particularly useful when working with complex Excel documents with many sheets. It offers a centralized way to manage safety, which can be a significant advantage in corporate environments or for large projects.

Method 3: block formulas from the context menu

The contextual menu offers a quick and intuitive method to protect the formulas in Excel. This approach is particularly useful when you want to apply the protection quickly to individual work sheets without navigating through the ribbon cards or the file menu.

Initial preparation

As in previous methods, it is necessary to prepare the sheet before applying protection:

1. Select all the cells of the sheet (Ctrl + A).
2. Open the Format Cells menu (Ctrl + 1).
3. In the "Protection" tab, deselect "Blocked" and confirms with "OK".
4. Use "Go to special" to select the cells with formulas.
5. Reopen the cell format menu and block only the selected cells.

Using the context menu

Once you prepare the cells, here's how to use the context menu to protect the sheet:

1. Right -click on the working sheet tongue at the bottom.
2. In the menu that appears, select "Protects sheet".
3. The "Protect Foglio" dialog will open.
4. Insert a password (optional but recommended).
5. Select the actions you want to allow users.
6. Confirm with “OK”.

Advantages of the context menu

This method offers several advantages:

• Quick access: it is the fastest way to protect a single sheet.
• Intuitive: ideal for users who prefer to work with contextual menus.
• Consistent: it works in the same way in all recent versions of Excel.

Practical applications

The context menu method is particularly useful in scenarios such as:

• Quick protection of sheets during meetings or presentations.
• Specific sheet protection application in complex working folders.
• Teaching to new users, thanks to its simplicity and immediacy.

Limitations to consider

There are some limitations to keep in mind:

• It does not offer advanced protection options.
• It must be applied individually to each sheet.
• Does not provide an overview of the safety of the entire document.

Customization of the context menu

To make this method even more efficient:

1. You can customize the Excel quick access bar to include the "Protects sheet" option.
2. Click on the descent arrow in the quick access bar.
3. Select “More Commands”.
4. Search for "Protects sheet" and add it to the bar.

This will allow you to access the protection function with only one click, regardless of the sheet you are working on.

The context menu method is an excellent choice for those looking for a quick and direct approach to the protection of formulas. Its simplicity makes it ideal for daily use, especially in environments where speed and efficiency are priority.

Method 4: block formulas with the VBA code

The use of the VBA code (Visual Basic for Applications) offers a powerful and flexible method to block the formulas in Excel. This approach is particularly useful when you want to automate the protection process on multiple sheets or apply complex protection rules.

Introduction to VBA in Excel

Before starting, it is important to familiarize yourself with the VBA environment:

1. Open the VBA editor by pressingAlt + F11Or going to "Development"> "Visual Basic".
2. If the "Development" card is not visible, activate it from "File"> "Options"> "Customize multifunction bar".

Creating a VBA module

To enter the code:

1. In the VBA editor, go to "Insert"> "Module".
2. A new window will open where you can write the code.

VBA code to block formulas

Here is an example of VBA code to block the formulas in all the sheets of a working folder:

 

Sub Blockingformule () DIM WS AS WORKSHEET DIM PASSWORD AS STRING PASSWORD = "MIAPASSWORD123" 'replace with your password for Each WS in THISSWorkBook.Worksheets WS.Uprotect WS.Cells.locked = False WS.Cells.specialls (XLCELLYTYPEFORMUS). True WS.PROTECT Password, Drawingobjects: = True, Content: = True, Scenarios: = True Next Ws Msgbox "Formulas blocked in all sheets!", Vinformation end sub

This code performs the following operations:

1. Unlock all the sheets (if they were already protected).
2. Unlock all cells.
3. Block only the cells containing formulas.
4. Protects each sheet with the specific password.

Running the code

To run the code:

1. Place the cursor inside the subroutine.
2. Press F5 or click on "Perform" in the toolbar.

Alternatively, you can create a button in your workbook and associate it with this macro for faster execution.

Code customization

You can customize the code according to your needs:

• Change the password in the code.
• Add conditions to protect only specific sheets.
• Includes additional protection options by changing the parameters of the methodProtect.

Advantages of using VBA

The VBA approach offers numerous advantages:

• Automation: it quickly protects multiple sheets.
• Flexibility: allows you to implement complex protection logic.
• Consistency: guarantees that the same protection is applied evenly.
• Personalization: it can be adapted to specific needs.

Security Considerations

When using the VBA for protection:

• Make sure the code itself is protected to avoid unauthorized changes.
• Consider saving the file as "Excel's macro activation" working folder "(.xlsm) to keep the macro active.
• Inform users on the presence of macro in the file.

Limitations of the VBA method

There are some limitations to consider:

• Requires basic knowledge of VBA programming.
• It could activate security notices in some Excel configurations.
• It is not accessible to users without permits to perform macro.

The use of the VBA code to block the formulas is a powerful and flexible solution, ideal for advanced users or administrators who manage complex working folders. It offers a level of control and automation that cannot be reached with manual methods, making it particularly useful in corporate environments or for large projects.

Method 5: block formulas with Office Scripts

Office Scripts represent the last frontier in Excel's automation, offering a modern and powerful approach to block the formulas. This method is particularly useful for Excel online users or for those who prefer to work with Javascript instead of VBA.

Introduction to Office Scripts

Office Scripts are a relatively new feature of Excel, available mainly for Excel Online and some desktop versions:

1. They are based on Typescript, a typed version of Javascript.
2. They offer a more modern and flexible programming interface than the VBA.
3. They can be performed on web and desktop platforms.

Access to Office Scripts

To start using Office Scripts:

1. Open Excel Online or a compatible desktop version.
2. Go to the “Automate” tab.
3. Click on "New Script" to open the script editor.

Creation of the script to block the formulas

Here is an example of Office Script to block the formulas in all sheets:

Function Main (WorkBook: Excelscript.Workbook) {Let Sheets = WorkBook.Getworksheets (); let password = "miapassword123"; // Replace with your password for (Let Sheet of Sheets) {Let Usnedrance = Sheet.Get MEDEDRANGE (); Let FormulaCells = used.Getspecialcells (Excelscript.specialltype.formulas); Usedrance.getformat (). Getprotation (). Setlocked (False); FormulaCells.getformat (). Getprotation (). Setlocked (True); Sheet.GetProttection (). Protect ({password: password, allwinertrows: false, allwinsertcolumns: false, allwdeleterows: false, allwdeletecolumns: false, false: false, allowfilter: false, alloweditobjects: false, alloweditscenarios: False }); } Console.log ("formulas blocked in all sheets!"); }

This script performs the following:

1. Unlock all the cells in the interval used of each sheet.
2. Identify and block only the cells containing formulas.
3. It protects each sheet with the specified password and personalized protection options.

Running the script

To run the script:

1. Save the script in the editor.
2. Go back to the Excel sheet.
3. Go to the “Automatizza” tab and select the saved script.
4. Click on "Perform" to apply protection.

Advantages of Office Scripts

The use of Office Scripts offers several advantages:

• Cross-Platform compatibility: it works on both Excel online and on some desktop versions.
• Modern language: use Typescript, more familiar for many web developers.
• Cloud integration: scripts can be saved and easily shared through OneDrive.
• Performance: often faster in execution than traditional VBA macro.

Advanced customization

Office Scripts offer large customization possibilities:

• You can add conditional logic to protect only certain sheets or intervals.
• It is possible to integrate the scripts with automated power for more complex automations.
• You can create personalized user interfaces for the execution of scripts.

Security Considerations

When using Office Scripts:

• Make sure to manage passwords safely, avoiding to include them directly in the script if possible.
• Check the Excel security settings to manage the execution of scripts.
• Consider the use of advanced authentication and authorization for critical scripts.

Limitations of office scripts

There are some limitations to keep in mind:

• They are not available in all versions of Excel.
• They require a basic knowledge of Typescript/Javascript.
• Some advanced features may not be available compared to the VBA.

Office Scripts represent an evolution in Excel's automation, offering a modern and flexible approach to block the formulas. They are particularly suitable for environments that mainly use Excel online or for organizations that are migrating to cloud -based solutions.

Comparison between the formula protection methods

After exploring five different methods to block and unlock the formulas in Excel, it is useful to compare them to understand which is the most suitable for different needs. Each approach has its own strengths and limitations, and the choice will depend on the specific context of use.

Comparison table

Here is a table that summarizes the main features of each method:

Method	Ease of use	Flexibility	Automation	Compatibility
Review tab	High	Average	Low	All versions
File menu	Average	Average	Low	All versions
Contextual Menu	High	Low	Low	All versions
VBA code	Low	High	High	Desktop
Office Scripts	Average	High	High	Online/Some desktop

Method analysis

1. Review tab:
   • Ideal for: occasional or beginner users.
   • Strengths: ease of use, immediate accessibility.
   • Limitations: manual process for each sheet.
2. File menu:
   • Ideal for: centralized safety management.
   • Strengths: overall vision of the protection of the document.
   • Limitations: less intuitive for quick operations.
3. Contextual Menu:
   • Ideal for: quick protection of individual sheets.
   • Strengths: speed and ease of use.
   • Limitations: limited options, individual application.
4. VBA code:
   • Ideal for: advanced users, complex automation.
   • Strengths: high flexibility, powerful automation.
   • Limitations: requires programming knowledge, limited to the desktop.
5. Office Scripts:
   • Ideal for: cloud environments, web developers.
   • Strengths: cross-platform compatibility, modern language.
   • Limitations: limited availability, learning curve for Typescript.

Usage scenarios

• For small companies or individual users: The methods of the review form or the context menu are often enough.
• For large organizations: The VBA code or Office Scripts offer the necessary scalability and automation.
• For hybrid environments (desktop/cloud): Office Scripts can be the best choice for their versatility.
• For complex projects with many sheets: the file menu method or code -based solutions are preferable.

Security Considerations

Regardless of the chosen method, it is important to consider some security aspects:

• Use strong passwords and manage them safely.
• Limit access to the source files of scripts or macro.
• Implement security policies at company level for the use of macro and scripts.
• It educates users on the Beast Practice of safety in Excel.

Future evolution

With the continuous evolution of Excel and Microsoft productivity tools, it is likely that we will see:

• Greater integration between traditional and cloud -based methods.
• Improvements in the safety and management of authorizations.
• New automation and protection features.

The choice of the best method to block the formulas in Excel depends on various factors, including the user's technical skills, the specific needs of the project and the work environment. By carefully evaluating these aspects, you can select the most suitable approach to effectively protect your formulas and guarantee the integrity of data in the calculation sheets.

Unlock protected formulas

After exploring the various methods to block the formulas in Excel, it is equally important to understand how to unlock them when necessary. The release of the formulas can be essential to make changes, update calculations or make reviews of the work sheets. Let's see how to proceed with each of the methods previously discussed.

Unlocking from the review tab

1. Select the worksheet containing the blocked formulas.
2. Go to the "review" tab in the multifunction bar.
3. Click on "Remove the sheet protection".
4. If a password has been set, enter it when requested.

This method is the most direct and easy to use, especially if the protection has been originally applied through the revision tab.

Unlock from the file menu

1. Click on the "File" tab in the upper left corner.
2. Select “Information”.
3. In the "Protects Folder" section, click on "Remove protection".
4. Select the specific sheet to unlock.
5. Insert the password if requested.

This approach is useful when managing the safety of multiple sheets simultaneously.

Unlocking from the context menu

1. Right -click on the working sheet tongue at the bottom.
2. Select "Remove sheet protection" from the context menu.
3. Insert the password if necessary.

This method is the fastest to unlock a single sheet of work.

Unlocking via VBA code

To unlock the formulas using VBA, you can use a similar code to the following:

 

SUB SBLOCPAFORMULE () DIM WS AS WORKSHEET DIM PASSWORD AS STRING PASSWORD = "MIAPASSWORD123" 'replace with the correct password for Each WS in thisworkbook.worksheets on Error Resume Next Ws.unprotect Password on Error Goto 0 Next WS MSGBOX "Formulas unlocked in all the sheets!" Vinformation End Sub

This script will try to unlock all the sheets in the workbook using the specified password.

Unlocking with Office Scripts

To unlock the formulas with Office Scripts, you can use a script like this:

Function Main (WorkBook: Excelscript.Workbook) {Let Sheets = WorkBook.Getworksheets (); let password = "miapassword123"; // Replace with the correct password for (Let Sheet of Sheets) {Try {Sheet.GetProtection (). UNPROTECT (password); Console.log (`` sheet "$ {Sheet.getname ()}" successfully unlocked .`); } Catch (Error) {Console.log (`` error in the release of the "$ {Sheet.getname ()}" sheet: $ {error} `); }}}

This script will try to unlock all the sheets in the working folder, managing any errors for unprotected sheets or incorrect passwords.

Important considerations for the release

1. Password management: Make sure you have access to correct passwords. If you have forgotten them, it may be necessary to resort to more advanced recovery methods.
2. Permissions: Check that you have the necessary authorizations to unlock the sheets, especially in corporate environments with stringent safety policies.
3. Backup: Before unlocking and changing protected formulas, it is always advisable to create a backup copy of the file.
4. Reprotection: After making the necessary changes, remember to reapply the protection to the formulas to maintain the integrity of the work sheet.
5. Documentation: Keep a register of changes made to the formulas unlocked, especially in collaborative projects.

Common problems and solutions

• Forgot password: If you have forgotten the password, you may have to resort to third party tools to recover access. However, this should be done only as a last resource and in accordance with corporate policies.
• Unlocking errors: If you meet errors during the release, check you have the most recent version of Excel and that there are no conflicts with add-in or macro.
• Partial unlocking: In some cases, you may want to unlock only some specific cells or intervals. In this case, you will have to remove the protection of the sheet, change the lock settings for the desired cells, and then reapply the protection.

The release of the formulas is an equally important process as their blockage. Knowing how to effectively unlock the formulas allows you to maintain the flexibility necessary to manage and update your calculation sheets, while maintaining a high level of security and data integrity.

 

Published inExcel