{"id":5549,"date":"2023-04-12T15:42:52","date_gmt":"2023-04-12T15:42:52","guid":{"rendered":"https:\/\/www.bizinfograph.com\/resource\/?p=5549"},"modified":"2024-05-15T17:12:39","modified_gmt":"2024-05-15T17:12:39","slug":"how-to-hide-formulas-in-excel","status":"publish","type":"post","link":"https:\/\/www.bizinfograph.com\/resource\/how-to-hide-formulas-in-excel\/","title":{"rendered":"How to Hide Formulas in Excel?"},"content":{"rendered":"<p>Hide Formulas in Excel provides a practical solution for safeguarding sensitive information and improving the presentation of your spreadsheets. Whether you&#8217;re sharing financial models, reports, or budget templates, Hide Formulas in Excel allows you to conceal complex formulas while preserving the integrity of your data. Say goodbye to concerns about accidental formula edits or unauthorized access to proprietary calculations and hello to enhanced confidentiality. From protecting intellectual property to creating professional-looking reports, Hide Formulas in Excel empowers users to customize their spreadsheets with confidence. Take control of your data privacy and presentation needs by leveraging the flexibility and convenience of Hide Formulas in Excel. With just a few clicks, you can hide formulas without affecting their functionality, ensuring that your Excel files are both secure and visually appealing. Embrace the power of Hide Formulas in Excel and elevate your spreadsheet management to new heights of efficiency and professionalism.<\/p>\n<p><strong>This Tutorial Covers:<\/strong><\/p>\n<ol>\n<li><a href=\"#how-to-hide-every-formula-in-excel\"><strong>How to hide\u00a0every formula in Excel<\/strong><\/a><\/li>\n<li><a href=\"#how-to-hide-formulas-in-excel-only-(and-keep-the-rest-of-the-cells-editable)\"><strong>How to hide formulas in Excel only (And keep the rest of the cells editable)<\/strong><\/a>\n<ul>\n<li>Step 1: Remove the lock Property from all the cells<\/li>\n<li>Step 2: Only enable the Locked and Hidden feature for cells with formulas<\/li>\n<li>Step 3: Protecting the worksheet<\/li>\n<\/ul>\n<\/li>\n<li><a href=\"#how-to-hide-formulas-without-encrypting-the-worksheet\"><strong>How to hide formulas without encrypting the worksheet<\/strong><\/a><\/li>\n<li><a href=\"#how-to-unhide-formulas-and-erase-protection-in-excel\"><strong>How to unhide formulas and erase protection in Excel<\/strong><\/a><\/li>\n<\/ol>\n<h2><strong id=\"how-to-hide-every-formula-in-excel\">1. How to hide every formula in Excel?<\/strong><\/h2>\n<p>When you have a formula in a cell, a person can see the formula in two ways:<\/p>\n<ol>\n<li>By selecting the columns with a double-click and switching to edit mode<\/li>\n<li>By choosing the cell and viewing the formula in the formula column<\/li>\n<\/ol>\n<p>Users won&#8217;t be able to modify the cell or see the formula in the formula bar when you hide the formulas (as we&#8217;ll see how to do in a moment).<\/p>\n<p>Assume you have the data collection depicted below, with the formula in column C.<\/p>\n<p><img fetchpriority=\"high\" decoding=\"async\" class=\"aligncenter wp-image-5552 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-1.png\" alt=\"Hide Formulas in Excel\" width=\"641\" height=\"188\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-1.png 641w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-1-300x88.png 300w\" sizes=\"(max-width: 641px) 100vw, 641px\" \/><\/p>\n<p><strong>How to hide excel formula is shown below:<\/strong><\/p>\n<p><strong>Step 1:<\/strong> Choose the cells in column C\u00a0that contain the formula that you want to hide.<\/p>\n<p><img decoding=\"async\" class=\"aligncenter wp-image-5553 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-2.png\" alt=\"Hide Formulas in Excel\" width=\"639\" height=\"198\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-2.png 639w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-2-300x93.png 300w\" sizes=\"(max-width: 639px) 100vw, 639px\" \/><\/p>\n<p><strong>Step 2:<\/strong> Go to the &#8220;Home&#8221; option. Click on the dialog box launcher in the &#8220;Number&#8221; group (it&#8217;s the tiny, tilted arrow icon in the bottom right of the group).<\/p>\n<p><img decoding=\"async\" class=\"aligncenter wp-image-5554 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-3.png\" alt=\"Hide Formulas in Excel\" width=\"639\" height=\"118\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-3.png 639w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-3-300x55.png 300w\" sizes=\"(max-width: 639px) 100vw, 639px\" \/><\/p>\n<p><strong>Step 3:<\/strong> Click the \u201cProtection\u201d tab in the \u201cFormat Cells\u201d dialog window that appears. Check the &#8220;Hidden&#8221;\u00a0option. Select OK.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-5555 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-4.png\" alt=\"Hide Formulas in Excel\" width=\"443\" height=\"403\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-4.png 443w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-4-300x273.png 300w\" sizes=\"(max-width: 443px) 100vw, 443px\" \/><\/p>\n<p><strong>Step 4:<\/strong> On the ribbon, select the &#8220;Review&#8221; tab. Select &#8220;Protect Sheet&#8221; from the &#8220;Protect&#8221; category by clicking it.<img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-5556 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-5.png\" alt=\"Hide Formulas in Excel\" width=\"638\" height=\"89\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-5.png 638w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-5-300x42.png 300w\" sizes=\"(max-width: 638px) 100vw, 638px\" \/><\/p>\n<p><strong>Step 5:<\/strong> Enter the password that would be required to unlock the worksheet in the &#8220;Protection&#8221; dialog window (if you don&#8217;t want to use a password, leave this field empty). Select OK.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-5557 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-6.png\" alt=\"Hide Formulas in Excel\" width=\"242\" height=\"362\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-6.png 242w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-6-201x300.png 201w\" sizes=\"(max-width: 242px) 100vw, 242px\" \/><\/p>\n<p>The aforementioned actions would protect the entire worksheet so that if you clicked on a cell with a value, the value would appear in the formula bar; however, if you clicked on a cell with a formula, no formula would appear in the formula bar.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-5558 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-7.png\" alt=\"Hide Formulas in Excel\" width=\"519\" height=\"194\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-7.png 519w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-7-300x112.png 300w\" sizes=\"(max-width: 519px) 100vw, 519px\" \/><\/p>\n<p>Additionally, since the worksheet is secured, you would be unable to double-click a column to enter edit mode (so the formula is hidden that way as well).<\/p>\n<p>While this technique is effective, you should be aware that protected Excel sheets and cells are simple for the user to unlock.<\/p>\n<p>Your protected workbooks can be readily broken into by any tech-savvy user (a simple Google search will give them multiple ways to break the protected worksheet). It&#8217;s not easy, but it&#8217;s not too difficult either.<\/p>\n<p>However, if you&#8217;re dealing with non-technical users, adding a password ought to suffice.<\/p>\n<h2><strong id=\"how-to-hide-formulas-in-excel-only-(and-keep-the-rest-of-the-cells-editable)\">2. How to hide formulas in Excel only (And keep the rest of the cells editable)?<\/strong><\/h2>\n<p>I explained how to protect the entire worksheet in the method above, including the cells without a formula.<\/p>\n<p>What happens, though, if you don&#8217;t want to secure the full worksheet? What if you only want to safeguard the columns that contain formulas while keeping the user from seeing those formulas?<\/p>\n<p>This might be the case if you want users to enter data but prevent them from seeing or editing the formula (like in a data entry form).<\/p>\n<p>This is also a simple process.<\/p>\n<p>In this method, we would only choose the cells that contain the formulas and protect those cells, as opposed to the prior method where we protected all the worksheet&#8217;s cells.<\/p>\n<p>The user could continue to modify the remaining portions of the worksheet.<\/p>\n<p>Consider the following data set, where you only want to hide and protect the formulas in column C. (which has formulas).<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-5559 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-8.png\" alt=\"Hide Formulas in Excel\" width=\"638\" height=\"188\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-8.png 638w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-8-300x88.png 300w\" sizes=\"(max-width: 638px) 100vw, 638px\" \/><\/p>\n<p>A cell cannot be protected unless both the protection from the ribbon and the &#8220;Locked&#8221; property are set. A cell can only genuinely become locked (i.e., uneditable) when both apply.<\/p>\n<p>Additionally, this means that even after you secure the worksheet, some cells whose lock property has been disabled may still be edited.<\/p>\n<p>The locked property will be turned off for all cells other than those with formulas using this concept.<\/p>\n<p>Let&#8217;s look at the procedure.<\/p>\n<ul>\n<li>\n<h3><strong>Step 1: Remove the lock Property from all the cells:<\/strong><\/h3>\n<\/li>\n<\/ul>\n<p>Therefore, in order to prevent these cells from being secured, we must first turn off the Locked property for each cell.<\/p>\n<p><strong>The steps to disable the lock property from all the cells are described below:<\/strong><\/p>\n<p><strong>Step 1:<\/strong> Pick every column on the worksheet (you can do this by clicking on the gray triangle at the top left part of the sheet).<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-5560 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-9.png\" alt=\"Hide Formulas in Excel\" width=\"443\" height=\"228\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-9.png 443w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-9-300x154.png 300w\" sizes=\"(max-width: 443px) 100vw, 443px\" \/><\/p>\n<p><strong>Step 2:<\/strong> Go to the &#8220;Home&#8221; option. Click on the dialog box launcher in the &#8220;Number&#8221; group (it&#8217;s the tiny, tilted arrow icon in the bottom right of the group).<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-5561 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-10.png\" alt=\"Hide Formulas in Excel\" width=\"639\" height=\"118\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-10.png 639w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-10-300x55.png 300w\" sizes=\"(max-width: 639px) 100vw, 639px\" \/><\/p>\n<p><strong>Step 3:<\/strong> The &#8220;Protection&#8221; option can be found in the Format cells dialog window. Select &#8220;Locked&#8221; and uncheck it. Select the &#8220;OK&#8221; button.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-5562 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-11.png\" alt=\"Hide Formulas in Excel\" width=\"416\" height=\"380\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-11.png 416w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-11-300x274.png 300w\" sizes=\"(max-width: 416px) 100vw, 416px\" \/><\/p>\n<p>The locked property has been removed from every cell in the worksheet using the aforementioned methods.<\/p>\n<p>Now, even if I go ahead and secure the sheet using the ribbon&#8217;s Review &gt;&gt; Protect Sheet option, the cells would still be partially editable.<\/p>\n<ul>\n<li>\n<h3><strong>Step 2: Only enable the Locked and Hidden feature for cells with formulas:<\/strong><\/h3>\n<\/li>\n<\/ul>\n<p>I now need to locate the cells that contain the formula and lock them in to hide the formula from all the worksheet&#8217;s cells.<\/p>\n<p>I would also make careful to hide the formula from the formula bar while locking these cells.<\/p>\n<p><strong>The steps to hide formulas are as follows:<\/strong><\/p>\n<p><strong>Step 1:<\/strong> Pick every column on the worksheet (you can do this by clicking on the gray triangle at the top left part of the sheet).<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-5563 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-12.png\" alt=\"Hide Formulas in Excel\" width=\"443\" height=\"228\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-12.png 443w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-12-300x154.png 300w\" sizes=\"(max-width: 443px) 100vw, 443px\" \/><\/p>\n<p><strong>Step 2:<\/strong> On the &#8220;Home&#8221; option, click. Click the &#8220;Find &amp; Select&#8221; link under the &#8220;Editing&#8221; category. Select &#8220;Go-To Special&#8221; from the menu.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-5564 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-13.png\" alt=\"Hide Formulas in Excel\" width=\"638\" height=\"186\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-13.png 638w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-13-300x87.png 300w\" sizes=\"(max-width: 638px) 100vw, 638px\" \/><\/p>\n<p><strong>Step 3:<\/strong> Click the &#8220;Formulas&#8221; choice in the &#8220;Go To Special&#8221; dialog box. This will pick every cell that contains a formula.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-5565 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-14.png\" alt=\"Hide Formulas in Excel\" width=\"255\" height=\"277\" \/><\/p>\n<p><strong>Step 4:<\/strong> Hold the Control key while pressing the 1 key while selecting the cells that contain formulas (or the Command key and the 1 key if using Mac). The &#8220;Format Cells&#8221; dialog window will then be displayed. Select &#8220;Protection&#8221; tab. Ensure that the &#8220;Locked&#8221; and &#8220;Hidden&#8221; checkboxes are selected. Select the &#8220;OK&#8221; button.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-5566 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-15.png\" alt=\"Hide Formulas in Excel\" width=\"421\" height=\"383\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-15.png 421w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-15-300x273.png 300w\" sizes=\"(max-width: 421px) 100vw, 421px\" \/><\/p>\n<ul>\n<li>\n<h3><strong>Protecting the worksheet:<\/strong><\/h3>\n<\/li>\n<\/ul>\n<p>The Locked property has been disabled throughout the procedure for all cells with the exception of those that contain formulas.<\/p>\n<p>As a result, only the cells with formulas would be protected if I protected the complete worksheet (as you need the Locked property to be enabled to truly lock a cell).<\/p>\n<p><strong>How to <\/strong><strong>Protect excel sheet is shown below:<\/strong><\/p>\n<p><strong>Step 1:<\/strong> On the ribbon, select the &#8220;Review&#8221; tab. Select &#8220;Protect Sheet&#8221; from the &#8220;Protect&#8221; category by clicking it.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-5567 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-16.png\" alt=\"Hide Formulas in Excel\" width=\"639\" height=\"90\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-16.png 639w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-16-300x42.png 300w\" sizes=\"(max-width: 639px) 100vw, 639px\" \/><\/p>\n<p><strong>Step 2:<\/strong> Enter the password that would be required to unlock the worksheet in the &#8220;Protection&#8221; dialog window (if you don&#8217;t want to use a password, leave this field<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-5568 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-17.png\" alt=\"Hide Formulas in Excel\" width=\"242\" height=\"362\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-17.png 242w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-17-201x300.png 201w\" sizes=\"(max-width: 242px) 100vw, 242px\" \/><\/p>\n<p>The aforementioned procedures would secure only those cells that contained a formula while also hiding the formula from users.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-5569 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-18.png\" alt=\"Hide Formulas in Excel\" width=\"520\" height=\"193\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-18.png 520w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-18-300x111.png 300w\" sizes=\"(max-width: 520px) 100vw, 520px\" \/><\/p>\n<p>The formula in the formula bar won&#8217;t be visible to users, and they won&#8217;t be able to double-click to enter modify mode. On the other hand, you can edit other cells.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-5570 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-19.png\" alt=\"Hide Formulas in Excel\" width=\"465\" height=\"225\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-19.png 465w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-19-300x145.png 300w\" sizes=\"(max-width: 465px) 100vw, 465px\" \/><\/p>\n<h2><strong id=\"how-to-hide-formulas-without-encrypting-the-worksheet\">3. How to hide formulas without encrypting the worksheet?<\/strong><\/h2>\n<p>The most effective method for hiding formulas in Excel from users without protecting the document is to use VBA code.<\/p>\n<p><strong>The steps to hide formulas without encrypting the worksheet are described below:<\/strong><\/p>\n<p><strong>Step 1:<\/strong> Right-click on the worksheet name to start. Choose &#8220;View\u00a0Code&#8221; from the menu.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-5571 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-20.png\" alt=\"Hide Formulas in Excel\" width=\"232\" height=\"276\" \/><\/p>\n<p><strong>Step 2:<\/strong> The aforementioned instruction will launch a new VBA module. Add the following code to the empty VBA module as well:<\/p>\n<p><strong>Dim zDic As New Dictionary<\/strong><\/p>\n<p><strong>Private Sub Worksheet_SelectionChange(ByVal Target As Range)<\/strong><\/p>\n<p><strong>Dim zCell As Range<\/strong><\/p>\n<p><strong>Dim zRg As Range<\/strong><\/p>\n<p><strong>Set zRg = Range(&#8220;C2:C7&#8221;)<\/strong><\/p>\n<p><strong>If zDic.Count &lt;&gt; zRg.Count Then<\/strong><\/p>\n<p><strong>For Each zCell In zRg<\/strong><\/p>\n<p><strong>zDic.Add zCell.Address, zCell.FormulaR1C1<\/strong><\/p>\n<p><strong>Next<\/strong><\/p>\n<p><strong>End If<\/strong><\/p>\n<p><strong>If (Target.Count = 1) And (Not Application.Intersect(zRg, Target) Is Nothing) And (Target.HasFormula) Then<\/strong><\/p>\n<p><strong>With Target<\/strong><\/p>\n<p><strong>.Value = .Value<\/strong><\/p>\n<p><strong>End With<\/strong><\/p>\n<p><strong>Else<\/strong><\/p>\n<p><strong>For Each zCell In zRg<\/strong><\/p>\n<p><strong>zCell.Formula = zDic.Item(zCell.Address)<\/strong><\/p>\n<p><strong>Next<\/strong><\/p>\n<p><strong>End If<\/strong><\/p>\n<p><strong>End Sub<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-5572 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-21.png\" alt=\"Hide Formulas in Excel\" width=\"639\" height=\"273\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-21.png 639w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-21-300x128.png 300w\" sizes=\"(max-width: 639px) 100vw, 639px\" \/><\/p>\n<p><strong>Step 3:<\/strong> In addition, select &#8220;References&#8221; from the &#8220;Tools&#8221; menu.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-5573 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-22.png\" alt=\"Hide Formulas in Excel\" width=\"491\" height=\"208\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-22.png 491w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-22-300x127.png 300w\" sizes=\"(max-width: 491px) 100vw, 491px\" \/><\/p>\n<p><strong>Step 4:<\/strong> The &#8220;References &#8211; VBAProject&#8221; dialogue box will now display. Next, select the &#8220;Available References&#8221; tab and select the &#8220;Microsoft Scripting Runtime&#8221; checkbox. Next, select OK.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-5576 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-23.png\" alt=\"Hide Formulas in Excel\" width=\"384\" height=\"307\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-23.png 384w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-23-300x240.png 300w\" sizes=\"(max-width: 384px) 100vw, 384px\" \/><\/p>\n<p><strong>Step 5:<\/strong> Press Alt + Q to exit the VBA module after that. Finally, select cell C2. As can be seen, the formula bar only displays the value and not the formula.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-5574 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-24.png\" alt=\"Hide Formulas in Excel\" width=\"438\" height=\"198\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-24.png 438w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-24-300x136.png 300w\" sizes=\"(max-width: 438px) 100vw, 438px\" \/><\/p>\n<h2><strong id=\"how-to-unhide-formulas-and-erase-protection-in-excel\">4. How to unhide formulas and erase protection in Excel?<\/strong><\/h2>\n<p>The steps listed below can be used to make the previously hidden formulas appear in the formula bar once again:<\/p>\n<p><strong>Step 1:<\/strong> On the ribbon, select the &#8220;Review&#8221; tab. Select &#8220;Unprotect Sheet&#8221; from the &#8220;Protect&#8221; category by clicking it.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-5575 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-25.png\" alt=\"Hide Formulas in Excel\" width=\"641\" height=\"93\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-25.png 641w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Hide-Formulas-25-300x44.png 300w\" sizes=\"(max-width: 641px) 100vw, 641px\" \/><\/p>\n<p><strong>Note:<\/strong> After removing the worksheet&#8217;s protection, you might want to uncheck the \u201cHidden\u201d checkbox if you chose to hide the formulas before protecting the worksheet. Since the formulas appear in the formula bar as soon as you remove the worksheet protection, this won&#8217;t have an instant impact. To prevent the hidden attribute from being selected for those cells in the future when you want to protect the same sheet but still allow users to see the formulas, select the formula-containing cells, press Ctrl + 1, open the Format Cells dialog, select the \u201cProtection\u201d tab, and uncheck the \u201cHidden\u201d box.<\/p>\n<p>This is how you can hide and lock formulas in Excel with protecting the worksheet and without protecting the worksheet. I hope you found this tutorial useful.<\/p>\n<h2>Application of Hide Formulas in Excel<\/h2>\n<ul>\n<li><strong>Confidentiality Protection<\/strong>: Conceal sensitive formulas in Excel to protect proprietary calculations and intellectual property from unauthorized access.<\/li>\n<li><strong>Presentation Enhancement<\/strong>: Improve the visual appeal of spreadsheets by hiding formulas, creating cleaner and more professional-looking reports or dashboards.<\/li>\n<li><strong>Data Privacy Compliance<\/strong>: Ensure compliance with data privacy regulations by hiding formulas containing personally identifiable information or sensitive data.<\/li>\n<li><strong>Preventing Errors<\/strong>: Minimize the risk of accidental formula edits or deletions by hiding complex formulas, reducing the likelihood of spreadsheet errors.<\/li>\n<li><strong>Focus on Outputs<\/strong>: Hide formulas to focus on the output data rather than the underlying calculations, simplifying data interpretation and analysis for end-users.<\/li>\n<li><strong>Safeguarding Formulas<\/strong>: Conceal formulas to prevent users from tampering with critical calculations, preserving data integrity and accuracy in Excel files.<\/li>\n<\/ul>\n<p><strong>For ready-to-use Dashboard Templates:<\/strong><\/p>\n<ol>\n<li><a href=\"https:\/\/www.bizinfograph.com\/dashboard-templates#Finance\" target=\"_blank\" rel=\"noopener\">Financial Dashboards<\/a><\/li>\n<li><a href=\"https:\/\/www.bizinfograph.com\/dashboard-templates#Sales\" target=\"_blank\" rel=\"noopener\">Sales Dashboards<\/a><\/li>\n<li><a href=\"https:\/\/www.bizinfograph.com\/dashboard-templates#Human-Resource\" target=\"_blank\" rel=\"noopener\">HR Dashboards<\/a><\/li>\n<li><a href=\"https:\/\/www.executiveknowledge.org\/dashboard-templates\/35\" target=\"_blank\" rel=\"noopener\">Data Visualization Charts<\/a><\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"<p>Hide Formulas in Excel provides a practical solution for safeguarding sensitive information and improving the presentation of your spreadsheets. Whether you&#8217;re sharing financial models, reports, or budget templates, Hide Formulas in Excel allows you to conceal complex formulas while preserving the integrity of your data. Say goodbye to concerns about accidental formula edits or unauthorized &hellip;<\/p>\n<p class=\"read-more\"> <a class=\"\" href=\"https:\/\/www.bizinfograph.com\/resource\/how-to-hide-formulas-in-excel\/\"> <span class=\"screen-reader-text\">How to Hide Formulas in Excel?<\/span> Read More &raquo;<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"default","ast-global-header-display":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"default","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","footnotes":""},"categories":[674],"tags":[675],"class_list":["post-5549","post","type-post","status-publish","format-standard","hentry","category-hide-formulas-in-excel","tag-hide-formulas-in-excel"],"_links":{"self":[{"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/posts\/5549","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/comments?post=5549"}],"version-history":[{"count":6,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/posts\/5549\/revisions"}],"predecessor-version":[{"id":9232,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/posts\/5549\/revisions\/9232"}],"wp:attachment":[{"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/media?parent=5549"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/categories?post=5549"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/tags?post=5549"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}