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:
54 Responses
Hi Chandoo,
This is awesome *****
Found 6, just one remaining, and I think it should be in sheet2, as I found 1 in each sheet but didn’t found anything in sheet2 (till yet, I am keep looking).
Very cleaver and amazing work, enjoyed a lot…
Thanks Chandoo for this beautiful work.
Wish you have great time at Hyderabad.
Regards,
Khalid
go to AB201 on Sheet2, you will see Panda there !!!
Press F2 in Cell A1 and then read the actual text !!
In Sheet 2 go to Cell AB201. You will find one.
There is one on first sheet, if you press F5 (goto), the word PANDA can be seen there.
Oh I found the last one, (custom format hmm)
Truly Amazing and the beauty of this forum.
You are an Artist Chandoo.
Hi Chandoo,
Wow, you really have magical skills. I am in office and this sheet ate up an hour of my time….didn’t expect that.
I could find 5 of the 7 pandas. Didn’t know one could hide so much data in innocent looking excel sheets.
Thanks!
-Ranjith
yeah! found all 7 panda, time to go to china.
This was very fun and challenging, thanks for posting! I found all of them (well, Sheet1 was tricky, it seems you’re supposed to find the cell and type it in yourself?). Wasn’t sure if it was cool to post the answers here or not, though. Guess I’ll post SPOILER ALERTS so you can skip the rest of the message if you don’t want to see what I came up with.
SPOILER! SPOILER! SPOILER!
My answers appear below.
Sheet1: type PANDA in cell PAN3489
Sheet2: cell AB201
Sheet3: cell J8 (Picture1)
Sheet4: cell H9
Sheet5: expand Chart1
Sheet6: formula = “=MID(ADDRESS(9,2^3*23*59,4),1,3)&BIN2HEX(11011010)”
Sheet7: named range (A1:I18)
Wookie – I would love to get a walkthrough of HOW you figured out sheet 1 and a bit of a formula walkthrough for Sheet 6.
Basically, I don’t know how I could have found that particular cell input message on Sheet 1.
And I have no clue about the BIN2HEX part of the formula…before your hint I was able to get the output to read AN9DA. The change to MID and the addition of that ‘,1’ changed it to PANDA…
Hi Rachel,
To get to the cell in sheet 1 you can press: ctrl G. Then special and then data validation: all. This is also the way to find panda in sheet 7 😉
I agree, this was a fun way to test your ability to navigate through the functionality of Excel! And since you already posted the SPOILER ALERT warning, I should be safe posting a reply to your comment with some solutions of my own… 🙂
I found all the same solutions you did with a few minor changes:
Sheet1: If you notice, cell PAN3489 has Custom formatting. You don’t have to type “PANDA”, just the number 1.
Sheet6: The MID function works as you described, but you can also simply change the RIGHT function to the LEFT function without having to add in the start and end positions for MID.
Sheet7: Yes, the range name for these cells is called PANDA, but you don’t see the actual word in the sheet unless you change the Zoom setting to 39% or less (hence the clue “Z” 39%).
Thanks again for a great post, Chandoo!!
I must admit sheet 7 defeated me, but I have some corrections
Sheet 1 – you type =LEFT(ADDRESS(ROW(),COLUMN(),2),3)&DEC2HEX(ROW())
in PAN3489 to get “PANDA1”. As it is the first panda. I think panda1 is appropriate, but maybe
=LEFT(ADDRESS(ROW(),COLUMN(),2),3)&LEFT(DEC2HEX(ROW()),2)
is better, because it leaves you with “PANDA”
Sheet 6 – I corrected to
=LEFT(ADDRESS(9,2^3*23*59,4),3)&BIN2HEX(11011010)
Picky I know, but who uses mid when a right or a left will do?
I know; that was weird. I did try using a LEFT formula, but I kept getting the $ prefix from the cell address. So I tried a couple of variations using MID and it gave me the result I needed. This is actually the first time I’ve ever tried using a MID formula starting at the first character, but I wasn’t trying to spend a lot of time on it, so I went with what worked.
Sheet1: Type 1 in PAN3489
Sheet6: =LEFT(ADDRESS(9,2^3*23*59,4),3)&BIN2HEX(11011010)
Slight change…
For sheet 1 goto PAN3489 and type in 1. The word PANDA appears.
Love it!
Sheet 6 was my favorite. How many people still know what binary, hex and octal are? :o)
—–Spoilers———
Alternate Solutions
1) Type “1” (not the quotes) in PAN3489 and Excel will turn “1” into “PANDA”
6) The formula Wookie lists also works with LEFT in place of MID
Lot of fun. Solve time ~20 mins.
@ Rob
How this 1 turns to PANDA .. means How this is done by excel any formula or something in VBA
Also how to reach cell PAN3489 .. there are no clues given on sheet 1
You reach PAN3489 by pressing Ctrl + End to bring you to the last used cell in Sheet 1
@ Navdeep I found PAN3489 by going to “Formulas” and then “Name Manager” and saw there was a field called “Clue1” listed in the Name Manger that references 3489. Finding PAN as the column index was just a bit of a lucky guess through trial and error. Then a note in cell PAN3489 when you navigate there says to try “typing something.” I tried scrolling through the Format Cells menu to see if the text typed in the cell needed to be formatted a certain way, and noticed that “1= Panda” was listed in the custom text menu and tried it. A bit brute force, but I think the desired text entry.
Clever!
The Data Validation one took me a bit. Had to resort to brute force.
Thanks for the fun!
Awesome! Found 7 pandas in 20 minutes)))
Sheets 1 and 6 were the best!
Thanks!
Sheet 1: The answer is not type in Panda. Type 1. There’s a special formatting that replaces 1 with Panda.
Sheet 6: Just replace right with left, don’t worry about changing the numbers.
Sheet 7: I found the named range, but don’t know what the Z 39% means. Thoughts?
when you changed the zoom level to 39% or below, you will see the name of namedrange (if any)
WOW! I’ve just found the secret eighth PANDA!
Truly awesome!!!
Am I the first one who figured that out, guys?
Btw, thanks for the puzzle!
Found them all – very inventive. Had to think outside the “box”. Great fun!
It was truly a artists work
chandoo you are grate
all sheets are deigned different from each other
@Wookiee: you have a good for others by posting the answers, Thank you too
it is fun and great invent
Guys I Got 8 PANDA in the workbook… 🙂
[Look Chandoo has against played great trick by reserving one more ester egg, but we are also fan of none other than Chandoo, who can get hold of hidden 8th (untold) ester egg]
Here is the full list:
1) Sheet1: Type 1 in Cell PAN3489
2) Sheet2: Goto Cell AB201
3) Sheet3: Check the picture located above cell J8
4) Sheet4: Goto Cell H8
5) Sheet5: Cells, viz., A4, A10, A16, A21, A29 have all alphabets of PANDA
6) Sheet5: Resize the chart to see PANDA
7) Sheet6: Correct the formula as LEFT(ADDRESS(9,2^3*23*59,4),3)&BIN2HEX(11011010)
8) Sheet7: Range A1:I18 is named as PANDA
1) Sheet1: Type 1 in Cell PAN3489
2) Sheet2: Goto Cell AB201
3) Sheet3: Check the picture in the cell J8
4) Sheet4: Goto Cell H9
5) Sheet5: Resize the chart to see PANDA
6) Sheet6: Correct the formula as LEFT(ADDRESS(9,2^3*23*59,4),3)&BIN2HEX(11011010)
7) Sheet7: Range A1:I18 is named as PANDA
Actually, for sheet7, if you set the zoom to 39% or less, you will see the word PANDA. Yet another PANDA! 🙂
Hi,
i want to know how to manage bill wise manage vendor invoice and payment in excel please suggest.
Thanks,
Ram
Hi Chandoo!
You rock with these amazing skills!
Sheet 1: ??
Sheet 2: ??
Sheet 3: Cell J8
Sheet 4: Cell H9
Sheet 5: A4, A10, A16, A21, A29
Sheet 6: B2
Sheet 7: ???
Sheet1 F5
Sheet2 AB201
Sheet3 Picture1
Sheet4 H9
Sheet5 Chart
Sheet7 Zoom to 30%
I love this time of year and look forward to Chandoo’s egg hunts. Whilst I got all the pandas, I do not understand how sheet 7 works; Where is the source data and why does it only work when zoomed out to 39% or more?
@Leon-K
When you change the zoom level to be less than 40% Excel shows the Ranges which have Names applied to them
Ha ha, that’s fantastic. Thanks Hui. @Chandoo, thanks for yet another method to decrypt worksheets in order to re-build or explain them better to clients.
These were fantastic and kept me intrigued until I could finish them. (Had to look here for help with Sheet1!) Definitely learning a lot about some new formulas. Awesome, Chandoo!
Ok, just saw the notes on the Zoom 39% on Sheet 7. Can someone explain what’s happening here and why PANDA shows up at that level?
@Bryan
When you change the zoom level to be less than 40% Excel shows the Ranges which have Names applied to them
Wow, great exercise.
Tried and solved 5 out of seven and other two solved incorrectly (1 & 6).
Thanks 🙂
Hi Chandoo,
Gr8 …had fun in searching it. I got 5 out of 7.
You are brilliant.
Found all except in sheet6 as not able to understand formula.
thanks
Raja Aurongzeb
Hi,
I am not able to find 1st Panda, which is on Sheet1. Rest all I have found.
Wonderful Chandooji… you are brilliant.
Wow.. Awesome set of puzzles Chandoo!!
Am now trying to figure out how sheet 7 was prepared.. 39% Zoom setting logic.. Can someone help me with a hint?
Thanks!
Looks like this is an XL feature.. Zooming out the worksheets below 40% level, by default displays all named ranges (more than 2 cells)! Had not come across this till date..
Great works! Was having FUN in finding the pandas. Thanks.
btw, I used one basic function (Find, CTRL+F) to find 2 pandas. Simply Find “Panda” within “Workbook”… To my surprise, seems no one mentioned that in the process.
On other other hand, Selection and Visibility Pane is a handy tool to see if there is “extra” shapes for locating pandas hidden in chart/picture.
Had fun doing this, Found 5 and the rest I saw clues on here 🙂
I really enjoyed when finding the pandas.And also I am so surprised.Very Nice thought and Excellent.
This was fun! Thanks!
Nice and fun post. Thanks, Chandoo!