My good friend Mitch , a highly technical computer buff but not an avid Excel user told me about an Excel add-in called Pivot4U. This add-in will allow for edits into the data field of a pivot table. My first thought was "That's not right!". Why would anyone want to edit the data field in a pivot table? That would destroy its integrity, and pivot presentations would become questionable . This may be a bad idea, so I had to try it.
Go to pivot4uwebsite.com to get the download. There is a free 14 day trial period. Then the product can be purchased for $14.95 make that 39.99 (more about that later).
After downloading and running the program a menu group (Pivot4u) will appear under the Add-Ins tab.
Here's how it works. Below I have downloaded a pivot that summarizes my CPE for 2009.
If I was a little short in my hours for the year, and if I had not taken my 3 hours of ethics, I might be inclined to place my cursor in cell F9 and add some hours to the pivot, but I know that the pivot will not allow this and return the error message "Cannot change this part of a Pivot Table report".
Click on the Pivot4U menu and engage the add-in by clicking on Pivot4U On.
It turns out that an entry directly into the Pivot Table creates a new line item in the data base or changes the value of the original data. A look back at the data tab shows that a new line (row 16) has appeared including entries for the Program, Hours and Type headings (all of the fields that are utilized in the Pivot Table). The PIvot4U add-in simply allows for reversing the data update from entering values directly into the pivot table versus changing the original data base and refreshing the Pivot Table. Entries can also be made to subtotals and totals in the Pivot Table which will affect the source data.
I have come across situations where I would use this application. I'm not sure, however, how much I would use this add-in. I'm so used to manually changing the data it might be hard to wrap my mind around using the reverse technology. But, like Pivot Tables themselves, the more you use it, the more you will recognize opportunities for using it again.
The trial period expired on me as I was writing this blog, so I decided to purchase the product for the original advertised price of $14.95. Meanwhile the price had increased to 39.99. I guess that those who hesitate....pay more. Regardless, I want to give a big shout-out to Mitch for bringing Pivot4u to my attention. Anyone else out there that wants to share?