How to Remove Vlookup Formula in Excel (6 Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.

Use Ribbon Options to Remove Vlookup Formula

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.

Use Ribbon Options to Remove Vlookup Formula

Step 4:

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

Use Ribbon Options to Remove Vlookup Formula

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.

Keyboard Shortcut to Search and Terminate Vlookup in Excel

Step 2:

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

This process identifies the cells holding Vlookup formulas.

Keyboard Shortcut to Search and Terminate Vlookup in Excel

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.

Keyboard Shortcut to Search and Terminate Vlookup in Excel

Step 7:

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

Keyboard Shortcut to Search and Terminate Vlookup in Excel

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.

Use Context Menu to Withdraw Vlookup Formula

Step 3:

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

Use Context Menu to Withdraw Vlookup Formula

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


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.

Mouse Trick to Remove Vlookup

Step 4:

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

Mouse Trick to Remove Vlookup

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.

Remove Vlookup Formula from Multiple Sheet

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.

Remove Vlookup Formula from Multiple Sheet

Step 3:

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

Remove Vlookup Formula from Multiple Sheet

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.

Remove Vlookup Formula from Multiple Sheet

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.

VBA Macro to Remove Vlookup Formula in ExcelStep 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.

VBA Macro to Remove Vlookup Formula in Excel

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

VBA Macro to Remove Vlookup Formula in Excel

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.

VBA Macro to Remove Vlookup Formula in Excel

Step 6:

  • Now, click on Cell G5.

VBA Macro to Remove Vlookup Formula in Excel

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

Alok Paul
Alok Paul

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo