# 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:

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

- Excel will paste the values from the source column.

**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:

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

- You should now see the
**Excel Options**dialog box. - There, click the
**Advanced**category on the left side. - Now, scroll down the right-side menu until you locate the
**Display**options. - In the
**Display**options section, uncheck the checkbox for the**Show formula bar**. - 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:

- Go to the worksheet that needs hiding of the cell formulas.
- Hit the
`Alt`+`F11`keys together to bring up the**Excel VBA Editor**. - There, click the
**Insert**menu on the toolbar. - On the context menu that shows up, select
**Module**. - 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
```

- In the above code, modify the
`"Sheet2"`

code element according to the worksheet name on your Excel file. - Now, click the
**Save**button on the toolbar. - Click the
**close button**to get rid of the Excel VBA Editor.

- To execute this VBA script, press
`Alt`+`F8`. - The
**Macro**dialog box will show up. - Select the
**HideFormulas**macro on the left side. - 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:

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

- A blank
**Module**will open automatically on the**Excel VBA Editor**. - 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
```

- Edit the cell range to the actual cell range that you want to cover by modifying this code element
`Range("A1:Z100")`

. - 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:

- Click the
**Automate**tab on your Excel web or desktop app. - Inside the
**Scripting Tools**commands block, click**New Script**. - 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();
}
```

- Click the
**Save script**button to save the script for later use. - 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.