• Skip to primary navigation
  • Skip to content
  • Skip to primary sidebar
  • Skip to footer
EDUCBA

EDUCBA

MENUMENU
  • Resources
        • Excel Charts

          • Histogram Chart Excel
          • Basic Excel Formulas
          • Text to Columns in Excel
        • Excel Charts
        • Excel Tips

          • Excel Gantt Chart
          • IFERROR with VLOOKUP
          • Data Table in Excel
        • Excel Tips
        • Excel Tools in Excel

          • Stacked Column Chart
          • Cheat Sheet of Excel Formulas
          • Excel Data Validation
        • Histogram chart in excel
        • Others

          • Resources (A-Z)
          • Excel Functions
          • Financial Functions in Excel
          • Logical Functions in Excel
          • Lookup Reference Functions in Excel
          • Maths Function in Excel
          • TEXT and String Functions in Excel
          • View All
  • Free Courses
  • All Courses
        • Certification Courses

          Excel Course 1
        • All in One Bundle

          All-in-One-Excel-VBA-Bundle
        • Excel course

          Excel-Training
        • Others

          • Excel advanced course
          • VBA Course
          • Excel Data Analysis Course
          • Excel for Marketing Course
          • Excel for Finance Course
          • View All
  • 120+ Courses All in One Bundle
  • Login

Excel Lock Formula

Home » Excel » Blog » Excel Tools » Excel Lock Formula

Lock Formulas in Excel

Excel Lock Formula (Table of Contents)

  • Lock Formula in Excel
  • How to Lock and Protect Formulas in excel?

Lock Formula in Excel

Lock formulas is an excel function used to protect the formulas inserted in cells so that when the file is shared across recipients they won’t be able to change or delete the formula. Also, the lock function is useful to hide your work from others, especially when you share it with your client and you do not want them to know how you have got the values.

Start Your Free Excel Course

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

How to Lock and Protect Formulas?

It is a very simple and easy task to lock and protect formulas. Let’s understand how to lock and protect the formulas with an example.

You can download this Lock Formulas Excel Template here – Lock Formulas Excel Template

Excel Lock Formula – Example #1

Consider the below example, which shows data of the sales team members.

Lock Formula in Excel - Data

In the below image, in column D the total has been calculated by inserting the formula =B2+C2 in cell D2.

Lock Formula in Excel - Calculating the Formula

The Result will be as shown below:

Lock Formula in Excel - Result

The formula in the total column has been copied from cells D2:D5.

Lock Formula in Excel - Copy the Formula

In this example, we are going to lock the formula entered in column D. So let us see the steps to lock and protect the formulas.

Popular Course in this category
Cyber Week Sale
Excel Advanced Training (14 Courses, 21+ Projects) 14 Online Courses | 21 Hands-on Projects | 129+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.8 (2,603 ratings)
Course Price

View Course

Related Courses
Excel Training (18 Courses, 9+ Projects)
  • Select all the cells by pressing Ctrl+A and unlock it.
  • Select the cells or the entire columns or rows, where you need to apply the formula.
  • Lock the cells which contain the formula.
  • Protect the worksheet.

Let us in detail show how are the above steps executed.

Step 1: Unlocking all the cells

The cells in excel are protected and locked in excel. As we need to lock particular cells in the workbook, it is necessary to unlock all the cells. So let us see how to unlock all the cells. The steps to unlock all the cells are as follows:

  • Press Ctrl+A to select the entire worksheet.
  • Right Click and select Format Cells from the options appeared in the context menu.

Lock Cell Step 1-1

  • Select the Protection tab and uncheck the Locked and Hidden option as well and then click OK.

Lock Cell Step 1-2

Step 2: Select and lock the cells containing the formula

Now here we need to lock the cells where we have entered the formula. The steps to lock the cells containing formula in excel are as follows:

  • Select all the cells in the worksheet by pressing Ctrl +A.
  • Go to the Home tab and select Find & Select option from the Editing menu.

Lock Cell Step 2-1

  • After selecting the Find & Select option, other options will appear under it, from which select Go To Special, option.

Lock Cell Step 2-2

  • Go To Special dialog box will appear as shown below.

Lock Cell Step 2-3

  • In which we have to select the Formulas option and also check all the options under the Formulas button are ticked and then click OK.

Lock Cell Step 2-4

Step 3: Protection of the Worksheet

This function is used to ensure that locked property is enabled not only for cells with formulas but also for all the cells in the workbook. Let us see the steps followed to implement the protection for the worksheet:

  • Go to the Review tab and select Protect Sheet option.

Lock Cell Step 3-1

  • After this, the Protect Sheet dialog box will appear.

Lock Cell Step 3-2

  • In which make sure that “Protect Worksheet and contents of locked cells” is selected.

Lock Cell Step 3-3

The user can also type a password in the text box under the Password to unprotect sheet, in order to make the worksheet safer.

Advantages of Lock Formulas in Excel

  • It helps the user to keep their data secure from others when they send their files to other recipients.
  • Helps the user to hide their work when the file is shared with other readers and users.
  • The user can use a password in the case to protect the entire workbook, which can be written in the text box named, ‘Password to unprotect sheet’.

Disadvantages of Lock Formulas in Excel

  • A new user, won’t be able to understand the function in excel easily.
  • There are cases when it becomes difficult if the user forgets to enter the password to unprotect the file.
  • It is sometimes not an efficient way, in terms of time as it consumes the time of a user to protect and unprotect the cells of the worksheet.

Things to Remember

  • All the cells are protected by default, do not forget to unlock the cells in order to lock formulas in excel.
  • After locking formulas in excel, make sure to lock the worksheet again.
  • The entire workbook can be protected, by using the option restricted or unrestricted access from the “Protect Workbook” option.
  • In case if the user needs to hide their work or formulas from others, they can tick the option “Hidden” by selecting the “Protection” tab from “Format Cells” dialog box.
  • In case the user needs to unprotect the complete file, simply type the password by selecting the “Unprotect Sheet” option.
  • A user can save time by moving the formulas to separate worksheet and then hiding it, instead of wasting time on protecting and unprotecting the worksheet.

Recommended Articles

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

  1. Remove (Delete) Blank Rows in Excel
  2. Highlight Every Other Row in Excel
  3. How to insert CheckBox in Excel
  4. Learn to create Combo Box in Excel
  5. How to Unprotect Sheet in VBA?

All in One Excel VBA Bundle (120+ Courses, 30+ Projects)

120+ Online Courses

30+ Projects

500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

1 Shares
Share
Tweet
Share
Reader Interactions
Leave a Reply Cancel reply

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

Primary Sidebar
Excel Functions Tutorials
  • Excel Tools
    • 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 (10+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (12+)
  • Lookup Reference Functions in Excel (30+)
  • Maths Function in Excel (39+)
  • TEXT and String Functions in Excel (25+)
  • Date and Time Function in Excel (20+)
  • Statistical Functions in Excel (55+)
  • Information Functions in Excel (4+)
  • Excel Charts (44+)
  • Excel Tips (195+)
  • VBA (180+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (14+)
  • Excel Formula and Functions (20+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED Training
  • EXCEL Training COURSE
Footer
About Us
  • Who is EDUCBA?
  • Sign Up
  •  
Free Courses
  • Free Online Excel Course
  • Free Vba Course
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
Resources
  • Resources (A To Z)
  • Excel Charts
  • Excel Tips
  • Excel Tools in Excel
  • Excel Functions
  • Financial Functions in Excel
  • Logical Functions in Excel
  • Lookup Reference Functions in Excel
  • Maths Function in Excel
  • TEXT and String Functions in Excel
  • Date and Time Function in Excel
  • Statistical Functions in Excel
  • Information Functions in Excel
Apps
  • iPhone & iPad
  • Android
Support
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions

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

EDUCBA

Download Lock Formulas Excel Template

By continuing above step, you agree to our Terms of Use and Privacy Policy.
EDUCBA
Free Excel Course

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

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

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

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

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

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

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

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

Let’s Get Started
Please provide your Email ID
Email ID is incorrect

Cyber Week Offer - All in One Excel VBA Bundle (120+ Courses, 500+ hours of Videos) View More

Cyber Week Offer - Cyber Week Offer - All in One Excel VBA Bundle (120+ Courses, 500+ hours of Videos) View More