How to Keep a Cell Fixed in an Excel Formula (2 Simple Ways)

When analyzing data or using a formula in Excel, we sometimes need to copy and paste it into another cell. And, in many cases, our formula includes cell references that must remain unchanged. However, if we do not lock the cell reference, after copying the formula to another cell, it will change. Thus, we need to keep the needed cell fixed in an Excel formula.

Suppose you are working with sales data where the total prices of the commodities are in column B and the tax rate is in cell E1. Now, to calculate the tax for each commodity, if you write a formula: =B2*E1 and copy it down, Excel will automatically update the E1 reference to E2, E3, and so on. This will lead to incorrect results, as the tax rate should remain the same for all rows. In this case, you need to fix the E1 cell to calculate the tax correctly.

In this article, we will explain how you can keep a cell fixed in an Excel formula using the $ symbol by adding it manually or with keyboard shortcuts, and using the INDIRECT function..

The absolute cell reference keeps a cell fixed in the formula, and so even if we copy the formula multiple times, it remains unchanged. Using the $ sign in front of both the column letter and row number, we can create an absolute cell reference and keep the cell fixed.

We will use the dataset below to explain how to keep a cell fixed using an absolute cell reference in an Excel formula.

This dataset contains information on some commodities in a shop, and the seller has to pay a 12% tax on all of them. Now, we will calculate the amount of tax he needs to pay for each product.

Steps:

➤ First, select cell F1 and write down Tax.
➤ Then, click on cell F2 and enter the following formula and press Enter:

➤ Now, we will lock cell E2 as it represents the tax rate, which will remain unchanged in the tax calculation for all products. To keep cell E2 fixed, first click on F2, and the formula will appear on the formula bar.
➤ Then, click on E2 in the formula bar and press  F4  on your keyboard. You can also manually type the $ symbol in front of E to lock the column and in front of 2 to lock the row. The formula will appear as:

➤ Press Enter to calculate the tax for Shampoo.

➤ Finally, drag the cursor to fill down all the cells to apply the formula to the other products.

The INDIRECT function returns the reference of a cell based on a text string, and thus, it can keep a cell fixed in the Excel formula.

Steps:

➤ First, click on cell F1 and write Tax
➤ Then, select cell F2 and insert the following formula:

➤  Finally, press Enter and drag the cursor down to apply the formula to the other cells.

Frequently Asked Questions

Can I Keep Only the Column Fixed in an Excel Formula?

Yes, you can. To keep only the column fixed in an Excel formula, you need to add the $ sign in front of the column letter. Suppose we have prices of a few fruits in cell A2: A7 and their corresponding prices in cell B2:G2. Now, to calculate the total price, we need to multiply the fruits by the prices and hence, need to keep the fruits column fixed. In this case, use this formula: =$A2*B2 and drag the cursor horizontally to get the prices.

How Can I Fix Only the Row in an Excel Formula?

To fix only the row in Excel, you need to add the $ sign in front of the row number. Suppose you have commodities units across A, B, C, and D columns in row 2 and prices in E2 to E5 cells. Now, to calculate the prices for the respective commodities, use this formula: A$2*E2, and row number 2 will remain fixed. Then drag the cursor across different columns to get all the prices.

Does F4 Fix Only the Entire Cell in an Excel Formula?

Not exactly. While pressing  F4  once locks or fixes a cell completely, you can press it multiple times to change the lock. The first press makes the reference absolute, such as $A$1, i.e., locks both row and column. Then, pressing it again locks only the row, such as A$1, and then, pressing it again only locks the column, such as $A1, and pressing it again that is cumulatively four times returns it to a relative reference such as A1.

Wrapping Up

In this article, we have learned about keeping a cell fixed in an Excel formula using the $ sign both manually and using the  F4  function key and the INDIRECT function. We have also discussed keeping the whole cell fixed, only the column fixed, and only the row fixed. Give these methods a try and let us know if you have any inquiries.