3 ms·
If you're doing a simple Pivot, sure. But here are the things I run into constantly. 1. When I group by days (i.e. group by 7 day chunks), why does Excel refus
by function_seven 8y ago
If you're doing a simple Pivot, sure. But here are the things I run into constantly.
1. When I group by days (i.e. group by 7 day chunks), why does Excel refuse to sort chronologically?
2. How do I make my Grand Total column add up all the subtotals when those subtotals are averages of the underlying records? (It will only do an overall average)
3. Why can't I collapse just this row without collapsing the counterpart rows inside other groups?
4. Why can't I make a chart of just this part of the Pivot Table (rather than the whole thing)?
5. Every time I refresh the PivotTable, new column headers are a different font size/alignment/wrap. I have to manually update the formatting each time. Why? (It won't let me set font formatting in the PivotTable Styles)
6. My PivotTable won't let me add a new item to an existing group. It instead adds a new level to the group hierarchy and the whole thing blows up.
7. Whenever I try to hide the last row in a certain PivotTable, I get an error message: "You cannot hide this selection." Why?? If I move the row somewhere else, it then lets me hide it.
- intended 8y agoThis guy excel troubleshoots. God so many of those are infuriating the first time you encounter them. On the flip side once you get the hang of it, you know right quick how you need your data set up.
- PeterisP 8y agoAre there actual answers to these questions, or are they all "tough luck. now that you have an expert's confirmation that it's impossible, stop trying and go on with your life" ?
- function_seven 8y agoMost are "tough luck" ones, a few are expert-help required. The most common way to solve a pivot table problem is to add a column to the underlying data. Kind of a "manual pre-pivot" in a lot of cases. There aren't direct solutions to the list of problems, but there usually is a workaround. Some of the problems (like being unable to format dates when grouping by days) are just "tough luck" I don't know what's going on with the inability to filter out the last item of a pivot table. One of these days I'll chase that down. I can't be the only one.
- shostack 8y agoOr, if you're fairly confident the pivot is fairly static and you just need to tack something on, then don't be afraid to do it if a custom calculation isn't solving it for you. It surprises people but there's really no reason you can't do that if you just need it to work and/or look a certain way.
- function_seven 8y agoI often end up doing that, but it feels “dirty”, you know? And I also underestimate how long-lived my report will be, and get stuck having to move those sideline calculations out of the way when I need to refresh the pivot (“This will overwrite existing data.”) Which brings up another gripe: why can’t the Pivot allow me to push those rows or columns down/over instead of clobbering them?
- shostack 8y agoIt absolutely feels dirty. Every. Single. Time. It has likewise bitten me more than once when I formatted everything identically, but then forgot that I had tacked something on that isn't updating. And yeah, one feature on my wishlist would be "It looks like you're about to overwrite a ton of data! Rather than make you cut/paste it elsewhere, how about we just shift them!" There's probably some repercussions I'm not considering for why that is problematic, but its a problem worth solving.