Simon Sez IT

Online software training and video tutorials for Microsoft, Adobe & more

  • Course List
    • Adobe
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Flash
        • Flash CS5
      • InDesign
        • InDesign CS6
        • InDesign CS5
      • Photoshop
        • Photoshop CS6
        • Photoshop CS5
        • Adobe Photoshop CS4
      • Photoshop Elements
        • Photoshop Elements 2019
        • Photoshop Elements 2018
        • Photoshop Elements 15
        • Photoshop Elements 14
        • Photoshop Elements 13
        • Photoshop Elements 12
        • Adobe Photoshop Elements 11 – 12 Hours Video Training Course
        • Adobe Photoshop Elements 10
        • Adobe Photoshop Elements 9
        • Photoshop Elements 8
    • Microsoft
      • Access
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Advanced Formulas in Excel
        • Excel for Business Analysts
        • Advanced PivotTables
        • Power Pivot, Power Query and DAX in Excel
        • Excel 2019 Beginners (Mac)
        • Excel 2019 Beginners
        • Excel 2019 Advanced
        • Excel 2016 Beginners
        • Excel 2016 Intermediate
        • Excel 2016 Advanced
        • Excel 2013
        • Excel 2013 Advanced
        • Excel 2010 Beginners
        • Excel 2010 Advanced
        • Excel 2007
      • OneNote
        • OneNote Desktop and Windows 10
        • OneNote 2016
      • Outlook
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • Power BI
        • Power BI
      • PowerPoint
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2019
        • Project 2019 Advanced
        • Project 2016
        • Project 2016 Advanced
        • Project 2013
        • Project 2013 Advanced
        • Project 2010
        • Project 2010 Advanced
      • Publisher
        • Publisher 2013
      • SharePoint
        • SharePoint Foundation 2013
        • SharePoint Server 2013
        • SharePoint Foundation 2010
      • Teams
        • Microsoft Teams
      • VBA
        • VBA for Excel
        • VBA Intermediate Training
      • Visio
        • Visio 2016
        • Visio 2013
        • Microsoft Visio 2010 – 9 Hours Video Training
      • Windows
        • Windows 10 (2020 Update)
        • Windows 10
        • Windows 8
        • Windows 7
        • Windows Vista
      • Word
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Pro 2021
        • QuickBooks Online
        • QuickBooks Canada
        • QuickBooks Pro 2020
        • QuickBooks 2019
        • QuickBooks 2018
        • QuickBooks Pro 2017
        • QuickBooks Pro 2016
        • QuickBooks Pro 2015
        • QuickBooks Pro 2014
        • QuickBooks Pro 2013
        • QuickBooks Pro 2012
        • QuickBooks Pro 2011
        • QuickBooks Pro 2010
        • QuickBooks Pro 2009
    • Web Development
      • AngularJs
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Bootstrap
      • Html/CSS
        • HTML/CSS Crash Course
        • HTML5 Essentials
      • Introduction to Alteryx
      • Introduction to Python
      • Java
      • JavaScript
        • JavaScript for Beginners
        • jQuery Crash Course
      • MySql
      • PHP
        • PHP for Beginners
        • Advanced PHP Programming
      • XML
    • Mac
      • OS
  • For Business
  • About Us
    • Testimonials
    • Contact Us
    • FAQ
    • Membership
    • About Us
  • Pricing
  • Free Resources
  • Sign In
  • Sign Up
Home > Microsoft Excel > Protecting Workbooks in Excel (And Worksheets, and Cells)

Protecting Workbooks in Excel (And Worksheets, and Cells)

The Protecting Workbooks in Excel tutorial is suitable for users of Excel 2010/2013/2016/2019 and Microsoft 365.  

Objective 

Protect Workbooks, Worksheets and Cells in Excel to help maintain the integrity of the spreadsheet and reduce errors. 

Protection Explained 

Imagine the scenario. You have spent weeks putting together a complex spreadsheet ensuring that the formulas work, it’s easy to update and nicely formatted. You share the spreadsheet with your team, and before you know it, mistakes have crept in, formulas have been accidentally edited or deleted, and links are broken. It is incredibly frustrating and creates unnecessary work for you as you attempt to rectify the mistakes.  

This can be avoided by protecting the worksheet. Not only can you protect the worksheet, but the entire workbook if you so desire. If people need to be able to edit certain cells or specific areas, that is no problem. You can define which parts of the workbook, worksheet, or cells you protect.  

Method 

Protecting the Entire Workbook 

You have three options when it comes to protecting the entire workbook, including all worksheets contained within.  

  • You can encrypt it with a password to limit who can open it.  
  • You can make the file open as read-only by default so that people must opt into editing it.  
  • You can protect the structure of a workbook so that anyone can open it, but they need a password to delete, rearrange, rename, or create new worksheets.  

Encrypt with a Password 

The highest level of protection is to encrypt the workbook with a password. On opening the workbook, it will prompt for the password and prevent anyone who does not have the password from accessing the file.

NOTE: Ensure that you make a note of the password. Once set, the password is not held anywhere and cannot be recovered if you forget it. 

  1. Click on the File menu 
  1. Click on the Info tab 
  1. From the Protect Workbook drop-down, select Encrypt with Password 

4. Enter a password to encrypt the contents of the file  

5. Confirm the password when prompted 

Now, when you re-open the spreadsheet, you will be prompted to enter the password.  

To remove the password protection from a workbook, open the workbook (which will require you to enter the password) and take the same steps you took to set the password. However, remove the password from the box, so it is blank and click ‘OK.’  

Open as Read-Only 

Your second option is to make a workbook read-only. The process is the simplest, but be aware that it provides the least amount of protection. Anyone can still open the workbook and just click ‘Enable Editing’ to make changes. It is best to look at this option as more of a warning to the person opening the file.  

  1. Click the File menu 
  1. Click the Info tab 
  1. From the Protect Workbook drop-down, select Always Open Read-Only 

Now, whenever you or anyone else opens the workbook, a warning bar will display across the top advising that the author has set the file to read-only to prevent accidental changes.  

  1. Click the Edit Anyway button to proceed with making changes.  

Protect a Workbooks Structure 

The third way you can protect a workbook is by protecting the underlying structure. For example, without the password, a user cannot move, rename, delete, rearrange, or create new worksheets in the workbook. However, they can still make changes to the data in the workbook.  

  1. Click the File menu 
  1. Click the Info tab 
  1. From the Protect Workbook drop-down, select Protect Workbook Structure 

4. Type in a Password and click OK .

Now, if you right-click on the worksheet tab, the options that allow you to change the structure of the workbook such as rename, move, or copy, delete, insert are greyed out.  

If someone knows the password, they can go to the Review tab, click the Protect Workbook button, and enter the password to make those options accessible again.  

Protecting a Worksheet 

You can also protect an individual worksheet from editing. When you protect a sheet, Excel locks all cells and prevents them from being edited.  

  1. Click the Review tab 
  1. Click Protect Sheet 
  1. Type a password 

At this stage, you can select actions that you want to allow. In this example, I am going to allow users to make select cells and format rows and columns.  

  1. Click OK 
  1. Re-enter the password to confirm 

To unprotect a worksheet, click the Review tab and select Unprotect Sheet.  

Protect Specific Cells from Editing 

Sometimes you might want to protect only specific cells from editing. For example, you might have a worksheet containing a mixture of input cells and formulas. You might wish for others to input figures into the input cells but lock the cells containing the formulas.  

In this example, I want users to be able to change the cell containing the VAT rate, but not the formulas contained in the other cells.  

  1. Select all the cells you DO NOT want to be locked  
  1. Right-click and select Format Cells 
  1. Click the Protection tab 

By default, ALL cells in Excel are locked. The reason they do not appear to be locked when working in a spreadsheet is that the locking does not take effect until the spreadsheet is protected. Therefore, you will see a checkmark in the ‘Locked’ box.  

  1. Uncheck the Locked box 
  1. Click OK 

Now, the selected cells are essentially unlocked and all other cells in the spreadsheet are locked. To enforce this, you need to protect the worksheet.  

  1. Click the Review tab 
  1. Click Protect Sheet 

Like what you see? Check out these links for more examples relating to protecting workbooks, worksheets, and cells.  

Microsoft – Protect a Worksheet 

Envatotuts+ – How to Protect Cells, Sheets and Workbooks in Excel 

Video Tutorial 

To see Cell, Sheet and Workbook Protection in action, please watch the following video tutorial.  

For more Free Excel tutorials from Simon Sez IT. Take a look at our Excel Resource Center.

Other Excel classes you might like:

  • Logical Functions in Excel
  • Designing Better Spreadsheets in Excel
  • Introduction to Power Pivot & Power Query in Excel

To learn Excel with Simon Sez IT. Take a look at the Excel courses we have available.

Most Popular Posts

  • Microsoft Teams Tutorial – Getting Started.
  • HLOOKUP in Excel
  • Dashboards in Excel Using Pivot Tables, Pivot Charts and Slicers
  • Free Microsoft Project Training Course
  • Microsoft Access Tutorial for Beginners
  • How to Use Blending Mode With Layers in Adobe Photoshop Elements 15
  • How to Create Charts and Graphs in Microsoft Excel 2016
  • How to View and Navigate Pages in Publisher 2013

Join our list to receive 4 Free Courses!

Simon Sez Business Banner

Similar Posts

Using Project 2010 Work Breakdown Structure (WBS) Codes – Part 1

How to Use the Layout Designer Tool in QuickBooks 2019 – Part 1

How to Download Bank Transactions to QuickBooks 2019

How to Mail Merge in Word

How to Use Microsoft Excel 2013 Functions – Part 1

How to Organize Photos Using an Album in Photoshop Elements 2019

Course Categories

  • Development
  • Business
  • Office Productivity
  • IT & Software
  • Photography

About Us

  • About Us
  • Blog
  • Affiliates
  • Became an Instructor

Products

  • Our Plans
  • Business Licensing
  • Government Discounts
  • Non-Profit Discounts

Support

  • FAQ’s
  • Contact Us
  • DVD support

Connect

YoutubeFacebook
© 2021 Simon Sez IT, Inc.
  • Terms
  • Privacy Policy
888.817.6665 Monday thru Friday 7:30 a.m. - 5:00 p.m. (ET)