Pinterest Pixel

3 Quick Ways to Remove Formulas in Excel

Bryan
There are times when I have an Excel worksheet full of formulas and I want to hard code the results and Excel remove formula completely.  This is very easy to do! Keep reading to know more.
Removing formulas and keeping the data in Excel may be necessary because of the following reasons: You don't require the value to be linked to any other cells and want to display just the value.

It will help you speed up your workbook.

You may have confidential information inside the formula that you don't want to disclose and it is best to Excel remove formula.

Whatever be the reason, sometimes it becomes extremely essential to remove formulas from your Excel workbook and show just the values.

Key Takeaways

  • You can remove formulas in Excel without deleting the underlying data using methods such as right-clicking the cell, using options in the Home tab, or employing keyboard shortcuts, making it easier to share reports without disclosing formula details.
  • Reducing formulas in Excel can also help in decreasing the file size, which can be particularly advantageous when working with large spreadsheets or when sharing files with others who may have limited system resources or storage space.
  • To further enhance Excel proficiency and learn more about managing formulas, there are additional resources such as tutorials on mastering Excel formulas, understanding why formulas may give incorrect answers, and applying formulas across entire columns.

Here is our sample worksheet which has the following formulas in Column E:

3 Quick Ways to Remove Formulas in Excel

Instead of showing the formula =REPLACE(D9, 1, FIND(“-“,D9), “”) in the address bar, you want the value i.e. the new phone number to be displayed.

Remove Formula using Right Click

Download this Excel Workbook and follow along to understand how to Excel remove formula from your worksheet:

download excel workbookHow-to-Remove-Formulas-in-Excel.xlsx

 

STEP 1: Select all the cells that have formulas:

3 Quick Ways to Remove Formulas in Excel

 

STEP 2:Right click and select Copy:

3 Quick Ways to Remove Formulas in Excel

 

STEP 3: Right click again and select Paste Values:

3 Quick Ways to Remove Formulas in Excel

Now you will see that the values are only retained and the formulas are now gone!

 

Remove Formula using the Home Tab

STEP 1: Select all the cells that have formulas:
3 Quick Ways to Remove Formulas in Excel

STEP 2: Go to the Home tab in the Clipboard group, click the arrow below Paste and then click Paste Values.

3 Quick Ways to Remove Formulas in Excel

Now, you will see that the cells don’t have formulas anymore. It has been replaced by values.

3 Quick Ways to Remove Formulas in Excel

Lastly, you can even use keyboard shortcuts to convert formulas to value.

 

Find & Remove Formula using Keyboard Shortcuts

In the above scenarios, you already knew which cells contain formulas.

Let’s go to an example where you have to first find the cells containing formulas and then learn how to remove formulas in Excel using keyboard shortcuts.

In the data below, the sales price for some product have been calculated using formulas and some are constant values.

3 Quick Ways to Remove Formulas in Excel  3 Quick Ways to Remove Formulas in Excel

You have to remove the formulas from your Excel Worksheet. Follow the steps below to learn how to remove formula in Excel shortcut:

STEP 1: Press Ctrl + G to open the Go To dialog box and then select Special.

3 Quick Ways to Remove Formulas in Excel

STEP 2: Select Formulas for the list and click OK.

3 Quick Ways to Remove Formulas in Excel

All the cells containing formulas will be selected.

STEP 3: Highlight these cells with color.

3 Quick Ways to Remove Formulas in Excel

The cells will be highlighted in yellow!

3 Quick Ways to Remove Formulas in Excel

STEP 4: Press Ctrl + C to copy the cells.

3 Quick Ways to Remove Formulas in Excel

STEP 5: Press Alt+E+S to open the Paste Special option dialog box.

3 Quick Ways to Remove Formulas in Excel

 

STEP 6: Press V to select values and click OK.

3 Quick Ways to Remove Formulas in Excel

The formula has now been replaced with value.

3 Quick Ways to Remove Formulas in Excel | MyExcelOnline

STEP 7: Repeat the same steps for other highlighted cells.

3 Quick Ways to Remove Formulas in Excel

Additional Tips and Tricks for Managing Excel Data

Avoid Common Mistakes When Removing Formulas

When you’re geared up to remove formulas, take care to avoid some common pitfalls. Double-check that you have selected the correct range of cells; an accidental slip-up here could lead to losing precious data. It’s important to ensure that the values you wish to retain are not dependent on other cells that you’re altering. Also, keep in mind the far-reaching impact of your changes. If other worksheets or workbooks are linked to the data you’re modifying, removing formulas could disrupt the entire system. Lastly, remember to verify your data after removal. Without the underlying formulas, some data might not be updated correctly if changes are made elsewhere later on.

Tools in Excel to Aid Formula Removal

Excel offers several built-in features to streamline formula removal. ‘Paste Special’ is your go-to feature for this task—it allows you to copy the data and then paste only the values, leaving the formulas behind. The ‘Find & Select’ tool is handy for highlighting all the cells that contain formulas, which you can then manipulate in bulk. Also, Excel’s ‘Go To Special’ function can specifically target cells with formulas, making them easier to identify and remove. Remember, while these tools are powerful, using them with understanding and caution will ensure you preserve your data accurately.

Excel Paste Special: A Values Multiplier

Integrating Other Excel Solutions Post-Formula Removal

Formatting and Presenting Your Data Effectively

Once the formulas are out of the picture, your data is ready for the spotlight. To make your dataset shine, focus on the formatting. Apply consistent fonts, align your text, use borders and shading, and choose appropriate number formats—like currency or percentages where needed. Excel tables and conditional formatting are your allies, offering a structured and visually appealing way to display data. And don’t forget to use charts and pivot tables for those high-impact visual summaries that help convey the story behind the numbers.

3 Quick Ways to Remove Formulas in Excel | MyExcelOnline

Leveraging Excel Functions for Future Data Analysis

After removing formulas and securing your data, you might still need to perform future analyses. This is where Excel’s function library becomes invaluable. Functions like SUM, VLOOKUP, and IF remain ready to help you analyze fresh data without hassle. These power players will help you swiftly sort through data, make conditional comparisons, and total up figures across your datasets with accuracy. Knowing how to use these functions effectively will keep you ahead in data analysis, making Excel an even more powerful tool in your arsenal.

FAQs

How do I ensure that only formulas are removed and not data?

To ensure that only formulas are removed and not data, use Excel’s ‘Paste Special’ function. Firstly, copy the cells with formulas, then paste using the ‘Values’ option. This process converts formulas to static data, leaving your cell values intact without the underlying formulas.

How do I turn off formulas in Excel?

To turn off formulas in Excel, go to the “Formulas” tab, select “Calculation Options,” and switch “Workbook Calculation” to “Manual.” Remember to uncheck “Recalculate workbook before saving” if you don’t want Excel to automatically update the calculations before saving.

Can I remove formulas from multiple cells at once?

Absolutely! You can remove formulas from multiple cells at once by selecting a range of cells or multiple ranges, copying them (Ctrl+C), and then pasting only the values using the ‘Paste Special > Values’ option. This replaces the formulas with their current values throughout your selection in one swift action.

Conclusion

There are 3 ways in which you can Excel remove formula using either right-click, home tab, or keyboard shortcuts. This will help you to remove the formula without deleting the data.

You can easily reduce the size of your file or hide the formula from recipients!

If you like this Excel tip, please share it



3 Quick Ways to Remove Formulas in Excel | MyExcelOnline


Bryan

Bryan Hong is an IT Software Developer for more than 10 years and has the following certifications: Microsoft Certified Professional Developer (MCPD): Web Developer, Microsoft Certified Technology Specialist (MCTS): Windows Applications, Microsoft Certified Systems Engineer (MCSE) and Microsoft Certified Systems Administrator (MCSA).

He is also an Amazon #1 bestselling author of 4 Microsoft Excel books and a teacher of Microsoft Excel & Office at the MyExecelOnline Academy Online Course.

See also  Get the Average of Values with Excel's AVERAGE Formula

Star 30 Days - Full Access Star

One Dollar Trial

$1 Trial for 30 days!

Access for $1

Cancel Anytime

One Dollar Trial
  • Get FULL ACCESS to all our Excel & Office courses, bonuses, and support for just USD $1 today! Enjoy 30 days of learning and expert help.
  • You can CANCEL ANYTIME — no strings attached! Even if it’s on day 29, you won’t be charged again.
  • You'll get to keep all our downloadable Excel E-Books, Workbooks, Templates, and Cheat Sheets - yours to enjoy FOREVER!
  • Practice Workbooks
  • Certificates of Completion
  • 5 Amazing Bonuses
Satisfaction Guaranteed
Accepted paymend methods
Secure checkout

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...