How To Disable Select Multiple Items In Pivot Table?

Pivot tables are effective data analysis tools that make it simple to summarise and analyse huge datasets. The ability to pick multiple items from a field is a typical feature of pivot tables that can be beneficial for filtering and digging deeper into specific data. To maintain data integrity or to streamline the analysis process, you might want to limit users from picking multiple items in some circumstances. We'll show you how to block the ability to pick multiple items in a pivot table in this article. You may prevent any unintentional data manipulations or intricate filtering scenarios by putting in place this limitation, which limits users to selecting a single item at a time.

This tutorial will provide you a clear and comprehensive explanation of how to disable the multiple item selection option in a pivot table, regardless of whether you are an Excel fan, a data analyst, or a business professional. To ensure that you may use these instructions regardless of the platform you are using, we will cover Excel 2016 and later versions as well as Excel Online. You should be familiar with Excel's user interface and have a basic understanding of pivot tables in order to follow along. So let's get started and discover how to prevent users from selecting multiple things in a pivot table, giving you the opportunity to speed up your research and safeguard the integrity of your data.

Disable Select Multiple Items In Pivot Table

Here, we will first create a VBA module and then run it to complete the task. So let us see a simple process to learn how you can disable selecting multiple items in a pivot table in Excel.

Step 1

Consider any Excel sheet where you have a pivot table. First, right−click on the sheet name and click on View Code to open the VBA application, then click on Inset and select Module.

Right click > View code > Insert > Module.

Step 2

Then copy the below code into the text box, similar to the below image.

Code

Sub DisableSelection()
Dim xPF As PivotField
Set xPT = Application.ActiveSheet.PivotTables(1)
For Each xPF In xPT.PivotFields
   xPF.EnableItemSelection = False
Next xPF
End Sub

Step 3

Then save the sheet as a macro−enabled template and click F5 to complete the task.

Note −

If you want to enable multiple items, use the below code.

Code

Sub DisableSelection()
Dim xPF As PivotField
Set xPT = Application.ActiveSheet.PivotTables(1)
For Each xPF In xPT.PivotFields
   xPF.EnableItemSelection = True
Next xPF
End Sub

Conclusion

In this tutorial, we have used a simple example to demonstrate how you can disable selecting multiple items in a pivot table in Excel to highlight a particular set of data.

Pradeep Kumar

Updated on: 11-Jul-2023

197 Views

Kickstart Your Career

Get certified by completing the course

Get Started