4

I'm having an issue where upon copy and pasting a formula into a column, lets say with 300 rows, instead of automatically pasting all the way to the end of the column like the other columns, excel keeps on creating extra rows in that column until it reaches something like row 2000

This makes my excel file balloon from 300kb to 190MB! I couldn't find any solution anywhere to solve this annoying issue. Anyone able to help me out?

Thanks

4
  • 1
    How are you copying the formula? Are you selecting 1 cell with the formula, copying, selecting an entire column, and then pasting? That will fill every cell. If we don't know what you're doing, we can't help you not do it.
    – jmac
    Sep 3, 2013 at 5:57
  • Yea that's exactly what I'm doing. Copying one cell, then copying into the whole column by selecting it from the top
    – jc.yin
    Sep 3, 2013 at 6:17
  • 1
    That will copy it down all the rows. You have all the rows selected. There is no way around that if you select the entire column...
    – jmac
    Sep 3, 2013 at 6:54
  • @jc.yin do you consider a VBA solution?
    – user222864
    Sep 3, 2013 at 7:33

2 Answers 2

2
  1. Copy the formula with Ctrl+C
  2. Go to the FIRST destination cell and select it. Press Ctrl+Shift+down to select all the rows that already contain data. (DO NOT select the whole column by selecting from the top)
  3. Paste the formula with Ctrl+V

For step (2), if the rows were initially empty, then go to the last row, eg B300, and Ctrl+Shift+up instead to select all the rows above it, and then paste the formula in.

0

Go to the last row you use.

  1. Press and hold Control and the press the right arrow. So Ctrl+Right
  2. Then press Ctrl-Down
  3. Then press Delete.
  4. Save

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .