How to Hide Source Data in Excel Pivot Table (With Easy Steps)

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.

• 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.

Sub FieldListHiding()
ActiveWorkbook.ShowPivotTableFieldList = True
End Sub

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.

Related Articles

• Pivot Table is Not Picking up Data in Excel
• Excel VBA to Change Pivot Table Source

<< Go Back to Pivot Table Data Source | Pivot Table in Excel | Learn Excel