And the Winner Is

The winner of the Access 2016 Bible is:


who commented

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
INTO Winners
FROM Commenters
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.

Access 2016 Bible

Announcing Excel Summit South: Auckland Sydney Melbourne in March 2016


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.


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.


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.


EarlyBird 20% discount available for registrations before December 31 2015.
Don’t miss out out on this unique Excel opportunity.


The ModelOff Meetup event  delivers plenty of opportunities to mingle, learn from your peers, talk to the speakers and have fun.



Access 2016 Bible

They’re here.

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:

Access 2016 Bible

Let’s get that to #1.

Filtering PivotTables with VBA? Deselect Slicers first!

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:
Pre Invert

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:
Slicer Dismissed
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. temporarily offline

Hi everyone,

Due to a change in domain registrar my site is temporarily unavailable. I expect it to be back online within the next couple of hours.


Jan Karel Pieterse

Structured Table Referencing and VLOOKUP

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.

Private Sub mxlApp_SheetChange(ByVal sh As Object, ByVal Target As Range)
    Dim vaSplit As Variant
    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, ",")
                End If
            Application.EnableEvents = True
        End If
    End If
End Sub

Private Function IsTableVlookup(ByVal sFormula As String) As Boolean
    Dim vaSplit As Variant
    Dim bReturn As Boolean
    Const sVL As String = "=VLOOKUP"
    Const sTBL As String = "tbl"
    On Error Resume Next
        vaSplit = Split(sFormula, ",")
        bReturn = Left$(sFormula, Len(sVL)) = sVL
        bReturn = bReturn And Left$(vaSplit(1), Len(sTBL)) = sTBL
        bReturn = bReturn And (vaSplit(3) = "TRUE)" Or vaSplit(3) = "FALSE)")
        bReturn = bReturn And InStr(1, vaSplit(2), "COLUMN(") = 0
        IsTableVlookup = bReturn
End Function

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.

Excel User Voice and Workbook Calculate

Excel User Voice

At the recent Excel Global Summit the Excel team were keen to explain how they have started using Excel User Voice to ask for and prioritise product improvement suggestions.

There are 2 important things to note about this:

  1. The Excel Dev Team actually read your suggestions on User Voice!
  2. 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.

Workbook Calculate

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.

So please vote for my suggestion if you think it’s a good idea!

Using Keyboard Shortcuts for Windows Explorer

This content is reposted from with the permission of the author.

In support of International Keyboard Shortcut day, I want to increase everyone’s productivity in using Windows Explorer.

Now I must add that one of the best productivity steps you can make is not to use Windows Explorer! I use Total Commander for most of my file management duties, and there are several (hundred) good reasons why I do. But having said that, if you work on Windows, you cannot really escape Windows Explorer completely, as it is the native file management tool, so most programs, when opening or saving files, simply make use of Windows Explorer for that file management portion. So even if you are working in Word or Excel or a multitude of other programs, when you open or save a file, you are actually using Windows Explorer for that action. And, I have found that some these basic principles described below, with a little creativity, can also be used when doing file management on a Mac (although I am not showing that in this post).

Thus, learning to use Windows Explorer effectively will prove to be a big boost to your productivity (just think how many times you browse to open or save a file each day, and the gains will be readily apparent).

To distinguish between these contexts I will speak, below, of working in Windows Explorer or of working in File Management (some of the shortcuts only work in one of those contexts, or work differently between the two).

I will also not cover “standard” keyboard techniques, like using Tab to move between parts of the window, pressing Enter as the default action, selecting items (files/folders, in this instance) using Ctrl or Shift in combination with the mouse, etc.

One last note. These are specifically for Windows Explorer, meaning they will work for Windows 7, most of them will work for Vista, and a smaller subset will also work for Windows XP. Having said that, apart from the ribbon addition with its own set of navigation tools, most of these functions will also work with File Explorer (i.e., Windows 8 or Windows 10), although I might miss something new from those over here.

Just to help with the explanations below, Figure 1 shows my names for the various parts of the interface, so that you know what I am referring to.

Figure 1 Windows Explorer Interface

Getting there

Charity begins at home, they say. So keyboard shortcuts should start with actually accessing the tools. For many programs (including all the Office programs), these keyboard shortcuts will get you started:

Listing 1 Shortcuts for Opening or Accessing Windows Explorer

File Save As dialog (from most applications)

Ctrl + O
File Open dialog (from most applications)

Win + E
Open Windows Explorer application

The basics

Like most Microsoft programs (I hate working in programs where the developers have not included a good stable of keyboard shortcuts), the Windows Explorer interface comes with a good set of keyboard shortcuts, only they are perhaps less easy to discover than the more traditional interfaces.


Listing 2 Shortcuts for Working with the Windows Explorer Interface

Alt + D or F4
Select Folder Address box (Alt + D selects the entry in the box, F4 selects the box and shows a history of previous entries)

Ctrl + F or F3 or Ctrl + E
Select Search box

Refresh the current window

F6 or Tab
Moves among panes in Windows Explorer (Adding Shift reverses the direction)

Alt + P
Toggle the preview pane (one of my favourite “hidden” shortcuts)

Ctrl + Scroll
Switch between views (e.g., List, Details, Small/Medium/Large/Extra Large icons). (I wish there were a keyboard only version of this, so if you know of one, enlighten me. I find the scroll functionality on this particular tool a bit fiddly).

Cancel in Windows File Management

Ctrl + N
Opens a new window in Windows Explorer

Ctrl + W
Closes the current window in Windows Explorer

Toggle full screen view—can also be done using standard Windows 7 navigation tools, ÿ+Up Arrow for full screen (maximize), or ÿ+Down Arrow for normal size (restore)


Listing 3 Shortcuts for File and Folder Management

Ctrl + Shift + N
Create new folder


Delete selected files or folders (to recycle bin)

Shift + Del
Delete selected files or folders (semi) permanently

Alt + Enter
Open Properties dialog for selected item

Ctrl + Click
Selects multiple individual items

Ctrl + Click and drag with mouse
Copy the item (Windows appends ” – copy” to the file or folder name)

Ctrl + Shift + Click and drag with mouse
Create a shortcut (*.lnk) for the selected item (Windows appends ” – shortcut” to the file or folder name)


Listing 4 Shortcuts for Windows Explorer Navigation

Alt + Right Arrow
Follow breadcrumbs forward

Alt + Left Arrow
Follow breadcrumbs backward

Alt + Up Arrow
Moves up one folder level in Windows Explorer

Displays the previous folder in Windows Explorer (i.e., same as Alt + Up Arrow).
Moves up one folder level in Windows File Management

A short explanation of the breadcrumbs is in order. As you navigate through your various folders, Windows Explorer maintains a list of all the locations you visited. These locations are not always in linear succession (i.e., in a succession of child- or parent folders). For example, if you are in a certain sub-sub-sub-sub-folder of an external drive, and you click on one of the shortcuts in the Favorites section of the Windows Explorer folder pane (don’t forget these, they represent one place where the mouse can save you a lot of time, although they can be accessed in about the same amount of time with the keyboard shortcuts already shown). This move will have taken you to a different folder on a different drive. You can click on the Back button (rather press Alt + Left Arrow) to go back to that sub-sub-sub-sub-folder of your external drive). And having done that, you can click Forward (rather press Alt + Right Arrow) to go back once more to Documents. Pressing F4 (not Alt + D will show you the complete list of your previous locations, and you can move up and down that list with the Up Arrow and Down Arrow arrows, and press Enter to select the location of your choice.

Folder pane

These shortcuts work only in the folder pane on the left of the file browser window:

Listing 5 Windows Explorer Folder Pane Shortcuts

Right Arrow
Expands the subfolders of the currently selected folder or goes to the first child if already expanded

Numeric Keypad +
Expands the subfolders of the currently selected folder

Numeric Keypad *
Expands everything under the currently selected folder (don’t do this on C:\)!

Left Arrow
Collapses the subfolders of the currently selected folder or goes to the parent if already collapsed

Numeric Keypad –
Collapses the subfolders of the currently selected folder

Ctrl + Shift + E
Expands the Navigation pane to the folder in which the selected item is found.

More advanced bits

Remember that if you have clicked in the file list of Windows Explorer, you can easily jump to certain files by typing the first letters of a name (you have to type reasonably quickly). So, for example, in Figure 1 I can get to the WordPress folder (and bypass the Word folder) by typing “wordp.” Also, if I wanted to move through the various folders starting with a “c,” I could just press “c” repeatedly with a short break in between.

Figure 2 Selecting an item by typing its name

Then, of course, one of my personal favourites, is typing the destination I want to get it, instead of browsing by a series of mouse clicks. This relies on using the file syntax that derives pretty much all the way from MS-DOS. The first thing you need to know is that you can specify lower-level folders by typing their names. You specify (see below) higher level folders with “..\” and the root with only “\” (all sans the quotes, obviously). You can introduce a succession of lower-level folders by typing the folder names, separated by “\” (again sans quotes).

Listing 6 Text Entries Used for Specifying Higher-Level Folders

Move one folder up (can be combined x number of times for x number of levels).

In Windows Explorer, takes you to the root of the main drive.
In Windows File Management, takes you to the root of the current drive.

Specifies folder ccc which is a subfolder of bbb, which is itself a subfolder of aaa, which is itself a subfolder of the current folder.

The best way to demonstrate the power of this technique is with a short video. I am saving a page from the Daily Dose of Excel blog by printing it as a pdf from Chrome. This is relevant for a tool that I am developing, and so I want to keep this, and look at it when I work on this tool. I obviously have to indicate where the pdf must be saved. I last saved a pdf in another folder two levels down in the Documents folder, so I first have to move two levels up (which I do by typing “..\..\”), and then down to “Computer Training\Excel\Tips – Excel\Macros\.” This is a lot of scrolling and clicking with the mouse. However, note that as I type the address into the file name box, Windows Explorer gives me all folder and files that match what I have typed. I then use this to select the folders I want in a fraction of the time it would take to do with the mouse (on an unrepresentative sample of one trial, 13s with the keyboard vs 20s with the mouse, for a 35% increase in efficiency!). Also note that by typing the folder names, I “lose” the file name, but Windows Explorer “remembers” it, so that when I eventually reach the folder I am aiming for, the name is given back (I can, of course, then type a different name if I want).

Video 1 Using typing to select a folder or file

One last tip. Sometimes, I have to “load” a file to a certain program. As one example: When I using the Spreadsheet Compare tool to compare two workbooks. I already have Windows Explorer open at the location where the file is (actually, I can do this trick much quicker in Total Commander, but this blog post is about Windows Explorer). In my program (e.g., Spreadsheet Compare) I have to click on the browse button and this opens the Windows Explorer file browser at a predefined location—e.g., a default location like “Documents” or the last used location, neither of which is what I want. But I can copy the address of the location for my file (in Total Commander I can copy the file name with its location, which means I often do not even have to click on Browse). I do this either by clicking in the Windows Explorer Folder Address box and copying the full location there (see Figure 3), or I can, of course, eschew the mouse and do it all with Alt + D, Ctrl + C, Esc (and I’m done before you even had a chance to see what I’m doing!). [As a side note, F4 doesn’t work as well as Alt + D for this one.] Now I can paste the location into the Spreadsheet Compare file browser (of course, I jumped there from Windows Explorer with Alt + Tab), press Enter, and jump straight to the location where the file is. Better yet, I can type “\” and start typing the file name, and Windows Explorer will give me the names that match—even quicker!

Figure 3 Selecting a folder address from the Address box

One last trick. You can also apply filters in the file name box. So, for example, in Figure 4 I want to see only the Excel files, not all the other files, so I type *.xlsx and press Enter. This will filter the display and show me only Excel (and only xlsx Excel) files.

Figure 4 Applying a filter in the file name box