XLplus Add-On: Interval Insert
This XLplus Add-Ons allows inserting columns, rows and / or functions in a specified interval.
The tool is created as an easy to use Wizard which supports you in creating your tasks. Using its default settings will insert a function every second column. Using its options you can adjust what and where to insert.
What:
Specify what to insert. You can insert an entire Column or Row. These options will move content right or below of it away. Alternatively, insert Functions either in existing Columns or Rows.
By default a single column or row will be inserted but you can specify any Amount of cells to insert.
Where:
Specify, where columns, rows or functions will be inserted. By default, the tool will identify a contigeous region of data based on the active cell. The regions´ First Cell as well as Last Cell will be noted in the corresponding text boxes. The Region will be selected for you. The default setting will use the Actual Selection.
In order to adapt the current settings click on Active Sheet. This will enable the options First Cell and Last Cell.
You can click 
in order to pick a new First Cell or Last Cell.
You can use 
in order to have the tool identify the next region of contigeous data based on the current First Cell.
Use 
in order to select until the last cell filled with data.
Note: Changing the data range will reset a previously defined Interval!
Interval:
Specify the Interval to use. For example, specify 3 to insert every third column.
You can use the lists for Columns or Rows provided to identify the interval by column or row headings used.
Format:
You can specify Format Settings for the data inserted. Formats can be assigned for background-color of cells as well as fonts.
Function:
Using Function you specify if and what function will be inserted.
When inserting Columns or Rows option No Function is selected. Obviously, this option is not selectable when you insert a Function. A function can be used from a cell of your spreadsheet. Simply select the cell and the referenced function will be used. Alternatively, select from the list of common functions provided.
The function selected will use the data range specified on Tab Where. Depending on your data range you might want to instruct the tool to adapt the data range to use within the function. In case you are inserting functions by rows, your first column of the data range might be a heading. In this case the function ought not to use this cell. Select First Column is a Headline in order to skip using this column within the function.
Example:
Assume the following data range to be given:
Once you specified Where using default settings the entire data range was selected. You intend inserting Columns each eight column containing a SUM formula.
In order to have the SUM formula correctly calculate totals per row you specify First Column is a Headline and First Row is a Headline. This will result in functions like =SUM(B2:G2).
Note:
Since the entire data range we use starts with a row containing a headline, too, we additionally specify: When Inserting Function, consider: - First Row is a Headline. Doing so will prevent writing a function within the first row.
We additionally adjusted the data range on Tab Where a little bit - this allows pasting a function after the last column of the data range, too. We specified O4 as Last Cell. The result looks like the next picture.
Finally you can type your own function into the text box. Please note that the function has to be typed using your current MS Excel language and regional settings (simply type it as you would do in Excel directly).
Note:
You can ease up function creation in case you specify an adapted data range. In case of the example data range you will have to follow the proposed way and apply settings for function creation and placement. In case your data range consists of one single column containing a headline you should exclude this from the data range.
Examples:
Insert Columns:
Using a given table as shown in the following picture we want to insert columns after each second column.
We specify Columns on Tab What and keep the given value for Amount unchanged. This will insert 1 column at the specified interval.
On Tab Where we specify the data range as shown in the picture above by setting First Cell to C3. The new data range will be adjusted and selected immediately.
On Tab Interval we specify an Interval of 3 which will insert one column every third column.
On Tab Format as well as on Tab Function we do not specify anything.
Running these settings with OK will change the table to the second table shown in the picture above.
Insert Functions:
Using the prepared table from the first example we can insert functions in the previously inserted columns. We will insert the AVERAGE function and the result might look like the next picture.
We specify Function and By Column on Tab What. This will insert a function at the specified interval.
On Tab Where we specify the data range by setting First Cell to C14 and Last Cell to R24. Using a cell outside the data range will instruct the tool to insert a function after the last column. Please recognize, that when inserting columns inclusive a function the last cell would be Q24. I hand over to you identifying the rules for the cell to choose ...
On Tab Interval we specify an Interval of 3 which will insert a function every third column.
On Tab Format we specify a background-color and various font-settings.
On Tab Function we select the AVERAGE function from the list of predefined functions. In case our data range consists of row 14 as well we additionally instruct the tool to create a formula which does ignore this row (First Row is a Headline). We additionally do not want to insert a function in row 14 so we keep the already selected option First Row is a Headline in the Consideration section of this Tab.
Running these settings with OK will change the table to the second table shown in the picture above.
Cross Calculations:
Starting with a simple table like shown below we will insert columns and rows plus a SUM function.
We specify to insert Columns first. The data range will be set by specifying First and Last Cell.
We specify using every 3rd column as Interval, specify Format settings and choose the SUM function which results in the table shown below.
Next we specify inserting Rows in the changed table. Again we set the data range by specifying First and Last Cell.
We specify using every 3rd row as Interval, specify Format settings and choose the SUM function which results in the table shown below.
Tip: Use the XLplus Add-On: Interval Select Wizard in order to select inserted cells, i.e. for applying a new format.