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 2022
        • Photoshop Elements 2019
        • Photoshop Elements 2018
        • Photoshop Elements 15
        • Photoshop Elements 14
        • Photoshop Elements 13
        • Photoshop Elements 12
        • Photoshop Elements 11
        • Photoshop Elements 10
        • Photoshop Elements 9
        • Photoshop Elements 8
    • Microsoft
      • Access
        • Access 2021 Beginners
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Data Analytics in Excel
        • Excel 2021 Advanced
        • Excel 2021 Intermediate
        • Excel 2021 Beginners
        • PivotTables for Beginners
        • Excel Dashboards
        • 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 2021
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • Power Automate
        • Introduction to Power Automate
      • Power BI
        • Power BI
        • Power BI Intermediate
      • PowerPoint
        • PowerPoint 2021
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2021 Beginners
        • Project for the Web
        • 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 Online
        • SharePoint Foundation 2013
        • SharePoint Server 2013
        • SharePoint Foundation 2010
      • Teams
        • Microsoft Teams (2023 Update)
      • VBA
        • Macros and VBA for Beginners
        • VBA for Excel
        • VBA Intermediate Training
      • Visio
        • Microsoft Visio 2019
        • Visio 2016
        • Visio 2013
        • Microsoft Visio 2010
      • Windows
        • Windows 11 (2023 Update)
        • Windows 10 (2020 Update)
        • Windows 10
        • Windows 8
        • Windows 7
        • Windows Vista
      • Word
        • Word 2021
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Desktop Pro 2022
        • QuickBooks Pro 2021
        • QuickBooks Online Advanced
        • 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
        • AngularJS Crash Course
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Bootstrap
        • Bootstrap Framework
      • Html/CSS
        • HTML/CSS Crash Course
        • HTML5 Essentials
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
      • Java
        • Java for Beginners
      • JavaScript
        • JavaScript for Beginners
        • jQuery Crash Course
      • MySql
        • MySQL for Beginners
      • PHP
        • PHP for Beginners
        • Advanced PHP Programming
      • XML
        • XML Crash Course
    • Data Analysis
      • Financial Modeling
        • Financial Risk Management
        • Financial Forecasting and Modeling
      • Data Analytics
        • Introduction to Analytics and Artificial Intelligence
        • Data Analytics in Excel
      • Alteryx
        • Alteryx Advanced
        • Introduction to Alteryx
      • Power BI
        • Power BI Intermediate
        • Power BI
      • Qlik Sense
        • Qlik Sense Advanced
        • Qlik Sense
      • R Programming
        • R Programming
      • Tableau
        • Tableau Desktop Advanced
        • Tableau Desktop
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
    • Work Productivity
      • Google Sheets
        • Google Sheets for Beginners
      • Confluence
        • Introduction to Confluence
      • Monday
        • Getting Started in Monday.com
      • Asana
        • Asana for Employees and Managers
        • Introduction to Asana
      • Jira
        • Getting Started in Jira
  • For Business
  • About Us
    • Testimonials
    • Contact Us
    • FAQ
    • Membership
    • About Us
  • Pricing
  • Free Resources
  • Sign In
  • Get Started
Home > Microsoft Excel > How to Hide Formulas in Excel? 2 Different Approaches

How to Hide Formulas in Excel? 2 Different Approaches

(Note: This guide on how to hide formulas in Excel is suitable for all Excel versions including Office 365)

Excel deals with large amounts of data in which the values operate using a variety of functions and formulas. When you enter a formula in Excel, the formula that pertains to the cell always shows up on the formula bar. 

This might be a great way to know what formula is used to obtain the particular value. But in some cases, the appearance of formulas might be a little irrelevant. So, what do you do when you don’t want the formula to appear in the formula bar in Excel?

In this article, I will show you how to hide formulas in Excel using 2 different approaches, without hiding the formula bar completely.

You’ll Learn:

  • Why Hide Formulas in Excel?
  • How to Hide Formulas in Excel?
    • Hide the Worksheet by Making the Cells Non-Editable
    • Hide Only the Cells with Formulas and Make Other Cells Editable
download wrap text worksheet
How to Hide Formulas in Excel – Workbook

Watch this Video on How to Hide Formulas in Excel

Related Reads:

How to Calculate Percentile in Excel? 3 Useful Formulas

The FORMULATEXT Excel Function – 2 Best Examples

How to Round Up in Excel(and Round Down)?

Why Hide Formulas in Excel?

Before we learn how to hide formulas in Excel, let us see how a formula shows up in Excel and why we need to hide them with an example.

When you enter a formula in any cell, they show up in the formula bar above the column. The value which populates the cell is a result of the formula or function consisting of variables, other cells, and operands.

Formula shows up in Formula Bar
Formula shows up in Formula Bar

Though the formula may be a great way to understand how we have obtained the value, in some cases, they do feel a bit irrelevant. 

Consider that you have to share the Excel sheet with a group of unrelated people. In such cases, it is better to keep the formulas and functions hidden, thereby maintaining confidentiality. Also, this helps in protecting the file from being edited and the user will be able to only view the data.

In addition, consider the above example where the formula =RAND() is used to find the random value. When showing it as an example or while taking a screenshot, the presence of the formula cell might not be necessary. 

In such cases, it would be better to hide only the formula without hiding the formula bar.

How to Hide Formulas in Excel?

Hide the Worksheet by Making the Cells Non-Editable

This is one method to hide the formula showing up in the formula bar. However, using this method to hide the formula bar also protects the sheet and makes the cell uneditable.

To hide the formulas and to make the cells uneditable, first, select the cells from which you don’t want the formula to show up. You can select a single cell, an adjacent group of cells, non-adjacent cells, and even the whole sheet.

Select the cells
Select the cells

After selecting the cells, right-click on the selected cells and click on Format Cells.

Click on Format Cells
Click on Format Cells

Another way to arrive at the Format Cells dialog box is by navigating to Home. Under the Number section, click on Number Format which appears as a small dropdown arrow.

Extend the Number Format
Extend the Number Format

This opens up a Format Cells dialog box. Go to the Protection tab and check the checkbox for Hidden. Click OK.

Navigate to Protection
Navigate to Protection

Once you have hidden the cells, navigate to Review. Under the Protect section, click on Protect Sheet.

Click on Protect Sheet
Click on Protect Sheet

Once you click on the Protect sheet, a dialog box appears. If the sheet is confidential to you, enter a password. You can unprotect the sheet only by using this password. In this case, let us leave the password textbox blank.

Make sure the checkbox for Protect worksheet and contents of locked cells is checked. The below two checkboxes for select locked cells and select unlocked cells are checked. This only lets the user click on them, but they are detained from doing any operation on the cells. Click OK.

Check the checkboxes
Check the checkboxes

This hides the formulas from showing up on the formula bar. When you click on any of the cells, you cannot see the formulas. In this case, when I previously clicked on the cells the formula =RAND() showed up in the formula bar. Now, after protecting the sheet the formulas are hidden for the selected cells.

How to hide formulas in Excel by making the whole sheet non editable
How to hide formulas in Excel by making the whole sheet non editable

In addition to hiding the formulas, this method also protects the sheet from being edited or altered. 

Suggested Reads:

How to Use the PROPER Function in Excel? 3 Easy Examples

Excel DATEVALUE – A Step-by-Step Guide

How to Use SUMPRODUCT Function in Excel? 5 Easy Examples

Hide Only the Cells with Formulas and Make Other Cells Editable

In the above method, we saw how to hide the formulas from showing up in the formula bar when a cell is selected, making the selected cells uneditable.

However, in some cases, the user might have to input some data. For example, any data entry sheet to be circulated should be editable by other users. Then the method of protecting the entire worksheet by making them uneditable won’t be useful this time. 

So, you can use the below-mentioned method to only hide the cells with formulas and keep the rest of the cells editable. This method is especially useful when there is a large amount of data and you cannot identify where the formulas are and which cells to hide or unhide. 

Consider an example where the sheet contains the profit and losses made by salesmen and the clients will rate them. Here, some cells require editing when different clients rate a salesman.

To make this method work, let us first disable the lock property of all the cells in the sheet. 

Select the entire sheet either by clicking on the gray arrow on the top-left corner of the column or by using the keyboard shortcut Ctrl + A.

Select all the cells
Select all the cells

Now to unlock the cell, navigate to Home. Under Number, click on Number Format which opens a dialog box. 

Under the Protection tab, uncheck the checkbox for Locked. And, click OK.

Navigate to Protection and disable Locked
Navigate to Protection and disable Locked

This disables the lock property of the cells and makes them editable. Once the cells are unlocked, the cell containing the formulas throws up a warning message.

A warning shows up
A warning shows up

Now then, let us see how to protect only the cells which have formulas. 

Again select all the cells in the sheet by using the gray triangle on the top-left of the sheet or by pressing Ctrl+A.

After you have selected the whole sheet, we only need to identify the cells which only have the formulas. To do that, navigate to Home. Click on the dropdown from Find & Select and click on Go To Special.

Go To Special
Go To Special

This opens up the Go To Special dialog box. In the dialog box, select Formulas and make sure all the checkboxes under the Formulas header are checked. Click OK. This step helps Excel identify the cells with formulas and selects them.

Select Formulas in Go To dialog box
Select Formulas in Go To dialog box

After the formulas are selected, let us now lock and hide the cells. You can either navigate to Home and click on Number and Number Format, or right-click on the selected cells and click on Format Cells. You can also use the keyboard shortcut key Ctrl+1.

In the Format Cells dialog box, under Protection, check the checkboxes for both Locked and Hidden. Click OK.

Go to Protection and enable Locked
Go to Protection and enable Locked

Now, you can see the warning symbols disappear on the cells with formulas.

After this, navigate to Review. Under the Protect section, click on Protect Sheet. Enter the password to protect the sheet and check the checkboxes for Protect worksheet and content for locked cells. Select the locked and unlocked cells, and click OK.

Click on Protect Sheet
Click on Protect Sheet

This locks only the cells with formulas, whereas the rest of the cells remain editable. If you click on any of the cells containing the formula, the formula does not appear on the formula bar, and also you cannot alter them. Whereas you can edit any of the other cells which do not contain a formula.

How to Hide Formulas in Excel with other cells editable
How to Hide Formulas in Excel with other cells editable

Also Read:

How to Fix the #Div/0 Error in Excel? 2 Easy Methods

How to Use ISTEXT in Excel? With 3 Different Methods

Short Date Format in Excel – 3 Different Methods

FAQs

Is there any way to hide without protecting the sheet?

No, there is no way to hide formulas in Excel without protecting the sheet. However, you can use VBA codes, but they can sometimes be inconsistent and lead to other errors.

What to do when you lose the password for a protected file?

There is no way in Excel to get back the password and unprotect the sheet using conventional methods. However, there are a lot of hacks and workarounds on the internet which might help you unprotect the sheet.

How can you remove only formulas and keep values in Excel?

You can copy the cells which contain the values, then paste them onto another sheet (or in a different place on the same sheet) by pasting them as Values. 

Closing Thoughts

In this article, we saw how to hide formulas in Excel without hiding the formula bar. You can either hide the formulas by making the whole sheet protected and non-editable, or you can just pinpoint and protect the cells containing the formulas by keeping the other cells editable. Both are useful in their ways, you can use the method which suits your purpose better. 

Please visit our free resources center to know more about the date and time functions. 

We have high-quality Excel guides and you will be really excited to know how handy and useful these functions are.

Simon Sez IT has been teaching Excel for over ten years. For a low monthly fee, you can access 130+ IT training courses. Click here for advanced Excel courses with in-depth training modules.

Simon Calder

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!

Most Popular Posts

  • Kanban vs Scrum: Project Management Methodologies and Their Differences [2022]
  • How to Insert a Checkbox in Excel? 3 EASY Examples
  • How to Autofit Excel Cells? 3 Best Methods
  • XLOOKUP Google Sheets – 4 Best Alternatives!
  • Dashboards in Excel Using Pivot Tables, Pivot Charts and Slicers
  • Free Microsoft Project Training Course
  • Free Microsoft Access Tutorial for Beginners (3.5 Hours Video)
  • How to Use Blending Mode With Layers in Adobe Photoshop Elements 15

Similar Posts

How to Count Cells with Text in Excel? 3 Different Use Cases

An Introduction to Asana Templates – 10 Essential Categories

How to Use Layers in Photoshop Elements 2019 – Part 3 – Fill Layers

How to Use Drawing Selection Tools in Photoshop Elements 15

How to Bulk Import Photos Into Adobe Photoshop Elements 2019

How to Use the LOOKUP Function with CHOOSE in Excel 2016

Course Categories

  • Adobe
  • Data Analysis
  • QuickBooks
  • Microsoft
  • Web Development
  • Work Productivity

About Us

  • About Us
  • Free Resources
  • Affiliates
  • Become an Instructor

Products

  • Pricing and Plans
  • Business Pricing
  • Government Discounts
  • Non-Profit Discounts

Support

  • FAQ’s
  • Contact Us
  • DVD support

Connect

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