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.

## August 11, 2008

### Excel Extend series - all rows

Posted by Dan Shultz at 1:37 PM

Subscribe to:
Post Comments (Atom)

very cool.

ReplyDeleteThis comment has been removed by a blog administrator.

ReplyDeleteWorks in xl 2003 as well. Thanks for the tip!

ReplyDeleteexcelent!

ReplyDeletethanx!

This comment has been removed by a blog administrator.

ReplyDeleteThanks 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