EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Excel Tips Excel Unprotect Sheet
Secondary Sidebar
Excel Functions
  • Excel Tools
    • Excel Shortcut Redo
    • Reduce Excel File Size
    • Quick Analysis in Excel
    • Goal Seek in Excel
    • Compare Two Lists in Excel
    • Excel Quick Analysis
    • Estimate Template in Excel
    • Pivot Table Count Unique
    • CSV Files into Excel
    • Excel Business Plan Template
    • Excel Export to PDF
    • Free Excel Template
    • Excel Repair
    • Color in Excel
    • Form Controls in Excel
    • Timecard Template in Excel
    • How to Unhide All Sheets in Excel?
    • Power Query in Excel
    • Power View in Excel
    • XML in Excel
    • Excel Evaluate Formula
    • Examples of Excel Macros
    • Consolidation in Excel
    • Ribbon in Excel
    • Excel Conditional Formatting for Dates
    • Protect Sheet in Excel
    • Data Model in Excel
    • Pivot Table Examples
    • Pivot Table Slicer
    • Pivot Table Filter
    • Watch Window in Excel
    • Slicer in Excel
    • Print Gridlines in Excel
    • Convert Numbers to Text in Excel
    • Freeze Columns in Excel
    • Spelling Check in Excel
    • Name Box in Excel
    • Solve Equation in Excel
    • Excel Spreadsheet Examples
    • What If Analysis in Excel
    • How to Print Labels From Excel
    • Excel Named Range
    • Excel TRANSPOSE Formula
    • Excel Merge and Center
    • Excel Freeze Rows
    • HLOOKUP Formula in Excel
    • Excel Create Database
    • Excel Gridlines
    • Excel Spreadsheet Formulas
    • Excel Sort By Number
    • Excel Concatenating Columns
    • Excel AutoCorrect
    • Conditional Formatting For Blank Cells
    • Excel Icon Sets
    • Excel CTRL D
    • Excel Accounting Number Format
    • Excel Regression Analysis
    • Excel Import Data
    • Excel Freeze Panes
    • Excel Calendar
    • Excel Developer Tab
    • Excel Enable Macros
    • Excel Autosave
    • Heat Map in Excel
    • Excel Toolbar
    • Excel Error Bar
    • Excel Status Bar
    • Excel Unprotect Sheet
    • Check mark in Excel
    • Excel Column Filter
    • Excel Header and Footer
    • Excel Drawing
    • Range in Excel
    • Timeline in Excel
    • Excel Lock Formula
    • Excel Table styles
    • Insert New Worksheet in Excel
    • Excel Column Lock
    • Excel Forms for Data Entry
    • QUOTIENT in Excel
    • Excel Sorting
    • Excel Sort by color
    • Excel Data Bars
    • Excel Tool for Data Analysis
    • Excel Flash Fill
    • Excel Auto Fill
    • Excel Quick Access Toolbar
    • Excel Wrap Text
    • Excel Exponential Smoothing
    • Excel ANOVA
    • Excel Merge Two Tables
    • Excel Conditional Formatting in Pivot Table
    • Dynamic Tables in Excel
    • Excel Sort by date
    • Excel Dynamic Range
    • Record Macro in Excel
    • Two Variable Data Table in Excel
    • Merge Cells in Excel
    • One Variable Data Table in Excel
    • Excel Fill Handle
    • CheckBox in Excel
    • Excel Table
    • Excel Combo Box
    • Auto Format in Excel
    • Advanced Filter in Excel
    • Excel AutoFilter
    • Excel Data Filter
    • Excel Data Validation
    • Excel Radio Button
    • Data Table in Excel
    • Text to Columns in Excel
    • Excel List box
    • Excel Solver Tool
    • Scrollbar in Excel
  • Excel Functions (12+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup and Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL COURSE
  • Online Excel Data Analysis Course
  • Excel for Finance Course

Excel Unprotect Sheet

By Jeevan A YJeevan A Y

Unprotect Sheet in Excel

Excel Unprotect Sheet (Table of Contents)

  • Introduction to Unprotect Sheet in Excel
  • How to Protect and Unprotect Sheet in Excel?
  • How to Protect and Unprotect Multiple Sheets using Excel VBA Code?

Introduction to Unprotect Sheet in Excel

We usually keep sharing a file with our subordinates. Often we do not want them to make any kind of changes to the sheet so that it remains unaltered. Before we explain to you the process of protecting & unprotecting excel sheets, let me tell you an important thing about the protection of sheets in excel. This is not complete security to your sheet; people still can see the data, insights, and everything; the only thing is that they cannot make any changes to the sheet, that’s all. This article will show you how to protect sheets and unprotect them with a password in excel. Follow this article closely to learn the process of locking & unlocking.

How to Protect and Unprotect Sheet in Excel?

Below are the steps to protect and unprotect the sheet in excel.

You can download this Unprotect Sheet Excel Template here – Unprotect Sheet Excel Template

#1- Protect sheet & Prevent Users from Making Changes

Assume you are sending the below file to all the department managers, and you don’t want them to make any changes; rather, just see the numbers.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,768 ratings)

Unprotect Sheet (department managers)

Follow the below steps to protect your sheet.

Step 1: Go to the Review tab and click on the Protect Sheet.

Review protect Sheet

Step 2: Now, you will see Protect Sheet window, which asks you to put the password.

Protect Sheet window

Step 3: Enter your password carefully here. Because you need the same password to unprotect the sheet later if you want to make some changes, a password is case sensitive so remember the exact characters as well. Here we have set the password as 12345.

Unprotect Sheet(Set Password)

Step 4: At the same time, we can allow a user to make some kind of changes like Insert Hyperlink, Insert row or column, etc… If you allow them to make changes, you need to select those options under “Allow all users of this worksheet to.”Click on OK.

select unlocked cells

Step 5: It will ask you to re-enter the password one more time. Re-enter and click on OK to complete the process.

Re - enter pasword

Ok, now we have protected this sheet. Users cannot make any changes.

In this sheet, users cannot do anything else apart from seeing the data. The only thing they can do is they can insert a new sheet and only work on that newly inserted sheet only.

#2 – How to Unprotect Sheet in Excel?

Now we have protected our sheet, which restricts the users from modifying the document. If you want to make changes, you need to unprotect the sheet first and make changes in excel. Follow the below steps to unprotect the excel sheet.

Step 1: Go to the Review tab & click on Unprotect Sheet.

Sheet sales table

Step 2: As soon as you click on Unprotect, it will ask you to enter the same password which you have typed while protecting the sheet.

typed while protecting the sheet

Step 3: After the password is entered, click on OK.

entered click on OK

Step 4: If the password is correct, you can make changes, or else it will say Password is Incorrect.

Password is Incorrect

How to Protect and Unprotect Multiple Sheets using Excel VBA Code?

Below are the steps to protect and unprotect multiple sheets using VBA Code in Excel.

#1 – Protect Multiple sheets Using Excel VBA Code

What if you have many sheets? Can you keep protecting the sheets one by one? It is a herculean task to do. But using VBA code, we can protect all the sheets in few seconds; all you need to do is copy the below code and paste it into the VBA editor.

Code:

Sub Protect_All_Worksheets()
Dim Ws As Worksheet
For Each WsIn ActiveWorkbook.Worksheets
Ws.Protect Password:="12345"
Ws.EnableSelection = xlNoSelection
Next Ws
End Sub

Step 1: Click on Visual Basic under the Developer tab.

Visual Basic under Developer

Step 2: Go to Insert & click on Module.

insert module

Step 3: Now, we can see that a new module has been added as Module 1 on the left-hand side of the panel.

module 1

Step 4: Double click on the module and paste the code.

Unprotect Sheet Step 2-4

Step 5: After pasting this code in Module 1, click on the RUN button on top. Or else you can press the F5 key by placing a cursor inside the macro.

Run Sub/UseForm

As soon as the code is run, it will protect all the sheets in your workbook.

#2 – Unprotect Multiple sheets Using Excel VBA Code

We know how to protect multiple sheets using VBA code in simple code in few seconds. If you have locked multiple sheets, it is not an easy task to unprotect one by one. For this also I have written VBA code, copy & paste the code.

Code:

Sub Unprotect_All_Worksheets()
Dim Ws As Worksheet
For Each Ws In ActiveWorkbook.Worksheets
Ws.Unprotect Password:="12345"
Next Ws
End Sub

Unprotect Sheet Step 2-6

Things to Remember

  • You need to save the file as Macro-Enabled Workbook after pasting the code to the module.
  • You can change the password to your own password. Remove 12345 and enter your password inside the double-quotes.
  • A password is case-sensitive.
  • If you forget the password, you need to google research and upload your file to the website to remove the password from the file.

Recommended Articles

This has been a guide to Unprotect Sheet in Excel. Here we also discuss how to protect and manually and also by using VBA code along with practical examples and downloadable excel template. You can also go through our other suggested articles to learn more –

  1. Worksheets in Excel
  2. Vba Protect Sheet 
  3. VBA Unprotect Sheet
  4. Unprotect Excel Workbook
Popular Course in this category
Excel Training (23 Courses, 9+ Projects)
  23 Online Courses |  9 Hands-on Projects |  110+ Hours |  Verifiable Certificate of Completion
4.9
Price

View Course

Related Courses

Excel Data Analysis Training (17 Courses, 8+ Projects)4.9
Excel for Finance Training (18 Courses, 7+ Projects)4.8
7 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Excel Course

Excel functions, formulas, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Unprotect Sheet Excel Template

EDUCBA

Download Unprotect Sheet Excel Template

EDUCBA

डाउनलोड Unprotect Sheet Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more