An Excel pivot table is a powerful solutions tool. A primary feature of a pivot table is its ability to filter data. This allows the user to limit the amount of data that he has to browse to get his answer. With just a little bit of Visual Basic for Applications, or VBA, programming, the user can build functionality that automatically changes the pivot table filter either in response to an update or as part of a standardised process.
- Skill level:
Create a data table in Excel. In row 1, column A, type the word "Color." In row 1, column B, type the word "Letter." In row 1, column C, type the word "Number." For several rows in column A, type in several colours, such as red or green. For Columns B and C, type in random letters and numbers to create the data table.
Insert a pivot table based on the data. Filter on the Color column by deselecting "Green."
Open the Visual Basic editor. Do this by pressing "ALT"+"F11" or selecting "Tools" from the top menu bar, then select "Macros" -> "Visual Basic Editor."
Click on "Insert" in the top menu bar and select the "Module" option.
Type the following Visual Basic code:
Public Sub FilterPivotTable()
.PivotItems("Green").Visible = True
Run through the code by pressing "F5" or walk through the code line-by-line by pressing "F8." The pivot table will now show both the Red and Green columns. To turn off the filter, change the word "True" to "False."
Tips and warnings
- It is almost always easiest to record keystrokes when creating a new Visual Basic module and then modifying what has been recorded. To record your keystrokes, select "Tools" from the top menu bar, then "Macro" and "Record New Macro." Filter and unfilter the pivot table a few times before stopping the recording. Open the Visual Basic editor and review what has been recorded. The code can easily be copied and modified as needed.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for