If you don’t have an ad blocker installed, you may have noticed we have a new sponsor: myexcelonline.com. If you do have an ad blocker installed, you should whitelist this page because I only show relevant, unobtrusive ads.
myexceloneline.com offers a free pivot table webinar when you click on the add in the sidebar. I didn’t sign up for the webinar, but I did watch a few of the free videos on that site. They’re very well made, professional videos. I really like the pace – they move along without feeling rushed.
If you have an interest in pivot table or dashboards, give myexcelonline.com a try.
Does it help my chances at all if I casually mention that Daily Dose of Excel is my browser’s home page?
No, it doesn’t help your chances. But it did help that the RAND() function next to your name returned the lowest value between 0 and 1. So good work!
ChipG wins the most clever comment prize for this comment:
SELECT Name, Email
WHERE Name = 'ChipG'
The prize for most clever comment is nothing. But I’m sending him a consolation prize anyway. Let’s see… What do I have handy… I guess all I have is another copy of Access 2016 Bible. That shouldn’t set a bad precedent for future contests.
Thanks to everyone who commented and retweeted. When you do go buy the book, I promise I’ll spend my share wisely.
It has taken me 2 years to put this series of Excel conferences in Australia and New Zealand together.
Now please help me spread the word!
For the first time ever some of the world’s leading authorities on Excel and spreadsheet models are coming together to share their knowledge.
If you use, rely on, tell stories with, worry about, or operate in the advanced areas of Excel, then there’s a track designed just for you.
EXTEND YOUR SKILLS
This is a unique opportunity to:
Learn from six of the world’s leading Excel MVP’s as they discuss the Excel topics most useful to you.
Hear industry leading speakers from around the world give you the latest views on Financial Modelling best practices, standards and spreadsheet risk.
Shape the future of Excel: Interact with members of the Microsoft Excel Dev Team as you explore with them the future of Excel.
Choose the sessions that best suit your needs from 23 masterclass sessions over two days of twin tracks for modellers and analysts.
INTERACT WITH THE EXPERTS AND MEMBERS OF THE EXCEL DEV TEAM
Use your opportunities, including two Panel discussions and Q&A sessions, throughout the two days to ask questions and discuss with the Excel MVPs and the industry experts. The Excel Dev Team members will use this opportunity to learn from their customers, understand how you use Excel and get feedback on your Excel experience.
Who wants one? Leave a comment on this post and I will select one lucky recipient at random. Leave your comment by midnight CST this Friday. And for you international types, I reserve the right to not spend a lot of money shipping this book, so you might be out even if you’re picked.
For the rest of you, you’ll have to pony up the dough for the “#2 in Books > Computers & Technology > Software > Databases” by clicking here:
I’m in the final stages of coding up a commercial add-in that gives you lot more filtering options for PivotTables at your fingertips than you get out of the box. Here’s how it looks, along with the PivotTable its connected to and a native Slicer for comparison:
As you can see, it offers you a lot more tricks than a native Slicer – including a nifty ‘Invert Filter’ function. It also lets you see a lot more items displayed in it compared to a Slicer: 22 items are visible in mine, vs just 17 in the correspondingly sized Slicer. And that’s one of the many beefs I have with Slicers…they take up far too much screen real estate for the scant options they offer. In fact, my version takes up no screen real-estate most of the time: it launches simply by double-clicking the PivotTable field header, and you can dismiss it when you’re done to free up space if you want:
So about that Invert Filter function. I love clicking on that sucker over and over again. It takes about 7 seconds to invert my sample PivotTable that has 1000 items in it (12 items of which are selected in this example). That’s actually pretty fast as far as inverting a PivotTable, because you need to change the .visible status of all 1000 PivotItems, and as per a previous post that’s very slow to do unless you get tricky. And 7 seconds is a vast improvement on the method Microsoft gave you…none. I can’t comprehend why simple options like these are not built in to Slicers and Tables, but your filtering pain is (hopefully) my financial gain. (Yep, this works on Tables too.)
If I click that Invert Filter label, then here’s the after-effect:
Beautiful: 7 seconds, and she’s turned completely inside out. Unless that is, that Slicer happens to be selected. Then it takes a full minute. Why? No idea. Moral of the story: if you’re writing code to filter PivotTables, then you probably want to make sure any Slicers for that field are deselected first. Not disconnected…you can leave ‘em in place quite happily, and the code won’t suffer.
Anyways, that’s the first look at my new baby. It does a lot more than just this inverting trick, too. Among other tricks, it lets you filter PivotTables based on external ranges, and gives you some absolutely fantastic new tools for filtering PivotFields based on just about any tricky search conditions you might want to string together – but that’s a subject for my next post.
Way back when, I wrote a post about naming columns that turned into a post about VLOOKUPs and MATCHes using structured table references. As usual, there was gold in them there comments. Since then I’ve been using the COLUMN() function in my VLOOKUP formulas to identify the return column. Like this:
This has the restriction that your table start in A1. Mine do. One table per worksheet and it starts in A1. This has been working well for me, but I’m sick of typing the COLUMN part. So I made a macro and here it is.
PrivateSub mxlApp_SheetChange(ByVal sh AsObject, ByVal Target As Range)
Dim vaSplit AsVariant
If Target.CountLarge = 1 Then If Target.HasFormula Then
Application.EnableEvents = False If IsTableVlookup(Target.Formula) Then
vaSplit = Split(Target.Formula, ",")
vaSplit(2) = "COLUMN(" & vaSplit(1) & "[" & vaSplit(2) & "])"
Target.Formula = Join(vaSplit, ",") EndIf
Application.EnableEvents = True EndIf EndIf
The IsTableVlookup function needs a little help. I just kept adding conditions as I tested it (and broke it), but I should probably just rethink the whole thing. Or maybe not, we’ll see. With this little gem in my custom class that handles application events, I can type
and it will insert the COLUMN part for me. I could even modify it for tables that don’t start in A1 by subtracting the COLUMN of the first column. The function also assumes you prefix all of your tables with “tbl”. You can change to suit. If you don’t use a naming convention, then I guess you’ll have to loop through all the tables and see if it matches one.
In an effort to be more like Debra, I made a video. As with every video I record with CamStudio, the first few seconds are blurry. I don’t know why.
The Excel Dev Team actually read your suggestions on User Voice!
Starting with Excel 2016 the development and ship cycle is much much faster than the traditional 3 years.
So it really is worthwhile making your suggestions to improve the product.
You get 10 votes on suggestions, and the suggestions are categorised by end-point and product area. So if you want to make a performance suggestion for Excel on Android phones you can focus down, see what other suggestions have been made in this area, and either cast a vote for an existing suggestion or make a new suggestion.
To try this out I made a suggestion (about calculation of course).
At the moment from the UI you can either calculate all open workbooks (F9 or Automatic) or a worksheet (Shift F9).
From VBA you can also calculate a range (Range.Calculate and Range.CalculateRowMajorOrder).
But you cannot calculate a single workbook.
This is really annoying when you have 2 versions of a large slow workbook open, or you have a small rapidly changing workbook open that links to a large mostly static workbook.
My suggestion is to add an additional calculation setting: Calculate Active Workbook Only and from VBA Workbook.Calculate.