3 Work-Arounds for Calculated Fields
Don’t get me wrong. Excel Pivot Tables are a great way to summarize, analyze and present the information in your data. And, creating Calculated Fields in Pivot Tables is a terrific way to extend your analysis.
However, there are a few “quirks” that you should be aware of when using a Calculated Field”
A Calculated Field is always performed against the SUM of your data!
Well, what if you want to calculate against the MIN or the MAX of your data? Or perform some other calculation?
3 Work-Arounds for Calculated Field Shortcomings
I encourage you to look at a Pivot Table as “a means to an end.” Let your Pivot Table take you to the 90% completion point – and a Pivot Table does this very well! Then, use one of these 3 strategies to help you to complete your analysis.
- Sometimes, you simply must return to your source data and add an additional field so that your Pivot Table has more information to work with.
- You can make a copy of your Pivot Table and then Paste Special the Values to another location. From there, you can complete your analysis with more flexibility.
- You can create formulas outside of the Pivot Table – as I demonstrate in this video, deselect the “pesky” GetPivotData Function first. There are some limitations here:
- Remember that all cell references in your formula must appear in your Pivot Table
- Your Pivot Table must essentially remain “static” – e.g. no more fields added or subtracted, etc.
Watch Video Tutorial in High Definition on YouTube
This video lesson is longer in length than normal. To watch it in High Definition, click on this link to go to my YouTube Channel – DannyRocksExcels
Download Extended Length Video Tutorials for Pivot Tables
I have created extended length video tutorial recordings for Pivot Tables in Excel 2003, Excel 2007, and Excel 2010. You can download them for only $9.95 USD.
Click on this link to get more information about my Excel Extended Length Video Recordings.
Speak Your Mind