18752

On row insertion autopopulate a formula in a cell

Question:

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.

Answer1:

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:

<a href="http://www.databison.com/table-formulas-in-excel/" rel="nofollow">http://www.databison.com/table-formulas-in-excel/</a>

<a href="http://excelsemipro.com/2011/04/formulas-and-structured-data-in-excel-tables/" rel="nofollow">http://excelsemipro.com/2011/04/formulas-and-structured-data-in-excel-tables/</a>

<a href="http://www.jkp-ads.com/Articles/Excel2007Tables.asp" rel="nofollow">http://www.jkp-ads.com/Articles/Excel2007Tables.asp</a>

Answer2:

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

Recommend

  • append value to environment variable on builder call
  • IntelliJ IDEA 15 How to get rid of namespace information at the top of PHP and Sass code windows
  • matplotlib plots too many ticks
  • How do I wrap a span around a section of text in javascript?
  • Unwrap optionals in for statement
  • Best modern way of creating indentation in a ?
  • WPF DataGrid lost focus after row delete
  • How do I control the soft menu button in Honeycomb?
  • Hudson dependencies
  • How to lookup value with multiple criteria in excel 2007 and newer
  • Reloading table causes flickering
  • Catching exception and throwing the same?
  • Binary Tree Traversal Sum Of Each Depth
  • Small video playback
  • Selecting a subset of data in ServiceStack.OrmLite
  • Is looping through all style sheets and classes a good idea in JavaScript?
  • ZipList with Scalaz
  • Implement JwtBearer Authentication in NSwag SwaggerUi
  • Does it make sense to call System.gc() and Thread.sleep() when working on Bitmaps?
  • azure media services - The request body is too large and exceeds the maximum permissible limit
  • Moving mysql files across servers
  • Asynchronous UI Testing in Xcode With Swift
  • What is Eclipse's Declaration View used for?
  • How to make a tree having multiple type of nodes and each node can have multiple child nodes in java
  • JavaScriptCore crash on iOS9
  • Cassandra Data Model
  • Rearranging Cells in UITableView Bug & Saving Changes
  • Circular dependency while pushing http interceptor
  • Run Powershell script from inside other Powershell script with dynamic redirection to file
  • PHP: When would you need the self:: keyword?
  • Linker errors when using intrinsic function via function pointer
  • Codeigniter doesn't let me update entry, because some fields must be unique
  • Load html files in TinyMce
  • How can I get HTML syntax highlighting in my editor for CakePHP?
  • Acquiring multiple attributes from .xml file in c#
  • coudnt use logback because of log4j
  • FormattedException instead of throw new Exception(string.Format(…)) in .NET
  • How to get NHibernate ISession to cache entity not retrieved by primary key
  • reshape alternating columns in less time and using less memory
  • How can I use threading to 'tick' a timer to be accessed by other threads?