PivotPal Help Page
This is the help page for the PivotPal Add-in for Excel. Here you will find video tutorials for each button and feature in the PivotPal window.
Please leave a comment below if you still have questions about how to use PivotPal. I will be happy to help. Thank you!
XL Campus Ribbon
Once PivotPal is installed the XL Campus tab will appear in the Excel Ribbon. On this tab you will see the buttons for PivotPal, My Number Formats, and the My Pivot Layouts features.
Pressing the PivotPal button will open the PivotPal window.
The following image explains the buttons and features on the PivotPal window.
There is a section on this page for each feature of PivotPal. Each section contains a video tutorial for that feature. You can watch all the videos at once in the video playlist below.
How to Open & Close the PivotPal Window
The PivotPal button has up to 4 different locations including:
- The XL Campus tab on the Ribbon
- The Options/Analyze tab in the PivotTable contextual tab on the Ribbon
- The right-click menu when a cell in a pivot table is selected.
- Add the button to the Quick Access Toolbar (QAT)
- Create a keyboard shortcut to open the PivotPal window from the QAT.
The PivotPal window automatically closes when you click a cell outside of the pivot table. The window will remain open if you click the Go To Data button and select cells in the source data sheet.
Working with the Field List
The field list in the PivotPal window contains a list of all the fields for the selected pivot table. The fields that are currently in any of the areas of the pivot table will be listed at the top. The area name is listed in the second column of the field list.
You can click on one or more fields in the field list, then press the any of the area buttons (or keyboard shortcuts) to place the selected fields in the area of the pivot table.
The Search Box
The search box in the PivotPal window allows you to quickly search for any field in the field list. This functions similar to a Google search, and the results are narrowed down as you type. Press Enter to select the field in the field list. Press Shift+Enter to add to the items that are already selected in the field list (select multiple).
You can press the keyboard shortcuts from the search box without having to press Enter to select it first.
Function and Calculation Types
The function and calculation type drop-downs allow you to quickly change the options for the selected fields in the values area.
You can also add fields to the values area by selecting a field in the list, then choosing a function or calculation from either of the drop-down lists.
Selecting one of the calculation options like percent of total will automatically change the field name to describe the calculation. Excel does not do this for you.
Save Time with Formatting Options
PivotPal automatically formats the numbers when a field is added to the values area. It detects the formatting of the cells in the source data for the field, then applies that same formatting to the pivot table. You can quickly change the formatting by selecting from any of the predefined formats in the formats drop-down.
You can modify this list by click the My Number Formats button on the XL Campus tab. The My Number Formats window also has an option to import any of your custom formats into the active workbook. This can be used even if your workbook does not contain pivot tables.
Filter Source Data
The Filter Source Data button will filter the source data for the selected cell in the values area of the pivot table. This is similar to the Show Details option built into Excel when you double click a cell in the pivot. However, PivotPal does NOT add an extra sheet to the workbook. Instead it applies filters to the source data sheet for all the filter criteria that make up the number in the pivot table.
Build Pivots from the Source Data Sheet
With PivotPal you can build and modify your pivot tables from the source data sheet. Click the Go To Source button on the PivotPal window to go to the source data sheet. When you select cells inside the source data range, the field will automatically be selected in the PivotPal window. You can then press any of the area buttons to add/change the pivot table.
My Pivot Layouts
Pivot tables have over 30 options and settings you can choose to get your pivot table looking and behaving the way you want it. These are some of the settings you probably change frequently:
- Turn off the automatic subtotals and grand totals
- Show a zero for empty cells
- Turn off the autofit column widths
- Use the classic pivot style
This usually requires you open the pivot table options menu and click different checkboxes on/off. That menu alone has 6 tabs of options.
To help save time with this I added a new feature in PivotPal called “My Pivot Layouts”.
This feature allows you to create custom layouts with your favorite settings and options, and then quickly apply those settings to any pivot table with the press of a button.
The nice part is that you can create as many custom layouts as you like. Some pivot tables you might want to format to look like a financial report, other times you might want it to look like a tabular format with repeating labels. These layouts can all be saved and applied anytime with this feature.
See it in action in the video below.
Automatic Number Formatting
The Automatic Number Formatting feature applies number formatting to the cells in the values area when you use the Pivot Table Field List to build a pivot table. Sometimes you might not want to open the PivotPal window to build your pivot table. So this feature applies number formatting to the cells in the pivot table when you add fields to the values area.
The Automatic Number Formatting feature will apply the number formatting from the field (column) in the source data range. If the column contains General formatting, then default number formatting will be applied. The default number format is the first number format in the My Number Formats window.
The option can be turned on/off from the right-click menu within the pivot table. See the video for more details.
Still Have Questions?
Please leave a comment below with any questions. You can also email me directly at firstname.lastname@example.org.