How to Remove Vlookup Formula in Excel (6 Methods)

Vlookup is one of the most significant functions of Microsoft Excel. We use this function to look up any object from a table or a range. An Excel file containing the Vlookup formula takes lots of time to load. Removing Vlookup also reduces the file size. So, we may want to remove the Excel Vlookup formula in such a way that the value remains unchanged. This article will light on 6 methods to remove the Vlookup formula in Excel.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

6 Methods to Remove Vlookup Formula in Excel

We will discuss several methods that will help users to withdraw the Vlookup function without hampering the values. We will use the below data set to explain the methods. This data set contains the Vlookup formula to get the outcome.

1. Use Ribbon Options to Remove Vlookup Formula

We will show the ribbon shortcut to remove the Vlookup formula in Excel.

Step 1:

• Go to Cell F7, which contains the come. In the function bar, the Vlookup formula is shown.

Step 2:

• Now, click on Cell F7.
• Then select Copy from the Clipboard group.

Step 3:

• Now, go to Paste from the Clipboard group.
• Select Values(V) from the Paste Values.

Step 4:

• Again, click on Cell F7. Now, look at the formula bar.

No formula is showing now. That formula has been removed from that cell.

Read More: How to Clear Formula in Excel (7+ Methods)

2. Keyboard Shortcut to Search and Terminate Vlookup in Excel

We will first search the Vlookup formula and then terminate that from Excel. We do not need to identify the formula manually.

Step 1:

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

Step 2:

• Choose Formulas from the Go To Special box.
• Then press OK.

This process identifies the cells holding Vlookup formulas.

Step 3:

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

Step 4:

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

Step 5:

• Now, press Ctrl+C to copy that cell.

Step 6:

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

Step 7:

• Select the Values feature from the Paste option.
• Then press OK.

Step 8:

• Again, click on Cell F7. Observe the formula bar again.

In the formula bar, no formula appears. Only a single value is showing.

3. Use Context Menu to Withdraw Vlookup Formula

In this section, we will show a simple mouse shortcut to withdraw the Vlookup formula.

Step 1:

• First, click on Cell F7 which contains a Vlookup formula.

Step 2:

• Now, press the right button of the mouse. Then press Copy from the list.

Step 3:

• Again, press the right button of the mouse.
• Choose Values(V) from the Paste Options.

Step 4:

• Again, click on Cell F7.

Now, notice the formula bar. The Vlookup formula is no longer there. But the value remains the same.

Similar Readings

• How to Remove Blank Rows in Excel Pivot Table (4 Methods)
• Delete Empty Rows and Columns in Excel VBA (4 Methods)
• How to Remove Drop Down Arrow in Excel (6 Methods)
• Remove Excel Add-In (3 Quick Ways)
• How to Remove Watermark in Excel (3 Ways)

4. Alternate Way Using the Mouse Trick to Remove Vlookup

We will show another way to remove the Vlookup formula using the mouse shortcut.

Step 1:

• First, select Cell F7 that contains a Vlookup The formula bar is indicating the Vlookup formula.

Step 2:

• Now, set the cursor on the marked position. Then press the right button of the mouse. Keep pressing the right button.

Step 3:

• Move the courser to the adjacent cell on the right side.
• Then, a list will show. Select the Copy Here as Values Only option.

Step 4:

• Now, click on Cell G7. Look at the formula bar.

No formula is showing in the formula bar, though we copy a cell holding a formula.

5. Remove Vlookup Formula from Multiple Sheet

We want to remove Vlookup from multiple sheets at a time. This is possible if the formula contains in the same cell of the selected sheets.

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

Step 1:

• First, select both sheets using the Shift button.

Step 2:

• Now, click on Cell F7 of any of the sheets. The Vlookup formula is present on that cell.
• Press Ctrl+C to copy that cell.

Step 3:

• Now, press the Right button of the mouse to Paste the value.
• Select Values(V), from the Paste options.

Step 4:

• Now, click on Cell F7 of the Worksheet_1.

The formula has vanished from the cell.

Step 5:

• Now, go to the Sheet name bar.
• Press the right button of the mouse.
• Select Ungroup Sheets option.

Step 6:

• Now, click on Cell F7 of the Worksheet_2.

No formula exists on this cell.

Read More: How to Remove Partial Data from Multiple Cells in Excel (6 Ways)

6. VBA Macro to Remove Vlookup Formula in Excel

By using VBA Macro code we can remove all the Vlookup formulas from an Excel file at a time. No matter how many formulas exist on a sheet.

We will consider the below sheet where two Vlookup formula exists on Cell G5 and G6.

Step 1:

• First, go to the Developer tab.
• Then click on the Record Macro option.
• Put the name “Remove_Vlookup_Formula” on the Record Macro box.

Step 2:

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

Step 3:

• Now, paste the following VBA code on 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

Step 4:

• Click the marked box from the main tab to run the code. Or we can press F5 to run the code.

Step 5:

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

Step 6:

• Now, click on Cell G5.

In the below, we can see the sheet name. And in the formula bar, no formula is showing.

Things to Remember

• When you want to remove all Vlookup formulas from a single sheet at a time then apply VBA operation.
• When need to remove the Vlookup formula from multiple sheets with the same position then, apply the 6th method.

Conclusion

In this article, we discussed how to remove the Vlookup formula in Excel I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.

Related Articles

• How to Remove Checkboxes from Excel (5 Easy Ways)
• Remove Subtotals in Excel (2 Easy Tricks)
• How to Remove Decimals in Excel (13 Easy Ways)
• Remove Everything After a Character in Excel (7 Methods)
• How to Remove Timestamps from Date in Excel (4 Easy Ways)
• How to Remove Data Validation Restrictions in Excel (3 Ways)