# Excel Cut and Paste **URL:** \ **Category:** Factual Questions\ **Created:** [November 8, 2005, 1:37am UTC](https://boards.straightdope.com/t/excel-cut-and-paste/329996 "2005-11-08T01:37:44Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![Mr.Slant](https://avatars.discourse-cdn.com/v4/letter/m/c57346/32.png) [@Mr.Slant](https://boards.straightdope.com/u/Mr.Slant)\ **Post date:** [November 8, 2005, 1:37am UTC](https://boards.straightdope.com/t/excel-cut-and-paste/329996/1 "2005-11-08T01:37:44Z")
I have an excel spreadsheet question, with layout as follows: It has columns A B C and D. A is date, B is Category (Spending or Paycheck), C is amount of credit or debit and D is running balance. D is in the format of =D2-C3. For example, D3 = D2-C3 D4 = D3-C4 D5 = D4-C5 Every time I select a block of A B C, cut and paste, the references in column D hose up, so if I move the contents of columns A B and C down 14 rows, D3 will be =D2-C15. How do I keep my “column D” from getting hosed up like this? ---
**Author:** ![TokyoBayer](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/tokyobayer/32/13989_2.png) [@TokyoBayer](https://boards.straightdope.com/u/TokyoBayer)\ **Post date:** [November 8, 2005, 1:52am UTC](https://boards.straightdope.com/t/excel-cut-and-paste/329996/2 "2005-11-08T01:52:02Z")
The only way I’ve found to solve it is to copy and paste, and then go back and delete the original block. ---
**Author:** ![Mr.Slant](https://avatars.discourse-cdn.com/v4/letter/m/c57346/32.png) [@Mr.Slant](https://boards.straightdope.com/u/Mr.Slant)\ **Post date:** [November 8, 2005, 2:02am UTC](https://boards.straightdope.com/t/excel-cut-and-paste/329996/3 "2005-11-08T02:02:03Z")
Tokyo: Wow. That’s so simple I never even thought of it. I’ll go give that a try. … 1 minute later … Didn’t work. Either I described my problem wrong, or you misinterpreted me. Hmmmmmmmmmmmmmmmmm. ---
**Author:** ![LiveOnAPlane](https://avatars.discourse-cdn.com/v4/letter/l/3d9bf3/32.png) [@LiveOnAPlane](https://boards.straightdope.com/u/LiveOnAPlane)\ **Post date:** [November 8, 2005, 2:09am UTC](https://boards.straightdope.com/t/excel-cut-and-paste/329996/4 "2005-11-08T02:09:25Z")
Just a question–are you entering the formula for column D in lower case? ---
**Author:** ![Crowbar\_of\_Irony\_3](https://avatars.discourse-cdn.com/v4/letter/c/f08c70/32.png) [@Crowbar\_of\_Irony\_3](https://boards.straightdope.com/u/Crowbar_of_Irony_3)\ **Post date:** [November 8, 2005, 2:14am UTC](https://boards.straightdope.com/t/excel-cut-and-paste/329996/5 "2005-11-08T02:14:26Z")
Have you tried any of the options from the Paste Special dialog (under the Edit menu)? ---
**Author:** ![don\_t\_ask](https://avatars.discourse-cdn.com/v4/letter/d/e68b1a/32.png) [@don\_t\_ask](https://boards.straightdope.com/u/don_t_ask)\ **Post date:** [November 8, 2005, 2:21am UTC](https://boards.straightdope.com/t/excel-cut-and-paste/329996/6 "2005-11-08T02:21:46Z")
Use paste special values. ---
**Author:** ![Mbossa](https://avatars.discourse-cdn.com/v4/letter/m/73ab20/32.png) [@Mbossa](https://boards.straightdope.com/u/Mbossa)\ **Post date:** [November 8, 2005, 2:23am UTC](https://boards.straightdope.com/t/excel-cut-and-paste/329996/7 "2005-11-08T02:23:04Z")
Here’s something you can try. It’s a bit kludgy, but it seems to do the job. Put this formula in EVERY cell in your D column (except for the top one, which I presume has an opening balance or something): =INDIRECT(“R[-1]C”, FALSE)-INDIRECT(“RC[-1]”, FALSE) This gives it the value of the cell immediately above, minus the value of the cell immediately to the left. Putting the references in strings makes it immune to Excel playing around with it. ---
**Author:** ![don\_t\_ask](https://avatars.discourse-cdn.com/v4/letter/d/e68b1a/32.png) [@don\_t\_ask](https://boards.straightdope.com/u/don_t_ask)\ **Post date:** [November 8, 2005, 2:23am UTC](https://boards.straightdope.com/t/excel-cut-and-paste/329996/8 "2005-11-08T02:23:47Z")
Sorry wont work with cut only copy and paste. ---
**Author:** ![ZipperJJ](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/zipperjj/32/211_2.png) [@ZipperJJ](https://boards.straightdope.com/u/ZipperJJ)\ **Post date:** [November 8, 2005, 2:28am UTC](https://boards.straightdope.com/t/excel-cut-and-paste/329996/9 "2005-11-08T02:28:00Z")
Have you tried cutting A, B, C AND D then using “insert cut rows” where you’re going to paste (instead of just “paste”)? Then D would carry over with the C values it was associated with, and the other D’s will line up. I admit I suck at Excel but I’ve been doing alot of cutting and pasting lately and it seems to work for me. ---
**Author:** ![Mbossa](https://avatars.discourse-cdn.com/v4/letter/m/73ab20/32.png) [@Mbossa](https://boards.straightdope.com/u/Mbossa)\ **Post date:** [November 8, 2005, 2:41am UTC](https://boards.straightdope.com/t/excel-cut-and-paste/329996/10 "2005-11-08T02:41:34Z")
> [@Mbossa](#): > > Here’s something you can try. It’s a bit kludgy, but it seems to do the job. > > Put this formula in EVERY cell in your D column (except for the top one, which I presume has an opening balance or something): > > =INDIRECT(“R[-1]C”, FALSE)-INDIRECT(“RC[-1]”, FALSE) > > This gives it the value of the cell immediately above, minus the value of the cell immediately to the left. Putting the references in strings makes it immune to Excel playing around with it. Just to clarify: when I said EVERY cell in your D column, I didn’t mean the entire column, but only those that you want the running total in (which may or may not be the entire column). I emphasised EVERY because that exact formula can be used in all those cells without having to change it. ---
**Author:** ![Mr.Slant](https://avatars.discourse-cdn.com/v4/letter/m/c57346/32.png) [@Mr.Slant](https://boards.straightdope.com/u/Mr.Slant)\ **Post date:** [November 8, 2005, 3:12am UTC](https://boards.straightdope.com/t/excel-cut-and-paste/329996/11 "2005-11-08T03:12:21Z")
> [@Mbossa](#): > > Just to clarify: when I said EVERY cell in your D column, I didn’t mean the entire column, but only those that you want the running total in (which may or may not be the entire column). I emphasised EVERY because that exact formula can be used in all those cells without having to change it. Thank you. Your solution is both elegant and worked perfectly. ---
**Author:** ![Mbossa](https://avatars.discourse-cdn.com/v4/letter/m/73ab20/32.png) [@Mbossa](https://boards.straightdope.com/u/Mbossa)\ **Post date:** [November 8, 2005, 3:25am UTC](https://boards.straightdope.com/t/excel-cut-and-paste/329996/12 "2005-11-08T03:25:46Z")
YEAHHH!!! WHO DA MAN!!! _ahem_ Glad to be of assistance. 🙂