Sometimes we need to hide the source data in Excel Pivot Table to make the dataset more protected as well as more efficient. Hiding it can prevent seeing important data from anyone having the workbook. In this article, we will learn how to hide source data in an Excel Pivot Table with some necessary steps and explanations.
How to Hide Source Data in Excel Pivot Table (With Easy Steps)
More often, if we hide data or delete the source data in the Excel pivot table, it may be recreated. Then it becomes an alarming situation for the sake of data security. Suppose we have a dataset (B4:E8). It contains the company’s customer information about the payment methods and amounts. Now, we are going to make a pivot table from this dataset and hide this source data.
Step 1: Create Excel Pivot Table
To analyze any data, we can use a powerful tool named PivotTable. It’s a summary of a dataset from which we can calculate easily and make any report quickly if needed. In the above dataset, we are going to use this tool:
- First, select the range B4:E8.
- Next, go to the Power Pivot tab.
- Now, select the PivotTable drop-down.
- Then select the From Table/Range option from that drop-down.
Note: Here we can get the PivotTable option in the Power Pivot tab by customizing the ribbon. To do that:
Right-click on the tab > Select Customize the Ribbon option > Click on All Commands > Select PivotTable > Click on Add > Select the desired Tab > Create New Group > Press OK.
- We can see a pivot table selection window.
- After that, in the window, select the Table/Range.
- Choose where we want to see the Pivot Table. Here, we have selected a New Worksheet.
- In the end, press OK.
- After pressing OK, we can see a PivotTable Fields list in a new worksheet (Sheet2).
- Furthermore, drag the Date into the Rows area and the Amount in the Values area.
- Consequently, it will create a Pivot Table in the worksheet.
Step 2: Hide Source Data in Excel
- In the beginning, right-click on the sheet Source Data from the Sheet Bar.
- Then select the Hide option from the context menu.
- Finally, we can see the dataset is hidden.
Step 3: Check for Hidden Data in Pivot Table
Now, we will check if we can still see the hidden data.
- First, if we drag the Customer option in the Rows area, we can see the hidden information still shows.
- Secondly, we select cell B4 in Sheet2 and double-click on it.
- Consequently, it will create a new worksheet (Sheet3) with hidden information.
Step 4: Apply Excel PivotTable Options
Here, we can see that although we hid the dataset, it’s still taking us to the source. To solve this issue, we are going to use PivotTable Options.
- Select cell B4 at first.
- After that, right-click on it.
- Now, from the Context Menu, select PivotTable Options.
- This will take us to the PivotTable Options window.
- Next, go to the Data tab.
- From PivotTable Data, uncheck the Enable show details option.
- Then, press OK.
Final Output
Finally, in the pivot table, if we double click on any cell, it’ll show us a message ‘We can’t change this part of the PivorTable’ (See Screenshot). That means we successfully hide the source data in the Excel Pivot Table.
How to Hide Pivot Table Field List in Excel
After creating a pivot table we can see a list named PivotTable Fields. Sometimes it becomes unnecessary after creating the final pivot table. To hide this list, we can follow the below steps:
STEPS:
- First, select cell B4 of the pivot table.
- Right-click on it to see the context menu.
- From here, select the Hide Field List option.
- In the end, we can see that the field list is hidden and it only appears when we click on the pivot table data.
- Moreover, we can use Excel VBA to hide this field list.
- In the beginning, go to the Developer tab.
- Now, select the Visual Basic option.
- This will open a Visual Basic Editor window.
- Next, go to the Insert tab.
- Then select the Module option.
- A VBA Module window opens here. We can open it by using the keyboard shortcut ‘Alt + F11’.
- Here, type the below code:
Sub FieldListHiding()
ActiveWorkbook.ShowPivotTableFieldList = False
End Sub- Click on the Run option or we can simply press the F5 key to run the code.
- We can see a confirmation Macros window pop up.
- Further, select the sheet name and click on the Run.
- Consequently, it will hide the pivot table field list.
Note: To show the pivot table field list, we can use the below VBA code:
Sub FieldListHiding()
ActiveWorkbook.ShowPivotTableFieldList = True
End SubThe other procedure will remain the same here.
How to Hide Pivot Table Data in Excel
Sometimes, we need to hide specific data in a pivot table. Follow the steps here to see how to do that.
STEPS:
- First, select the required data that we want to delete.
- Secondly, select the arrow in the Row Labels cell.
- Next, uncheck the value that we don’t want to see.
- Now, click on OK.
- Finally, we can see that the data is hidden in the pivot table.
How to Protect Source Data from Being Hidden in Excel
Suppose we have a pivot table and we want to protect data from being hidden. To do that, follow the below instructions.
STEPS:
- In the beginning, right-click on any cell of the pivot table.
- Next, select PivotTable Options.
- This will take us to the PivotTable Options window.
- Now go to the Data tab.
- Further, uncheck the option Save source data with file.
- As well as, check the option Refresh data when opening the file.
- Finally, click OK.
- Consequently, this will protect the source data from being hidden and show us the below message box.
- If we don’t put a checkmark on the option Refresh data when opening the file, then it will show us the below message.
- To avoid that, we will need to refresh data manually after filtering data which is very irritating. So it’s better to put a checkmark on that option.
Download Practice Workbook
Download the following workbook and exercise.
Conclusion
By using these step-by-step procedures, we can quickly hide source data in the Excel Pivot Table. We also discussed hiding the pivot table field list, hiding pivot table data, and protecting source data in Excel. There is a practice workbook added. Go ahead and give it a try.
