Using the same formula down a column with gaps in rows

Question

Greetings all! I’m looking to set up a formula that will apply to (nearly) an entire column (excluding about 30 rows at the top for headings etc.). I am using a large excel file with eight different formulas in different columns, and at the moment I have copied the formula down to 1000 rows which has made the excel file very large, which is worse when I then have to duplicate this tab. I’m looking into a way to have excel copy the formula down automatically when data is added into a new row, but the problem is that for these eight formulas there isn’t always data on each row. I’ve looked into array formulas using the {} brackets, but if there is a break in a row for the data the formula doesn’t then “jump” to the next row with data entered to carry on.&nbsp;

sergeibaklan · Answer

Keggsy&nbsp;
Are there any reasons not to use Excel Tables?

keggsy · Answer

SergeiBaklan&nbsp;Hi Sergei, thanks for getting back to me. I've not used excel tables before, and it does look promising to be able to control the formula. However, when I select whole columns to be in the range, then the file size is still around the same as just copying the formula down. &nbsp;Also, do the columns in the table have to be next to each other, or assign for example columns A, C, E to a table?&nbsp;

sergeibaklan · Answer

Keggsy&nbsp;
Sorry, I didn't catch. Table allows you to add formulas automatically. How file size linked to that?

keggsy · Answer

SergeiBaklan&nbsp;if I make an excel table using cells&nbsp;=$A$1:$C$1000, then the file size is 18 KB. If I make an excel table using cells&nbsp;=$A:$C (i.e. the whole of columns A, B &amp; C), then the file size is 8,547 KB. Ideally, if the formulas copied down when a new row has data, then the file size is still small and doesn't take long to open, and I don't have to edit the tables when the data goes over what it is set to (e.g. changing the table from cells&nbsp;=$A$1:$C$1000 to&nbsp;=$A$1:$C$2000).&nbsp;

sergeibaklan · Answer

Keggsy&nbsp;
But you don't need to make the table for entire future range. Make, for example, for A1:C2, put required formula in C2. After that add data row by row when needed, table will be expanded, and formula will be kept in column C.

Forum Discussion

Using the same formula down a column with gaps in rows

6 Replies

Resources