A Shortcut to Locking Formulas in Excel

If you’re like most Excel users, you probably spend a lot of time creating formulas. And if you’re like most Excel users, you probably also spend a lot of time accidentally changing those formulas. Maybe you accidentally hit the delete key while trying to select a cell. Or maybe you accidentally dragged a cell that you didn’t mean to. Either way, it’s easy to accidentally change a formula, and it can be frustrating to have to go back and fix it. Fortunately, there’s a shortcut that you can use to lock formulas in Excel. Here’s how it works:

1. Select the cells that contain the formulas that you want to lock.

2. On the Home tab, in the Editing group, click Find & Select. Then click Go To.

3. In the Go To dialog box, click the Special button.

4. In the Go To Special dialog box, select the Formulas option. Then click OK.

5. On the Home tab, in the Editing group, click Find & Select. Then click Replace.

6. In the Find what box, type an equal sign (=).

7. In the Replace with box, type a single quotation mark (‘).

8. Click the Options button.

9. In the Search options dialog box, make sure that the Match entire cell contents check box is selected. Then click OK.

10. Click the Replace All button.

That’s it! All of the formulas in the selected cells will now be locked. This shortcut works by adding a single quotation mark in front of the equal sign in each formula. This doesn’t change the value of the formula, but it does prevent the formula from being accidentally changed. So next time you’re working with formulas in Excel, try this shortcut to lock them down.

Excel is a powerful tool that can help you manage and analyze data with ease. One of the most important features of Excel is the ability to use formulas to perform calculations on your data. However, sometimes you may want to protect your formulas from being accidentally deleted or modified. In this article, we will explore a shortcut to locking formulas in Excel.

Why Lock Formulas?

Formulas are the backbone of any Excel spreadsheet. They allow you to perform complex calculations and analysis on your data. However, if someone accidentally deletes or modifies a formula, it can have serious consequences for your data. That’s why it’s important to lock your formulas to prevent them from being accidentally changed.

How to Lock Formulas in Excel

Locking formulas in Excel is a simple process that can be done in just a few steps. Here’s how:

Step 1: Select the Cells with Formulas

The first step is to select the cells that contain the formulas you want to lock. You can do this by clicking and dragging your mouse over the cells, or by clicking on the first cell and then holding down the Shift key while clicking on the last cell in the range.

Step 2: Open the Format Cells Dialog Box

Next, right-click on the selected cells and choose “Format Cells” from the context menu. Alternatively, you can go to the “Home” tab on the ribbon and click on the “Format” button in the “Cells” group, then choose “Format Cells” from the dropdown menu.

Step 3: Lock the Cells

In the “Format Cells” dialog box, click on the “Protection” tab. Check the box next to “Locked” to lock the cells. Then click “OK” to close the dialog box.

Step 4: Protect the Worksheet

Finally, you need to protect the worksheet to prevent anyone from editing the locked cells. To do this, go to the “Review” tab on the ribbon and click on “Protect Sheet” in the “Changes” group. In the “Protect Sheet” dialog box, choose the options you want to apply, such as allowing users to select locked cells but not edit them. Then enter a password to protect the sheet and click “OK”.


Locking formulas in Excel is an important step to protect your data and ensure the accuracy of your calculations. By following the simple steps outlined in this article, you can easily lock your formulas and protect your worksheet from accidental changes. So next time you’re working with Excel, remember to take a few extra seconds to lock your formulas and keep your data safe.


Did you find this article useful?

Share it with your friends or colleagues

About Aepoch Advisors

We are a boutique accounting and consulting firm servicing international businesses operating in China. We offers book keeping and business advisory service. We also help our clients select and implement SAAS business applications
Cloud technology significantly reduces cost foreign companies traditionally spent on tax compliance and ERP systems. Our cloud professionals can help with streamlining your management and controling structure, as well as advising you on how to reduce risks and maximize profits with software purposely built for Chinese business. Contact us today to learn more about our services.