EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Login
Home Excel Excel Resources Excel Tips Excel Hide Formula

Excel Hide Formula

Madhuri Thakur
Article byMadhuri Thakur

Updated June 2, 2023

Hide Formula in Excel

Excel Hide Formulas (Table of Contents)

ADVERTISEMENT
Popular Course in this category
MS Excel Course Bundle - 36 Courses in 1 | 13 Mock Tests

Start Your Free Excel Course

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

  • Introduction to Hide Formula in Excel
  • Different ways to Hide the Formula in Excel
    • #1 – To protect the worksheet and activate the hide formula option
    • #2 – To hide the formula bar of an Excel workbook

Introduction to Hide Formula in Excel

There are two ways to Hide any formulas in Excel. The first way we can protect the current worksheet or workbook is from the Protect Sheet and Protect Workbook option, which is available in the Review menu under the Changes section. There we need to provide the password to lock the changes in Sheet. This will prevent other people from changing their formulas. And other is by Format Cells option, which can be accessed by short cut key Ctrl + 1. From the Format Cells box, under the Protection tab, select the Hidden option and locked option. By doing this, it will lock the selected cell preventing it from editing.

  • This feature in Excel is introduced to hide the formulas from others.
  • When writing a formula in an active cell, Excel automatically displays the formula in the formula bar.

Hide formula in MS-Excel 1-1

Hide formula in MS-Excel 1-2

  • Sometimes, it is necessary to hide the formula for security or confidentiality reasons. In that case, we use the Excel hide formula.
  • Excel makes it simple to hide formulas from getting changed.

Example:

Hide formula in MS-Excel 1-3

Different Ways to Hide the Formula in Excel

There are different ways of hiding Excel formulas to protect against accidental or intentional changes in the worksheet, as mentioned below –

You can download this Hide Formula Excel Template here – Hide Formula Excel Template
  1. To protect the worksheet and activate the hide formula option.
  2. To hide the formula bar of the workbook.

#1 – To protect the worksheet and activate the hide formula option.

Steps to hide the Excel formula

Hide formula in MS-Excel 1-4

Step 1: To Unprotect the Sheet

  • The first step is to make sure that the worksheet is unprotected.
  • Go to the Review tab in the Excel ribbon to check the same.

Hide formula in MS-Excel 1-5

  • If you find the Protect Sheet button in the toolbar, then it means the worksheet is not protected. In that case, you can directly start the steps to hide the formula.
  • If you find the Unprotect Sheet button in the toolbar, then it means the worksheet is protected. Click on the same and unprotect the sheet by typing the correct password.

Hide formula in MS-Excel 1-6

Hide formula in MS-Excel 1-7

Step 2: To hide the Formula in Excel.

  • As per the in-built feature of MS Excel, by default, the active cell formula appears in the formula bar.

SUM formula in MS-Excel 1-1

  • To hide the Excel formula, you need to select the range of cells for which you want to hide the formula.
  • Right-click the cell or range of cells. Select Format Cells or press Ctrl+1.

Hide formula in MS-Excel 2-1

  • Once the Format Cells dialog box appears, click on Protection.

Format Cells dialog box 2-2

  • By default, Excel selects the “Locked” checkbox and deselects the “Hidden” checkbox. The selected Locked check box prevents the user from changing the contents of the cells in a sheet, whereas the Hidden checkbox is an optional feature. It helps hide the formula and prevents the user from seeing the same. It needs to select manually.

Hide formula in MS-Excel 2-3

Step 3: To Protect the Sheet

  • This step is very important in hiding the formula. The above formula will remain ineffective until the sheet is protected.
  • Go to the Review tab in the Excel ribbon. Click on Protect Sheet.

Hide formula in MS-Excel 1-5

  • Protect Sheet dialog box will appear. Type the password to protect the sheet. Once the sheet is protected, no one except the real user can unprotect it, make changes, and then click OK.

Protect Sheet dialog box 2-4

Note: By default, Protect worksheet and contents of the locked cells check box are selected. Also, Select locked and unlocked cell checkboxes to remain selected by default.

  • Retype the password, which will help to prevent a typographical error in the password from locking the spreadsheet forever. Confirm Password dialog box will appear. Click OK.

Confirm Password dialog box 2-5

  •  Once you protect the sheet, the formula of the active cell will not be shown up in the formula bar. Thus, the formulas in the cells are protected and cannot be seen or edited.

Hide formula in MS-Excel 2-6

  • Whenever you want to make changes to the protected sheet, the following message will be displayed.

Hide formula in MS-Excel 2-7

Note: The user without a password can click on the cells but cannot make changes to the content of the protected sheet. To make the changes, first, make the protected sheet unprotected.

How to Unprotect the Protected Sheet?
  • Select the Review tab in the Excel ribbon. Click on Unprotect Sheet option.

Hide formula in MS-Excel 2-8

  • Unprotect sheet dialog box will appear. Type in the password and then click Ok. The sheet will become unprotected.

Hide formula in MS-Excel 2-9

#2 – To hide the formula bar of an Excel workbook

The formula bar exists below the formatting toolbar or ribbon area. It is divided into two different parts:-

The left part is the Name box displaying the active cell number.

On the right, Excel displays the formula of the active cell.

Hide formula in MS-Excel 2-10

Steps to hide the formula bar:

  • Go to the File tab in the Excel ribbon. Select Options.

Select Options in MS-Excel 2-11

  • The Excel Options dialog box will appear. Select Advanced.

Select Advanced Option 2-12

  • On the right-hand side panel, scroll down to the Display section.

Display section 2-13

  • Unselect the Show formula bar checkbox and then click OK.

Show formula bar 2-13

Note: If you select the “Show formula bar” checkbox, Excel will display the formula bar; otherwise, it will not be displayed.

Hide formula in MS-Excel 2-14

How can the Hidden formulas be found?
  • Go to the Home tab in the Excel ribbon in the Editing section, select Find & Select, and then click the Formulas option.
  • Excel will highlight and select all the cells that have formulas.

Hide formula in MS-Excel 2-16

Things to Remember about the Hide Formula in Excel

  • This option helps you hide crucial and confidential formulas from others, thus protecting your documents.
  • As discussed above, there are two ways of hiding formulas. In the first method, we can hide and protect the formulas sheet-wise. In other words, in the same workbook, we can apply it to protect and hide the Excel formula in one sheet, whereas the second sheet remains unprotected.
  • In the protected worksheet, we can only see the data and put the cursor on the cells but cannot view the formulas and make changes to them.
  • The second way to hide the formula bar applies across any file opened in the MS Excel application. However, formulas remain hidden because of the aforementioned feature. The worksheets are not protected.

Recommended Articles

This has been a guide to Hiding Formula in Excel. Here we discuss how to use Hide Formula in Excel, practical examples, and a downloadable Excel template. You can also go through our other suggested articles-

  1. Unhide Columns in Excel
  2. TRIM Formula in Excel
  3. Excel Show Formula
  4. Write the Formula in Excel
ADVERTISEMENT
MICROSOFT POWER BI Course Bundle - 8 Courses in 1
34+ Hours of HD Videos
8 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
MS Excel & VBA for Data Science Course Bundle - 24 Courses in 1 | 10 Mock Tests
87+ Hours of HD Videos
24 Courses
10 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
All-in-One Financial Analyst Masters Training Program Bundle - 550+ Courses | 300+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.9
ADVERTISEMENT
All-in-One Data Science Bundle - 400+ Courses | 550+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.7
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • 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

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

EDUCBA

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

EDUCBA

Download Hide Formula Excel Template

Let’s Get Started

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

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

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

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

Forgot Password?

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

EDUCBA

Download Hide Formula Excel Template

EDUCBA

डाउनलोड Hide Formula 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

🚀 Extended Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW