# Excel question: explicit cell reference

**URL:** <https://boards.straightdope.com/t/excel-question-explicit-cell-reference/562208>\
**Category:** Factual Questions\
**Created:** [November 28, 2010, 9:34pm UTC](https://boards.straightdope.com/t/excel-question-explicit-cell-reference/562208 "2010-11-28T21:34:25Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![pullin](https://avatars.discourse-cdn.com/v4/letter/p/c57346/32.png) [@pullin](https://boards.straightdope.com/u/pullin)\
**Post date:** [November 28, 2010, 9:34pm UTC](https://boards.straightdope.com/t/excel-question-explicit-cell-reference/562208/1 "2010-11-28T21:34:25Z")

</div>

I’m not very familiar with Excel and am trying to do some simple programming with it.

Suppose I’ve got a formula in a cell: =E7/(E7\*(-E8)-((E6_E9_K12_K12/2_E10)))

and I want to copy it repeatedly to the cell below;

But I only want to increment the references to cells in column K, like this: =E7/(E7\*(-E8)-((E6_E9_ **K13** \ ***K13** /2\*E10))). I don’t want E7 to become E8 (ditto for the remaining E’s)

Is there some prefix or indication that indicates to Excel I don’t want the other cell references incremented when I copy this formula to the 99 following cells? (using CTRL-D)

Thanks for any and all help. Hope this makes sense.

---

<div class="post-metadata">

**Author:** ![ragerdude](https://avatars.discourse-cdn.com/v4/letter/r/d07c76/32.png) [@ragerdude](https://boards.straightdope.com/u/ragerdude)\
**Post date:** [November 28, 2010, 9:38pm UTC](https://boards.straightdope.com/t/excel-question-explicit-cell-reference/562208/2 "2010-11-28T21:38:14Z")

</div>

Put a $ in front of each reference you want to remain fixed. E7 becomes $E$7:

=$E$7/($E$7\*(-$E$8)-(($E$6\*$E$9_K12_K12/2\*$E$10)))

---

<div class="post-metadata">

**Author:** ![pullin](https://avatars.discourse-cdn.com/v4/letter/p/c57346/32.png) [@pullin](https://boards.straightdope.com/u/pullin)\
**Post date:** [November 28, 2010, 9:39pm UTC](https://boards.straightdope.com/t/excel-question-explicit-cell-reference/562208/3 "2010-11-28T21:39:22Z")

</div>

Then can I just copy/paste, and expect the non-$ cells to increment?

---

<div class="post-metadata">

**Author:** ![Zakalwe](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/zakalwe/32/270_2.png) [@Zakalwe](https://boards.straightdope.com/u/Zakalwe)\
**Post date:** [November 28, 2010, 9:40pm UTC](https://boards.straightdope.com/t/excel-question-explicit-cell-reference/562208/4 "2010-11-28T21:40:31Z")

</div>

You should also know that you can lock the column or the row separately:

Thus: $E7 when copied one row down will become $E8.

E$7 when copied one row down will stay E$7, but if copied column over will become F$7.

---

<div class="post-metadata">

**Author:** ![pullin](https://avatars.discourse-cdn.com/v4/letter/p/c57346/32.png) [@pullin](https://boards.straightdope.com/u/pullin)\
**Post date:** [November 28, 2010, 9:44pm UTC](https://boards.straightdope.com/t/excel-question-explicit-cell-reference/562208/5 "2010-11-28T21:44:35Z")

</div>

Oooh! Just tried it and it worked!

Thanks everyone. (Now to correct my formula – numbers went off in the weeds, but that’s an error in my math) 🙂

---

<div class="post-metadata">

**Author:** ![K364](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/k364/32/5_2.png) [@K364](https://boards.straightdope.com/u/K364)\
**Post date:** [November 28, 2010, 10:14pm UTC](https://boards.straightdope.com/t/excel-question-explicit-cell-reference/562208/6 "2010-11-28T22:14:02Z")

</div>

Tip: use the F4 key to cycle around the possible combinations of relative/absolute… put your cursor on the cell reference in the formula bar, and repeatedly press F4 - you’ll see what I mean.

---

<div class="post-metadata">

**Author:** ![Munch](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/munch/32/5281_2.png) [@Munch](https://boards.straightdope.com/u/Munch)\
**Post date:** [November 28, 2010, 11:51pm UTC](https://boards.straightdope.com/t/excel-question-explicit-cell-reference/562208/7 "2010-11-28T23:51:05Z")

</div>

> [@K364](#):
>
> Tip: use the F4 key to cycle around the possible combinations of relative/absolute… put your cursor on the cell reference in the formula bar, and repeatedly press F4 - you’ll see what I mean.

Neat! That’s really useful.