It’s still raw in the middle, a bit.
- PivotTables are great, but why can’t I filter one based on an external range?
- Shape Styles would be cool, if I could add my own styles.
- Slicers are great, but why isn’t there an easy way to instantly get an overview of what Slicers are connected to what PivotTables, and at the same time use the same interface to effortlessly manage this and set up exclusions?
- Charts are great, but why do I have to manually cut and paste them many times in order to make up the equivalent of a Small Multiple chart?
Fortunately, in most cases Excel is pretty programmable. If it doesn’t do something out of the box – or if using the built-in functionality is tedious – then given the requisite skills or the right Google search then you can almost always work around such bottlenecks fairly efficiently.
Unfortunately, Excel isn’t fully programmable no matter what skills you have. The Partial Text Search box that you can use to manually filter PivotFields on partial text pretty much instantly is awesome. But you can’t address it via VBA…it’s for humans only. Which means you’ve got to iterate through potentially large PivotItems collections s-l-o-w-l-y in maybe minutes in order to do something that users can do in seconds. (I won’t mention VBA support for PowerPivot as another example, because PowerPivot is still fairly new. Rough rule of thumb: Don’t complain about something until it’s been sitting around at least 5 years in an unfinished state. At least, that’s what I tell my wife).
I’ve got some great code to handle the Filtering Pivots and the Slicers things. And I can code up a cut and paste thing for Charts pretty easily. But that Partial Text Search box I can’t help you with. Maybe this will help get it on someone’s radar, but obviously including such ‘niceties’ in the in-box functionality isn’t trivial. Otherwise the dialog box for MSQuery (which I still use) would let me expand it. And the Name Manager would let me do the things that the better Name Manager will do. And so on.
So presumably these things are not trivial to enhance, or they would be enhanced already. But here’s the thing: by comparison, my code for filtering pivots or managing Slicers is trivial to roll out to users compared to the effort required to add this functionality natively.
So question: Why don’t MS supplement their great unfinished app by building and offering to users useful workarounds in one of the most agile-ready platforms there is…VBA? Why aren’t they monitoring forums and blogs for the best and brightest productivity enhancements, and buying the IP from content creators at a song, then offering it to users as add-ins that plug the gap until they get around to finishing Excel?
Regards,
Jeff Weir.
Cosmetic Surgeon.
Ooooh, good question!
My take is that MS have for years not wanted anybody to use VBA, promoting the execrable VSTO as the preferred automation “solution”, ignoring one minor inconvenience: that most Excel users lack the technical skills to work that way and don’t see why they should, given that they already have something that does most of what they want.
I learned something today:
ex·e·cra·ble
adjective \ˈek-si-krə-bəl\
1. detestable execrable crimes
2. wretched execrable hotel food
e.g. Living conditions in the slums were execrable.
Her execrable singing finally brought a complaint from the neighbors
That’s a cool word.
Absolutely agree that most Excel users lack the technical skills to work that way and don’t see why they should, given that they already have something that does most of what they want.
I’m starting to do more and more training where I work that focuses on showing what kinds of efficiency outcomes are possible, rather than how to use things (although obviously there is crossover). I figure that users don’t have to know how to do something in order to become more efficient. THey just need to know that it can be done. Google will take them the rest of the way.
I’m also teaching them how to Google. (Which reminds me of a post by Jon Peltier some time back titled Does Excel “Suck”?: “Oh my, such anger, such invective, such inability to use Google.”
hello, Jeff.
Pivot tables can filter based on another table – set up the pivot table from the multiple data sources with the appropriate SQL. This approach requires neither VBA nor formulas and should be quick. It has been available for many years. SQL might be like
SELECT C.* FROM CurrentTable C INNER JOIN FilterTable F ON C.commonfield = F.commonfield
This will generally be possible.
Or a different way via a formula, may be acceptable: add another field to the pivot table’s data, “ShowInReport” and populate with TRUE/FALSE via a formula like =ISNUMBER(MATCH(commonfield, filtertable,0)). Include this field as a pivot table page field and set to TRUE.
Enjoying the posts. Thank you
Thanks for the comment, Pete. I’m enjoying the writing. You’re right of course that filtering Pivots can be done in most cases. But I bet less than 0.001% of PivotTable users know how. Certainly most won’t know how to use SQL and/or how to use MSQuery. And then you’ve got to amend the connection string each time you save the workbook with a different name or email it somewhere. So it can be done quick by someone with the requisite skills. But not at all by most pivot users.
My point with this post is that this functionality should be available right out of the box – it would be very simple for MS to implement either as an add-in or as in-build functionality. In fact, check out my post at http://dailydoseofexcel.com/archives/2013/11/14/filtering-pivots-based-on-external-ranges/ on my code approach.
The formula approach requires you to have a pivot based on data stored in a range. And I just don’t think that users should have to jump through such hoops to do something that should be core functionality.
Thanks again for the comment. Keep ’em coming.
The article starts with “PivotTables are great, but why I can’t I filter one based on an external range?”
Yet just above is the comment, “You’re right of course that filtering Pivots can be done in most cases.” [And this with just a simple formula, btw.]
And then “My point with this post is that this functionality should be available right out of the box …”
In my experience a small minority of Excel users know about pivot tables at all. Only a small minority of them would want the functionality described, and it can be done easily in most cases. If users don’t know how to do these things, they can google or ask in a question forum.
I’m not sure why a whole separate article has been created with lots of code to do this quite specific task via VBA when a simple formula does the job. No VBA is needed.
I don’t understand the comment that MS should provide this very specific functionality when it is just one of many possibilities – likely all of which can already be handled, and probably have been since pivot tables were introduced. Excel 95?
Another user might want to filter to exclude records listed in an external range, for example. Or filter from data in two separate external ranges. Or three. Or whatever.
So, rather than provide one specific function to filter from an external range, an approach is provide the tools and let the users work out how to do what they want. And this can currently be done generally with simple formulas or otherwise some simple SQL. Both without VBA which gives even more possibilities.
The pivot table is a report. Rather than make the report and then add extra filtering from an external range, make the pivot table from the desired (filtered/modified/aggregated/whatever) dataset initially.
Pete: Re your comment Rather than make the report and then add extra filtering from an external range, make the pivot table from the desired (filtered/modified/aggregated/whatever) dataset initially. I agree, where that is efficient or practicable. But this isn’t always the case. They don’t always have the skills. THey don’t always have the access to IT or to the Data in a way that will let them add formulas or amend SQL, even if they could. And they don’t always have the time or the will. Nor should they, neccessarily. I’ve got Senior Managers using this code. They love it…they can filter any pivot that they ever open instantly. Not when someone can amend things for them. And they get it on any pivot in any workbook on any day they like. And I’ve got little old ladies in accounts using it and loving it for exactly the same reason. And I use it. Why the heck would I want to muck around adding formulas to create an extra dimension when most of the time I only want to filter a pivot one time for a one-off exercise? Forget it…I’ll just hit Ctrl + Shift + F and be done while you’re still looking for the source data.
Most of the people I support are not data analsts, but work in accounts payable and HR and other corporate roles where they don’t ever need to even do a VLOOKUP. Many of them know how to drive a pivot, because that’s the form that the data in their reporting comes to them in. But they don’t necessarily know how to set one up, let alone know how to put a VLOOKUP or SEARCH function together in a way that allows them to filter a pivot. And IT don’t see it as their job to help low-level users run business tools. So for them, it is not simple to add a new dimension to a pivot, in order to filter. And I’m not always around to do it for them. Because there’s just one of me, and there’s a couple of hundred of them. And so before I wrote this code, they did it manually. Which added minutes – sometimes hours – of unproductive drudgery to their days. But now they just use this code. Which I’m in the process of incorporating into a larger add-in that does all sorts of things that you would otherwise need time or experience to do. With a few tweaks, this code will invert a filter selection in a pivot. And it will do it faster on large pivots than any code I’ve come across.
Why has a whole separate article been created to do a quite specific task? Because a) it is a very good coding challenge and the mere act of me coding it up made me a better coder. And b) because I think it is useful – not just to users who can plug and play, but also to people who create dashboards and the like. For instance, I use a routine similar to this one that lets users filter multiple pivots all based off different data sources pretty much instantly. All they have to do is type their search strings in a box, and *bing* the entire dashboard gets filtered without them having to lift a finger. I don’t fancy management having to learn Excel or get their hands dirty just to get more oomph out of their pivot-based management reporting and dashboards. So yes I use formulas to do heavy lifting when it’s me doing the lifting. But I also use VBA for lightweights. Not everyone can, should, or will become advanced enough to follow your ‘better’ approach.
Appreciate your comments, Jeff. Thank you. Via VBA you’ve provided a good solution.