How to Hide Formulas Without Protecting the Sheet in Excel: 5 Handy Methods

If you want to hide formulas in Excel without protecting sheets, here are the most convenient approaches to do that. 

When you apply a formula in a cell in Excel, it is usually visible to the users once they select that particular cell. If you want to protect it from getting deleted or altered by other users of that sheet, you can protect or lock the whole sheet. 

However, this doesn’t stop the users from viewing the formula. Also, some shared sheets can’t be protected as it requires input from them in some sections of that sheet. 

So, what you need here is to hide the formula in Excel without protecting the sheet. Thus, you can make sure no one else can see the formula while being able to edit the sheet when necessary.

Why You May Want to Hide Formula in Excel Without Protecting Sheet

Confidentiality

The most common reason behind hiding an Excel formula is to keep it a secret. Often, we need to use sensitive or proprietary formulas in a shared Excel spreadsheet. By hiding the formulas, you can maintain confidentiality and prevent others from viewing or copying them.

Data Integrity

Any visible formula has the risk of someone modifying them both intentionally and unintentionally. It’ll lead to errors in the calculations. You may want to ensure the integrity of your spreadsheet by hiding the formula.

Professional Look

You also need to hide formulas when you’re going to use the Excel sheet in presentations or business reports. This helps you offer a cleaner and more professional look to your audience with only the final results. Thus, they will not get distracted by unnecessary formulas and see only the information they need.

Clutter-Free Experience

Hiding formulas reduces the clutter and complexity of the Excel spreadsheet and thus, improves the user experience.

Users not familiar with Excel will find it easier to work with a spreadsheet with hidden formulas as it only shows the relevant data and results. Visible formulas can confuse them and hamper their productivity.

There are very few methods to hide a formula in an Excel worksheet or workbook without protecting the spreadsheet. Find all those methods explained in a step-by-step manner below:

Convert Formulas to Values to Hide Formula

A rudimentary way to prevent someone from seeing the actual formula you used to derive a set of values is converting the resulting data into values.

Then, delete the old column that contained the formulas. This way, no one will know which formula you used.

However, don’t forget to create a backup copy of the original file that contains the formula column. Find below the steps you should follow:

1. Select the entire column of values for which you need to hide the formulas.
2. Now, press Ctrl + C keys together to copy the column of data.
3. Highlight the first cell of the adjacent blank column and press Ctrl + Alt + V.
4. The Paste Special dialog box will show up.
5. There, select the Values option under the Paste menu and click OK.

6. Excel will paste the values from the source column.

7. Right-click the source column and click Delete on the context menu that appears.

Disable the Formula Bar

Another effortless way to hide the formulas of an Excel spreadsheet without setting up protection is by hiding the Formula Bar. Find below the steps you must follow:

1. Go to the worksheet where you need to hide the formulas.
2. Click the File tab on the Excel ribbon.
3. On the left-side navigation pane that pops up, click Options.

4. You should now see the Excel Options dialog box.
5. There, click the Advanced category on the left side.
6. Now, scroll down the right-side menu until you locate the Display options.
7. In the Display options section, uncheck the checkbox for the Show formula bar.
8. Click OK to save the changes and close the Excel Options dialog box.

Now, the recipient won’t be able to view the formulas in calculated fields or cells. However, if they double-click any cell containing a formula, they can see a part of the formula. The visibility of the formula in this way will depend on the length of the formula and the cell.

Use a VBA Script to Replace Formulas With Results

You can use the following VBA script to hide the formulas in an Excel sheet without locking it. This is an irreversible process so make a backup of the original Excel file. Let’s get started with the steps outlined below:

1. Go to the worksheet that needs hiding of the cell formulas.
2. Hit the Alt + F11 keys together to bring up the Excel VBA Editor.
3. There, click the Insert menu on the toolbar.
4. On the context menu that shows up, select Module.
5. Into the new and blank module, copy and paste the following VBA code:

Sub HideFormulas()
    Dim ws As Worksheet
    Dim cell As Range
    
    ' Specify the worksheet where you want to hide the formulas
    Set ws = ThisWorkbook.Worksheets("Sheet2") ' Replace "Sheet2" with your sheet name
    
    ' Loop through each cell in the used range of the worksheet
    For Each cell In ws.UsedRange
        ' Check if the cell contains a formula
        If cell.HasFormula Then
            ' Hide the formula by setting the cell's value to its current value
            cell.Value = cell.Value
        End If
    Next cell
End Sub

6. In the above code, modify the "Sheet2" code element according to the worksheet name on your Excel file.
7. Now, click the Save button on the toolbar.
8. Click the close button to get rid of the Excel VBA Editor.

9. To execute this VBA script, press Alt + F8.
10. The Macro dialog box will show up.
11. Select the HideFormulas macro on the left side.
12. Click the Run button to execute the macro.

This Excel VBA script loops through the active or the selected worksheet to find all formula cells. Then, the code replaces all the formulas with their resulting values.

Use a VBA Script to Hide Formulas in Real-Time

You can use another VBA script to convert formulas to resulting values as you type the formulas in an Excel worksheet. Here’s the code and the steps to use this method:

1. Go to the worksheet where you’re going to type formulas.
2. Right-click the worksheet name at the bottom border of Excel.
3. On the context menu that shows up, click the View Code option.

4. A blank Module will open automatically on the Excel VBA Editor.
5. There, copy and paste the following VBA script:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim cell As Range
    
    ' Set the range where you want to convert formulas to values
    Set rng = Range("A1:Z100") ' Modify the range as per your requirements
    
    ' Check if the changed cell is within the specified range
    If Not Intersect(Target, rng) Is Nothing Then
        Application.EnableEvents = False ' Disable events to avoid recursive calls
        
        ' Loop through each cell in the changed range
        For Each cell In Intersect(Target, rng)
            ' Check if the cell contains a formula
            If cell.HasFormula Then
                ' Convert the formula to its value
                cell.Value = cell.Value
            End If
        Next cell
        
        Application.EnableEvents = True ' Enable events
    End If
End Sub

6. Edit the cell range to the actual cell range that you want to cover by modifying this code element Range("A1:Z100").
7. Click the Save button and close the VBA Editor.

Now, go to the worksheet again and type a functional formula. Hit Enter to get the resulting value. If you check the Formula Bar now, you won’t find the formula you typed. Instead, you’ll only see the value.

Using an Office Scripts Code to Hide Formula

Suppose you want to automate the process of hiding formulas from a worksheet in an Excel desktop and web app. You can use Office Scripts for this purpose if you have access to it.

Usually, if you own a Microsoft 365 Business Standard or higher subscription, you’ve got Office Scripts at your disposal. It’s the Automate tab on your Excel for Microsoft 365 desktop and web app.

The good thing is you can securely share the script with your friends and colleagues who want to do the same automation. Also, this is the only scripting feature of Excel that works both on the desktop and web app. Find below the Office Scripts code and steps to utilize this code:

1. Click the Automate tab on your Excel web or desktop app.
2. Inside the Scripting Tools commands block, click New Script.
3. Now, copy and paste the following script inside the Code Editor panel on the right side border of Excel.

function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();
	// Paste to range D2 on selectedSheet from range C2:C6 on selectedSheet
	selectedSheet.getRange("D2").copyFrom(selectedSheet.getRange("C2:C6"), ExcelScript.RangeCopyType.values, false, false);
	// Paste to range D1 on selectedSheet from range C1 on selectedSheet
	selectedSheet.getRange("D1").copyFrom(selectedSheet.getRange("C1"), ExcelScript.RangeCopyType.all, false, false);
	// Delete range C:C on selectedSheet
	selectedSheet.getRange("C:C").delete(ExcelScript.DeleteShiftDirection.left);
	// Auto fit the columns of range C:C on selectedSheet
	selectedSheet.getRange("C:C").getFormat().autofitColumns();
}

4. Click the Save script button to save the script for later use.
5. Hit the Run button to execute the Office Scripts code.

Excel will copy the values from column C and paste them into column D without the formulas. Then, it’ll also delete the source column so that no one can know the formulas used to get the values.

The above code works when the source data is in column C and you’re copying the values to column D. If you’ve got the source in another column and want to copy values in a different column than D, here’s what you’ll need to do:

• Change getRange("D2") to getRange("E2") or any other column
• Modify the cell range address in getRange("C2:C6") code to the one in your dataset like getRange("D2:D6")
• Change getRange("D1") to the appropriate cell address for the first cell of the destination column
• Modify getRange("C1") to an appropriate cell address for the first cell of the source column header
• Edit the column range in getRange("C:C") code according to the source data column

Conclusion

For most of us, protecting the Excel spreadsheet is the easiest way to hide the formulas. However, there comes situations when you’re unable to lock the entire sheet and allows other to edit certain cells.

In that case, you can always hide the formula without protecting the complete sheet. This will not only secure the cell data that depends on formulas but also make sure the sheet is accessible to others for data entry.

Here, I’ve mentioned some easy methods of hiding the formula. If you want to hide formulas in Excel without protecting the sheet, you can follow any of the approaches depending on your Excel expertise.