You just can’t simply delete any column in Excel which has formulas in other columns. Doing this blindly will remove your hard-written formula and data. In this article, we will discuss how you can delete the column in Excel without affecting the formula.
Download Practice Workbook
2 Easy Ways to Delete Column in Excel Without Affecting Formula
Here, we discussed how you can delete column content in Excel without affecting the cell content or formula in two separate ways. Consider the following dataset. Here we have Unit Production in column C and Per unit Cost in column. Moreover, after multiplying these two we get the total cost per week and adding all the weekly total costs we get the monthly cost. Now we want to delete columns D and C without affecting the formula in column E. In order to avoid any compatibility issues, try to use the Excel 365 edition.
1. Use Excel Go To Special Feature for Deleting Column
Excel Go To feature is a special feature that can carry versatile tasks at ease. This feature can be turned on by simply pressing a keyboard shortcut. The process is described below.
- Select all the data (C5:E9) and press F5. A Go To box will appear.
- Click on Special.
- After that, the Go To Special box will appear. Select Constants.
- Then click OK.
- So, it will select your data cell only from your selected range of cells.
- After that, press Delete. Only your selected data cell will be deleted.
- Then if you check, the formula in column E5 is still functional.
- If you give some new dummy entries in the range of cells C5:D6, you will get new values in the range of cells E5:E6.
- Lastly this means we have successfully deleted the column content without affecting the formula in cells.
Read More: How to Clear Contents in Excel Without Deleting Formulas
- How to Delete Columns Based on Header Using VBA in Excel
- [Solved!] Can’t Delete Extra Columns in Excel (3 Solutions)
- How to Delete Extra Columns in Excel (7 Methods)
- Delete Multiple Columns in Excel (5 Suitable Methods)
- How to Delete Blank Columns in Excel (4 Quick Ways)
2. Erase Column Without Affecting Formula with Paste Special Feature
If you want to keep the values of your formula cell before deleting the columns, you can use the Paste Special feature. For this, we obviously have to copy the dataset first.
- Select the range of cells E5:E9 that contains the formula and press Ctrl+C.
- Select an empty cell, like cell F5. Right-click on it.
- After that, from the context menu, select Paste Special.
- After pressing Paste Special box will appear. Select Values under the Paste option.
- Moreover, select None under the Operation option.
- Then press OK.
- Doing so will copy your values of the formula cell and paste their range of cell F5:F9.
- Now select the cells of the column you want to delete.
- In this case, it is C5:D8.
- To delete contents in cells, go to Home > Editing > Clear > Clear Contents.
- Hence, it will delete the cells of the column, but your formula values won’t be affected.
Read More: How to Delete Columns without Losing Formula in Excel (3 Easy Steps)
To sum it up, we can delete a column in Excel without affecting the formula here in two separate ways. For this problem, a workbook is available to download where you can practice these methods. Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the Exceldemy community will be highly appreciable.
- How to Delete Columns with Specific Text in Excel (3 Methods)
- Delete Hidden Columns in Excel (2 Simple Methods)
- How to Delete Empty Columns with Header in Excel (4 Easy Ways)
- Delete Multiple Columns in Excel with Condition
- How to Delete Columns in Excel That Go on Forever (6 Ways)
- Can’t Delete Blank Columns in Excel (3 Problems & Solutions)