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 about how to hide source data in Excel Pivot Table with some necessary steps with explanations.


Practice Workbook

Download the following workbook and exercise.


Step by Step Procedures to Hide Source Data in Excel Pivot Table

More often, if we hide 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 by Step Procedures to Hide Source Data in Excel Pivot Table


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 From Table/Range option from that drop-down.

Step by Step Procedures to Hide Source Data in Excel Pivot Table

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 PivotTable. Here, we have selected a New Worksheet.
  • In the end, press OK.

Step by Step Procedures to Hide Source Data in Excel Pivot Table

  • After pressing OK, we can see a PivotTable Fields list in a new worksheet (Sheet2).
  • Furthermore, drag the Date into the Rows area and Amount in the Values area.
  • Consequently, it will create a Pivot Table in the worksheet.

Step by Step Procedures to Hide Source Data in Excel Pivot Table


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 by Step Procedures to Hide Source Data in Excel Pivot Table

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


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 is still showing.

Step by Step Procedures to Hide Source Data in Excel Pivot Table

  • Secondly, we select cell B4 in Sheet2 and double-click on it.

Step by Step Procedures to Hide Source Data in Excel Pivot Table

  • 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 of it, 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 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.

How to Hide Pivot Table Field List in Excel

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

How to Hide Pivot Table Field List in Excel

  • 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 pops 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 Sub

The others 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.

How to Hide Pivot Table Data in Excel

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

Read More: How to Hide Chart Data in Excel (With Quick Steps)


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.

How to Protect Source Data from Being Hidden in Excel

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

How to Protect Source Data from Being Hidden in Excel

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

Read More: How to Hide Confidential Data in Excel (5 Easy Ways)


Conclusion

By using these step-by-step procedures, we can quickly hide source data in 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. Visit the ExcelDemy website for more articles like this. Feel free to ask anything or suggest any new methods.

Nuraida Kashmin

Nuraida Kashmin

Hi Everyone! I am Nuraida. Cordially welcome you to my profile. I am a Team Leader of Excel and VBA Content Developer in ExcelDemy. Here I will also post articles related to Microsoft Excel. With a strong educational background in Mechanical Engineering through experimental learning from Rajshahi University of Engineering & Technology. Skilled in Microsoft Word, Creative Writing, Microsoft Excel, Project Management, Support to Organize Different Events, Reporting, Monitoring & Documentation, Online Advocacy and Event Management Related to SAP and Youth Leaders.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo