Removing Formulas in Excel: How to Retain Data

After using formulas to generate desired results in Excel, you may want to remove them while preserving the data they produced. This can be for various reasons, such as saving processing power, avoiding confusion, or protecting formulas from accidental changes.

Using Keyboard Shortcuts

Control + Shift + V (For Excel for Microsoft 365 and Excel for Web)

Select the cell range containing the formulas.

Copy the selected cells with Control + C.

While keeping the range selected, press Control + Shift + V.

This shortcut pastes copied data as plain text values, removing formulas.

ALT + E + S + V + Enter (For older Excel versions)

Select the cell range with formulas.

Copy the cells with Control + C.

Keep the range selected and press ALT + E + S to open the Paste Special dialog box.

Press V to select the Values option.

Hit Enter to paste the values, removing formulas.

Using Paste Special Option

Paste Special Dialog Box

Select the range with formulas.

Copy the range (Control + C).

Right-click and choose Paste Special.

Select Values and click OK.

Paste Special Option in Ribbon

Copy the range (Control + C).

Go to the Home tab.

Click the small arrow below Paste in the Clipboard group.

Choose Paste Values.

Cursor Wiggle Trick

Select the cells with formulas.

Place the cursor at the right edge.

Hold the left mouse key and wiggle the cursor to the right.

Bring the cursor back to the original column.

Release the mouse key and select 'Copy Here as Values Only' from the menu.

Using VBA (Visual Basic for Applications)

For advanced users who frequently need to remove formulas:

Removing Formulas from a Specific Range: Use VBA code to specify the range and replace formulas with values.

Removing Formulas from the Entire Worksheet: Apply VBA code to remove formulas from all cells in the active worksheet.

Removing Formulas from all Sheets in the Workbook: Extend the VBA code to iterate through each worksheet in the workbook.

Disclaimer: The provided content offers guidance on Excel functions and techniques. Users should exercise caution and ensure understanding before applying methods. Additionally, always maintain a backup of your data before making significant changes.

By employing these methods, you can efficiently remove formulas in Excel while retaining valuable data. Remember to handle VBA code with care and consider your specific needs when choosing a method.