August 11, 2008

Excel Extend series - all rows

I recently had to deal with very large (300K+ rows) Excel spreadsheets. I needed to add an additional column based on calculations of other existing columns. I was familiar with the extend series functionality of Excel for extrapolating a function over multiple rows, but what a pain to drag for 300,000 rows. There has to be an easier way to apply a function to all rows. So I Googled and found a nice easy way to Extend Series to all rows, just double-click the square in the bottom right corner of the column with the function applied and presto, the calculation is applied to all rows in the spreadsheet:

Create the function in row 1


You can extend the function to subsequent rows by dragging the square in the bottom right corner of the selected cell...



...or just double-click the black square, and the function is extended all the way to the last cell ie; the 17th row function is =a17*b17.



Special thanks to Phil for his help.

Share |

6 comments:

  1. This comment has been removed by a blog administrator.

    ReplyDelete
  2. Works in xl 2003 as well. Thanks for the tip!

    ReplyDelete
  3. excelent!

    thanx!

    ReplyDelete
  4. This comment has been removed by a blog administrator.

    ReplyDelete
  5. Thanks for the tip! I tried it on 2007 and couldn't get it to work until I figured out that the double click can only be used on a cell that is next to another populated cell. If you want to leave a blank column for spacing you have to insert that column after you do the auto-fill.

    ReplyDelete