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

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.


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.

Sample Data

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

Effective Ways to Insert Column without Affecting Formulas in Excel

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.

Effective Ways to Insert Column without Affecting Formulas in Excel

Step 3: Select a Column

  • Click on the D to select the entire column.

Effective Ways to Insert Column without Affecting Formulas in Excel

Step 4: Insert a Column

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

Effective Ways to Insert Column without Affecting Formulas in Excel

  • As a result, your newly added column will show like the image below.
  • Besides, the formula in cell I5 remains unchanged.

Effective Ways to Insert Column without Affecting Formulas in Excel

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.

Effective Ways to Insert Column without Affecting Formulas in Excel

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


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 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,

Effective Ways to Insert Column without Affecting Formulas in Excel

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

Effective Ways to Insert Column without Affecting Formulas in Excel

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)

Effective Ways to Insert Column without Affecting Formulas in Excel

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,

Effective Ways to Insert Column without Affecting Formulas in Excel

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

Effective Ways to Insert Column without Affecting Formulas in Excel

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)

Effective Ways to Insert Column without Affecting Formulas in Excel

Step 7: Final Formula to Sum

  • Therefore, the formula becomes.
=SUM(INDEX($1:$65536,5,3):INDEX($1:$65536,5,7))

Effective Ways to Insert Column without Affecting Formulas in Excel

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

Sample Data

Step 9: Select a Column

  • Now, click on the D to select the whole column.

Sample Data

Step 10: Insert a Column

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

Sample Data

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

Sample Data

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

Sample Data

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.


Related Articles

Bhubon Costa

Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

We will be happy to hear your thoughts

Leave a reply

5 Excel Tips
You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo