How to remove formulas in Excel in 7 practical steps
By Indeed Editorial Team
Published 2 May 2022
The Indeed Editorial Team comprises a diverse and talented team of writers, researchers and subject matter experts equipped with Indeed's data and insights to deliver useful tips to help guide your career journey.
Excel spreadsheet allows professionals to perform various procedures, including data entry, dashboard design and calculations of large data sets. It contains critical formulas for calculating simple and complex values with ease which you can add or remove whenever the need arises. Removing a calculation operator in this spreadsheet can make you lose data, which is a concern. In this article, we discuss why removing formula while retaining your data is necessary and explain how to remove formulas in Excel without losing the values.
How to remove formulas in Excel
Learning how to remove formulas in Excel can help you complete tasks faster, work with precision and appear more professional. Using an arithmetic operator in a typical worksheet appears with the cell's address bar characters. While attempting to remove them, you want to ensure that only the figures appear in the address bar after completing your task. Follow these steps to learn how to remove formulas without losing your data:
1. Identify the cells containing formulas
Proceed by detecting the cells bearing an equation. Locating these cells can be accessible in a small worksheet. Identifying equations in a workbook can be challenging when there are multiple cells and you're unsure which ones contain formulas. Discovering these cells may imply that successfully applying methods to remove a mathematical expression is feasible. Use the 'Go to special' feature to find cells bearing formulas.
Start by using the 'Ctrl + G' shortcut on the active worksheet. Tapping 'Ctrl' and 'G' keys opens the 'Go to' window containing the 'Go to' and 'Reference' boxes. Ignore the boxes and press the 'Special' button, which opens the 'Go to special' window. Select 'Formulas' and ensure to tick the 'Numbers', 'Logicals', 'Text' and 'Errors' boxes before pressing 'OK' to save your settings. Apply the various methods of removing formulas once you access the cells.
2. The 'Paste Special' function
This is perhaps the most common means of removing formula safely from a cell without damaging the worksheet. Start this procedure by locating the cells containing formulas and copying them. Use the 'Ctrl + C' shortcut to copy the cells and right-click them for copy and paste options. Find 'Paste Special' in the options and click on its arrow. Clicking this arrow opens the 'Paste Special' window. Under the 'Paste' section, tick 'Values', then 'None' in the 'Operation' section below. After concluding this process, press the 'OK' button to save your changes.
3. The 'Quick Access' method
Unlike other techniques, the procedure starts with customising the 'Quick Access Toolbar' before selecting the cells containing the formulas you intend to remove. This toolbar gives you a quick route to access specific features and settings. It includes 'Redo', 'Save' and 'Quick Print'. With this method, you want to customise the 'Quick Access Toolbar' to ensure that it contains the 'Paste Special' feature to remove formulas from cells.
To change an Excel operator requires accessing the many other commands you can only find by clicking the drop-down arrow. Find 'More Commands' in the list of commands to customise the toolbar. Select 'Paste Special' from this list in the 'Excel Options' window and press 'OK' to add the feature to the toolbar. After successfully adding the function, copy cells and remove their formulas using the new command.
4. The basic 'copy and paste' method
This is perhaps the most straightforward method for removing formulas. It only takes seconds to complete and this technique takes no more than four steps. This technique enables you to select cells and apply a relatively superior method to eliminate the formula. Performing this task gives you a cell with only figures and no formulas.
To apply this method, identify all the cells in your workbook consisting of formulas. Highlight all the cells, right-click on any of them and click 'Copy'. Alternatively, use the 'Ctrl + C' shortcut to copy the cells after highlighting them. After copying for the first time, place the cursor on the same cells and right-click for the second time for 'Paste Options'. Select the paste icon with a '123' image from the options to remove the formulas and retain only the values.
5. The 'Home' tab
The Excel ribbon contains groups and tabs that can help you separate formulas from figures. Perform the formula removal with the options in the 'Home' tab. While working on a worksheet, select or highlight the cells.
Upon selecting the cells, copy them using the 'Ctrl + C' shortcut to prepare them for pasting. Next, go to the 'Home' tab to discover various paste options. Look for 'Paste' in the clipboard group on the home page. Click the drop-down arrow under 'Paste' for different paste options, including 'Paste', 'Paste Values', 'Other Paste Options' and 'Paste Special'. Click 'Paste Values' and select the paste icon with the '123' image. Doing this removes the formulas that may exist in the cells to ensure that only numbers remain.
6. Keyboard shortcuts
There are other simple ways to complete this process, including applying keyboard shortcuts. These shortcuts are a combination of keys that helps you avoid excessive right-clicking while saving time. They're also ideal if you prefer using keys or have a faulty mouse. The first set of keys is 'Alt', 'S', 'E', 'V' and 'Enter'. To use this combination, identify the cells with the formulas you want to remove. Select the cells, highlight them and use the 'Ctrl + C' shortcut to copy the contents of the cells.
After copying the cells, use the 'Alt + S + E + V' shortcut by pressing the 'Alt', 'S' and 'E' keys to open the 'Paste Special' window. Next, press the 'V' key to select the values and the 'Enter' key to complete the process. This removes the formulas from the cells, leaving only the figures. Alternatively, use the 'Ctrl + Alt + V + Enter' combination. After copying the cells, press 'Ctrl', 'Alt' and 'V' keys simultaneously, push the 'V' key again and press 'Enter'. This process produces a cell of values without formulas.
7. Move the cells across the worksheet
This is one of the methods that only takes seconds to complete. It involves adjusting the position of the cells bearing formulas. Find the cells you want to edit and highlight them to start this process. Doing this makes it possible to change their position permanently. Excel provides editing or repositioning options when you select cells and drag them around the worksheet. Highlighting these cells changes the cursor from a single direction arrow to the arrow facing four directions.
With this arrow, you can move the cells or their content. Drag the cells with this arrow to the right side slightly. While you drag the cells, ensure that you're holding the right key of the mouse. Next, move the cells to the left before taking your hand off the right key of the mouse. Upon releasing the mouse's right key, a window opens with various editing options, including 'Move Here', 'Copy Here', 'Link Here' and 'Copy Here as Values Only'. Select the 'Copy Here as Values Only' option to remove the formula.
Deleting vs. removing formula
There may be a period when formula ceases to perform its primary function and a change is necessary. You may decide to remove or completely delete the calculation operator when this happens. It's easier to highlight an Excel operator, then click 'delete' to erase it once and for all. Deleting a mathematical code is different from removing one as it affects the formula and the results it produces.
Many professionals resist removing an Excel formula to avoid ruining their spreadsheet. Taking off the scientific code is the better option if you intend to keep the data. Removing equations in Excel can keep them from malfunctioning while helping you retain data. If you have no use for the formula or its values, deleting it is ideal. Choosing to delete or remove a scientific code depends on your current needs and objectives.
Reasons for keeping data while removing formulas in Excel
Here are the reasons why retaining your data after taking out a calculation method may be crucial to the outcome of your projects:
Speed: Removing the formula without erasing the numbers can help you complete your task faster, as some techniques only take seconds to apply. Also, the file size reduces when there are fewer maths operators in the worksheet, enabling the computer to process data or load faster.
Professionalism: Part of being professional is completing a task without showing the rough draft. Displaying the formula after completing your workbook can be unnecessary and untidy.
Confidentiality: When working on an assignment, revealing the formula for arriving at your final results may be needless. Removing the formula helps you maintain discretion and lets the recipient see only the figures.
Please note that none of the companies, institutions or organisations mentioned in this article are affiliated with Indeed.
Explore more articles
- Marketing project management: definition, tools and benefits
- What is typesetting? (Plus its importance and tips)
- What are Agile strategies? (Plus how to implement them)
- People management: 7 tips to consider when managing staff
- What is a strategy map and how to use it in business?
- How to insert a text box in Google Docs in different ways
- 7 useful tips for mediators (with definition and FAQs)
- 15 construction technology jobs (with duties and salaries)
- Examples of digital strategy (with components and tips)
- A comprehensive guide to parametric estimating (with tips)
- What is surveying in engineering? Definition and job details
- What are methodologies? Definition, explanation and examples