Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

3 Answers 3

up vote 2 down vote accepted

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):

Before Double-Click

After:

After Double-Click

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.