1

I have a formula column in my spreadsheet to calculate values based on another column. I want to know if there is a way to make the formula default so that when I insert a new row, the cell of that column automatically gets the formula pre-set, no need to copy and paste from above again.

This is desirable because the spreadsheet will be filled in by non-technical users. The current workaround is to pre-copy-&-paste the whole column with the formula but this is not ideal as the file becomes unnecessarily large and slow to open.

Any method even with VBA is acceptable but a non-VBA solution is preferable.

Community
  • 1
  • 1
user1589188
  • 5,316
  • 17
  • 67
  • 130
  • 2
    You can write macro for inserting formula to newly added rows. You can use Worksheet_Change event for this. Refer this link: http://stackoverflow.com/questions/7479721/excel-vba-determine-whether-user-are-adding-rows-or-deleting-rows – Paresh J Feb 11 '15 at 06:16

2 Answers2

3

My suggestion would be to change your used range on your worksheet to a table. When you insert a row into a sheet with a table (either within or directly below the table) the formulas within the table drag down. Although, you will have to change your formulas slightly as you must reference the cells within the table slightly differently when they are within a table.

Here's a couple links to some resources, although there are bound to be some far better resources:

http://www.databison.com/table-formulas-in-excel/

http://excelsemipro.com/2011/04/formulas-and-structured-data-in-excel-tables/

http://www.jkp-ads.com/Articles/Excel2007Tables.asp

Dane I
  • 742
  • 9
  • 22
  • Using tables works! But just have to type right below the last row, not really defaulting the formula to the whole column. But I guess this is the easiest solution, I will pick yours. – user1589188 Feb 12 '15 at 06:44
-1

Try checking Extend data range formats and formulas in Editing options under Advanced in Excel Options.

pnuts
  • 58,317
  • 11
  • 87
  • 139