Excel: autofill formulas in lists

Question

I have a rather huge Excel sheet where the first 20 columns contain input data, further 15 columns contain formulas which use the input data for calculations.

I frequently remove the entire input data set and replace it with a new one, the input data volume varies and ranges anywhere from 4.000 to 10.000 rows.

Is there support in Excel to auto-adjust the formula part to the number of input data rows? I do that manually now, either deleting rows if the input data is smaller than the previous set or copying formula cells if the input data became larger. This is quite tedious, so I am looking for a way to automate this.

Some of the formulas produce #N/A if no input data is present and I condense the calculated data with pivot tables, so I need the formulas exactly matching the input data rows.

I am on Excel 2003.

Answer 1

To add rows, you can just select the last cell in each formula column and double-click the bottom right hand corner, just like you were going to drag, but double-click instead. This will fill the formulas down to match the values to the left.

Before (double-click where the little black box is):

After:

Answer 2

Some possible solutions....

1) Can you use Calculated Fields - that is PivotTable formulas? Calculated fields works great if your formula refers to the columns on each row only (itself). Since they're part of the Pivot, they don't take up space nor slow down your spreadsheet as much. If your formulas are vertical or complex, it will likely not work.

2) Use the automatic table formatting (Insert Table), rather than just plain data. Formulas entered in the automatic table mode will be deleted or added as you remove or add rows.

3) Use IF(ISERROR(formula, then blank or zero, else formula) in your formulas to hide invalid results. This will not work well if your PivotTable count data - as it may count those empty rows, but should work well if you mainly rely on sums of values.

Answer 3

If this is something you do a lot, you could create a VBA button control on your sheet to clear all the data in the first 20 columns and leave only on the formulas in the first row for the next 15 columns. That way you can just click the button, then paste in your new data, and then fill down row 1 for col U to AJ with a double click.