PivotPal – A Fast New Way To Work with Pivot Tables

Click Here to Download PivotPal Today!

 

There is no doubt that pivot tables are incredibly powerful, and save us a lot of time when summarizing data.

However, we also spend a lot of time doing repetitive tasks with pivot tables.  Do you spend extra time doing any of the following?

  • Searching for fields in the field list
  • Formatting numbers
  • Working with source data
  • Changing layout settings

These small tasks can be very time consuming and frustrating!

Pivot Table Built-in Menus Are Slow and Time Consuming in Excel

Do You Spend Extra Time Building Pivot Tables?

If you use pivot tables frequently then you could be wasting a lot of time with some of these boring repetitive tasks, which takes time away from the fun stuff.

If you have pivot tables with more than 20-30 fields, then just scrolling through the field list to find the field you're looking for can take a lot of time.

Scrolling Through the Pivot Table Field List

And if you don't know the name of the field you are looking for, then you probably do something like the following:

  1. Find the sheet that contains the source data.
  2. Scroll horizontally through the columns until you find the field.
  3. Go back to the sheet that contains the pivot table.
  4. Find the field name in the field list.
  5. Add it to the pivot table.
  6. REPEAT for all the fields you want to add.

This process alone can be very time consuming.

How Can We Make This Faster?

Jon Acampora Circle MVP ProfileMy name is Jon, and I have been using Excel all day everyday for over 10 years. I love Excel! 🙂

And, I have probably built thousands of pivot tables. I use pivot tables for everything from a quick list of unique values, to reconciling accounts, to creating sophisticated interactive dashboards. They have a seemingly infinite number of uses, and they are just awesome!

When you start using pivot tables frequently, you begin to realize that some of Excel's built-in tools could be a little more efficient. There are certain tasks that just take way too much time with the built-in field list and various options menus.

Pivot Table Options and Settings Menus

Fortunately, VBA (the language used to write macros) allows us to program pivot tables to help automate some of these tasks. Over the years I have written a lot of macros that perform specific tasks to modify or update a pivot table.

However, I wanted to bring these automation routines into an application that everyone could use. This is where the PivotPal add-in was born.

PivotPal started as a simple form with a search box where you could quickly find a field in the field list, then add it to one of the areas in the pivot using keyboard shortcuts. That feature alone saved me a ton of time.

PivotPal Search Box Find Fields and Add to Pivot Table

But the features quickly grew from there… I also wanted a way to quickly format numbers, instead of having to navigate through four menus just to change a number format.

I also wanted the ability to work on the pivot table from the source data sheet. Why should we have to constantly jump back and forth between two sheets that might not be located next to each other in the workbook?

The list goes on, and I spent over a year adding features to enhance PivotPal.

PivotPal Saves You Time

The goal of PivotPal is to save you a ton of time when working with your pivot tables. The add-in is packed features that make things faster and easier.

PivotPal Window Diagram v1.0

Let's take a look at some of the features.

Quickly Find Fields & Add Them to the Pivot

PivotPal contains a search box that allows you to quickly find fields in the field list. It works similar to a Google search, and the results are narrowed down as you type.

Once you find the field, you can use keyboard shortcuts or the mouse to add the field to any of the areas in the pivot table. You will never have to spend time scrolling through the field list again.

Search Fields and Add to Areas of Pivot Table with PivotPal

You can also select multiple fields in the list to quickly add or remove fields, and even change number formatting.

Click Here to Download PivotPal Today!

Number Formatting Made Easy

When formatting a number in the values area you typically have to step through 4 menus just to change a number format. There is no doubt that this takes extra time.

Pivot Table Menus for Number Formatting 4 Steps

PivotPal contains a few features that relieve a lot of pain with this process.

First, PivotPal automatically applies number formatting.  When you add a field to the values area with PivotPal, the number formatting for the field in the source data is detected and automatically applied to the pivot table. This means you have to take zero steps to format the field. It's automatically done for you!

Source Number Formatting in Pivot Tables with PivotPal

If you the source data is not formatted, or you want to change the formatting of the field, you can do so in one step by selecting from the list of formats in the Formats drop-down menu on the PivotPal window. This list of number formats can be easily customized using the My Number Formats tool that is included with PivotPal.

The My Number Formats tool also allows you to apply your favorite number formats to any cell in the workbook. It is not limited to pivot tables.

Save and Apply Custom Number Formats in Excel with PivotPal

If you are constantly adding custom number formats like ($1.0M) to your workbooks, then this tool will allow you to do that in one click.

New Feature: Automatic Number Formatting

I've added a new feature that automatically formats the numbers in the Values area of the pivot table when using the built-in Pivot Table Fields List.

Number Formatting Automatically Applied GIF2

The Automatic Number Formatting feature detects the number formatting that is used in the source data range, and automatically applies it to the pivot table when a field is added to the values area.  If the source data is not formatted, or does not exist in the workbook, then a default number format that you specify is applied to the pivot table.

Either way, you will never have ugly looking pivot tables with the General number format.

Click Here to Download PivotPal Today!

Working With Your Source Data

When building pivot tables it is only natural to spend a lot of time working with the source data. This includes tasks like:

  • Finding fields
  • Investigating blanks and errors
  • Converting data types (dates, numbers, text)
  • Writing formulas
  • Changing the data source range
  • Drilling down on the details of a number in the pivot

The built-in menus don't allow you to work with source data while building the pivot table. Unless your pivot table is on the same sheet as the source data, you spend a lot of time flipping back and forth between the pivot sheet and source data sheet.

PivotPal solves some of these problems with a few great features.

Build Pivots from the Source Data Sheet

Often times you will know what field you want to add to a pivot table, but you don't know the name of the field. This is especially true when you are working with data that is exported from a database system. The field names aren't always descriptive.

Add Fields to Pivot Tables from the Source Data Sheet with PivotPal

Let's say you want to add the field that contains the product groupings, but you don't know the name of the field.

With PivotPal you would simply press the GoTo Data button on the PivotPal window. This will take you to the source data sheet, and the PivotPal window will remain open.

You can then browse through your sheet or use Excel's Find menu to find the field.

Selecting any cell in the column of the field will automatically select that field in the PivotPal window.

Now all you have to do is press one of the Area buttons on the PivotPal window to add the field to your pivot table. It's that easy!

Press the GoTo Pivot button to return back to your pivot table and view the results.

Click Here to Download PivotPal Today!

Filtering Your Source Data

When you double-click on a cell in the values area of the pivot (right-click> Show Details…), Excel adds a new sheet to your workbook that contains the rows of data that make up that slice (intersection) of the pivot. I call these drill-down sheets.

Pivot Table Detail Sheets are Not Formatted and Clutter Workbooks in Excel

This can be handy information if you are trying to investigate a number, but your workbook can quickly become cluttered with drill-down sheets. The other problem is that the drill-down sheet is not formatted, and usually difficult to read.

PivotPal has a Filter Source button that will show the exact same results by applying filters to your source data sheet.

Filter Pivot Table Source Data with PivotPal

This means you can investigate the actual source data, instead of a drill-down sheet. If you need to make changes to the data, then it is already filtered for the rows you want to see.

And, your workbook will NOT become cluttered with drill-down pages.

This feature has a lot of great benefits, and you will find yourself using it all the time.

Click Here to Download PivotPal Today!

Layout Settings Done Your Way

Did you know that there are over 30 different layout and options settings for pivot tables? We spend a lot of time changing these settings for each pivot table we create.

Often times we are applying the same settings over-and-over again. Do you find yourself doing a process similar to this:

1. Change layout to Tabular format
2. Turn Subtotals off
3. Display zeros for Blanks
4. Insert blank line after item

Everyone has their own preferences, but applying these settings can take extra steps.

PivotPal has a feature called My Pivot Layouts that allows you to create profiles with your favorite settings, then quickly apply them to any pivot table with the click of a button.

Apply Custom Options and Layout Settings to Any Pivot Table

Just selecting the layout profile you want to apply, and all the settings will be automatically adjusted for you. It will save you a ton of time!

Click Here to Download PivotPal Today!

Additional Features

PivotPal is packed with features to help you get your job done faster.

  • Source Range Warning – Have you ever forgotten to update the data source range after adding new data? I have, and it can be an embarrassing mistake. PivotPal will warn you when there are more rows in your source data then what is currently set as the source range in your pivot table. In other words, it will save your butt from embarrassing mistakes. 🙂PivotPal Warning When Pivot Table Source Data Range Needs to be Extended
  • Apply Number Formats to Any Workbook – Your workbook does not have to contain pivot tables to use the My Number Formats tool. You can apply your custom number formats to cells in any workbook.
  • Jump to any Pivot Table – There is a drop-down menu at the top of the PivotPal window that contains a list of all the pivot tables in the workbook. It also tells you what sheet the pivot table is on.  This makes it easy to jump to the other pivot tables using the GoTo Pivot button. You can also quickly check to see which fields are used on other pivot tables by just selecting from the list. The field list will be loaded for the selected pivot table.
  • Pivot Info List – The button to the right of the Pivot Drop-down opens a window that contains a list of all the pivot tables in the workbook. This list contains additional details about the pivot table including the pivot cache index number, source range, source type, and total pivot cache count. This feature will be enhanced in the future to include a lot more.
  • Works with PowerPivot – PivotPal will work with your Power Pivot models too. This saves a lot of time when searching for fields in many different tables.
    PivotPal with PowerPivot Data Model
  • Function and Calculation Types – PivotPal contains two drop-down menus for the function (sum, count, average) and calculation types (% of totals, rank, etc.). This allows you to quickly change the calculation type of the value field(s) you have selected in the list. Yes, you can apply these settings to multiple fields at the same time. When selecting a calculation type, the name of the field is automatically changed to describe the calculation. Instead of “Sum of Revenue2”, the field will be named “Revenue % of Total”. This is something that Excel does NOT do.

Change How You Work With Pivot Tables

PivotPal will help increase your productivity, and alleviate some of the frustrations you face when working with pivot tables.

PivotPal is meant to compliment the built-in menus in Excel. It won't completely replace these menus, but instead it provides a quicker solution for some of the tasks we do the most.

This time savings will allow you to spend more time on creative and analytic (fun) part of your job. Spending time on this type of work will make a bigger impact for your organization and your career.

What Others Are Saying About PivotPal

Wow!!! Pivot Pal makes customizing pivot tables super easy and fast. It also saves your custom setups for reuse in new pivot tables.Jim F
PivotPal is a great tool! It saves me a ton of time when working on unfamiliar data when I am creating a Pivot Table by allowing me to construct the Pivot while looking at the source data. That is an extremely helpful feature.

Between constructing Pivot Tables while looking at the source data and being able to add more than one item at a time to a Pivot Table field saves me quite a bit of time.

Another great feature is being able to instantly filter the source data to match what is currently filtered in the Pivot Table takes away the hassle of drill down sheets and deleting drill down sheets when I am finished analyzing the data.Peter R

I really appreciate having been able to review the PivotPal ahead of its launch and its had a serious workout already on a Dashboard I’ve created for my company’s global use.

Using the PivotPal has been quite a ‘light bulb moment’ in that it has helped to really cement my understanding of how Pivots in MS Excel work – quite apart from making Pivot Tables much easier and faster to set up, especially for multiple tables, it’s also helped me understand much better MS Excel’s high level Pivot Table workflow.

As well as being feature rich PivotPal also uses the valuable screen and workspace real estate much more efficiently than Excel’s native Pivot management which has been a real benefit whilst developing my spreadsheet Dashboard in meeting rooms and on my sofa using just my laptop without the luxury of twin monitors back at my desk.

I work for a leading automotive manufacturer with a global manufacturing and design footprint and your tool has been key in developing a new dashboard we’ve just agreed to at our most senior level to use globally as was one of our new key metrics in our Product Development teams. So it was very timely to have an easy to manage and understand PivotPal tool to help!David W

PivotPal is now a part of our Hero Tools Suite

The Hero Tools Suite combines all of our add-ins into one Excel add-in with over 100 features that will save you time with your everday Excel tasks.

Click Here to Learn More about Hero Tools

180 comments

  • Hi Jon,

    Will PivotPal also work with non English Excel versions?
    Do you sell a pack of all your add-ins?

    And is there also a option in PivotPal to find witch tab of my multiple Pivot Tables tabs thath contains a overlap to another Pivot Table?

    • Hi Mr. Z,

      Thank you for contacting me. The majority of the PivotPal features will work in non English versions. I have had some users report that the GoTo Data button does not work in some non English versions. I don’t have a pack of add-ins yet.

      Pivot tables cannot overlap. Excel does not allow this. So I’m not sure I understand your last question. I hope that helps. Please let me know if you have any questions. Thank you!

  • We have 12 people on my team at work. I am thinking PivotPal might help all of them. Would we buy 4 standard versions or is there an option to buy multiple licenses at one time?

  • It is possible to Install and re-install the same in other PC after surender the license
    i would like purhcase for company use at my own expenses but for for any reason if want to use for personal purpose in my own pc.
    is it possible to surrender and re-install the add-on on other PC
    Please advise

    • Hi Yahya,
      You can install PivotPal on up to 3 computers that you use at any one time. So yes, you can use it on both your work and personal computers. As you get new computers in the future, you can use it on those as well. I hope that helps. Please let me know if you have any questions. Thank you!

    • Hi James,
      Correct, PivotPal does not work on the Mac versions of Excel. I do not have plans to create a Mac version at this time. The 2016 Mac version still does not have a solid VB Editor for which to test and deploy and add-in like this. Hopefully Microsoft will change that in the future. Sorry about that.

  • I do like the simplicity of building the pivot tables with PivotPal, and was excited about the dashboard slicers, my question is: Do the slicers work without PivotPal? The reports i create are for management.

    • Hi Cynthia,
      Yes, slicers will still work with PivotPal. PivotPal does not limit anything in the file. It is just an enhanced version of the Pivot Tables Field List and adds other functionality. You can still use all the built-in feature of Excel. Please let me know if you have any questions. Thanks!

  • Hi Jon
    My current laptop has some issue and sooner I may choose to replace with another laptop.
    Will purchasing your product now and installing on my current laptop require me to purchase another set for my new one?

    Can you provide a prudent approach?

    Ravi

    • Hi Ravi,

      Yes, you can install PivotPal on up to 3 computers that you use at any one time. So as you get new computers in the future, you can install it on those without having to buy another license. Please let me know if you have any questions. Thanks!

  • Is there an eval version available? I’ve had many problems with Addins clashing, especially on 64-bit Excel.

    Thx

    • Hi Dave,
      I don’t have a eval or trial version at this time. I do have a full money-back guarantee. If it does not work for you for any reason, you can get a full refund. The add-in does work on 64-bit. Please let me know if you have any questions. Thank you! 🙂

  • Hey Jon, refreshing a pivot table is relatively easy, of course, with Shift-F10/Refresh. But to improve productivity I try to eliminate clicks, or even keyboard shortcuts as much as possible. I have set my Excel files containing pivot tables to refresh on opening. In addition, I use a macro to refresh after saving the file so I see results after adding new data before closing the file.

    Option Explicit
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Me.RefreshAll

    End Sub

    I have added this macro to each file with pivot tables, but a more “elegant” solution would be to have a “central” macro, so each file does not have to be saved as an .xlsm file. I have added the code above to the PERSONAL.XLSB in a module in the Modules folder. But it does not work. Should I put the code in the ThisWorkbook instead? If so, how do I handle it with some other code that is already there?

    Or would an add-in be more appropriate? I have looked how to create an add-in but somehow I can’t get it to work.

    Maybe you can point me in the right direction. And maybe you find it useful to add this to your PivotPal add-in?

    • Hi Peter,
      Great question! To run macros from the personal macro workbook or an add-in when events happen in other workbooks, we need to use application events. Here is an article by Chip Pearson on App Events. I use this technique in my List Search Add-in to trigger the SelectionChange event when the user selects any cell in any workbook. The VB Project is unlocked for List Search so you can see the code.

      Great suggestion on adding a feature to PivotPal. Thanks!

  • 1) Do all add-in functions work in the same way with regular pivot tables and with OLAP?
    2) Is it possible to make several calculations over a single field (for example, sum and count) through the add-in and output both simultaneously?
    3) Will the add-in work on Russian localization?

    • Hi Jon (and Serg), I have the same questions as Serg ( 1 and and 2 that is ;). Especially question on working with OLAP.

      Your add-in contains the functionality I was looking for (for decades now). I’d like to hear from you.

  • Jon,
    Your PivolPal product really annoys me!…

    It’s exactly what I would have done if I had your prior experience with these Excel time consumers.
    I’m disgusted that you beat me to it. Bravo! on a great idea and I wish you continued success with it.
    Cheers. -d

  • Hi, Jon,

    Wow, I’ve not used a pivot table yet but feel that I need one! I just joined as a volunteer at Veterans Support Group, Inc.(501-c-3) charitable organization. They are using Excel and so far have entered a great amount of information and records. Can I install your Pivot Pal on top of Excel and have it work beautifully? I work at their office and also bring home work. I have Excel 2016 and they have Office 365 with Excel. Will I have a problem if I use your program on these two different Excel programs?
    If this will work out the way I wish, I’d order your big package tomorrow. BTW your videos are great. You’re an excellent teacher.
    Sincerely,
    Chuck Sotis

  • Hi Jon,

    Everytime I open excel file, pivot pal is disappearing under Excel campus tab. I have also checked in the add-ins manager, its is installed. Please help me to fix up this problem. Because everytime I have to install add-in, its really annoying.

    Regards,

    LMSK

    • Hi Sathish,
      This issue that causes the add-ins to disappear is due to a Microsoft Office security update. There is a fix for this, and you will need to Unblock the add-in. This is a new property of the file. The installation instructions that came with the add-in explain how to unblock the file. I also have an article and video that explain this issue and solution in more detail.

      https://www.excelcampus.com/vba/add-in-ribbon-disappears/

      I hope that helps. Please let me know if you have any questions. Thank you!

  • Your program, is to be installed on a computer in the corporate network, how do we get over the security concerns from IT?

    • Hi William,

      Great question! The add-in file is just an Excel file type that is saved on your computer and installed from within Excel.

      This does NOT require you to install a program on the computer. There is no exe file or installer.

      You will just save the add-in file (.xlam) on your hard drive and install it through the Add-ins Manager in Excel.

      I hope that helps. Please let me know what specific concerns IT has, and I will be happy to try and address them. Thanks!

  • Hi Jon,

    Thanks for the tool. I am contemplating purchasing Pivotpal after having watched your Youtube videos. Could you please tell me if Pivotpal needs to be installed on the computer or if it can be run as a non-installation Addin? The reason I ask is because I am not allowed to install programs on my work notebook.

    Thanks,
    Anirudh

    • Hi Anirudh,
      Thanks for your question. PivotPal does NOT need to be installed on the computer. It is delivered as an .xlam Add-in file that you install through the Add-ins Manager within Excel.

      I do NOT package my add-ins with installers for this reason. A lot of companies do not allow you to install programs. So, the add-in file is just saved to a folder on your computer and then installed through Excel.

      Here is a page with installation instructions for the add-ins so you can see the process. Please let me know if you have any questions. Thanks!

  • Hi Jon

    m javed and really your pivot table tutorial very helpful. i love it, besides your pivotpal had great values but i am not in a position to purchase it but i love it.

  • If I have created a spreadsheet with a pivot table using your tool. If I send this spread sheet to a friend to use must he have also have PivotPal installed. He will not be making changes to the pivot table – only the source data and want to view the results.

  • Does this also work with pivot charts? I am constantly changing field value setting from count to max and if this can effectively make max the default when I create a new pivot chart, this’ll save me an aggregate of hours per week I think. Thanks.

  • Hi Jon,

    What does the VBA version mean. I know this is a silly question to most however, I am a new pivot table user. I am thinking about purchasing Pivot Pal. I will be doing Pivot tables and reports for my leadership group. I want to thank you for posting the Pivot table videos on YouTube, they helped me to understand how easy it is to use Pivot tables and create dashboards.
    I look forward to hearing back from you.

    Robin

  • Hey Jon,

    Being from Belgium and working a lot with grouping in pivots, does the filter source in Pivot Pal work also with non US dates (e.g 25/01/2017 instead of 1-25-2017) and grouped fields?

    Thanks a lot

    Bart

  • Dear Jon,

    $37 for PivotPal Standard Version, how long I can use for this add?

    Thanks,
    Phong.

  • Hi Jon,

    Good day to you! i have a question. I already have the PivotPal Standard version, is it possible to upgrade it to Developer version?

    thank you.

    Best regards,
    Larry

  • Hi Jon,

    The pivot pal is allowed to install up to 3 computers. I have 1 personal laptop, 1 company computer & 1 Laptop and 1 tablet.

    I would like to assign 2 add in license to my personal laptop and tablet.

    1 license share between with company computer and laptop. In this case, can I install at company computer and un-install from company computer and reinstall the add in to company laptop when nessacery. Can I withdraw this license to re-install to new company computer/laptop?

    Please advice. I am considering to purchase pivot pal + VBA if the add-in is flexible when I change company/laptop for company usage and personal usage.

    Many thanks

  • Can you change source data from the pivot (i.e. drill into source see items you’d like to change in the source then repivot with changes?)

  • If i purchase pivotpal developer version, and create workbooks on personal computer, to use at work with colleagues (more than 3) can they use the workbook I create with pivotpal without having add on installed on their computer?

  • I have a similar question as John McCreary. I work in a team environment and for a Govt. entity. Is there a public sector type of license for multiple users? Since the license would be ordered, paid and installed by my employer, can those 3 licenses be used by 3 different employees and/or do separate licenses need to be purchased?

  • Will Pivotpal allow me to create pivot tables with text instead of numbers. right now, by default and without VBA, numerical data values that are read as text because they have a text parameter in the same field, show up as ‘0’ in the value area.

  • Does PivotPal have a way to save setting for a pivot table already in a layout of interest? Meaning after you open Pivot Layout window in PivotPal all its fields are set to the current pivot table layout.

  • I have version 1.0 of Pivot Pal. Is there a more recent, updated version? If so, how do I get it?

    Thanks

  • I Joe,
    I know you don’t have a Mac version of pivot pal.
    I have a question: can I use the excel file created on windows with PivotPal on my Mac or in a computer without PivotPal?

    thanks
    Francesco

  • Does Pivot Pal have an option to clear the Pivot Table Cache used for slicers and filters? I have had issues after doing data refreshes that the Pivot Table slicers and filters still show old data in the filter that is no longer there. I know there are some manual process to clear the cache, but a tool to ensure it is done right would be nice.

  • Jon,
    I saw a couple of questions above that haven’t been replied to (as I can see) & was curious myself:
    VBA version vs your “regular” version, in layman terms what is the difference and how will I know what I need to purchase.
    I create pivot tables for my Manager who reviews and may or may not make changes, will they need PivotPal as well?
    Will this work with Power BI?

    thanks so much!
    Kathleen

  • Dear Jon,

    Is this only for business? I am a school teacher and want to sort answers from different tests and into different classes. This would my staff which areas need improvement ect.
    Regards

    Sam

  • Hi Jon
    I am trying to get a job as a finance manager at foreign company in myanmar. I have many experience local companies . But I need more smart presentation and want to get more confidence. During time I search excel trainings on Youtube . I Prefer your channel . Your video channel is clear . short easy to understand for me. I saved your video (off line video)in my i pad i try my laptop. All video are not finished yet . As your instruction I will start to learn VLookUp .
    Thank you Sir.

  • Great job as always. Your examples are great and your narrative concise. I have been using Excel for years as a Senior Financial Analyst / MBA but I always learn something from you.

  • You’re a great teacher. After viewing your free on-line videos (Parts 1, 2, and 3) I’m now going to purchase PivotPal. After viewing the free videos I have come to your website wondering what price your product would be. Finding it a very reasonable price I immediately decided to purchase it.

    In addition, you were not pushy about your product and I appreciate that.

  • If I use this on my home PC and then go to another computer which does not have it installed, will the pivot tables still work as standard pivot tables?