# How to Keep a Cell Fixed in Excel Formula (4 Easy Ways)

In Microsoft Excel, while working with a dataset, sometimes we need to use the same formula in multiple rows or columns. In this article, we will see how to keep a cell fixed in an Excel formula. We will illustrate this method to you with 4 easy examples with explanations.

## How to Keep a Cell Fixed in Excel Formula: 4 Easy Ways

### 1. Use of F4 Key in Excel Formula to Keep a Cell Fixed

In this example, we will use the F4 key to keep a cell formula fixed. We have a dataset of fruits with their weight, unit price, and total price. Sellers will pay 5% tax over the total for all kinds of fruits. Letâ€™s see why we need to fix the cell formula to calculate this:

• In the beginning, select cell F5.
• Insert the following formula:
=C5*D5
• Press Enter.
• So, we get the tax amount for the first fruit item.

• Next, if we drag the Fill Handle tool, we donâ€™t get any values.
• Look at the corresponding formulas. The cell reference is changing downwards.
• We need to fix the cell value D12 for all the formulas.

• Now select cell F5. From the formula select the D12 part and press F4. The formula will look like this:
`=E5*\$D\$12`
• Press, Enter.
• Drag the Fill Handle to the end of the dataset.

• Finally, we get the actual tax amount for all the fruits.

### 2. Freeze Only Row Reference of a Cell

In this example, we have the following dataset of six salespeople. Their sales commission rate is 5%. This value is located in Row 5. We will calculate sales commissions for all salespeople. So, we will fix Row 5. To perform this action just follow the below steps:

• Firstly, insert the following formula in Cell D6.
`=C6*D5`
• We get the amount of sales commission for John.
• Next, drag down the Fill Handle.

• Here, we see the error. Because the reference of the value 5% is not fixed in the formula.

• To solve this select the formula of Cell D6.
• Insert a â€˜\$â€™ sign before row number 5.
• Hit Enter.
• Drag down the Fill Handle.

• Lastly, we can see that, we get the sales commission value for all the salespeople.

### 3. Keep the Column Reference Fixed in Excel Formula

In this example, we will keep the column reference fixed whereas we only kept the sale reference fixed in the previous one. We will add a new column 10% sales commission to our previous dataset. We willÂ  go through the following steps to perform this action:

• First, select the cell range (D6:D11).
• Drag the Fill Handle tool Horizontally.

• We can see that we get the 10% of the sales commission value of 5% not over the total sales value. This happens because the column reference doesnâ€™t remain fixed.

• Now insert a â€˜\$â€™ sign before column number C to fix the column reference.
• Select (D6:D11).
• Drag the Fill Handle tool Horizontally.

• Finally, we get the 10% sales commission for actual sales value.

### 4. Both Column and Row References of a Cell Fixed

In this example, we will fix the reference of a column and a row at the same time. We will use this method in our following dataset of workers to calculate their total income. Letâ€™s see how can we do this following simple steps:

• In the beginning, select cell D5. Insert the following formula:
`=C5*C12`
• Press Enter.
• Drag the Fill Handle tool to Cell D10.
• Here, we donâ€™t get the income for all the workers because the cell reference of Cell C12 is not fixed.

• To fix the reference select the formula of cell D5. Insert dollar sign before C and 12. The formula will look like this:
`=C5*\$C\$12`
• Press Enter and drag down the Fill Handle.

• Finally, we get the total income for all the workers.

## Related Articles

<< Go Back to Cell Reference in Excel |Â Excel Formulas | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Mukesh Dipto

Mukesh Dipto is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including Rhino3D, Maxsurf C++, AutoCAD, HTML, CSS, JavaScript, and WordPress. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF