On a Table if I Create a Line Does Will It Continue a Forumula

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.


Download Practice Workbook

You can download the practice workbook from here.


4 Easy Ways to Keep a Cell Fixed in Excel Formula

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:

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

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

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

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

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

  • Now select cell F5. From the formula select the D12 part and press F4. The formula will look like this:
  • Press, Enter.
  • Drag the Fill Handle to the end of the dataset.

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

Read More: How to Lock a Cell in Excel Formula (2 Ways)


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 commission for all salespeople. So, we will fix Row 5. To perform this action just follow the below steps:

Freeze Only Row Reference of a Cell

  • Firstly, insert the following formula in Cell D6.
  • 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.

Freeze Only Row Reference of a Cell

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


Similar Readings:

  • Different Types of Cell References in Excel (With Examples)
  • Relative and Absolute Cell Address in the Spreadsheet
  • Example of Relative Cell Reference in Excel (3 Criteria)
  • Absolute Cell Reference Shortcut in Excel (4 Useful Examples)

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:

Keep the Column Reference Fixed in Excel Formula

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

Keep the Column Reference Fixed in Excel Formula

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

Keep the Column Reference Fixed in Excel Formula

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

Related Content: Example of Mixed Cell Reference in Excel (3 Types)


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:

Both Column and Row Reference of a Cell Fixed

  • In the beginning, select cell D5. Insert the following formula:
  • 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.

Both Column and Row Reference of a Cell Fixed

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

Both Column and Row Reference of a Cell Fixed

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


Conclusion

In this article, we have tried to cover almost everything about how to keep a cell fixed in an excel formula. For greater efficiency, download our practice workbook added to this article and practice yourself. If you have any queries or have any kind of confusion just leave a comment below. We will try to reply to you as soon as possible.


Related Articles

  • Cell Reference in Excel VBA (8 Examples)
  • Absolute Cell Reference in Excel (4 Examples)
  • Reference Another Sheet in Excel (3 Methods)
  • Excel Sheet Name in Formula Dynamic (3 Approaches)
  • Difference Between Absolute and Relative Reference in Excel

mantopower1.blogspot.com

Source: https://www.exceldemy.com/keep-a-cell-fixed-in-excel-formula/

Komentar

More Articles

164.68 L27 15 : Kumpulan 111.90.l50.204 Japanese 164.68.l27.15 111.90150 ... - Anda akan melihan berbagai macam fitur di sajikan oleh situs tersebut, dan bukan hanya satu atau dua yang di sajikan pada halan situs.

حاسبة جميلة - الة حاسبة جميلة وأنيقة : كلام جميل فيس بوك , وصور بوستات فيس بوك جميلة.

111.90 L.150.204 Viral / 111 90 L 150 204 Viral Variante Virus Covid Variante Virus Puo Essere Anche Apa Bila Anda Akan Melihat Sebuah Kumpulan Video Nada Tentu Bisa Menggunakan 111 90 150 204 Nonton Karena Banyak

Локомотив Крылья Советов : Локомотив — Крылья Советов. Прогноз и ставка за 1.75 / 02 ... - Результаты матча, счет 3:1, обзор по минутам, видео.

Andai Kakak Ku Tidak Menggodaku Waktu Itu : Andai Saja Kakak Tidak Menggodaku : Jika kalian penasaran dengan apa yang terjadi pada hal yang viral ini, maka kalian harus simak pembahasan yang akan admin bagikan sampai akhir ya sob.

Jack Grealish - Villa Star Jack Grealish Reveals He 'Almost Died' From ... - Jack grealish (born 10 september 1995) is a professional footballer who plays for premier league club aston villa as a midfielder.

How To Make Home Made Pork Rinds : How to Make Chicharrones (Fried Pork Skin) at Home | Fried ... : How to make pork skins.

New Cabinet Ministers - How many of the new Cabinet Ministers can you name?

Gambar Sketsa Tatto : Inspirasi Tato Sketsa Tato Joker Keren / 20 gambar sketsa kumpulan gambar sketsa bunga sketsa tato, polres karawang membuat sketsa tato simple sumber.




banner