How to Remove Vlookup Formula in Excel (6 Methods)

We will use the following dataset to explain how you can remove a VLOOKUP formula from a cell or table.

Method 1 – Use Ribbon Options to Remove the VLOOKUP Formula

Steps:

• The VLOOKUP formula is in cell G7.

• Click on Cell F7.
• Select Copy from the Clipboard group.

• Go to Paste.
• Select Values(V) from Paste Values.

• Click on Cell F7 to show that the formula has been removed.

Method 2 – Keyboard Shortcut to Search and Remove VLOOKUP in Excel

Steps:

• Press Ctrl + G. The Go To dialog box will appear.
• Select Special.

• Choose Formulas.
• Press OK.

• To view the cells with the VLOOKUP formula specifically, change the color of the fonts from the Font group.

• Cell F7 is the cell that contains a formula. Click on that cell.
• The formula bar shows the VLOOKUP function.

• Press Ctrl + C to copy that cell.

• Press Alt + E + S, and a new dialog box will appear named Paste Special.

• Select the Values option from the Paste option.
• Press OK.

• Click on Cell F7 to see that the formula is gone.

Method 3 – Use the Context Menu to Remove the VLOOKUP Formula

Steps:

• Click on Cell F7 which contains a VLOOKUP formula.

• Right-click on the cell and press Copy from the list.

• Right-click and choose Values(V) from Paste Options.

• Click on Cell F7 to check if the formula has been removed.

Method 4 – Copy Values to Remove a VLOOKUP Formula

Steps:

• Select Cell F7, which contains a VLOOKUP formula.

• Move the cursor to the right edge of the cell.
• Right-click and hold.

• Move the cursor to the adjacent cell on the right side.
• Release the right-click.
• A list will show. Select the Copy Here as Values Only option.

• Click on Cell G7 to confirm the formula is gone.

Method 5 – Remove the VLOOKUP Formula from Multiple Sheets

Here, we have two sheets named Worksheet_1, and Worksheet_2. Both of the worksheets contain a VLOOKUP formula on cell F7.

Steps:

• Select both sheets using the Shift button.

• Click on Cell F7 of any of the sheets. The VLOOKUP formula is present in that cell.
• Press Ctrl + C to copy that cell.

• Right-click and select Values(V) from the Paste options.

• Click on Cell F7 of the Worksheet_1.

The formula has vanished from the cell.

• Go to the Sheet name bar.
• Right-click.
• Select the Ungroup Sheets option.

• Click on Cell F7 of the Worksheet_2.

No formula exists on this cell.

Method 6 – VBA Macro to Remove VLOOKUP Formula in Excel

We will consider the below sheet with two VLOOKUP formulas in cells G5 and G6.

Steps:

• Go to the Developer tab.
• Click on the Record Macro option.
• Put the name “Remove_Vlookup_Formula” on the Record Macro box.

• Click on Macros.
• Select the Remove_Vlookup_Formula macro and click on Step Into.

• Paste the following VBA code in the command window.

Sub Remove_Vlookup_Formula()
Dim Location As Range
Dim Sheet_property As String
Sheet_property = InputBox("Enter Desired Sheet Name: ")
Set Location = Sheets(Sheet_property).Cells
Location.Copy
Location.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub

• Click the play button from the main tab to run the code, or press F5.

• An input box will appear. Input the desired sheet name Sheet_VBA.

• Click on Cell G5 to see the result.

Related Articles

• 10 Best Practices with VLOOKUP in Excel
• 7 Practical Examples of VLOOKUP Function in Excel
• VLOOKUP Example Between Two Sheets in Excel
• How to Use Dynamic VLOOKUP in Excel
• Transfer Data from One Excel Worksheet to Another Automatically with VLOOKUP
• How to Make VLOOKUP Case Sensitive in Excel
• VLOOKUP from Another Sheet in Excel
• How to Use VLOOKUP Formula in Excel with Multiple Sheets
• How to Apply VLOOKUP to Return Blank Instead of 0 or NA
• How to Hide VLOOKUP Source Data in Excel
• How to Copy VLOOKUP Formula in Excel

<< Go Back to Excel VLOOKUP Function | Excel Functions | Learn Excel