Rafiul Haq
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.
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.
- Then, select the cell range B13:C21, and from the Home tab → Font Color → select White.
- Doing so, the VLOOKUP data source will be hidden.
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.
- Afterward, from the Category section → select Custom.
- Then type triple semicolons “;;;” and press OK.
- Thus, it will hide the VLOOKUP source data in Excel.
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.
- Lastly, choose Hide from the list of options.
- Thus, we have hidden the source data of the VLOOKUP function.
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.
- Then, a dialog box will pop up.
- Select Rows and press OK.
- Then, a minus (“–”) sign will appear on the Excel file.
- Click on it.
- Doing so, there will be a plus sign (“+”) in the Excel file and the source data will also be hidden.
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)
- Then, press ALT+F11 to bring up the VBA Module window.
- After that, select Sheet7 (Source).
- Then, from the Properties pane, select “2 – xlSheetVeryHidden“.
- 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.
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.
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!
