Automatically expand formulas to next row without fill handle.

**spangpang** · 03-29-2013, 04:00 PM

Hi there,

I've spent a lot of time tryin to find out how to do this but I still haven't found what I'm looking for. The problem is as follows:

I've got an excel sheet which uses a set of data which should be expanded every day. Now I don't want to use the fill handle to drag all the rows to row number 100000. Is there any chance the formula (applicable in the selected columns), can be expanded to the next row automatically, if the previous row contains a value?

Thanks in advance!

**shg** · 03-29-2013, 04:24 PM

Convert the range to a table: http://www.jkp-ads.com/articles/Excel2007tables.asp

**spangpang** · 03-30-2013, 02:53 PM

Thank for your response! Unfortunately, this doesn't work. The table has to be resized to copy the formulas. If the table would expand itself automatically, this would be perfect. Any way this could be achieved?

**FDibbins** · 03-30-2013, 03:32 PM

If you cant use the TABLE option, then use an if() statement to test for data. Pick a cell that will always have data in it if there is data in that row - lets say you are woerking on row 100, and we use A100 for the test....

=if(A100="","",your-formula)

You can then copy this down as far as you want, it will only show a value IF there is data in the A column

**aydeegee** · 04-01-2013, 09:42 AM

In order to reduce the number of IF statements, it can be done in a very few keystrokes.

If your last cell is currently (say) R100:

Put your cursor on that cell. In the name box (top left currently showing R100) overtype the R100 with R100000 (or whatever your last line will be). Hit Enter which will take you to the desired cell

Press Ctrl+Shift+Cursor Up
Press Ctrl+Shift+ Cursor Left
Press Ctrl+d

Job done

Regards
Alastair