• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • SALE 20% Off All Courses
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course โ€“ Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Logout
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Worksheet Protection

You are here: Home / Excel / Excel Worksheet Protection
Excel Worksheet Protection
April 20, 2015 by Mynda Treacy

Excel worksheet protection is a great way to prevent people accidentally messing up the reports youโ€™ve spent hours creating.

However, it is not going to keep confidential data a secret. If someone really wants to get to your data, or see how your model was put together and all the amazing formulas youโ€™ve used then there is nothing built into Excel security thatโ€™s going to stop them. The best you can do is make it difficult (for the majority or people) to get to your data/model.

Bottom line: Excel workbook protection is designed to keep honest people honest.

Thatโ€™s not to say it isnโ€™t worth using, so letโ€™s look at how can set up our workbooks to leverage it.

Video Instructions

For best results view in full screen HD

Written Instructions:

Things you can Protect

First of all letโ€™s talk about the different elements of the workbook that we can protect:

  • Cells โ€“ including formulas, formatting and data inside the cells
  • Structure โ€“ rows/columns/sheets
  • Objects โ€“ pictures, charts, Slicers, form controls i.e. anything that floats above the cells of the worksheet.

Note: you can still interact with Form Controls (check boxes, buttons etc.) even when they are โ€˜lockedโ€™ and the sheet is protected. Makes sense, right?

By default all cells and objects in a worksheet are protected. However that protection only becomes effective when you actually protect the sheet. Youโ€™ll find the sheet and workbook protection on the Review tab of the ribbon:

Excel Worksheet Protection

Clicking on Protect Sheet brings up a dialog box which asks you what you want to protect and you can set a password if you want to use one. The Protect Workbook dialog box asks you if you want to protect the structure and/or the windows.

The Windows option under Protect Workbook restricts changing the size and position of the windows for the workbook when the workbook is opened and moving, resizing, or closing the windows.

Tip: Before setting protection you must unprotect any cells or objects you want people to edit. So let's look at that next.

Cell Protection โ€“ open the Format Cells dialog box (CTRL+1) > on the Protection tab uncheck โ€˜Lockedโ€™:

Protect cells

Tip: Checking the Hidden box will prevent a cellโ€™s contents being visible in the formula bar. You can still see the result in the cell.

Object Protection โ€“ this varies from object to object but youโ€™ll typically find it by right-clicking the object > Size and Properties > locate the โ€˜Lockedโ€™ check box and set it accordingly. E.g. here is the Properties dialog box for a Shape:

protect shapes

Protection โ€“ Humans vs Excel

Protection in Excel is preventing humans from editing your worksheets/workbooks, Excel can still edit objects, values, charts, PivotTables and the like. So if a human makes a change to a cell that is not protected, which in turn affects a formula in a cell that is protected, Excel will still update the formula.

VBA/Macros are considered human in this context unless the UserInterfaceOnly parameter is used:

ActiveSheet.Protect UserInterfaceOnly:=True

Ok, so now you know where to find the protection tools letโ€™s look at how to use them.

Protecting Dashboard Reports

One of the FAQโ€™s from my Excel Dashboard webinar series where I built the interactive dashboard shown below was:

โ€œHow can I protect my dashboard but allow people to select the months in the Slicer, and still have the dashboard, PivotTables and formulas all update?โ€
Excel Dashboard Webinar

Well, like I mentioned above, Excel can still edit protected cells so all you need to do is edit the Slicer protection; right-click on the Slicer and choose Size and Properties. The dialog box will open:

  • Under Position and Layout - check โ€˜Disable resizing and moving'
  • Under Properties - select โ€˜Donโ€™t move or size with cellsโ€™ and uncheck โ€˜Lockedโ€™

protect cells Excel 2010

protect cells Excel 2013

Once youโ€™ve set the properties for the Slicer you can go ahead and protect the worksheet.

And if you donโ€™t want people to see your formulas you can also uncheck โ€˜Select locked cellsโ€™ which will prevent any protected cells being selected:

protect sheet

These settings will allow the user to interact with the Slicer but they canโ€™t mess it up. When selections are made in the Slicer it will automatically update the dashboard, but the user canโ€™t select any protected cells in the dashboard itself.

You can also hide the workings sheets in your file and then protect the workbook so they underlying data isnโ€™t accessible either.

Protection Helps Navigation

Another benefit of protecting cells is for data entry; when cells are protected the Tab key will move through the unprotected cells, skipping any protected cells. This makes data entry quick and easy.

Excel Worksheet Protection

More Excel Posts

excel templates

Where to Find Free Excel Templates

Where to find free Excel templates and how to create your own Excel templates. Using templates saves time and effort.
Easily Remove Password Protection from Excel Files

Easily Remove Excel Password Protection

How to remove Excel password protection when youโ€™ve forgotten the password. Works for sheets, workbooks and read only files.
Import data from a picture to Excel

Import Data from a Picture to Excel

Import data from a picture to Excel. Works with pictures from a file or the clipboard and loads it to the spreadsheet.
excel online

5 Excel Online Features Better than Desktop

5 Excel Online Features Better than Desktop including searchable data validation, track changes, single line ribbon and more.

10 Common Excel Mistakes to Avoid

10 common Excel mistakes to avoid, including merge cells, external links, formatting entire rows/columns and more.
new Excel features

Cool New Features in Excel for Microsoft 365

Cool New Features in Excel for Microsoft 365 including the navigation pane, smooth scroling, unhide multiple sheets and more.
dynamic dependent data validation

Dynamic Dependent Data Validation

Dynamic Dependent Data Validation with dynamic array formulas like FILTER make it quick and easy to set up.
QAT

Excel Quick Access Toolbar

The Excel Quick Access Toolbar is not only a handy for your mouse, but it also enables some super easy keyboard shortcuts.

Share and Collaborate in Excel

Share and Collaborate in Excel just like Google Sheets! Show changes, custom views, threaded comments with @ mentions and more.
Workbook Protection

Excel Workbook Protection

Excel Workbook protection can prevent your users from breaking your reports while still allowing interaction with Slicers and refreshing.




Category: Excel
Previous Post:text labels on chart y axisExcel Chart Vertical Axis Text Labels
Next Post:Create a Single Excel Slicer for Year and MonthSingle Slicer for Year and Month

Reader Interactions

Comments

  1. Emad Nessiem

    August 4, 2021 at 5:21 am

    Hi Dear,

    By applying all procedures to the slicer, the issue is that even after protecting the worksheet, yes the entire worksheet is protected, but the right-click menu on slicers is active, and unwanted changes can happen.

    How to protect the slicer from activating the right-click menu options

    Reply
    • Mynda Treacy

      August 5, 2021 at 11:21 am

      Unfortunately, you can’t protect the Slicer from the right-click menu AND allow users to click the buttons in the Slicer.

      Reply
  2. Eddie

    July 20, 2020 at 3:20 pm

    Hello Mynda. Thanks so much for the tutorial. I was wondering if there was link to download the worksheet for this video. If not, I have just one question (hopefully, other folks on this platform can help too :)):

    What formula did you use to create the “Jan to Dec” headline in the Google Analytics Dashboard to connect with slicer selections?

    Thank you and hope to hear from you soon

    Reply
    • Mynda Treacy

      July 20, 2020 at 4:45 pm

      Hi Eddie, the headline is created using a dynamic text label. Hope that points you in the right direction. If you get stuck please post your question in our forum where you can also upload a sample Excel file and or screenshots. Mynda

      Reply
      • Eddie

        August 4, 2020 at 1:25 pm

        Thank you for the swift response Mynda.

        I actually used an “IF” statement and the “TEXT” function for the dynamic title and it worked perfectly.

        Have a great day.

        Reply
        • Mynda Treacy

          August 4, 2020 at 1:43 pm

          Great to hear, Eddie!

          Reply
  3. chidubem

    July 18, 2019 at 9:44 pm

    Hi Mynda, tnx for your pointers, i encountered a problem….
    my excel 2016 only shows 3d map, please how do i select a map chart from it?

    Reply
    • Mynda Treacy

      July 19, 2019 at 9:31 am

      3D maps are only available to Office 365 users. If you don’t see it, then it suggests you don’t have Office 365.

      Reply
      • chidubem

        July 20, 2019 at 8:25 am

        is it possible to incorporate office 365 into my excel 2016, if so, how do i go about it??
        if not, can you send me a direct link to download it??
        i really need to get this office 365
        tnx in anticipation to hearing from you.

        Reply
        • Mynda Treacy

          July 20, 2019 at 9:37 am

          Office 365 and Office 2016 are separate installs. You’re best to uninstall Office 2016 and then purchase Office 365. There are lots of different options for Office 365 licences, so you should refer to their site for the one that suits you best: https://products.office.com/en-us/buy/office.

          Mynda

          Reply
          • chidubem

            July 20, 2019 at 10:17 pm

            thanks a lot

  4. chidubem

    July 18, 2019 at 8:33 pm

    this is really the best, i learnt a lt from this

    Reply
  5. Claudio

    September 12, 2018 at 5:09 am

    I followed these steps and the protected dashboards seem to be functioning as expected, however when opening the file now I get an error message that says
    “That command cannot be performed while a protected sheet contains another PivotTable report based on the same source data. To Remove protection from the sheet that has the other report, click the sheet tab, and then click Unprotect Sheet (Review Tab, Changes group). Then try command again.”

    The error goes away when I remove the sheet protection – which defeats the entire purpose of protecting the sheet.

    Can you help?

    Reply
    • Mynda Treacy

      September 12, 2018 at 9:17 am

      Hi Claudio,

      Do you have PivotTables on your Dashboard sheet? If so, did you check the box to allow users to ‘Use PivotTable & PivotChart’ in the Protect Sheet dialog box?

      If you don’t have PivotTables on your Dashboard sheet, then did you protect the sheets containing your PivotTables or hide them as shown in the video tutorial?

      Mynda

      Reply
  6. Suhair Nusair

    July 1, 2016 at 4:13 am

    Hi Mynda,
    Thank you for your tip, I did all the steps according to your tip, so now the user can not press the Delete bottom, but he can right click and delete the slice.
    How can I prevent that?

    Reply
    • Mynda Treacy

      July 1, 2016 at 10:04 am

      Hi Suhair,

      Perhaps you did not set the protection correctly. The Slicer’s protection settings should be set to ‘Protect’ by default (right-click the Slicer > Properties). Then all you need to do is Protect the sheet and ensure the ‘Edit Objects’ option is not checked (it’s not checked by default).

      If you want to send me your workbook containing said Slicer I can check it for you.

      Mynda

      Reply
  7. david Ostreicher

    February 19, 2016 at 7:07 am

    Hi Mynda,

    I have a spreadsheet with a huge data entry table which also has many columns dispersed throughout which are loaded with different formulas. I need to make sure that the people entering the data cannot edit the formulas, but at the same time, I want the table to automatically add rows as data is entered. Once I protect the sheet, no matter which choices I check off, the table growing ability seems not to work anymore. Is there any way to protect the formulas but still allow the table to grow?
    (If necessary, I’m not afraid of a little VBA)

    Thank you very much

    Reply
    • Catalin Bombea

      February 19, 2016 at 3:07 pm

      Hi David,
      Here is the code I’m using to autoexpand tables on protected sheets:
      Option Explicit
      Private Declare Function OpenClipboard Lib "User32" (ByVal hwnd As Long) As Long
      Private Declare Function CloseClipboard Lib "User32" () As Long

      Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      If Target.Cells.Count > 1 Then Exit Sub ' unprotect only when a single cell is selected
      If Sheets("Switch").Range("AutoExpand") Like "Disabled" Then Exit Sub

      Dim Tbl As ListObject, Off As Integer, ExitCode As Label
      Dim TblFirstRow As Long, TblFirstColumn As Integer
      Dim FirstRowAllowed As Long

      On Error GoTo ExitCode
      Off = 0: If Target.Row > 1 Then Off = -1
      Set Tbl = ActiveSheet.ListObjects(1)
      TblFirstRow = Tbl.HeaderRowRange.Row
      TblFirstColumn = Tbl.HeaderRowRange.Cells(1, 1).Column
      OpenClipboard 0 ' when a macro runs, usually the clipboard is emptied; opening the clipboard will preserve whatever you have in there;
      FirstRowAllowed = TblFirstRow ' the table will be unprotected if the user selects a cell from this row down

      If Target.Row >= FirstRowAllowed And Target.Row <= Tbl.ListRows.Count + TblFirstRow + 1 And _ Target.Column <= Tbl.ListColumns.Count + TblFirstColumn And _ Target.Cells.Offset(Off, 0).Locked = False Then Unprotect CloseClipboard Else GoTo ExitCode End If Exit Sub ExitCode: Protect DrawingObjects:=False, Contents:=True, Scenarios:= _ False, UserInterfaceOnly:=True, AllowFormattingCells:=True, AllowFormattingColumns:=True, _ AllowFormattingRows:=True, AllowInsertingRows:=True, AllowSorting:=True, _ AllowFiltering:=True, AllowUsingPivotTables:=True CloseClipboard End Sub Private Sub Worksheet_Change(ByVal Target As Range) Dim Tbl As ListObject Set Tbl = ActiveSheet.ListObjects(1) If Not Intersect(Target, Tbl.Range) Is Nothing Then With Application .EnableEvents = False If Target.Columns.Count > 1 Then .Undo
      .EnableEvents = True
      End With
      End If

      End Sub

      The code should be pasted into a sheet module, where the table is located.
      You have to protect the cells (columns) with formulas, and set the other cells unlocked (where users can edit)
      The code is flexible, you don’t have to make any changes, it will work with the first table found in that sheet (Set Tbl = ActiveSheet.ListObjects(1))
      Let me know if you managed to make it work.
      Note that there is no password set, you have to add a password in code, otherwise users will be able to unprotect from ribbon. But even if the users can manually unprotect the sheet, when they select a protected cell, the code will protect the sheet back, so a password may not be necessary, you have to test and see.
      Catalin

      Reply
      • david Ostreicher

        February 23, 2016 at 3:10 am

        Thanks Catalin!

        That’s some serious coding!

        Just two questions before I try it. 1) If I’m using 64 bit, do I need to modify anything in the code (say… “User32” or similar). 2) I notice you said that when users select a protected cell, the code will protect the sheet back. This sheet is still a work in progress, so even though I don’t want anybody else changing anything, I still need the ability to do so myself. Will I still be able to do that?

        Thanks again! I’m anxiously awaiting your reply so I can try this out!

        — David

        Reply
        • Catalin Bombea

          February 23, 2016 at 3:37 am

          The function declarations that needs to be adjusted… (OpenClipboard and CloseClipboard declarations)
          You can take a look at this sample workbook: Autoexpand Tables on protected sheet.xlsm
          These are the function declarations that will work on 64-bit systems:
          #If VBA7 Then
          Private Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As LongPtr) As Long
          Private Declare PtrSafe Function CloseClipboard Lib "user32" () As Long
          #Else
          Private Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
          Private Declare Function CloseClipboard Lib "user32" () As Long
          #End If

          I also use a so called Switch, to turn on and off this functionality, see the sheet named “Switch”
          Catalin

          Reply
  8. Khushnood Viccaji

    April 23, 2015 at 3:00 pm

    Very informative post, as usual, Mynda ๐Ÿ™‚

    Just my two-bits of gyaan on one of the points above:
    “And if you donโ€™t want people to see your formulas you can also uncheck โ€˜Select locked cellsโ€™ which will prevent any protected cells being selected.”

    Here are a few more ways to prevent people from seeing your formulas.

    1. Select all the formula cells (using Goto Special), then in the Format Cells dialog box, Protection tab, check the ‘Hidden’ setting.
    Now protect the sheet as above.
    Voila! The user can see the cell values, but the formula bar shows nothing.

    2. If you want neither the formulas, nor the results in those cells to be visible after applying sheet protection, apply the custom format “;;;” (without the double-quotes) to such cells.
    I use this when I don’t want the user to see / know how some of the calculations work, or even realise that there is something in those cells.

    3. Simply hide the Formula Bar itself ๐Ÿ™‚
    To do this, uncheck the Formula Bar option on the View tab.

    Reply
    • Mynda Treacy

      April 23, 2015 at 8:38 pm

      Thanks for sharing, Khushnood. ๐Ÿ™‚

      Reply
  9. MF

    April 21, 2015 at 12:35 pm

    “Excel workbook protection is designed to keep honest people honest.”
    Can’t agree more! ๐Ÿ™‚
    Cheers,
    MF

    Reply
    • Mynda Treacy

      April 21, 2015 at 1:01 pm

      ๐Ÿ™‚ I should credit Catalin with that line!

      Reply
      • Akash

        October 16, 2017 at 3:01 am

        hiii manda
        i want to protect my whole excel files on my computer so can u please please help me out
        i have near about 60 excel files and i want to protect them in such a way that i can setup a password once and i want that without entering the password nobody will be able to see that excel files.. so is it possible ?? eagerly waiting for your reply hope u will help me out
        thankyou

        Reply
        • Philip Treacy

          October 16, 2017 at 12:11 pm

          Hi Akash,

          You can’t do that kind of protection from within Excel. You’ll need to set folder permissions to restrict access to the folder where the files are. How you do this depend son whether you are sharing files off a Windows server, or if you are trying to restrict access from your local hard drive.

          This article will give you a good overview of what you need

          Shared Folder Permissions

          Regards

          Phil

          Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Course Sale

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
trustpilot excellent rating
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.