When dealing with a huge data set, it is possible to overlook any data in a column. If you put a column between the data set at that moment, the cell references of the entire worksheet will change. So, in this tutorial, we will show you how to insert a column without affecting formulas in Excel.

## Insert Column without Affecting Formulas in Excel: 2 Effective Ways

In the image below, we have provided a data set, where we will count the **Total Sales** from the year **2015 **to **2020 **for different months. But we have missed including the **Sales **for the year **2016**. So, we need to insert a column in between the data sets. To do so, we will go through **2 **approaches by applying **the INDIRECT function**, and **the INDEX function**.

### 1. Apply INDIRECT Function to Insert Column without Affecting Formulas in Excel

In the following section, we will count the total value by applying **the SUM function**. Additionally, we will insert the** INDIRECT **function nested with the **SUM** function as the cell references remain intact. To accomplish the task, follow the simple steps below.

**Step 1: Use the INDIRECT Function**

- Firstly, insert the following formula of the
**INDIRECT**function with the**SUM**function to sum the range**C5**to**G5**.

`=SUM(INDIRECT("C5: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.
- Therefore, 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**

- Then,
**right-click**the mouse. - Click on
**Insert.**

- As a result, you will be able to
**insert a new column to the left**and your newly added column will show like the image below. - Besides, the formula in cell
**I5**remains unchanged.

**Step 5: Final Result**

- Add the value for the year
**2016**in the new column. - Therefore, it will still keep the formula in cell
**I5**and change the value according to the formula.

**Read More: How to Insert a Column Between Every Other Column in Excel**

**Similar Readings**

### 2. Use INDEX Function to Insert Column without Affecting Formulas in Excel

By applying the **INDEX **function, we can achieve the same task as we did before to insert a column without affecting formulas. Follow, the simple task to apply the function.

**Step 1: Insert the Array Argument of INDEX Function**

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

`=SUM(INDEX($1:$65536,`

**Step 2: Enter the row_num Argument of the INDEX Function**

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

`=SUM(INDEX($1:$65536, 5`

**Step 3: Type the column_num Argument of the INDEX Function**

- Enter
**column_num**as**3**for the reference cell**C5**.

`=SUM(INDEX($1:$65536,5,3)`

**Step 4: Enter the Array Argument of INDEX Function**

- Similarly, write
**$1:$65536**to select the entire sheet.

`=SUM(INDEX($1:$65536,5,3):INDEX($1:$65536,`

**Step 5: Write the row_num Argument of the INDEX Function**

- Type
**5**as the row number for cell**G5**, as the second reference cell,**row_num**is**5**.

`=SUM(INDEX($1:$65536,5,3):INDEX($1:$65536,5`

**Step 6: Write the column_num Argument of the INDEX Function**

- Then, insert
**7**as the column number of the second cell reference.

`=SUM(INDEX($1:$65536,5,3):INDEX($1:$65536,5,7)`

**Step 7: Final Formula to Sum**

- Therefore, the formula becomes.

`=SUM(INDEX($1:$65536,5,3):INDEX($1:$65536,5,7))`

**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*)

`=SUM(INDEX($1:$65536,11,3):INDEX($1:$65536,11,7))`

**Step 9: Select a Column **

- Now, click on the
**D**to select the whole column.

**Step 10: Insert a Column**

- Then,
**right-click**the mouse and click on the**Insert**.

- Therefore, your new column is added and the formula remains the same.

- So, write anything in between the columns, that will adjust the value with keeping the formula.

**Read More: How to Insert Column with Excel VBA**

**Download Practice Workbook**

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

## Conclusion

To conclude, I hope this article has given you some useful information about how to insert a column without affecting the formula in Excel. All of these procedures should be learned and applied to your dataset. Take a look at the practice workbook and put these skills to the test. Weâ€™re motivated to keep making tutorials like this because of your valuable support.

If you have any questions â€“ Feel free to ask us. Also, feel free to leave comments in the section below.

Stay with us & keep learning.