Columns and Rows | Insert

Inserting rows or columns into a spreadsheet is such a basic operation that Excel offers a really quick short cut for it.

Just one problem: the command doesn't work if any of the rows or columns to be inserted intersect any cells that

OAK's Insert Rows/ Columns commands do allow you to insert rows and columns through arrays and merged cells.  In addition, the Insert Rows/ Columns command has an option which copies formulas into the newly inserted region; this option will only be of interest to users who follow good practice in developing models and use left-to-right, or top-to-bottom, consistent formulas.

Why would you want to use it?

Spreadsheet users often find they need to extend the width of a model.  Perhaps the project lifetime has been extended or the initial estimated horizon was too short.  When you encounter such a problem, you typically have two choices: to manually extend the model into the new columns or rows, or to insert new columns or rows through the middle of the model.

The latter approach means that you must insert a row or a column through existing formulas, but this has the enormous benefit of extending any named ranges through which the insertion was made.  The former approach, by extending formulas at the end of the model, does not do this, which means that many named ranges must be recreated.

Therefore, insertion appears to be the way forward.  However, there are two drawbacks to using the standard Excel insert commands.  One is that Excel will not allow the insertion to take place if the row or column intersects an array, data table, or a merged cell.  The other is that you must copy the formulas and values into the inserted region.

The OAK Insert Rows/ Columns command removes both of these obstacles.  You can insert a row or a column through an array, data table (though not a pivot table), or merged cell, and you are offered the option to copy all the formulas across into the newly inserted region.

How to use it

The OAK insertion command is used in a slightly different way from the Excel insert function.  You must first select the entire row or column you would like to insert, and then use the appropriate OAK command.  You can select more than one row or column to insert or delete at a time.  There are useful shortcut keys for selecting a column (Ctrl+Space) or row (Shift+Space).

When you use the OAK Development | Worksheet | Columns and Rows | Insert command, a secondary dialog is displayed. This dialog asks whether you want formulas to be copied from the left or from above into the newly created row or column.  This is a very useful option if your formulas are left-to-right or top-to-bottom consistent.

If you click the ‘Yes’ button, the row or column is inserted and the formulas are copied across consistent with the adjacent formulas.  Note, however, that constants are not copied across – you must enter them explicitly.

If you click the ‘No’ button, the row or column is inserted, but is left blank.

Clicking the ‘Cancel’ button causes the dialog to disappear without doing anything.

The Insert Rows/ Columns command works exactly the same way when inserting multiple columns or rows.  Select the number of rows or columns to be inserted and choose the command from the menu.  Again, you have the option of copying the formulas across.

The option which allows you to copy formulas into the newly inserted rows or columns is only useful when the formulas are consistent from cell to cell in the same row or column.  This option has been included only for convenience and users of this OAK function are advised to double-check that any formulas pasted as a result of this are as required.  The following points should be noted when using the Insert and Delete Rows/ Columns commands.  

HOW IT WORKS

OAK's insert rows/columns command functions by detecting merged cells, arrays and data tables; turning them into unmerged cells with simple scalar formulas in them; having Excel perform the insertion in the usual way; and then restoring all the merger cells, arrays and data tables.

What you could do if you didn't have OAK

There is no obstacle in principle to doing the things that OAK does by hand through the Excel user interface.  But it would be tiresome and difficult to do accurately.

Wrinkles

When inserting a row or column within an array, it will be likely that because a similar change may not have been implemented on the “area” of cells to which the array formula refers, “#N/A” errors may result within the array.

Insertion of rows or columns within an array will mean that any previously left-to-right or top-to-bottom consistent (scalar) formulas that refer to the cells (by use of cell reference instead of names) within array will now not be consistent and would require amendment by the user.  In the case of deleting rows or columns from an array, any left-to-right or top-to-bottom consistent (scalar) formulas which refer to cells in the array would show “#REF!” errors.

There are a couple of slight  difference between the way in which the Excel and the OAK insertion and deletion commands work.