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.
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
Try checking Extend data range formats and formulas in Editing options under Advanced in Excel Options.
