DOWNLOAD OUR FREE
101 BEST EXCEL TIPS & TRICKS E-BOOK!
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 dont 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 dont 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.
Here is our sample worksheet which has the followingformulas in Column E:
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.
This can easily be done in Excel using either of the two methods:
- Remove Formula using Right Click
- Remove Formula using the Home Tab
- Find & Remove Formula using Excel Shortcuts
Remove Formula using Right Click
Watch it on YouTube and give it a thumbs-up!
Download this Excel Workbook and follow along to understand how to Excel remove formula from your worksheet:DOWNLOAD EXCEL WORKBOOK
STEP 1: Selectall the cellsthat have formulas:
STEP 2:Right click and selectCopy:
STEP 3:Right click again and select Paste Values:
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:
STEP 2: Go to the Home tab in the Clipboardgroup, click the arrow belowPaste and then click Paste Values.
Now, you will see that the cells dont have formulas anymore. It has been replaced by values.
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.
Lets 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.
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.
STEP 2: Select Formulas for the list and click OK.
All the cells containing formulas will be selected.
STEP 3: Highlight these cells with color.
The cells will be highlighted in yellow!
STEP 4: Press Ctrl + C to copy the cells.
STEP 5: Press Alt+E+S to open the Paste Special option dialog box.
STEP 6: Press V to select values and click OK.
The formula has now been replaced with value.
STEP 7: Repeat the same steps for other highlighted cells.
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!
Make sure to download our FREE PDF on the333 Excel keyboard Shortcuts here:
You can learn more about how to use Excel by viewing our FREE Excel webinar training onFormulas,Pivot Tables,Power Query,andMacros & VBA!
Click Here To Join Our Free Formulas & Functions Webinar Training on VLOOKUP, IF, SUMIF & INDEX/MATCH & Advance Your Excel Level!
TrustpilotIf you like this Excel tip, please share it
LinkedinDOWNLOAD OUR FREE
101 BEST EXCEL TIPS & TRICKS E-BOOK!