How to Insert a Column without Affecting Formulas in Excel (2 Ways)

In the following dataset, we will count the Total Sales from 2015 to 2020 for different months. However, we have missed including the Sales for 2016. So, we need to insert a column between the data sets. To do so, we will take two approaches: applying the INDIRECT function and the INDEX function.

Method 1 – Apply the INDIRECT Function to Insert Column without Affecting Formulas 

Step 1: Use the INDIRECT Function

• Insert the following formula of the INDIRECT function with the SUM function to sum the range C5 to G5:

Step 2: Apply the INDIRECT Function in Each Cell in a Column

• Apply the same function according to the cell references for each cell.
• The Total column will appear, as shown in the image below.

Step 3: Select a Column

• Click on the D to select the entire column.

Step 4: Insert a Column

• Right-click the mouse.
• Click on Insert.

• As a result, you can insert a new column to the left, and your newly added column will show as in the image below.
• The formula in cell I5 remains unchanged.

Step 5: Final Result

• Add the value for the year 2016 in the new column.
• It will keep the formula in cell I5 and change the value according to the formula.

Read More: Insert a Column Between Every Other Column in Excel

Method 2 – Use the INDEX Function to Insert Columns without Affecting Formulas 

Step 1: Insert the Array Argument of the INDEX Function

• To apply the INDEX function in the worksheet, use cells 1 to 65526 as an array with the following formula.

Step 2: Enter the row_num Argument of the INDEX Function

• Insert the row no. of the first reference cell C5 as 5.

Step 3: Type the column_num Argument of the INDEX Function

• Enter column_num as 3 for the reference cell C5.

Step 4: Enter the Array Argument of the INDEX Function

• Enter $1:$65536 to select the entire sheet.

Step 5: Write the row_num Argument of the INDEX Function

• Type 5 as the row number for cell G5, and as the second reference cell, row_num is 5.

Step 6: Write the column_num Argument of the INDEX Function

• Insert 7 as the column number of the second cell reference.

Step 7: Final Formula to Sum

• The formula becomes:

Step 8: Apply Formulas for Each Cell in the Column

• Repeat the pattern for the other cell references with the following formula:

(For example – cells C11 to G11)

Step 9: Select a Column

• Click on the D to select the whole column.

Step 10: Insert a Column

• Right-click the mouse and click on the Insert.

• Your new column has been added, and the formula remains the same.

• Type anything between the columns that will adjust the value while keeping the formula.

Read More: Excel Fix: Insert Column Option Greyed out

Download the Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Related Articles

• Shortcuts to Insert Column in Excel
• Cannot Insert Column in Excel

<< Go Back to Columns in Excel | Learn Excel