Tables, PivotTables, and Macros: music to your ears

Howdy folks. Jeff Weir here again. You may remember me from posts such as What would James Bond have in his Personal Macro Workbook and my now infamous music review. Today – and this truly will be music to some ears – we’re going to concentrate more on the former and less on the latter.

Today we’re going to talk about that mystical place where hard tasks just disappear into thin air. Where is that place, I hear you ask? (I have supernatural powers). In that famed triangle of folk-law, of course:

No, not that one. Stop jumping to conclusions and pay attention, will you! This one:

Suddenly not quite as intrigued? Well, sure…if you add these three things together, no compasses go haywire, no spooky fog will obscures all physical features, and no planes, ships, or movie budgets will go missing. But plenty of tedious mind-numbing pivot-table formatting will disappear. Because combining these three things together in the right way could quite possibly remove ship-loads of needless clicking from your day. Let me explain.

Turn the Tables on Excel

The problem with Excel is that it is so damn high-maintenance: if you add new data to a spreadsheet, you might have to adjust the references in dozens of formulas and charts that point to the original data, so that the new items show up in your calculations and charts.

That’s where Tables come in. Excel Tables – known as ListObjects to VBA developers – were introduced in Excel 2007, and if you’re not familiar with them then I strongly suggest you check out Chandoo’s Introduction to Structural References and this great video he did with MrExcel.

A large part of their appeal is that they spookily expand to accommodate anything you put in them. Even better, anything pointed at that table – Formulas, Charts, Data Validation lists – gets automatically updated at the same time. Here, let’s look at an image, shall we?

Here’s a table that also has a formula, some Data Validation, and a Chart pointed at it. As you can see, whatever is in that table shows up in that formula, validation, and chart too.

I’ve put a red box underneath the table above, to highlight where we’re shortly going to add some new data. At the moment, the table above has got our weekly diet plan in it: Vege, Fruit, and Meat. Hardly a balanced diet. Watch what happens when we add something new under the table where that red box was…because man cannot live on fruit, veges, and meat alone. Well, not this man anyhow.

Wow, will you take a look at that: the table expanded automatically to hold our new category of ‘Beer’ (just like my stomach does). And wow…those three things we had pointed at that table all got updated automatically, before we could say ‘Prost’. Spooky!

So how do Tables help with PivotTables?

First, let’s look at life without tables. Let’s say we make a PivotTable out of this ‘traditional’ block of data, and we make it display Total Sales by Region:

*BING!*

Later on we scroll to the bottom and add a new record for a whole new region:

…and then we refresh our Pivot:

*BING!*

Ahh, that’s right…when we initially set up our PivotTable, that Create PivotTable dialog box had a hard-coded range in it:

…which means we need to click this puppy:

…and then change the hard-coded reference accordingly so that it includes the new data:

…and we need to do that each and every time we add new data. Maybe monthly. Maybe weekly. Maybe daily. Maybe for multiple pivot tables. Tedious.
 
 

Take two, with Tables

This time, we’ll turn our source data into an Excel Table first. There’s a couple of icons in the ribbon you can click to create a table – and bizarrely those icons are different – but I like to use the keyboard shortcut of Ctrl + T, which is easy to remember, as T stands for Terrific Table.

*BING!*

And now let’s create a PivotTable out of it:

*BING!*

Now watch what happens when we scroll to the bottom and add the new date for our new record:

Well that in itself is pretty nifty. Yep, folks…tables have some smart functionality that in themselves can save you significant faffing around. Now let’s put in the rest of the data for that new record:

 
And here’s the punch-line: look what happens when you refresh that Pivot:

*BING!*

…and I can tell from here just how excited you are by that from the look on your face (you left your web-cam on again), because…

Let’s throw some Macros into the mix

I promised you I was going to save you a ship-load of clicks. So far I’ve saved you…let me see…exactly one. What about them others I promised?

Well, given we’ve just established that Pivots love Tables more than I love this album, let’s whip up a macro that will not only create a PivotTable but also automatically turn the source data range into an Excel Table. Then we can assign it to a handy shortcut key – something like Ctrl + Shift + P (“P” for Pivots…I know what you’re memory is like) – so that all you have to do is select a cell in your raw data and in one keyboard shortcut do two things that otherwise would require several clicks. Now that would be worth reading this far, wouldn’t it!

And while we’re at it, let’s code it up so that if you run it on an existing PivotTable, it will retrospectively turn the source data into a Table if needed, and then re-point the Pivot at that Table. That would be handy too.

But why stop there? How ’bout we get it to do a whole bunch of other tiresome things that we routinely do manually in order to set our Pivots up just the way we like ’em. Because if there’s one thing I just can’t stand about pivots, it’s the huge number of things I have to do every single time when I whip one up in order to get it looking just how I like it.

So – as Prince once said, “Let’s go crazy“:

• Let’s make it put the PivotTable that we just created at the edge of the used part of the sheet that we’re working in – which is usually right by our data, and usually exactly where I actually want it – rather than having to uncheck that pesky “In New Worksheet” button and then having to manually select the range where I want my new pivot to go;
• Let’s have it cut that PivotTable with a Ctrl + C, so that if we choose to, we can then navigate to any cell we want and simply hit Ctrl + V to paste it into it’s new home. (And if we choose not to move it, we simply do nothing, because it actually stays where it is unless we actually paste it somewhere else;
• Let’s change the Report Layout to “Show in Tabular Form” instead of the default “Show in Compact Form” setting that I never use;
• Let’s turn on the “Repeat All Item Labels” option that I practically always want;
• Let’s turn off Subtotals, because I almost never need them on any field, let alone all of them;
• Let’s turn off Grand Totals, because those totals don’t always make sense in the context of my Pivots, and they’re simple enough to turn back on if I do need them;
• Let’s turn off that really annoying “Autofit Column Width on Update” setting, so that my pivot doesn’t stupidly screw up all my carefully set up column widths each time I refresh it;
• Let’s turn off the “Save Source Data with file” option. No point saving the PivotCache along with the source data, given it only takes an instant to recreate the PivotCache from scratch in the event that we need to. (More on this here).

Wait a minute Jeff…you missed a really annoying thing…

Oh yeah, so I did. Let’s make the Pivot automatically adopt the same source formatting as the original data has – like Mike does over at the Bacon Bits blog – because if there’s one thing guaranteed to make an Excel user do this:

…it’s either an off-topic post, or (more likely) this:

Here’s the code that will free you from this Pivot Hell:

Just cut the below code, and paste it into your Personal Macro Workbook. Don’t know what that means? Think I’m speaking gibberish? Head over to my earlier post What would James Bond have in his Personal Macro Workbook to find out just how easy this is, and you’ll be a ninja plus in no time!

Sub InstantPivot()

'   InstantPivot: Just Add Water
'   Assign this to Ctrl + Shift + P or something like that.

'   Description:    * Turns selection into Excel ListObject
'                   * Makes a Pivottable out of it at the edge of the used range
'                   * Applies my preferred default settings
'                   * Selects the Pivot and cuts it, so that
'                     you can then use arrow keys
'                     and Control + V to paste it where you wants
'                     without having to touch that unclean dusty rodent
'                     you keeps at the edge of your Desk.Usedrange
'


'Here's the settings it applies.
'   1.  Changes the Report Layout to "Show in Tabular Form"
'   2.  Turns on  "Repeat All Item Labels" option
'   3.  Turn off Subtotals
'   4.  Turn off Grand Totals
'   5.  De-selects the Row Headers option from the Design tab.
'   6.  Turns off 'Autofit Column Width on Update'
'   7.  Turns off 'Save Source Data with file' option.
'   6.  Adopts the source formatting


'   Programmer:     Jeff Weir
'   Contact:        weir.jeff@gmail.com or jeff.weir@HeavyDutyDecisions.co.nz

'   Name/Version:   Date:       Ini:   Modification:
'   InstantPivot    20140213    JSW     Initial programming
'   InstantPivotV2  20140216    JSW     Added error handler and check for multiple cells
'   InstantPivotV3  20140216    JSW     Adopted SNB's approach of setting numberformat while turning subtotals off
'   InstantPivotV4  20140216    JSW     If run on existing pivot that is not based on ListObject, turns source into ListObject
'   InstantPivotV5  20140216    JSW     Now ignores Values fields and doesn't apply format if pf.function = xlCount
'   InstantPivotV7  20140216    JSW     Now ignores Values fields and doesn't apply format if pf.function = xlCount

'   Inputs:         None

'   Outputs:        PivotTable is formatted accordingly. World recognizes my genius and forgives me my occasional off-topic post.

    Dim pc As PivotCache
    Dim pf As PivotField
    Dim pt As PivotTable
    Dim lo As ListObject
    Dim rng As Range
    Dim strLabel As String
    Dim strFormat As String
    Dim i As Long
    Dim wksSource As Worksheet

    
    'Check that we're dealing with a version of Excel that supports ListObjects
    ' In fact, play it safe, and ignore Excel 2007.
    If Application.Version >= 14 Then
    

        On Error Resume Next
        Set pt = ActiveCell.PivotTable
        On Error GoTo errhandler
        If pt Is Nothing Then
            Set lo = ActiveCell.ListObject
            If lo Is Nothing Then Set lo = ActiveSheet.ListObjects.Add(xlSrcRange, Selection.CurrentRegion, , xlYes)
            Set rng = Cells(ActiveSheet.UsedRange.Row, ActiveSheet.UsedRange.Columns.Count + ActiveSheet.UsedRange.Column + 1)
            Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=lo)
            Set pt = pc.CreatePivotTable(TableDestination:=rng)
        Else:
            'Check if pt is based on a ListObject.
            '  *  If so, set lo equal to that ListObject
            '  *  If not, turn that source data into a ListObject
            On Error Resume Next
            Set lo = Range(pt.SourceData).ListObject
            On Error GoTo errhandler
            If lo Is Nothing Then
                Set rng = Application.Evaluate(Application.ConvertFormula(pt.SourceData, xlR1C1, xlA1))
                Set wksSource = rng.Parent
                Set lo = wksSource.ListObjects.Add(xlSrcRange, rng, , xlYes)
                pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=lo.Name)
            End If
    
        End If
    
        With pt
            .ColumnGrand = False
            .RowGrand = False
            .RowAxisLayout xlTabularRow
            .RepeatAllLabels xlRepeatLabels
            .ShowTableStyleRowHeaders = False
            .ShowDrillIndicators = False
            .HasAutoFormat = False
            .SaveData = False
            .ManualUpdate = True
            If ActiveCell.CurrentRegion.Cells.Count > 1 Then
                For i = 1 To .PivotFields.Count - .DataFields.Count 'The .DataField.Count bit is just in case the pivot already exists
                    Set pf = .PivotFields(i)
                    With pf
                        If pf.Name <> "Values" Then
                            .Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
                            On Error Resume Next
                            .NumberFormat = lo.DataBodyRange.Cells(1, i).NumberFormat
                            On Error GoTo errhandler
                        End If
                    End With
                Next i
            End If
        End With
        
        ' Get DataFields to match the formatting of the source field
        ' Note that this will only be neccessariy in the case that we're
        ' running this code on an existing pivot
        On Error GoTo errhandler
        If pt.DataFields.Count > 0 Then
            For Each pf In pt.DataFields
                If pf.Function <> xlCount Then pf.NumberFormat = pt.PivotFields(pf.SourceName).NumberFormat
                ' Do away with 'Sum of' or 'Count of' prefix etc if possible
                On Error Resume Next
                pf.Caption = pf.SourceName & " "
                On Error GoTo errhandler
            Next pf
        End If
    
        'This needs to go before the .Cut bit, otherwise the .Cut stack gets wiped
         With Application
            .ScreenUpdating = True
            .EnableEvents = True
            .Calculation = xlAutomatic
        End With
          
        With pt
            .ManualUpdate = False
            .TableRange2.Select
            .TableRange2.Cut
        End With
    Err.Clear
errhandler:
            If Err.Number > 0 Then
                With Application
                    .ScreenUpdating = True
                    .EnableEvents = True
                    .Calculation = xlAutomatic
                End With
                MsgBox "Whoops, there was an error: Error#" & Err.Number & vbCrLf & Err.Description _
                         , vbCritical, "Error", Err.HelpFile, Err.HelpContext
            End If
    End If
    
End Sub

What will you do with all your new spare time?

I’m glad you asked. Why, you’ll have PLENTY of extra free time now in which to give my new favorite album a listen:

TRANSMISSION ENDS

—Redux—
It’s just gone 22 minutes past Midnight here in New Zealand, and I’ve just got back from Tami Neilson’s album release party for her album Dynamite. It certainly was. I’d say sparks were flying off guitarist and co-producer Delaney Davidson’ guitar but that would be poor poetic license. Because in actual fact, blazing chunks of molten steel were flying off of that beast’s bridge.

I’d say that Tami was twice the woman live than she is recorded. And that’s not too far from the truth, because she is 6 months pregnant, and counting. Not that that was any hindrance whatsoever to her belting out an incredible lyric. That baby of hers is going to have one hell of a set of lungs, if genetic predisposition is anything to go by.

And if we focus on the nurture side of the nurture/nature argument, then that baby is going to have one hell-of-a sense of rhythm too, because it had front-row seats to the craftiest drummer I have ever heard. Why at one stage that drummer threw down his sticks and wrestled beats out of that kit with his bare hands like he would wrestle a live bear. And the bear definately came off second best.

And then there’s the bass-player. Not only was he a damn fine singer, but he also had the longest g-string on stage by far. (Explanation: one of the strings on a Bass guitar is tuned to ‘G’, as is one of the strings on a Guitar. And because a Bass Guitar has a longer neck than an electric rhythm/lead guitar, that G-string is longer. I know, it’s a bad joke.) Eligible Bachelor Number Two was his name. Fastest fingers in the west.

Don’t even get me started on Eligible Bachelor Number Three, the rhythm acoustic and fiddle player. Ye-haw and yes-siree does not even begin to cut it as a compliment to this dude. If I still had a soul and a willing buyer for it, I’d only end up with half the riffs this guy can pull off in exchange for it.

What a night.

🙂

Here’s what you missed: