Columns and Rows | Delete

Deleting rows or columns from 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 Delete Rows/ Columns commands do allow you to delete rows and columns even if they pass through arrays and merged cells.

Why would you want to use it?

Many financial models are based on templates, intended as the starting point for models of  a variety of deals.  Sometimes the templates contemplate the possibility of a long project but you are modelling a short project.  Chopping out the unnecessary columns will make the model smaller, quicker to load, quicker to calculate, and easier to email.

However, the standard cell delete operation built into Excel will not allow the deletion to take place if the row or column intersects an array, data table, or a merged cell.

The OAK Delete Rows/ Columns command overcomes this obstacle.  You can delete a row or a column that passes through an array, data table (though not a pivot table), or merged cell.

How to use it

The OAK deletion command is used in a slightly different way from the Excel delete function.  You must first select the entire row or column you would like to delete, and then use the OAK4 | Worksheet | Delete Rows/Columns . You can select more than one row or column to delete at a time.

HOW IT WORKS

OAK's delete 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 deletion in the usual way; and then restoring all the merged 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.

Deletion 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 deletion commands work.