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:
35 Responses to “Tables, PivotTables, and Macros: music to your ears”
Jeff
So disappointed that your last line wasn't "Oh, What a night"
With the following hyperlink http://www.youtube.com/watch?v=wD7LNDLKfWY&feature=kp
Ha!
Great post. I laughed, I learned. What else could I ask for on a Friday morning.
What else could I ask for on a Friday morning.
A server outage. Followed by a phonecall from the boss saying "Don't bother coming in today...the computers aren't going. Have a day off on us",
🙂
OK folks, once is unlucky, twice is just careless. Jeff, I don't know what you put in your contributions that breaks the email versions of your posts, but you did it again. I clicked on the latest chandoo email just now, and once again my email app (Groupwise) was a goner. Just not responding. Really difficult to kill it and start over, and I thought for a while it was going to take everything else down with it and require forcing a power off, but fortunately not; once I managed to close Groupwise, other apps came back to life. So I unsubscribed from the email list 🙁
Maybe it's something to do with the embebbed YouTube videos? That's what my two most recent posts have in common. But I don't follow how that makes me 'careless'? Sounds like you're holding me personally responsible for some Groupwise thing.
I"ve had the same thing happen in the last two emails.
But when I check some of the older emails I have saved, I'm getting the same thing with them.
Those older emails opened up perfectly fine when the came in, not they are causing a not responding, and even using ctrl,alt,del it takes ages and ages to shut them down.
Hope someone has a fix for this
🙂
typo ... should read ... now they are causing
Hi,
As far as I recall - since "Excel 2007" was launched, - if you transform the Pivot Table's source range from 'cells range' into 'Table' - there is NO NEED to re-adjust the PivotTable's source range, because it is re-adjusting automatically after the PT has been 'Refreshed' (and, yes, it is a great PT disadvantage that one must run a refresh after every change in the data source (unless you use a Worksheet_Change n Event-Macro in order to refresh the PT).
Michael (Micky) Avidan
As far as I recall – since “Excel 2007? was launched, – if you transform the Pivot Table’s source range from ‘cells range’ into ‘Table’ – there is NO NEED to re-adjust the PivotTable’s source range
Yes Micky, that is exactly what my post says.
Well...., Jeff,
Although English was not my mothers tongue - I reffered to what you wrote [quote]:
"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." [end of quote]
So, as far as I understood this mean that the user must re-adjust the PT range whenever a record/row is added ? and I'm talking on a situation BEFORE converting the source range into "Table' ?
If you would have emphasized that you refer to "Excel 2000-2003" then you were right - but in my comment I reffered to 2007 and above which are nowadays the most popular versions.
Did I made myself clear or am I missing something ?
Thanks, Micky
So, as far as I understood this mean that the user must re-adjust the PT range whenever a record/row is added ? and I’m talking on a situation BEFORE converting the source range into “Table’ ?
Correct.
If you would have emphasized that you refer to “Excel 2000-2003? then you were right – but in my comment I reffered to 2007 and above which are nowadays the most popular versions.
My comment applies to any version. I'm saying that:
That U R Applying 2 all kind of version was understood (by me) right from the beginning.
What was (and still) missing, within the content(!) the tip(!), is the second halt of your comment:
If you happen to have Excel 2007 or later, then if you change that range into a table before you create the PivotTable, then you don’t have to use that ‘Change Data Source’ button ever again.
"within the content(!) the tip(!)" becomes vital because not everyone reads all the following comments especially when they get 2 B as long as volume #1 of "War and Peace" by tolstoy. 😉
Have a great weekend,
Micky
Okay, I think you're talking about the fact that I forgot to mention that tables are only available in Excel 2007 or later?
If so, have amended the article to include this:
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.
Cheers.
Jeff
Without suggesting that this technique is superior to "formatting" your PT source data as a structured table, there is an alternative: create a Defined Name that refers to the source data range and link the PT to that name. However, rather than using fixed cell references in the RefersTo field of the Defined Name, use a combination of OFFSET and COUNTA (or COUNTA) functions to make the references 'dynamic' by adjusting the width (columns) and height (rows) expand/contract to match the size of the range. The RefersTo formula is like this:
=OFFSET($A$1,0,0,COUNTA(A:A),COUNTA(1:1)) where A1 is the first field heading, A is a column in the source data that will always have a string value for every record (doesn't need to be first column), and row 1 holds the field headings.
...and by the way:
During the 1990's, 5 string Bass guitars became widely used by professional bassists.
The fifth string is a low "B".
Michael (Micky) Avidan
Yeah, that's why I said 'usually'
Doesn't 'usually' means most of the time ?
If so - then the word 'rarely' is more appropriate...
Haved changed my somewhat labored joke to this:
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.)
It might be a lame joke, but it makes an excellent bumper sticker:
Bass players have longer G-strings
🙂
Hay Jeff!!
Long time listener, first time caller... hahhaah
1) you're posts are insightful and crazy entertaining
2) have you had your meds lately!?
HAHAHAHHA
Yep. Took the whole bottle, in fact.
Nice job, Jeff! I don't think I've ever read an Excel post that made me laugh that hard. For my cultural education though, I'm hoping that you might be able to roll a bit more hints to listen to country music in to the next couple of posts. I'm not quite convinced yet. 😉
Hi Ken. I added a video of Tami's latest song to the bottom of the post. Check it out. Not country. Great song. Great vid too. There's other songs on the album in the same style. Especially the title track "Dynamite"
Give it a listen, and post back on your thoughts...good, bad, or indifferent.
Laughed my socks off, but more importantly, learnt a massive amount. Given me a whole new perspective on the development of dynamic reports and charts.
This is really the way applications should be taught. Love it, keep it up - just don't force me to listen to Country Music - PLEASE!
Mark - that is the highest praise a man could get. Thank you most kindly. Check out the vid of Tami that I added to the original post...not country. Just awesome.
Jeff, awesome post as usual! Loved the infamous post as well - actually loved the comments mainly (well handled) and the references in your support elsewhere on the web! Great code here thank you, very helpful as is and great to review as a learning tool as well. Cheers from across the Tasman, I'll go now and have a Monteiths or something in your honour. 🙂
Monteiths? NOOOOOOOOOOOOOOOOOOOO!
Thanks for the feedback Dave.
Hey Jeff, really enjoyed the post. Will definitely be sticking this one in my personal workbook!
Jacob
Hey there. Not just groupwise, I got javascript errors and crashes on the email too.
So since I'm using Excel 2007 on a locked down system that I don't have any upgrade privileges on, does that mean that the line
'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...
Means I don't get any time to go listening to music???
Hi Crystal. You could try changing that to If Application.Version >= 12 and see if Excel plays ball. Let me know if you get any errors and on what line they occur, and I'll take a stab at amending it accordingly. Unfortunately I don't have 2007 anymore to test.
[…] word about everlastin’ VBA serenity. You may remember me from mah preeeevious sermons such as Tables, PivotTables, and Macros: music to your ears and Big trouble in little […]
Hi Jeff Sir,
Great post, I learned very new things and enjoyed your post.
Thanks for sharing.
Have a great weekend.
Khalid
Hi,
I have a lot of pivots for which show the data for 5 weeks. Now every new week, I have to take the oldest week number out and select the new week in. Is there a VB script to do this?
Hi,
I am trying this out with Excel 2013 and the table does not seem to want to add the new lines of data to the table. Also, the Pivot table that was created after the table data was entered does not update on it's own. They reside in separate workbooks if that makes a difference.
Thanks
I have checked for myself and it does not seem to work in Excel 2013 the way that was shown above. I could be missing a step or something....but I don't think so.