How to Hide VLOOKUP Source Data in Excel (5 Easy Ways)

Often, it is desired to hide the source data in Excel. This will keep the users from accidentally or willingly editing the source data. In this article, we will show you five quick approaches to hide VLOOKUP source data in Excel.


Download Practice Workbook


5 Handy Approaches to Hide VLOOKUP Source Data in Excel

To demonstrate the methods, we have picked a dataset with 3 columns consisting of “Name“, “Gender“, and “Height (cm)“. This data represents information of 6 employees from a particular retail store.

We will add another column to it and use the VLOOKUP function there to return a value from a data source. The first 4 methods will show you how to hide source data within the same Sheet. Then the last method will show you how to hide source data when it is in a different Sheet.

Hide VLOOKUP Source Data in Excel 1


1. Hiding VLOOKUP Source Data by Changing Font Color

In this section, we’ll discuss how to conceal VLOOKUP source data in Excel by matching the font and background colors.

Steps:

  • Firstly, add a new column named “City”.
  • Next, type the following formula in cell E5, and AutoFill the rest of the cells using the Fill Handle. Here, the VLOOKUP data source is in the cell range B13:C21 (including the header rows).

=VLOOKUP(B5,$B$16:$C$21,2,0)

  • This formula matches the value from cell B5 in the cell range B16:C21. If it finds a match, then it returns the corresponding value from the second column, as denoted by 2 in the formula. Lastly, there is a 0 in the formula which indicates an exact match for the value.

Hide VLOOKUP Source Data in Excel 2

  • Then, select the cell range B13:C21, and from the Home tab → Font Color → select White.

Hide VLOOKUP Source Data in Excel 3

  • Doing so, the VLOOKUP data source will be hidden.

Hide VLOOKUP Source Data in Excel 4


2. Applying Custom Cell Format

We’ll use a Custom Cell Format for the second technique to hide the values of the source data in Excel.

Steps:

  • To begin with, select the cell range B13:C21.
  • Then, press CTRL+1. This will bring up the Format Cells window.

Hide VLOOKUP Source Data in Excel 5

  • Afterward, from the Category section → select Custom.
  • Then type triple semicolons “;;;” and press OK.

Hide VLOOKUP Source Data in Excel 6

  • Thus, it will hide the VLOOKUP source data in Excel.

Hide VLOOKUP Source Data in Excel 7


3. Hiding Rows to Conceal VLOOKUP Source Data

In the third approach, we will simply select and right-click the rows and choose the Hide option to conceal the data source.

Steps:

  • At first, select rows 13 to 21 and right-click on them. This will bring up the Context Menu.

Hide VLOOKUP Source Data in Excel 8

  • Lastly, choose Hide from the list of options.

Hide VLOOKUP Source Data in Excel 9

  • Thus, we have hidden the source data of the VLOOKUP function.

Hide VLOOKUP Source Data in Excel 10


4. Grouping VLOOKUP Source Data

In this fourth method, we will group all rows from the data source to achieve our goal of hiding VLOOKUP source data.

Steps:

  • First, select the cell range B13:C21.
  • Afterward, from the Data tab → select Group from the Outline section.

Hide VLOOKUP Source Data in Excel 11

  • Then, a dialog box will pop up.
  • Select Rows and press OK.

Hide VLOOKUP Source Data in Excel 12

  • Then, a minus (“”) sign will appear on the Excel file.
  • Click on it.

Grouping Source Data

  • Doing so, there will be a plus sign (“+”) in the Excel file and the source data will also be hidden.

Output Method 4


5. Changing Visible Property of Source Data Sheet

In this last method, our source data is on another Sheet named “Source” and we will change the Worksheet Visible property to xlSheetVeryHidden from the VBA window to hide the VLOOKUP source data.

Steps:

  • We have changed the formula to refer to another Sheet. Our goal is to hide this Sheet.
  • This is the modified formula that we typed in cell E5.

=VLOOKUP(B5,Source!$B$14:$C$19,2,0)

Method 5 Dataset

  • Then, press ALT+F11 to bring up the VBA Module window.
  • After that, select Sheet7 (Source).
  • Then, from the Properties pane, select “2 – xlSheetVeryHidden“.

Method 5 Hide Sheet

  • Next, go to the Workbook and it will show there is no Sheet no unhide. Thus, we can hide the VLOOKUP source data in another way.

Unhide Option is Grayed Out


Practice Section

We have added a practice dataset in a Sheet named “DIY” to the Excel file. So that you can follow along with our methods easily.

Practice Dataset


Conclusion

We have shown you 5 quick ways to hide VLOOKUP source data in Excel. If you face any problems regarding these methods or have any feedback for me, feel free to comment below. Moreover, you can visit our site ExcelDemy for more Excel-related articles. Thanks for reading, keep excelling!


Related Articles

Rafiul Haq

Rafiul Haq

Hello! This is Rafiul. I have an engineering degree and an MBA (finance) degree. I am passionate about all things related to data, and MS Excel is my favorite application. I want to make people's lives easier by writing easy-to-follow and in-depth Excel and finance related guides here at ExcelDemy.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo