EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Excel Tools Protect Sheet in Excel
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 ADVANCED Training
  • EXCEL Training COURSE

Protect Sheet in Excel

By Manisha SharmaManisha Sharma

Protect Sheet in Excel

Excel Protect Sheet (Table of Contents)

  • Introduction to Protect Sheet
  • How to Protect (Lock) Cells in Excel?

Introduction to Protect Sheet in Excel

Protect Sheet in Excel is used to protect and safeguard the data in any workbook and worksheet with the help of setting up a password in it. There are 2 ways to protect the sheet. One is by selecting the Protect Sheet option from the right-click menu list on Sheet’s name, and the other is choosing the Protect Sheet option, which is in the Review menu option under the Changes section. Once we click on it, we will get a protect sheet box that has all the possible options to lock the sheet. We can lock any row, column, cells by sorting. Choose an option as we choose and then enter the password to protect the sheet. And there is no limit to password characters.

How to Protect (Lock) Cells in Excel?

It is very simple and easy to protect cells in Excel. Let’s understand the working of protecting cells in excel with some examples.

You can download this Print Gridlines Excel Template here – Print Gridlines Excel Template

Example #1

Suppose we have an Excel with three different values. Our objective is to calculate their average.

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,596 ratings)

Average Formula 1-1

Protect Sheet Excel 1-2

As we can see from the screenshot above, we have three values in cell A2, B2, and C2. We calculate the average of these numbers in cell D2. Now we wish to lock the cell D2, which contains the formula, so that it doesn’t get accidentally altered when the excel is passed from one person to another.

Now, since all cells are locked in Excel by default, let us first unlock them. To do this, we first need to select all the cells by pressing Ctrl+A or Command+A. Next, open the Format Cells dialog box by pressing Ctrl+1.

Now we will need to go to the “Protection” tab.

Protection tab 1-3

Now under the “Protection” tab, we will need to untick the “Locked” option and click OK. All the cells in the worksheet will then be unlocked.

Protect Sheet Excel 1-4

Now, we will need to select the cell that contains the formula, i.e. cell D2. We will now press Ctrl+1. This will once again open the Format Cells dialog box. We will navigate to the Protection tab.

Protect Sheet Excel 1-5

This will lock the cell formula. Now, we have to protect the formula. To do this, we will need to navigate to the “Review” tab and click on the “Protect Sheet” option.

Protect Sheet Excel 2-1

This will open up the next dialog box, as shown below.

Protect Sheet Excel 2-2

It is also possible to provide a password to protect the sheet. This will result in Excel asking for a password when a user tries to access this Excel sheet.

Protect Sheet Excel 2-3

At this point, Excel will prompt to ask you to re-enter your password just to make sure that there are no mistakes with the password.

Protect Sheet Excel 2-4

After this, the cell will be locked, and it will no longer allow any further modifications to be made. If we click on this cell and try to press any keys in order to modify the formula, we will get the following pop up message.

Protect Sheet Excel 2-5

It is interesting to note that only this particular cell is protected, and no other cell will result in a similar pop up upon modification. A locked cell does not guarantee that the underlying formula will be protected from modifications. Therefore, it is vital to protect the formula after locking the cell. Thus, we will need to unlock all the cells first and then lock the formula cell and then protect the formula. This will prevent the formula cell from any accidental modifications, while all the other cells are open to modifications.

Example #2

Now, let us suppose that we have sales data for several products in different zones. Our objective is to calculate the total sales for each product separately and then identify the product with the highest sales. We need to lock the cell with the formula to calculate the highest sales and protect it from modifications. However, the cells with the summation formula and the sales data itself should be modifiable.

Protect Sheet Excel 3-1

We will first calculate the total sales for each product.

Protect Sheet Excel 3-2

So, to do this, we applied the SUM () function, passed the array for the first row, and then dragged the formula to the remaining rows since the column range for summation for all rows is the same.

Next, we will find the max sales. Adjacent to this table, we will select a cell and key in the index-match formula to find the product with the max sales.

Protect Sheet Excel 3-3

Max Cell 3-4

Now, we need to lock and protect the cell with the formula for Max Sales. Since all cells are locked in Excel by default, let us first unlock them. To do this, we first need to select all the cells by pressing Ctrl+A or Command+A. Next, open the Format Cells dialog box by pressing Ctrl+1.

Protect Sheet Excel 1-3

Now we will need to go to the “Protection” tab.

Now under the “Protection” tab, we will need to untick the “Locked” option and click OK. All the cells in the worksheet will then be unlocked.

untick the Locked option 1-4

Now, we will need to select the cell that contains the formula, i.e. cell D2. We will now press Ctrl+1. This will once again open the Format Cells dialog box. We will navigate to the Protection tab.

Protect Sheet Excel 1-5

This will lock the cell formula. Now, we have to protect the formula. To do this, we will need to navigate to the “Review” tab and click on the “Protect Sheet” option.

Protect Sheet Excel 2-1

This will open up the next dialog box, as shown below.

Protect Sheet Excel 2-2

It is also possible to provide a password to protect the sheet. This will result in Excel asking for a password when a user tries to access this Excel sheet.

Protect Sheet Excel 2-3

At this point, Excel will prompt to ask you to re-enter your password just to make sure that there are no mistakes with the password.

Confirm Password 2-4

After this, the cell will be locked, and it will no longer allow any further modifications. If we click on this cell and try to press any keys in order to modify the formula, we will get the following pop up message.

Protect Sheet Excel 3-5

Alternatively, after unlocking all cells in the worksheet, we can go to the “Home” tab and then navigate to the “Editing” section and select “Find & Select”, and from the dropdown, we can select “Go To Special”.

Find & Select 3-6

  • Excel would then open up the “Go To Special” dialog box, and we would then need to select “Formula” and click on OK. This would then select all the cells which have the formula in their contents.

Go To Special Dialog Box 3-7

  • We can now repeat the process of locking the formula cells and then protecting the formula.

Protect Sheet Excel 3-8

Things to Remember About Protect Sheet in Excel

  • Simply locking the cells in Excel does not guarantee protection from accidental modifications.
  • We will need to both locks the cells and protect the formulae to completely make them tamper-proof.
  • By default, all cells in the workbook are locked.
  • Like the first point, simply protecting the formula cells without locking them first would not guarantee accidental modifications.

Recommended Articles

This has been a guide to Protect Sheet in Excel. Here we discuss how to Protect the Sheet in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles-

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

View Course

Related Courses

Excel Training (23 Courses, 9+ Projects)4.9
0 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, 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 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 Print Gridlines Excel Template

EDUCBA

Download Print Gridlines Excel Template

EDUCBA

डाउनलोड Print Gridlines 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