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

## Download Practice Workbook

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

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

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 set. 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, 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 changes the value according to the formula.

**Read More: How to Insert a Column Between Every Other Column in Excel (3 Methods)**

**Similar Readings**

**Excel Fix: Insert Column Option Greyed out (9 Solutions)****Insert Column with Name in Excel VBA (5 Examples)****How to Insert a Column to the Left in Excel (6 Methods)**

### 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 do 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**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 (4 Ways)**

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

We, the **ExcelDemy** Team, are always responsive to your queries.

Stay with us & keep learning.