# Excel question

**URL:** <https://boards.straightdope.com/t/excel-question/365325>\
**Category:** Factual Questions\
**Created:** [July 20, 2006, 1:22pm UTC](https://boards.straightdope.com/t/excel-question/365325 "2006-07-20T13:22:53Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![Johnny\_L.A](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_l.a/32/1084_2.png) [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)\
**Post date:** [July 20, 2006, 1:22pm UTC](https://boards.straightdope.com/t/excel-question/365325/1 "2006-07-20T13:22:53Z")

</div>

I have a spreadsheet that I add to frequently. A typical column might be =(B3:B17). When I add a row I’ll click on the previous box, brab the corner, and move it down. The formula for the new cell becomes =(B **4** :B18). I’ve tried clicking on the whole row and dragging the corner, but I get the same result. How do I copy the formula without having B3 (or whatever) incremented? That is, I always want to add from the original cell.

---

<div class="post-metadata">

**Author:** ![yoyodyne](https://avatars.discourse-cdn.com/v4/letter/y/a9a28c/32.png) [@yoyodyne](https://boards.straightdope.com/u/yoyodyne)\
**Post date:** [July 20, 2006, 1:24pm UTC](https://boards.straightdope.com/t/excel-question/365325/2 "2006-07-20T13:24:40Z")

</div>

Change the reference to $B$3

---

<div class="post-metadata">

**Author:** ![Johnny\_L.A](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_l.a/32/1084_2.png) [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)\
**Post date:** [July 20, 2006, 1:25pm UTC](https://boards.straightdope.com/t/excel-question/365325/3 "2006-07-20T13:25:26Z")

</div>

Thanks. I’ll give that a try today.

---

<div class="post-metadata">

**Author:** ![jjimm](https://avatars.discourse-cdn.com/v4/letter/j/ba8739/32.png) [@jjimm](https://boards.straightdope.com/u/jjimm)\
**Post date:** [July 20, 2006, 1:27pm UTC](https://boards.straightdope.com/t/excel-question/365325/4 "2006-07-20T13:27:11Z")

</div>

Or more specifically, =sum($B$3:$B$18). If you want the B18 bit to increment, but not the B3, leave out the dollar sign. If you want to drag right and increment the column, leave the dollar sign off the letter.

---

<div class="post-metadata">

**Author:** ![jjimm](https://avatars.discourse-cdn.com/v4/letter/j/ba8739/32.png) [@jjimm](https://boards.straightdope.com/u/jjimm)\
**Post date:** [July 20, 2006, 1:28pm UTC](https://boards.straightdope.com/t/excel-question/365325/5 "2006-07-20T13:28:25Z")

</div>

Sorry: “leave out the dollar sign on the B18”.

---

<div class="post-metadata">

**Author:** ![Johnny\_L.A](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_l.a/32/1084_2.png) [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)\
**Post date:** [July 20, 2006, 1:30pm UTC](https://boards.straightdope.com/t/excel-question/365325/6 "2006-07-20T13:30:39Z")

</div>

[QUOTE=jjimm]  
Or more specifically, =sum($B$3:$B$18). If you want the B18 bit to increment, but not the B3, leave out the dollar sign. If you want to drag right and increment the column, leave the dollar sign off the letter.  
[/QUOTE]

Thanks. (And I left ‘sum’ out of my original example.)

---

<div class="post-metadata">

**Author:** ![yoyodyne](https://avatars.discourse-cdn.com/v4/letter/y/a9a28c/32.png) [@yoyodyne](https://boards.straightdope.com/u/yoyodyne)\
**Post date:** [July 20, 2006, 1:33pm UTC](https://boards.straightdope.com/t/excel-question/365325/7 "2006-07-20T13:33:12Z")

</div>

The help phrase to look for is “absolute reference”

---

<div class="post-metadata">

**Author:** ![PoorYorick](https://avatars.discourse-cdn.com/v4/letter/p/ecccb3/32.png) [@PoorYorick](https://boards.straightdope.com/u/PoorYorick)\
**Post date:** [July 20, 2006, 1:53pm UTC](https://boards.straightdope.com/t/excel-question/365325/8 "2006-07-20T13:53:58Z")

</div>

Helpful hint/worthless trivia: If you click in the cell reference and hit the \*\*F4 \*\*key, Excel will cycle through the absolute reference notations. For instance, if you click the reference “=E7,” [ul]  
[li]First click: =$E$7 (absolute reference)[/li][li]Second click: =E$7 (column relative, row absolute)[/li][li]Third click: =$E7 (column abosolute, row relative)[/li][li]Forth click: =E7 (relative reference)[/li][/ul]

---

<div class="post-metadata">

**Author:** ![Johnny\_L.A](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_l.a/32/1084_2.png) [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)\
**Post date:** [July 21, 2006, 12:46am UTC](https://boards.straightdope.com/t/excel-question/365325/9 "2006-07-21T00:46:06Z")

</div>

Okay, got it to work with (for example) =SUM($D$3:D18)

Now is there a way for my histogram to be automatically updated with the new data when it’s added?

---

<div class="post-metadata">

**Author:** ![Johnny\_L.A](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_l.a/32/1084_2.png) [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)\
**Post date:** [July 21, 2006, 12:47am UTC](https://boards.straightdope.com/t/excel-question/365325/10 "2006-07-21T00:47:09Z")

</div>

The chart is on a different page.