Open In App

Protecting Excel Worksheets and Workbooks

Last Updated : 01 Aug, 2022
Improve
Improve
Like Article
Like
Save
Share
Report

Sometimes while sharing your spreadsheets you may not want the receiver to change the content of your worksheet or perhaps change only specific content and leave the rest untouched. To protect your worksheet from being edited by other people, Excel offers a protection feature. By following a couple of steps you can set protection to your work.

Protect an entire workbook from editing: A workbook can be protected by encrypting the workbook with a password, making the workbook read-only, or protecting the structure of a workbook.

Encrypt a workbook with a password:

To prevent other people from accessing your Excel files, protect them with a password. Head on to the file menu and do the following: 

Step 1: Select File > Info.

Step 2: Select the Protect Workbook box and choose Encrypt with Password.

Step 3: Enter a password in the Password box, and then select OK.

Step 4: Confirm the password in the Re-enter Password box and then select OK.

TIP: Excel can’t retrieve your password once forgotten hence it’s advised to keep it easy to remember.

Making a workbook Read-only :

By making a workbook read only the user can read the content of the file and then enable editing if the user wishes to make changes, it gives the user a hint to be cautious about editing the file.

To make a workbook read-only open the Excel file you want to protect and then :

Step 1: Head on to File>info 

Step 2: Select the Protect Workbook box and choose Always open Read-Only.

Step 3: Open the file and a warning will be displayed stating the author prefers the file to be opened in read-only mode. This makes the user aware of the author’s concerns.

Protecting the structure of a workbook: 

To prevent other users from viewing hidden worksheets, adding, moving, deleting, or hiding worksheets, and renaming worksheets, you can protect the structure of your Excel workbook with a password. 

Step 1: Click on Review tab>Protect workbook (under Changes ).

Step 2: Enter a password in the password box.

Step 3: Click ok and re-enter the password to confirm and hit OK again.

To remove the password from the workbook Head on to Review > Protect Workbook > type in the password and the workbook will be free from the password.

Protecting a worksheet from editing: 

To protect the Workbook from editing follow the below steps:

Step 1: Open the worksheet you want to protect.

Step 2: Head on to Review tab > Protect sheet. 

Step 3: This opens a project sheet dialog and prompts you to enter a password for the sheet. You can check the boxes you want the user to be able to edit after protection and uncheck the rest.

Step 4: Hit OK and confirm password  (don’t lose this password). 

Your sheet is now protected, try typing into the sheet and see what excel prompts!

As a developer if you want to make changes to your sheet you can hit the unprotect sheet under the review tab>enter the password> make the changes.

Protect specific cells from editing: 

By default, every single cell in an Excel worksheet is locked. So if you head on to the Review tab and hit protected every single cell in the worksheet gets protected and no one can make any changes. But if you want the user  to be able to edit certain cells  and leave the rest unedited then follow these steps :

Step 1: Open the file you want to protect. 

Step 2: Select the cells from the file that you want the users to be able to edit and make changes to(cells you want to unlock).

Step 3: Then under the font section click on the arrow pointing downwards icon on the bottom-right (called font settings)

Step 4: As the Format cells dialog box opens up, head on to Protection, turn off the locked property for the selected cells, and hit ok.

These cells are no longer locked and can be edited by the user. Deciding which cells should be locked and which shouldn’t is important while protecting the sheets.

Step 5: Head on to the Review tab > Protect sheet. This opens a protect sheet dialog and prompts you to enter a password for the sheet. You can check the boxes you want the user to be able to edit after protection and uncheck the rest.

Step 6: Hit OK and confirm password  (don’t lose this password). 

Step 7: Click ok and re-enter the password to confirm and hit OK again.

According to the situation at hand, you can protect your worksheet or workbook in one of the many ways.


Similar Reads

Retrieving Data from Folders and Workbooks with Different Sheet Names
Writing and running commands or queries on a database are often required for data retrieval. The database searches for and obtains the desired data using the query that was provided. Typically, applications and software use a variety of queries to retrieve data in various formats. Data retrieval also involves obtaining large amounts of data, typica
3 min read
Editing Excel Macros in Excel
Excel Macro is a set of actions that can be recorded, saved, used multiple times. This feature saves us a lot of time when dealing with repetitive tasks and huge data sets. We can always make changes to an existing Macro in Excel. There are two ways to record and run macros: Excel Commands Excel Visual Basic for Applications(VBA).1. Copying a Macro
1 min read
Excel Table Styles and Formatting: How to Apply, Change and Remove
In Excel, you can format tables using various styles, colors, fonts, and other visual elements to make your data more organized and visually appealing. However, there may be situations where you want to remove all the formatting from a table and revert it to its default appearance. Have you ever felt stuck in the maze of Excel tables? Let’s break f
7 min read
How to Declare and Initialize String Array in Excel VBA?
A string array is an array where we can store only string values in the array, with the help of a string array, we can store more than one string value. We can declare the string array in many ways like declaring a static string array, declaring a variant size array of string using the Array function, and a string array using the split function whi
2 min read
How to Automatically Insert Date and Timestamp in Excel?
The Date and Timestamp is a type of data type that determines the date and time of a particular region. It contains some characters along with some encoded data. This format may vary from language to language. Keeping track of date and time helps in managing records of our work as well as segregate the information day-wise. In this article, we will
4 min read
How to Create Slicers and Timelines in Excel?
Slicers and Timelines in Excel are used to implement with the Excel tables and Pivot Tables to help in filtering out large amounts of data to get the high level of information from a collection of data. Finding Slicer and Timeline in Excel Navigate to the Insert menu ribbon, Select the Filters section in the dropdown you can see Slicer and Timeline
3 min read
Positive and Negative Trend Arrows in Excel
A Positive Trend is an up arrow that indicates an upward trend and a Negative Trend is a down arrow that indicates a downward trend. In this article, we will look into how we can create Positive And Negative Trends in Excel. To do so follow the below steps: Step 1: First format your data. Step 2: Calculate the change % between two year. Firstly, we
2 min read
How to Create a Pareto Chart in Excel (Static And Dynamic)?
A Pareto Chart is a type of chart that contains both, a line chart and a bar chart where the cumulative total is represented by the line chart. They are generally used to find the defects to prioritize, in order to observe the greatest overall improvement. The chart is named for the Pareto principle, which, in turn, derives its name from the noted
3 min read
INDEX and MATCH Function in Excel
INDEX-MATCH has become a more popular tool for Excel as it solves the limitation of the VLOOKUP function, and it is easier to use. INDEX-MATCH function in Excel has a number of advantages over the VLOOKUP function: INDEX and MATCH are more flexible and faster than VlookupIt is possible to execute horizontal lookup, vertical lookup, 2-way lookup, le
10 min read
How to Group and Ungroup Pivot Chart Data Items in Excel?
A pivot chart is the visual representation of a pivot table in Excel. Pivot charts and pivot tables are connected with each other. Pivot chart are much more flexible than normal chart because Pivot Chart is linked to a PivotTable. Filters, sorts, and data rearrangements applied to Pivot Table are reflected on the chart. Steps to create Pivot chart
2 min read
Article Tags :