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.
PivotPal Window
The following image explains the buttons and features on the PivotPal window.
Video Tutorials
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 [email protected].
Thanks!
ONCE A PIVOT TABLE IS BUILT, CAN YOU PERFORM STREAM ARITHMETIC
FOR EXAMPLE: DIVIDE BY 12
ONCE I’VE SUMMED A LOT OF INVIDUAL VALUES (12 MONTHLY VALUES FOR 5000 ITEMS) I THEN WANT THE AVERAGE OF TOTAL NOT THE AVERAGE OF THE INDIVIDUALS.
TIM
Hi Tim,
Great question! Pivot Tables don’t really calculate the average of averages. You could calculate that outside the pivot table using a simple formula. If you want to send me your file I would be happy to take a look at it. You can send it to [email protected]. Thanks
I loaded but on my screen I only see the Pivot Pal, My NumberFormat and Pivot layouts – how do I get the above? Thanks
Hi Justine,
Thanks for your question. Those are the correct buttons for PivotPal. The other buttons on the XL Campus tab are for a few of my other add-ins Tab Hound and Paste Buddy. Please let me know if you have any other questions. Thanks again Justine!
I have multiple fields in my row area. One of the default PivotLayout option is Subtotals at bottom. I know I can “skip” applying a subotal using Field Settings in the pivot table and changing option to None (the current Excel method). Is there a way on PivotPal to do apply/unapply subtotals to individual fields?
Hi Larry,
Great question! The short answer is no, not at this time. The Layouts tool will apply the Subtotal setting to all the fields in the pivot table.
That is a great suggestion though and I will have to think of some ways to make it happen. Please let me know if you have any other questions. Thanks again!
What is the easy way to print your PivitPal help page?
I hope this hasn’t already been addressed, I haven’t searched previous comments. I noticed that when I go to used the FILTER SOURCE I get an error when I am using the ad-hoc group features of pivot tables. Is there anyway around this besides not using the ad-hoc group feature? Thanks.
Hi David,
Unfortunately, there is not a direct way around it with PivotPal. The groups are not exposed in VBA in a way that I could determine what the pivot items are for the grouped field of the selected cell. I have tried really hard to find a solution for this, and it has been deemed impossible by a few Excel experts.
The workaround would be to create the grouping in an additional column in the source data. If you were grouping dates into months, you could create a new column in the source data with a formula that calculated the month based on the date column. =MONTH([date]) or = TEXT([date],”mmm”)
Please let me know if you have any questions on the calculated column. Thanks!
How do I share this with my colleagues (2) to be exact?
Hi Leah,
Thanks for your question. The PivotPal Add-in license is for a single user. That user can install PivotPal on up to 3 computers that you own.
You can download the files on each computer and then install them through Excel. The installation instructions are included in the zip file you download at the time of purchase. You should also have received an email with a link to download the file.
Please let me know if you have any other questions. Thanks!
Fantastic, time-saving pivot table add in. It’s intuitive and easy to use, actually fun to work with and is the best one yet for pivot tables and I have tried many of them. I actually purchased all of Jon add-ins to my excel toolbox.Amazing time savings for completing tasks and pumping out reports. The only thing I do not see is the ability to rename my pivot tables in this utilities so that’s on my wish list for maybe a future upgrade to PivotPal
Thanks for the great feedback Stephen! I’m really happy to hear you are enjoying the add-ins, and that they are saving you time. That is the most important thing. 🙂
Great suggestion on renaming pivot tables. I will add that to the list. Thanks again and have a great day!
How can i get developer and excel campus rivon/comand in my excel?
Hi Sayeed,
The XL Campus tab will appear when you install any of my add-ins. The Developer tab can be enabled from the Excel Options menu. Here is a video that shows how to enable the Developer tab.
Hi Jon,
I have added Pivot Pal with no issues but source formatting gives me an error. “Source formatting could not be determined.” I am able to set the formatting manually through PivotPal and was able to create a custom format. Did I miss a setting?
Pam
Hi Pam,
Is the source data in a range in the same workbook? Or is the source formatting in the data model in PowerPivot?
If the source data is in a range in the same workbook then the source formatting option should work. If possible, can you send me the file? [email protected]
Thanks!
Well, I tried to duplicate the issue with no luck. The good news is that it’s working! I’ll be more detailed if I get the error again. I’ve just been playing around with test data. I’m just starting to using it in real work. It’s going to save a lot of time.
Thanks,
Pam
I have a data source that is a table from another source (SQL), but the table is in the workbook, but the “Source formatting could not be determined.” Is that to be expected?
Hi Jon,
Thanks for the wonderful work in your videos and add-ins. Well explained, good level, and original new material. I have a question about the PivotPal pop-up menu. The upper part of the menu isn’t showing. Only the ‘i’ is there. Some parts only appear when you click on the space they are supposed to be (like he Pivot table list, Goto Pivot, Refresh Pvt, Filter Source; they show but don’t work) and the Goto Data doesn’t show up at all. When you move the PivotPal pop-up menu to another location, the upper part disappears again. I’ve tried a couple of things, like restarting Excel, restarting my computer, removing the add-in by unclicking it in the options/add in location, but this doesn’t resolve the problem.
My Excel 2016 is in Dutch. I bought the PivotPal developer version, but I’m not very VBA-experienced yet…
Do you have a suggestion how to get PivotPal working 100%?
Hi Roger,
Thanks again for purchasing PivotPal. I’m sorry to hear you are experiencing that issue. This was a bug in Excel 2016 that has been fixed. You should be able to Update Excel to fix the problem. You can run the update from the File > Account menu in Excel. Please let me know if you have any questions. Thanks again!
Thanks Jon,
Thanks to you my problem is solved! Never thought Excel didn’t update automatically…
Looking forward to more of your excellent video’s, add-ins and free downloads.
Hi Jon, GoTO Data doesn’t show up at all for me.
I use office 365 for windows 10.
can you help me.
thank you.
Hi Alessander,
The GoTo Data button will only be enabled when the source data for the pivot table is in the same workbook as the pivot table. Please let me know if you have any questions.
Thanks!
Hi Jon,
Is it possible to Filter Source based on all filtered fields in PivotTable ?
I need to obtain the same result in source data as is displayed in PivotTable.
Jozef
Hi Jozef,
You should be able to select the Grand Total Cell for rows & columns in the pivot table, then press the Filter Source Button. That should apply the same filters you have applied to the pivot table. I hope that helps. Please let me know if you have any questions.
Hi, Jonh
How do i take the filter off my data source sheet?
Hi Yesenia,
You can clear all filters from the Data tab of the ribbon by clicking the Clear button. Keyboard shortcut: Alt,A,C.
Here is a video series on the filter menus that you might find useful.
Please let me know if you have any questions. Thank you! 🙂
Hi Jon,
I recently purchased and downloaded pivotpal as described along with a few of you free add ins, Thanks for those, however every time I open excel I get an error message for each add in saying add in cannot be found.
I have opened new sheets and gone through the add in process again, but after saving and closing, when I reopen again error message add in not found. Any ideas please.
Hi Pete,
Thank you for your purchase. I’m sorry to hear you are having that issue. This can happen if the file location moves or if they are stored on a cloud drive. You might want to try completely removing the add-ins and reinstalling them. Also try saving the files to a folder on your hard drive, if you haven’t already. Here is a video on how to completely uninstall an add-in. I hope that helps. Please let me know if you have any questions. Thanks again!
Hi Jon,
Thank you for your great tutorials. It took me a while to catch on to some items, but I’m moving along pretty well now. I had to go through the Add-in procedure again because I made a change to a folder that had all my personal stuff in it. I flew right through the Trust section with no trouble, but when I got to the Unblock feature, I couldn’t locate the .xla or .xlam files. I did this when I first set up your program, but for the life of me, I don’t know why I’m stumped! Would you please help me out? Thank you in advance.
Hi Chuck,
After you Unblock the add-ins the first time, the Unblock option will no longer appear in the Properties menu. So, the checkbox might not be there if you have already unblocked the files.
If you download the files again then you will see the Unblock checkbox. It only appears for files that have been downloaded from the internet.
I hope that helps. Please let me know if you have any questions. Thanks!
Is there a keyboard shortcut to launch PivotPal? It would also be nice to have keyboard shortcuts for calculation and formatting.
Hi Tomas,
Great question! There is no dedicated shortcut key, but you can add the PivotPal button to the Quick Access Toolbar by right-clicking it and selecting Add to Quick Access Toolbar (QAT).
All buttons in the QAT have keyboard shortcuts with Alt+Number Key.
The video on this page on How to Open & Close the PivotPal Window explains how to set this up. Please let me know if you have any questions. Thanks again! 🙂
I have several pivot tables that I built previously that included rows 1 though 200 on the data sheet. My data sheet now has and additional 50 rows. Is there an easy way to change datasource to include the extra 50 rows. E.g., current data source range: A1:Q200. Now data source should be A1:Q250.
Hi Bill,
Great question! The best way handle source data that changes is to use an Excel Table for the source data range. Here is a video that explains more about Excel Tables.
You can convert your existing range to an Excel Table, then change the source data range to reference the table name, instead of the range reference. When you add new data to the bottom of the table, the pivot table will automatically include this data. You will just have to refresh the pivot table to include the new data.
Thanks!
Hi John,
My Pivot Pal has disappeared from the menu again?? I do see the add in and it’s allowed but not in the ribbon menu.
My Pivot Pal/Excel Campus keeps disappearing from ribbon. Please advise ASAP. Thanks.
Hi Jon,
I seem to be having trouble getting Tabular Layout in “My Pivot Layouts” to work properly. In My Pivot Layouts, I have the Layout selected as “Tabular”, and have saved the layout, yet I always have to go back to the Pivot Table Design tab in Excel and manually select the Tabular format.
Using Office 2010 with Windows 7 Home Premium.
Thanks!
Charlie
Hi Jon,
I want to know how can I get the folder ec_pivotpal.xlam?
Do I have to download from any place? or Do I have to buy?
Thanks
Jorge
Hi Jon
I would like to display the filter criteria applied to a pivot table in a specific cell
In this particular case I would like to display a “date filter” where the criteria is “between…and”
thanks
aris
Hi Tim,
I do not have the option to show PivotPal while right-clicking on a selected pivot. My version of Excel is 2013.
Hi,
Just wondering if there is a feature to easily remove “Sum of”/”Count of” from values column headers? This is actually what I was looking for and for some reason thought your add-in did this, but I can’t find it.
Also, I am not finding the custom layouts useful as you have to apply a whole layout, not just individual settings and I have many different pivots in my files with different combinations of settings that change with each project so I just want to change ONE setting at a time easily via a button or similar.
I guess this add-in just wasn’t what I was looking for, I really want a whole ribbon of buttons (like I have created for myself for other functions in excel) without having to write all the macros (fun, but can’t justify the time at the moment).
Thanks, Regards, Claire
Hi Claire,
Thank you for the feedback. PivotPal does not remove the Sum of/Count of word at this time. It’s something I might add in the future.
In regards to the Layouts, you can set the Options to Skip if you don’t want those settings applied. You could then create a few layouts to apply various settings that you use frequently. It won’t be as straightforward as individual buttons, but could still be faster than hunting through the menus. You would just select each layout from the Layouts drop-down and hit apply.
That’s a great idea to have buttons, and something I will also consider for the future. Thanks again! 🙂
Hi Jon,
I am not getting the right click option to open PivotPal, when I have the pivot selected in my Excel 2013 version. How can I correct this please?
Hi Lyman,
I’m sorry about that. Looks like there is an issue with the right-click menu, and I believe I fixed it. I just uploaded a new file to the download page. Please update to that version and give it a try.
This fix is a bit of a workaround. You will first need to open PivotPal from the XL Campus Tab. Once you do that, it will run the code to add the button to the right-click menu. You should only have to do this once when you open Excel for the first time. I’m looking at other ways to make this more automatic, but that should work for now. Let me know if it still doesn’t work for you. And thanks for bringing this to my attention!
Hi Jon,
Just tried it and it worked! Thank you for your speedy attention to this!
Hi Jon
I am starting to get the message “A PivotTable report cannot overlap another PivotTable report”. Usually I can find the overlap and correct, but now I can’t find an overlap anywhere in the file. The last change after which refresh stopped working shouldn’t have made any difference to the size of any pivot table I do have about 40 pivot tables in the file.
Can PivotPal help?
Kind regards
Tom
Hi Sir,
How can I get the Pivotpal extension to install it on my Laptop?
After I use the Filter Source option, how to I unfilter it?
I just purchased downloaded and installed the PivotPal with VBA. When I go to use it, the field scroll box in the pivotpal drop down exceeds the size allotted for it and it obscures the top row of buttons below it.
Also the same issue on the number format drop down. I can’t get to the Apply or OK button because it is obscured by the list of formats.
I keep getting an error message of Microsoft Visual Basic when I start Pivot Pal from the Excel Campus tab.
I choose a new layout before I really knew what I was picking. How do I go back to the default pivot table look so that I can restart building the layout I prefer.
Hi Sir. I followed all the steps as per your videos but my pivotpal is not working. It is in the list of add in and I click ok but I can’t see it on excel bar. Can u help otherwise it will be useless for me?
Hi. How can I create my own fields? I want to use this add-on to manage lists not figures. Thank you. 🙂
Not sure what is going on. Bought and downloaded Pivotpal.
Installed the add-in. Don’s see it on the menu bar.
I just want the pivot pal number formatter as a starting point! I spend a lot of time updating the formats in my tables.
Will Pivot Pal work on my mac with Boot Camp
Excel 365 can apply a “top 10” filter to the grand total column of a pivot table. But there does not seem to be a way to top 10 any other column.
Does pivotpal add that functionality?
I have added custom formats to my number formatter but every time I exit Excel I loose my saved custom formats. How do I get this to stay for excel every time I open a new instance?
can someone tell me how I can become a member of Excel Campus? I thought I was but my email will not take..?
thank you
nice
Is this add-in discontined