PivotPal Help Page - Excel Campus

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.

XL Campus Ribbon and PivotPal Buttons

 

PivotPal Window

The following image explains the buttons and features on the PivotPal window.

PivotPal Window Diagram v1.0

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:

  1. The XL Campus tab on the Ribbon
  2. The Options/Analyze tab in the PivotTable contextual tab on the Ribbon
  3. The right-click menu when a cell in a pivot table is selected.
  4. Add the button to the Quick Access Toolbar (QAT)
  5. 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.

Custom Layouts for Your Pivot Tables - PivotPal

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 jon@excelcampus.com.

Thanks!

 

Click Here to Leave a Comment Below 33 comments
Tomas - July 24, 2017

Is there a keyboard shortcut to launch PivotPal? It would also be nice to have keyboard shortcuts for calculation and formatting.

Reply
    Jon Acampora - July 24, 2017

    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! 🙂

    Reply
Chuck Sotis - July 17, 2017

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.

Reply
    Jon Acampora - July 19, 2017

    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!

    Reply
Pete - May 22, 2017

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.

Reply
    Jon Acampora - May 25, 2017

    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!

    Reply
Yesenia - March 23, 2017

Hi, Jonh
How do i take the filter off my data source sheet?

Reply
Jozef - February 9, 2017

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

Reply
    Jon Acampora - February 15, 2017

    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.

    Reply
Roger Hendriks - November 18, 2016

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%?

Reply
    Jon Acampora - November 21, 2016

    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!

    Reply
      Roger Hendriks - November 22, 2016

      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.

      Reply
Pam - October 25, 2016

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

Reply
    Jon Acampora - October 31, 2016

    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? jon@excelcampus.com

    Thanks!

    Reply
      Pam - November 4, 2016

      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

      Reply
sayeed - May 23, 2016

How can i get developer and excel campus rivon/comand in my excel?

Reply
Stephen - February 10, 2016

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

Reply
    Jon Acampora - February 11, 2016

    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!

    Reply
leah mcelhaney - December 2, 2015

How do I share this with my colleagues (2) to be exact?

Reply
    Jon Acampora - December 18, 2015

    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!

    Reply
David Collins - November 2, 2015

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.

Reply
    Jon Acampora - November 2, 2015

    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!

    Reply
daily 08/11/2015 | Cshonea's Blog - August 11, 2015

[…] PivotPal Help Page – Excel Campus […]

Reply
daily 07/16/2015 | Cshonea's Blog - July 16, 2015

[…] PivotPal Help Page – Excel Campus […]

Reply
Henry Huth - June 24, 2015

What is the easy way to print your PivitPal help page?

Reply
Larry - June 12, 2015

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?

Reply
    Jon Acampora - June 14, 2015

    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!

    Reply
justine - May 27, 2015

I loaded but on my screen I only see the Pivot Pal, My NumberFormat and Pivot layouts – how do I get the above? Thanks

Reply
    Jon Acampora - May 29, 2015

    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!

    Reply
TIM KNOBLOCH - February 24, 2015

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

Reply
    Jon Acampora - February 24, 2015

    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 jon@excelcampus.com. Thanks

    Reply

Leave a Reply: