Abrar
We often need to remove certain data from a dataset and replace those data with new ones and while removing such data, we often want to delete a column or delete multiple columns without affecting a formula in Excel. In this article, we will talk about how we can delete a column in Excel without affecting the formula.
📁 Download Excel File
Download the practice workbook below.
Learn to Delete Column in Excel without Affecting Formula with These 2 Approaches
To demonstrate things easily, we consider a dataset where we present different products, their unit prices, quantity, and total costs in a table. The total cost is the product of unit price and quantity in the number of a particular product. So, our goal is to keep the formula in the Total Cost column constant and delete the other columns without affecting the formula. The dataset is presented below.
1. Utilizing Paste Special Feature
The Paste Special Feature in Excel allows us to make the pasting operation smoother by letting us choose which formatting we can keep while pasting values such as source formatting, destination formatting or just values. In this approach, we will use this feature and the clear contents option to delete a column without affecting the formula. The steps for this approach are as follows:
⬇️⬇️ STEPS ⬇️⬇️
- At first, we select the cells C6:D11. To keep its contents, we copy the value by right-clicking and selecting copy or by pressing Ctrl+C.
- Next, we right-click on cell G6 and select the Paste Special option.
- We select values and press OK.
- A copy of the cell range values will be present in cells G6:H11.
- Now we select the cell range C6:D11 and then go to the Editing group.
- In the Editing group, we select the Clear option, and from the drop-down menu, we select the Clear Contents option.
- The cell range will become empty whereas the formula in the Total Cost column will remain unchanged.
- The results will look like the following image.
2. Using Go To Special Feature
The Go To Special menu lies on the Find and Select option in the Editing Ribbon. We can use this menu to select constants and manipulate them. This approach is discussed as follows:
⬇️⬇️ STEPS ⬇️⬇️
- At first, we select the cell range C6:D11.
- After that, we go to the Home Menu and click on Find and Select option. A drop-down menu will appear and from the menu, we select the Go To option. Alternatively, we can press the shortcut F5 or Ctrl+G.
- A Go-To box will appear. We select the Special Alternatively, we can also select Go To Special directly from the drop-down menu.
- In the Go To Special box, we select Constants and press OK.
- Finally, we press the Delete The selected cells will become empty whereas the Total Cost column will retain the formula. The final result will be like the table below.
How to Remove Formula and Keep Values in Excel
Suppose, we want to replace the formulas in the cell range E6:E11 and keep only values in that column. To do this, we simply need to copy the cell values and then paste the copied entries as values. The steps to do this are as follows:
⬇️⬇️ STEPS ⬇️⬇️
- At first, we select the cell range E6:E11.
- After that, we copy the cell entries by pressing Ctrl+C or by right-clicking and selecting the copy option.
- Then we right-click and go to Paste Options and select values.
- The formulas in the cell range E6:E11 will go away and the cells will be replaced by only the numeric values.
📕 Read More: 5 Easy Ways to Delete Infinite Columns in Excel
📄 Important Notes
🖊️ The Clear Contents option can alternatively be accessed just by right-clicking after selecting the cell range. The Delete key also clears the contents in a cell.
🖊️ We should take care while using the Paste Special option so that we can choose the correct format when we are pasting values.
📝 Takeaways from This Article
You have taken the following summed-up inputs from the article:
📌 The Clear Contents option can be used to clear the contents in a cell or a cell range to delete the values in columns without affecting the formula in a related cell.
📌 We can also use the Go To Special menu to select the constants and delete them in a cell in a way that retains the formulas in the related cells.
Conclusion
In conclusion, these are the ways in which we can delete columns in Excel without affecting formulas. I hope that we all have acquired some insights about deleting columns without altering the formula. If you have any questions regarding the article then feel free to comment. Follow our page ExcelDen to know about more such informative Excel articles.
