Subscribe for more Excel tips and tricks.
Avoid GETPIVOTDATA
You are hacking the PivotTable. All these little tricks are going to make you such a better analyst.
There is one more trick I want to show you. As an analyst, you do a lot of so-called “back of the envelope” math. This is quick, ad-hoc, “unofficial” math. Let’s say that in our example you may need to add up the sales and profits of a random combination of districts. There are ways to group the items in a PivotTable. But you just need a “quick and dirty” estimate before pursuing.
Simple enough — you know how to sum two cells in Excel! Let’s add the sales and then the profits for Regions 3 and 6.
You got some kind of monstrosity. It is confusing. And notice that when you try to drag your formula over a cell to pick up the sum of profits, you get the same number!
There is a legitimate reason for Excel to do this. What if you added data to your source and refreshed the PivotTable? Your desired value might not have the same cell reference if you add more values. But right now, that isn’t a consideration. We just want to do some quick number-crunching on the side. Fortunately, there is a way around it. It’s pretty simple yet few know about it.
Type in your cell reference instead of clicking on it. Now you can use normal cell references. Back-of-the-envelope away!
Again, this may not seem like much, but these time-saving tips really add up, paying huge dividends later on. Specifically, this investment will get you a job.
Leave a Reply