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.

**Table of Contents**Expand

## 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:** Insert a Column Between Every Other Column in Excel

### 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 inserting 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: **Excel Fix: Insert Column Option Greyed out

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

## Related Articles

**<< Go Back to Insert Columns | Columns in Excel | Learn Excel**