Quickly Discover Patterns and Trends in Your Data using PivotTables in Excel October 23, 2019

This is a recording of the October 2019 monthly webinar in the Excel MVP Webinar series. In this free 59-minute webinar, Bill Jelen covers many pivot table techniques:

Table of Contents
(0:00) Welcome to the Pivot Table Webinar
(1:36) Traditional Pivot Cache Pivot Tables
(2:45) Setting up data for a successful pivot table
(3:40) Using Alt+Enter for headings
(4:00) Avoiding tiny blank columns between columns
(4:15) Accounting Style Underlines
(5:35) Pivot Table 101 – Your First Pivot Table
(7:55) Changing Number Format in Pivot Tables
(8:35) Moving fields around in a pivot table
(9:40) Drill-down on any pivot table number
(10:45) Refreshing pivot table if the underlying data changes
(11:25) Year-over-Year Pivot Tables – Grouping Dates
(14:05) Adding a formula outside the pivot table and problems with GetPivotData
(16:15) Variance percentage with % Change From Previous
(18:00) Top 10 Pivot Table - Introduction
(18:15) Changing heading from Sum of Revenue
(18:45) Changing the calculation in a pivot table
(19:10) Double-click a field heading to open Field Settings dialog
(19:20) Percentage of Column Total
(19:40) Rank column in a pivot table
(20:00) Running Total in a pivot table
(20:35) Filtering to Top 10
(21:45) Grand Total seems to be wrong after filtering
(22:10) Include Filtered Items in Totals is greyed out
(22:55) Dan in Philadelphia has a solution
(23:25) AutoFiltering a Pivot Table using a Hack
(25:30) Filtering using the Filter drop-zone
(25:45) The pain of (Multiple Items) header and Shrink to Fit
(26:25) Replacing Filter area with Slicers
(26:45) Resizing slicers
(27:30) Replicate a pivot table for each customer (Szilvia)
(28:50) Tabular form instead of Compact Form
(29:55) Suggesting changes to the Excel team with Excel.UserVoice.Com
(30:20) Defaults for all future pivot tables
(31:30) Data Model Pivot Tables - Introduction to Part 2 of Webinar
(32:30) Mash up data from multiple sheets into one pivot tables using the Data Model
(35:55) Count Distinct in a pivot table
(37:35) True Top 10 Report With Correct Total without any hacks
(39:30) Median in a Pivot Table using DAX formula
(41:35) Showing Text in the values area of a Pivot Table
(45:25) One set of Slicers for two different data sets
(48:25) Create a Pivot Table on a Map using 3D Map in Excel
(52:10) Is there a use for GetPivotData?
(57:20) Recap
(58:49) Pitch for next month's Liam Bastick webinar

Transcript of the video:

Yeah, Hey. This is Bill Jelen, now from Mister Excel we could start getting started here in about 1 minute. Just hang on, I'll get ready to start.
All right, yeah, let's dive in here. I want to thank everyone for signing in today or if you're watching the recording here. Today's webinar. Modern Excel Web and R quickly discover patterns and trends in your data using Excel Pivot Tables. I'm bill Jelen from misterexcel.com.
I love talking about pivot tables. I've written a lot of books on pivot tables along with Mike Alexander the pivot table data crunching series and whether you've never created a pivot table before you're a pivot table pro. I'm hoping to pick up some tricks.
Uh in this one hour session. I want thank Microsoft for hosting this once a month. They are putting on an Excel Seminar with a different MVP. I know that Liam Bastic will be there next month, so check that one out and Chandu last month, so there's recordings out there Alright, so I'm going to start off in the first half. You're talking about traditional pivot cache pivot tables. Just insert pivot table not checking the box read that data to the data model and then this first demo I'm going to do.
Pivot Table 101 how to create your first pivot table and rearrange that pivot table and then some cool tricks with year over year pivot tables? How to group daily dates up to months and years. Then there's a problem bug. I don't know if it's a bug they would say it's a feature with getpivotdata away to avoid that first by turning off Getpivotdata or a solution with percentage change from previous year. Uh then Top 10 report, which also has another uh thing that annoys me. I suppose it's right to some people and and in a way around to solve that so I'm going to show you some of the Gotchas.
Let's say the things that can be a problem with pivot tables and then how to solve those will take a look at show report filter pages and then using slicers instead of the filters.
So let's just jump over to Excel here and will start to dive in so the data set. I'm going to use in this first half.
Uh is this data, with region product date sector.
Which is like industry customer and then quantity revenue cost of goods sold which is abbreviated there is COGS or cogs and then profit and this is a tiny data set only has 563 rows of data. Plus, a heading so 564, there at the bottom with these concepts would work whether we had 500 rose or 500,000 rows right so we have a nice clean data set here. One row of Headings. At the Top no blank rows. No blank columns.
Those are really important that you have headings above everything I had a manager once.
Who would create headings like this you take that word profit and put it on 2 rows like that and that is a problem because I won't know how to deal with that, so you want to keep that heading in a single row. And if you really needed to look like it's on 2 rows my favorite trick here is to press alt and then enter goes to a new line in that sell so alt enter. It's like turning on rap text. But it allows you to control where the words rap so that's a little trick there and then.
Yeah, the other thing you have to watch out for this was another manager I had this guy.
This guy drove us crazy, he would insert tiny little columns between the columns and make them real narrow like that, and you do that between every single column and the reason he was doing it was all because of this crazy reason that he didn't like how Excel underline things you want to underline dated only underlines just the characters that are there and he wanted to underline across the whole column so he was using the bottom border instead of the underlying but of course, if you put a bottom border here.
Not all of these cells, it becomes just a single long line and that's not what he wanted.
He wanted individual little underlying so he would add those extra columns in sideways bottom borders would look right, which is just a really dangerous thing to do because someone's going to sort part of this data, and not all of this data, so now. I don't work for that guy anymore.
But heck you might be working for him or if you ever end up working for him or someone else who'd likes to do that. Check out this amazing feature in Excel. I love all of the crazy obscure features in Excel file.
Choose those headings and then come down here to an accounting style. Now you would think that that's going to change the underlines is going to change the dollar signs currency and the parentheses and things like that. But it also doesn't amazing thing here with our heading so I changed to accounting style and then turn on the underlying see as a completely different underlying.
It just it doesn't just underline DATE. It underlines 90% of the cell like that right so if you are always trying to get to a solid block of data and if you have blank columns because your managers trying to create those. That was underscores uh an accounting underline is a great way to go alright. So here's our data and I want to create a pivot table from this my manager came to me and said Hey. I need to see total revenue by let's say region and product regions down inside product across the Top and revenue an before pivot tables. This would have been a 15 minute process with advanced filter unique transpose and other advanced filter and then a D. Some function and a data table too. It was hard, but now it's all down to 6 clicks.
First click is click on the insert tab second click here go to pivot table how point out if you actually for this pivot table if I wanted to recommend a pivot tables. I could get there to click shorter but since I know what I want to do with pivot table and it detects the edge of the data A1 to I-564 so because I have no blank rows and blank columns that figured it out and then it offers to go to a new worksheet and I'll have to tell you a lot of times. I'll create the pivot table right out here. I'll leave one blank column and put it in maybe K2 or something like that. But today will let it go to a new worksheet click OK. And.
There we are right this big intimidating blank report here.
The pivot tables going over here on the left hand side, but let's take a look over here on the right hand side for a minute.
The pivot table fields pain. It has a list of all the fields in the original data set a little check box next to each and down here at the bottom. This is how we build the report. We take fields from the Top and we drag it down here to the bottom four drop zones are going to use today columns, which stands for the things that go across the Top of your report and beware of that icon that icon is actually the wrong icon. That icon belongs down here next to Rosen this icon here belongs next to columns that got reversed somewhere after Excel 2003 and never been fixed. Since then I rose this dance were the field, you went down the left hand side of your report and again. That's the icon that should go there and then values is the things we want to add up right so I have to do is I check Mark region and it flies down here to the Rose field like that check mark revenue and for those of you who are counting. I'm 5 clicks in insert pivot table OK check Mark region check mark revenue.
And there is my report now I want to have product going across the Top so I'm not going to just check mark this because we're coming in a row field here? Instead, I'm going to take a product field and drag it and drop it. In the columns area like that now. Hey someone sent in a great question just before the web and R started and they said. Why won't pivot tables ever remember the number formatting now in my case, the underlying data wasn't formatted but even it had been it doesn't know the number for nothing because when we created the pivot table to actually take the data. Off of that sheet and loaded into memory so in memory.
There is no formatting so this.
I don't think we'll ever get fixed. We have to right click in number format and then choose either currency. I just I just want to get thousands separators in so I don't need currency thousand separator 0 decimal places in yeah, we're going to have to do that every single time we created pivot table for the rest of our lives. I don't think they'll ever add that functionality in Alright, so we print this out. Give it to her manager and a manager says has no no no that's not what I want to redo. It put products down the side and region across the Top now. If your manager like my manager doesn't understand how to do pivot tables. It's very simple. You just look at the manager sincerely and say, Alright, I'll be back in 15 minutes and then you head back to your desk and it's 3 really, really simple steps 1st. I'm going to take the product field here from the columns area and drag it and drop it in the Rose area like that.
That second step, I'm going to take the region field from the Rose area and drag it and Drop it in the columns area like that. And then this Thursday.
I'm going to do something else for 14 minutes to make it look like it's actually hard alright so there's our first pivot table. No advanced filters. Nodi some formulas know what, if analysis. Dave is able to all just a matter of dragging and dropping fields around the report. An amazing amazing right ago, her. I'm going to take the product field out here. I'll uncheck this and replace it with a customer field so you see we have customers down the left hand side. Uh a beautiful thing about pivot tables is when your manager comes to you and says Oh wait a second. This this can't be right? Where did that number come from right there's no way that this customer bought 219 thousand E region word. You get that with a pivot table that question. Where did you get that is super easy. Just go to that sell and double click right now, you'll notice down here in the lower left hand corner on sheet 4 when I double click on this ality and start a new sheet to the left of sheet for that makes up all of the E region not publishers and there is the 219.
1115 and you can do that all day. Where did this number come from double click here and you get a report. Now let's say there was actually a miscoding here. This shouldn't be W region. It should be E region while you want to back into your original data and change it here don't change it on the drill down report. That's not going to do any good, so it's a great way to identify problems. But you need to be aware that if you are going to make changes go back into the original data and Oh by the way. If something changes here. It's not going to automatically refresh into the pivot table right so right now. Let's take a look. We are at 6.7 million dollar 6.7 million dollars and I'll come here and I'll just go to this first cell and I'll add in a million dollars.
And come back to the pivot table seeing it still 6.7 million to capture that underlying change compared to the pivot table at analyze tab. We have 2 tabs active when I pivot table is there an right here. This refresh button will cause actually had 10,000,000 will cause that new data to be picked up right so let's go back and get this back to the original data and then refresh here. So just a couple of a couple of simple little things you have to be aware of that, if the underlying data changes are going to have to change the pivot table alright. So the next example. I wanted to go through here with traditional pivot tables is creating. A year over year report every year reporting in this particular data set. I have 2 years worth of data. I choose dates here down the left hand side. I look at that they automatically grouped the data for me and this is this is an option right so if we go to file options. This started happening in Excel 2016, here on the data tab of Excel options.
Uh you can disable automatic grouping of datetime columns in pivot tables. An I'll be honest with you while I appreciate this because it teaches people? How to group data. I don't particularly like the rules that they use. It has to be a full year to get the year in there like let's say that you are closed on New Years Day right and so you only build on January 2nd and your closed on New Year's Eve through December 30th well. Sometimes they won't group that up to year. So I tend to not allow them to do that. I'll turn that off and then we'll just get. Daily days like this and then I can choose I have so I prefer to choose how it's going to roll the data up alright so. Got a simple pivot table their dates down the left hand side in this particular data set is about 450 rose because we build on every weekday except for Holidays and so I want to roll. This data up to months and years. I go to group field right here on the pivot table. Analyzing you have to be on either the date heading or the First Date Field Choose Group Field, an check this out seconds minutes. Hours days months quarters or years is always cracks me up. I always worked for a manufacturer where we're billing daily and shipping daily and I can't imagine anyone who has data. Well, they're rolling it up to minutes or rolling it up to ours. But I suppose if you're a cell Phone company, you might be doing that, so I'm going to roll this up to months and years. Or maybe you choose quarters in years.
Whatever works for you in this particular case so I'm going to choose months in years and click OK and what's awesome. About this is they create a brand new field so the date field now becomes months. And there's a new virtual field over here on the pivot table field. List called years point. I really appreciate that made this be a separate field because what can I do? I can take that years field and pivoted? To go across like that right, so I have months down the left hand side and then years going across so for every item. Now I can see each month? What we did last year and what we did this year and at this point, I can now take the data and put sector there and see whether we're up or down each year or put customer in and see whether we're up or down so it's super Super Flexible. Let's go back to dates, though now. What I'd really like to see in column D?
Is I want to see a change percentage change or we up? Are we down right the grand total doesn't mean that much. I just need to know for each month or we are, we up where we down so right here. I'm going to right click, and remove granted when there's 2 different ways to do this and I'm going to do the way that I do, which is dangerous, and you'll see why it's dangerous. I'm going to come outside of the pivot table and build a calculation out here. I just regular formula. That's going to show the percentage change that C5 divided by B 5, - 1. Looks like we're up maybe 5% or so here. So equal sign C5 divided by B 5, - 1 and take a look at this formula. I use the mouse or the arrow keys to build that formula and instead of C5.
They put this stuff in getpivotdata? What is this started happening to us back in Excel 2217 years ago with no explanation right people were freaking out back then like?
What is this and the problem promise that calculation is right right? That's the right number but when I?
Double Click to copy this down it is January, all the way down to create the formula that you can't copy now. 99.9% of the time. If someone's talking about getpivotdata. There question is make it stop. I don't like this make it go away later. I'll show you how it will actually use that but there's two ways to make it go away. The 1st way just type your formula don't use the mouse don't use the arrow keys. C5 divided by B 5, - 1.
That creates the exact same answer see but when I copy it down. It works right. We're getting February March April May. Now, if you're in the camp, who hates getpivotdata and you just never wanted to happen to you again. Make sure to go back into the pivot table so we have the pivot table tools up here on the ribbon over here on the left hand side is an options button and I don't want you to click the options button instead. I want you to go into this little drop down here and uncheck generate getpivotdata you turn it off once and it'll be off wherever it will never happen to you again. So if you'd like to build your formulas outside the pivot table that is a great great way to go all right now.
There is another way to do this and I'm going to revenue field and drag it down here. A second time. So now we have some of revenue and some of Revenue 2 and I'll rearrange this.
Putting the values ahead of the years right so I just dragged that order. So now we have 2018 2019. For this summer revenue, too, though. I want to change how that calculation happens, so back here on the analyze tab. I go into the field settings and this is where we have the classic calculations some count average.
Wrong spot field settings.
Or some count average Max Min, but here I'm going to show values. As and say, I want to show it as a percentage change from percentage difference from and it's going to be the year field.
And then previous item right This is really, really deep how this is hidden here. I'll change this 2% change right. So we're going to do percentage difference from years field based on the previous item and what that's going to do is this 2019 column is going to show us whether we're up or down from the previous year. Now the one hassle here is the 2018 is blank because you see we have no 2017 data. But it's simple just to hide that column and now it looks like your PivotTable has.
2018, 2019, and then the percentage change from the previous year, cool cool way to solve solve that problem all right there, so that's my second tip your or your pivot tables.
The problem with getpivotdata.
Anna solution on percentage change from previous let me show you another another trick that I love to do here and this is one that I used to create all the time I'm group. These dates and take dates output customer customer down the left hand side. And I'm actually going to take the revenue field right now, we have some revenue by the way that word summer revenue.
You don't have to stick with that right. It sounds like like a little bit of math professor wrote that kind of thing you just come here and type anything like total revenue. You're fine.
You're allowed to change that heading and they have no problem with that, although the one thing you can't type. There is the one thing that sometimes you want to type which is just revenue if you try and type revenue. There they'll say, Oh no we already have a field called revenue can't use revenue again so call it sales.
Or even better call it revenue space from space. They have no problem with that all right now.
I'm going to take the revenue field down here and drag it in a second time and a third time.
L4 types in column D is still hidden on hide that alright so now I have 4 different versions of revenue. They all have the same numbers? Why would I do that because I could change the calculation in each fast way to get the field settings. It's the double click on this heading right here is rather than going back to pivot table analyze and then clicking field settings.
Just double click on some of revenue. Some count average Max Min. I have to tell you I never use. These the good ones are on the second tab and this one. I want to show as a percentage of the column total. So here are some revenue is going to save percentage of total. Click OK.
Hanson out for each item. We see what the percentage of the total is for the 3rd one. I'll come over here and I love this they added this in Excel 20. Two 2010 something called rank largest to smallest so our largest customer will be ranked number one if we Scroll down here worldwide importers number one customer at 12.96% and call that rank see if you forget to type the heading inside the inside the field settings. You can just type in new heading here and then for this one. Now it doesn't make as much sense in this case, but imagine if you had years.
Or months or quarters down the left hand side. A beautiful setting. It's called an accumulated total so it's a running total running total like that. So we're glad accumulated revenue.
And click OK alright antsy that will accumulate down to the 6.7 million dollars right so all kinds of different calculations.
We can do now. In my fake data set here. You might recognize this as the customers in Contoso. It's just 27 customers right so it all fits on one page. But in real life when I was really creating this report.
Well, we had.
At 800 customers a month right.
It was maybe 20 pages and you can't give that to your vice president sales his eyes were just going to glaze over right so we said alright what we want to do is we want to report. It just the Top 10 just enough for a half a page of data but I want to call attention here to 2. Two numbers 6.7 million dollars in our largest customers 12.96%.
Six point 7 million in 12.96%.
Those numbers are going to come into play here. When I use this next feature in Excel I go into the customer. Drop down here and go to value filters and say Top 10 alright. Although it's called Top 10 uh this is so flexible flexible could be Top or bottom.
It doesn't have to be 10 it could be 20 or 5 or 1212 it doesn't have to be any special number anything. You want and you can even say. Give me enough to get me 80% or give me enough records to get me to 5 million dollars or 3 million dollars, so ask the Top 5 items based on revenue click OK and there, they are our Top customers 12345 that's beautiful. But here's the problem remember worldwide importers was previously 12.96%, one ounce is there 24% in and the problem is there's 24% of this smaller number. This says grand total, but just the total of the things we can see alright. I really want that to say 6.7 million and I really want that to say 12.96% now back here on the design tab there's a solution. But not really so underneath subtotals, the 4th item says include filtered items in totals. This is grayed out.
Every day I come into work I start Excel first thing I do is I come here and check to see if this got turned back on I'm waiting for this to turn this back on this got grayed out. I remember the exact date was January 30th 2007.
Most people remember their kid was born. I remember the day that this feature that would have saved me so much trouble got great out in Excel. I don't know why I keep checking it's been 12 years and 9 months now at this point. It's probably never going to get turned back on right if that will get turned back on this granted would say 6.7 million dollars that memory back to the original amount and life would be great.
Bad luck, I wouldn't brought this up if I didn't have an awesome. Awesome solution to this and I was doing a live one of my live Excel seminars. I travel around the country. Doing these seminars. I was in Philadelphia, PA. Nice fellow there named Dan was in the seminar and I was complaining about this. He's like. Hey, bill, there is a solution to this and that's why I love doing the live seminars because every time I do a seminar someone in that room know some Excel trick.
I've never seen before right so I go there. I do seminars.
Everyone gets a book but I always leave the seminar with a couple of new tricks.
Every single time in this day that I met Dan Boy Dan had an awesome trip for this. He's like. Oh, you're using the wrong filter. You don't want to use the filters in the pivot table tools. You want to go back to the data tab and use this filter here. And you'll see that filters grayed out to all right.
But here's the thing. Here's the thing. There's several different ways that people use this filter. I'm just going to go back to the original data. If I had to filter. This data set. I would just choose one cell and click filter or choose one selling use the auto filter that I have up here in the quick access toolbar. Other people do control shift tell some people do control T. Some people select the whole data set. Dan had discovered a 6th way? What Dan does. He says look at all of the headings go to the very last heading cell go 1 cell to the right of that, so in this case, J1 and he didn't call this. But I started calling this. The Magic cell for some weird reason from the magic cell. I can click the filter icon and it filters.
The adjacent data C and it doesn't filter J1. It's a really weird trick. I don't know why anyone would ever want to do that. Unless you want to filter a pivot table seat because when you click outside of the pivot table out here into the nothingness. The filter icon is available to you. All right, So what we have to do is click in the Magic cell and they forgot to Gray out the filter so right now. I'm about to add filter drops down drop down to these 3 cells here, even though it's completely illegal against against all the rules and then when I use this filter dropdown.
I'm using the old auto filter go into number filters. Top 10 and I'm going to ask for to get the Top 5. I asked for the Top 6.
Why the Top 6 because that filter the grand total is the largest row right so that gives me the grand total and then the Top 5 customers back to 12.96% of 6.7 million dollars. It's a beautiful trick or I'm really happy that I met Dan from Philadelphia that day and he taught me about the Magic cell just great great great way to achieve something it generally would be impossible right now.
Hey look, we're about finishing up here on regular pivot tables.
We need to move on to the new pivot tables. But let me just show you 2 cool things here, we haven't talked about this.
Khan at all, I'll drag the sector field down there and this would allow you to create a report. Let's say of just manufacturing or something like that, so there's all the manufacturing customers an in Excel 2007. Eight give us the ability to select multiple items. I could choose manufacturing and services. All right, but I really don't like about that is this word multiple items like what multiple items.
You print this out, no one is going to know what this means and I kind of had a discussion with the guy in Excel team created this, I said, You know why don't you put both items there he's like well it's not going to fit like would fit if you used the shrink to fit right here under alignment. There's a setting got shrinked opinions like well, yeah, but that's if you choose 2? What if you choose 10. I said well then you won't be able to read it. It will be 1 point font, but like right now, it's not useful. Any of the time all right now, I'll take I'll clear this filter or go back to all the beautiful thing in Excel 2010. Uh this was great and actually made me stop using this filter up here in Excel 2010.
They gave us an awesome thing called a slicer so with slicers I could choose sector and product like that, and put it right in the center of the screen. You can always rearrange the product. I'm going to make it be really small by making me 3 columns and I'll be able to shrink that down and then sector let's see sector. Oh let's go with two columns there.
What are all right so everything fits on one screen on a touch screen but this would just be amazing right so if I would choose manufacturing you see the customers in manufacturing choose publishing. Those are the customers in publishing if I need to choose 2 while it's going to look right manufacturing and services so now when you print this. The slicer prints and you'll be able to see let's select in the slicer. So, just a great great improvement. There, the slicers.
I love those especially if you have a touch screen. You can build. A whole bunch of pivot tables that all rely on those slicers like that alright one last trick here before we leave great trip for my friend Sylvia in Santa Monica CA showed up in my seminar one time and said Oh yeah, this report is fine. But what I need you to do is I need you to make a report for every single sector. So yeah, let me write a macro for that. She's like no no macros. Yeah, I don't know what? Are you talking about? She says I knew it, she had a copy of my book pivot table data crunching the 1st edition all way back from 2000 four 2005. And she's like I read your whole book and you don't talk about this feature. Alright so I learn something new every single seminar. This obviously is going to be it. She says, Alright go to the options.
Dropdown not the options button itself and go to show report filter pages. And then say show all pages off sector. That's the only thing up in our filter area and when I click OK.
Look at that they just inserted 7 new sheets in the workbook so they're on the first years financial and then healthcare and manufacturing and publishing on this case. It just inserted 7 sectors. That's nothing really amazing. But imagine if it was 10 customers or 50 customers or 27 product lines being able to replicate a pivot table like that using show report filter pages. One an awesome awesome.
Awesome trick there from Sylvia in Santa Monica.
Oh, by the way here. Let me just let me show you one more cool thing and then we'll move on to the new paper tables. I'm going to sector and drag sector to be the 1st row area. So we have sector in column A an customer in column B and I'm bragging that my pivot table looks so awesome. Your PivotTable most likely starting Excel 2007 had this compact form shown compact form, which is great. Maybe if you're doing a presentation. But if you're actually going to use this data contact form is not the best thing. And the whole world and I will brag that I'm the I'm the person who wants the Excel team ready for this came out. I said, Hey people aren't some people. Some people who are going to reuse. This data don't like this view and we really need to give them away. You exulting really ought to give them away to have a check box.
That's a all future pivot tables start in tabular form and it took years. It was about 6 years me asking and asking and asking and asking every year at the MVP summit. Shouldn't be that hard and finally it's funny, they wouldn't necessarily listen to me, but they sent me out here to this website, calledexcel.uservoice.com, and they said right up your idea and see if anyone else votes for it.
So sure enough down here allow people to set her choose defaults for pivot tables 560 votes, but I didn't take that many both somewhere around 2:00 or 300 they called and said Alright. Yeah, we're going to do it, but we're not going to do it. The way you want to do it.
We think it should be broader than that, so if you're in Office 365 or Excel 2019.
Go to file options look for this new category called data this button right here. Edit default layout. So I'm able to say all future pivot table start in tabular form always check the box for repeat all item labels on I love how much better. They made it then. When I ask for pivot table options or empty.
Cells are empty cells right here. I just uncheck that so that way. I get zeros instead of empty cells. If you Love Classic Pivot table layout background.
Display you can make all future pivot table started classic pivot table layout change your favorite settings here click OK and then when you create a pivot table. No more compact form if you would rather have it in tabular form like that right so just I love how the Excel team is just does a great job of listening to people on user voice and making those changes when there's a large cry for him back. If you have anything that's driving you crazy in Excel go out to excel.uservoice.com right, it up. It helps to be a little bit, funny or sarcastic so that other people will see your idea and laugh maybe and vote for that idea. All right, we're going to talk about uh this box tiny little box in Excel 2013 that debuted in the insert pivot table dialog box. It's a sentence that says add this data to the data model right not a lot of explanation down there.
But what it does no huge red neon arrow, saying check. This it will add superpowers to your pivot table, but that's what it does super powers to your PivotTable alright. So we're going to talk about a few different examples here, matching up data from.
From different sheets how to do account distinct in a pivot table how to really do a true Top 10 report without using that hack from Dan Philadelphia and creating new formulas calculations that are impossible and regular pivot tables like medians or text. One set of slicers for 2 different datasets and even a pivot table on a map alright. So lots to demo here.
Let's switch back to Excel and will go to a file that eliminate V. Look up with the data model now. I love you. Look up.
And even better than that, the new X. Look up that's coming out soon. But there's a lot of people that can't do if you look up so here I have 2 sheets worth of data the green data on left is on sheet. One this blue data really belongs on sheet. Two I just put it here. So we can see it side by side. The green data.
Hundreds of rows of Invoice Data with a customer field and then over here, a little. Look up table for customer and sector and my manager wants me to merge these 2 he wants to see total revenue by sector total revenue by sector well if you look up.
Think about if you really had 200,000 rows of data and you had to put 3 or 4 columns. In doing V. Look up is going to be so slow So what we do is we take both of these datasets format as table so control T or home format as table now when you do that on table design. They call this one table. One and this one Table 2, but you can rename that write my data. My name is called data and sectors. I'm not sure that any better than table one or Table 2, but at least it's descriptive starting in Excel 2016. On the data tab we have this new icon here called relationships, it back in Excel 2013. You didn't have a relationships icon. But you could create the relationship after the fact definitely doing it ahead of time. I'll create a new relationship here and we just tell it. We have a table called data, it has a field called customer. The related table is sectors, and it has a field called customer and click OK right now. We've told Excel how those 2 tables are are should be joined. It's like doing a join an access or SQL Server as faster than a V. Look up Alright. So now I've created those two data sets and will come here insert.
Pivot table and choose that box.
Add this data to the data model right down there that that box.
You might not even notice that there it's I would say this sentence is such an undersell right. It's such a boring sentence for all the things that unlocks I choose OK.
Ah Alright In one little subtle difference here above the field names. I have the table name data and even more subtle at the Top is is not a tab called All and all will now give me all of the fields. Are all of the tables on all the sheets so I want to see sector and then revenue check that out look?
What just happened. It took the data from sheet one in the data from sheet to in memory and merge those and gave me my report now when I right click here in number format still have to come in and say currency was 0 decimal places right there's no good solution to that. Even with the pivot table defaults.
You can't say that we're always going to start in currency right so it's just one of those things. Beautiful joining 2 datasets and you can join multiple datasets right as long as it's a one to many relationship like a look any.
Look up table would be and I tell you what, if you go to ignite this year and he talked to any of the power by people or the power pivot. People data platform people they'll talk about merging this data, all day. But for me as an Excel Guy, who's just living in Excel with simple little datasets all the time I love the other.
Powerful things that I get from checking that box of add. This data to the data model. So I'm gonna come back here to just a real boring data set 563 Rose No. Look up tables at all nothing spectacular. Why would ever run. This through the data model well because it unlocks all kinds of features, so I'm going to insert a pivot table.
Add this data to the data model, not even going to make it into a table right. I don't have anything to join it. They're going to create fake tablename called range. That's fine. I have no problem with that alright I'm gonna build a report here with sector down the left hand side and then customer and I just want to do this to show you that we have 2 customers in finance 2 in healthcare and 3 and manufacturing 2, two and 3.
You can remember that right 2.
Two and 3 and my manager wants to know how many customers.
There are in each of those sectors so I take the customer field. I drag it over here to values like that, and it says it's giving me account of customer. But it's not it's telling me how many orders are where it wasn't 2. Two and 3.
It's 4496 and 44 completely the wrong answer now in a regular pivot table. I would double click here and I can change it from account to a sum to a Max 2:00 AM in because I checked that box for add. This data to the data model. There is at the bottom a secret choice. That's not in regular pivot tables called a distinct count choose OK and now 2, two and 3. It's actually the right number out there, so if you need to count.
The number of unique items are distinct items in each sector or in age anything changing to a distinct count and the only way to get to distinct out is to check the box or add that data to the data model alright will take customer out. And we'll go back to a uh an example here that I showed in the traditional pivot table. So I'm going to customer down the left hand side revenue twice feel that same example trying to get the Top 10 report and the second one. I'll do show values as and I'll say percentage of column total like this.
Alright so there's my report he's remember worldwide importers 12.96% of the total.
On 12.96% of the total Alright Customer Top 10 and before when I did this.
My totals changed, but now I do it. My totals stay the same 6.7 million dollars and at grand total has an asterisk next to him, saying that asterisk means that the items that the filtered out are still included in this total and the reason that that Astra showed up is because, on the design tab where this has been greyed out for 12 years.
It's not greyed out anymore right. So it's funny. If you would hover over this. You know when it was greyed out they should pop up a tooltip, saying Hey. Would you like to use this?
Just go back and re create the pivot table choosing the box that says add this data to the data model would be like what?
Why would that change, it, but it changes it right now. The reason that this automatically showed up without me, having to choose it is because in my pivot table defaults file options.
Data edit default layout right here. I said include filtered items and totals. I actually checked that box, hoping I could trick Excel into turning it on and regular pivot tables. But it only works when it is a data model pivot table right so that we took data from sheet. One sheet 2 and merged. It didn't count distinct created a Top 10 report without having to resort to the Magic cell now here.
Here's one more let's take that total field out.
And I'd like to replace it with an average well. That's super easy to do I just double click there and choose average alright, but then? Why manager says Alright now replace it with a median median.
There is no choice for meeting here if I come here and go to show values as or show summarize values by there is no medium in the list right can't do it shoot sorry can't do that. I just read some crazy formulas and there's no median if formula. I don't know sorry can't do that. But we can because the data model includes a whole new formula language or even create new calculations.
We have tables that are impossible in regular pivot tables so I want to create a medium.
If you have if you have the data tab and this manage data model well. That'll get you to it or if you don't and Excel 2016 inches right click. The table name and say add measure measure database term for a calculated field it's funny.
From one version of Excel they called it a calculated field in the database. People prevailed and said no. It's a measure make it go back so I hate that you know how the nomenclature there. When you see measured just then calculated Field Alright. So here's our new measure. I'll create a measure called median revenue.
And the form is going to be equal median.
Open paren and they give me a list of all my fields, I choose the. Revenue field like that.
A check this out when I'm creating a measure I get to specify up front that it should be currency with 0 decimal places and what the currency symbol is so that's a nice improvement choose OK and because I came in here by right clicking range. It doesn't automatically added to the pivot table. I just have to check it.
And we get median revenue, which is different of course than average. This is looking for that value right in the middle and that's just one calculation out of out of thousands of calculations that we can do in.
And data model pivot table.
Let's see. I'm going to switch over here and it show cool example. I was doing a seminar down in Melbourne, FL, Melbourne, FL done seminars in Melbourne, Australia. But this one was from Melbourne, FL and the person said look. I want to create a pivot table. But what I want to do is I want to show text.
Text in the pivot table and uh you know they said. We have an original not revised we want to show the difference. Difference between those and you would think that that's something you couldn't do all right. But if I create a pivot table insert pivot table. Add this data to the data model. And we'll put uh let's say sales Rep down the side and the original versus revised and what we're trying to do is trying to get a list of their choices. Both before and after right now. One thing I ran into here. These can get large, especially in the grand total so we definitely want to right click, and remove the grand total and then right click, and remove the grand total and then we'll add a measure in here.
Let's take a look at the other way to do this.
On the data tab click the manage data model.
Oh, that's not where I wanted to go sorry. I never actually do it this way. I want to come here to the power pivot tab and say measures new measure. And we're going to say uh all.
Codes will be our measure name and will do equal concatenate X connection. One is the values from the code field.
And we want.
Take one code.
And then comma space between each one that's our delimiter actually ordered by expression.
One and then.
I don't know it's a little right there. Yeah, it's linear comma space.
I have this one right check no errors in formula big question will be what happens when we get the result.
Alright so Andy originally chose guava and now chose date.
Barbarously chose guava and now chose melon. This is crazy. It's giving us text in calculated text in the values area of a pivot table. I want to take.
And put a new field in here like market right and So what market before Rep. That means here in the Atlanta total or the LAX total. It's actually concatenating the various values together and let's see if we go with region. 1st.
Take market out.
Right so.
Here in the E region. The answer should be guava fig fig date cherry, but they're smart enough to say, Oh, figs there twice.
We're only going to put it in once right so this is super super cool being able to create a measure that it actually calculates text and again be really careful here that you don't put the grand totals in this tiny data that wouldn't be a problem. But the bug is if the result of a formula is more than 32,767 characters, the pivot table just simply stops.
Reacting so like I showed this one of my seminars and then someone ran off and did it like Hey. This pivot table broke. We had to kind of do some archeology to figure out what the underlying reason was between it one last example here of modern pivot tables. Slicers are great if all the data is coming from the same source. But what if you have data coming from 2 different sources. I like here. I have last year's data and this year's data and they?
Share store name in common and I want the Slicers from these 2 pivot tables. One slicer to control both pivot tables, but you can't because it's coming from 2 different places. Now, maybe I can merge this data, using power query and that's a story for another day. But right now. I want to get these lasers to work So what I'm going to do is have 1/3 table 3rd table has each of the stores that are in either the first two tables.
Maybe some extra fields out here and on table design. You'll see that this is called last year.
This is called this year, Ty for this year. And then just something called stores and when we go into data and then manage.
Beautiful thing here called the diagram view and you'll see that this is the great table in the far right hand side. It sits in the middle and it talks to both the left table last year table and the right table. This year table. The whole trick here is that if I build a slicer with store name. It's going to come from this center table to table stores, not from the one on the right is one of the right can't see the one on the left not from the one on the left 'cause It can't see the one on the right.
The whole trick is to have the center table alright so we'll start here just from a completely brand new area.
Insert pivot table.
All right now is to use this workbooks data model. It's going to go right to that sell.
And 1st I'll build a last year report. Store name and sales.
And then over here will put a new pivot table so insert pivot table use. This workbooks data model and this time from this year, so store name and sales.
And now I want to have a slice through the controls. Both of those so from pivot table.
Airlines insert slicer and city only offer me the tables that are in play. But if I go to all I can find that table stores and say, I want to do it by store name and by Mall Developper.
Choose OK right because this data model has these sitting in the middle. If I ask for all of the four city stores.
Oh no it's not working uh uh OK alright. So here's what we do, we go to this slicer and go to the slicer tools an report connections an hook it up to all of the pivot tables that we have. Like that there we are so now we choose from the slicer and everything updates automatically so the promenade must be a new store. We have this year that was not there last year and of course. We have to do that, from the store name slicer as well, just choose all these I have seen great forum posts out there where people are doing this with VBA and people kissing other VBA doesn't work well using this extra table definitely definitely makes it work alright. Here we are so that's the modern pivot table one awesome feature that came along all the way back in Excel 2013 is called the 3D Map, 3D map and that is creating a pivot table, but it's a special kind of pivot table because it's a pivot table that we're going to put on a map and this is a modern pivot table as well. So here I have 2 sheets where the data. These are a bunch of houses 2400 houses in a neighborhood on sheet one and then 6000 rows of data for each of those houses. Every time it was ever sold going back to the beginning of time here and I want to create a pivot table from from these data sets and visualize that data on a map so we come here and choose insert 3D map.
And this always takes a little bit of time and you have to have an Internet connection for this to work because they're going to geocode that data all right, So what you're looking at here is all of the houses in 2 neighborhoods between Route 528 and Route 520. Here's our field list and the drop zones. The different colors. Here are different building developments.
So the purple is one builder.
The yellow is another builder.
This light blue and dark blue it's unfortunate there next to each other. And then the height of the house is the last sale price the height of the last sale. And you know, we have the XY location based on the street address so let's just go back here to the beginning of time.
For the standard which is 1961 is one this neighborhood started built this is actually my neighborhood here in Florida so they're going up. There is Cape Canaveral, where they launch rockets. Those are all the rocket launch sites in this area was built up in the 1960s, when the Apollo Engineers started to move into town and so I'm going to click the scrubber down here and let this day to animate overtime and because I know the data pretty well. I know where to look where these first houses are built, the first purple houses here and some of the green. Allotment by 1966 and just a few of the yellow houses and nothing up here yet, so I can watch this neighborhood get built overtime right and really understand which neighborhoods are the oldest which ones are the newest houses and we're actually using it. So we're trying to find a house to buy when we moved down here and it's funny spends all her time on Zillow, she loves loves finding houses. And she can recognize depending on the neighborhood who the builder was like from the front says I'll look you know it has. That window there.
That's the master bedroom. And this is the one where you have to knock down these walls bill.
And I'm like no, I'm not knocking down any walls because I'm really good at Excel I can do pivot tables with my eyes closed, but I'm horrible with a hammer so I became very fascinated with this neighborhood right up here. You can see on the satellite picture today. There's houses there. But as of 1985 that hadn't been built. These houses have to be 20 years, newer than those houses down there so I discovered. These would be the good houses to go for until watch these get built over the next couple of years and remember, the height of the column is the price of the house. Uh never mind will come back down here to these charming houses from the 60s and I'll hire someone to swing. The hammer right so This is crazy.
This is Excel 3D map taking Excel data 6000 rows of Excel data and plotting it with color and location and height of the columns. Just an amazing way to analyze data putting a pivot table. On a map and animating it overtime all right now, we have about 8 minutes left and on my traditional pivot table example.
I talked about 99.9% of the time. People hate getpivotdata.
They want to turn it off and so the big question is why isn't there is there a use for Getpivotdata Anna friend of mine and Rob collie who used to be on the Excel team. We were having lunch one time and just having a great time discussing all kinds of things about.
Excel and you know it was, it was beautiful and then Rob says he makes some statement about our internal customers will be really upset that getpivotdata doesn't work. Uh in this new this new world order and I will wait a second wait a second. I said Rob everything that you and I have said. Today I completely agree with except for your statement that anyone would be upset about Getpivotdata. So yeah, Hey bill, you're right outside of Microsoft.
Getpivotdata is not very popular. But I'm talking about the accountants inside of Microsoft. They love getpivotdata, I'm like what are you talking about? How can this be that someone loves getpivotdata alright so here I have a report here 150 stores in column A for every store.
I have a plan for January and then after that, I plan for February. All the way through December have plan for all 12 months and then we start to get actuals right so as we're going through the year. Like I now have axles up through April and my manager wants stores down the left hand side months going across the Top if I have the actual she wants to see it. So January February March. April actuals after that just the plan. Well guess what you can't create this insert pivot table OK stores down the left hand side months across the Top type.
And then sales.
Stores that left hand side once across the Top.
Right so there's my report there is the store name January.
Actual January plan. This January total. This is actual plus plan. You never want that that's not a thing right so we're going to get rid of that.
I'm going to field settings and say subtitles none. But my problem now is I can't get rid of the January plan without getting rid of all the plans so that means every month. I create this pivot table copy. It paste as values and start deleting columns. Hoppity accounts at Microsoft said no no. No, we're going to let this pivot table live there and no one will ever see it and what they did is they built what I call a shell report this beautiful looking report like that with blank rows and formatting. This doesn't look like a pivot table at all does it and what they do is they were going to fill this report.
From the pivot table right here.
This sell this cell is the store called Baybrook, January actual.
I'm going to click in equal sign here. And I haven't turned off getpivotdata, yet. I'm on allow myself to be get pivot dated I didn't know that was a verb. But I just made it into a verb.
Baybrook January actual. I click on that and sure enough, they getpivotdata me. Right? But here's the difference, I'm actually going to use it right so take a look at this formula.
I remember this day specifically specifically up to this point in my life, I was always deleting getpivotdata for the first time I had to go into Excel help and read and figure out what this does and see. Here's the problem. It says we're looking for a store and what store we looking for. We're looking for the one called Baybrook. That's as I copy. This gives me baybrook everywhere. But the accounts at Microsoft Smart Group of people right they said.
We're going to do is instead of putting baybrook in there. We're going to click on the cell that says, Baybrook. That way when we copy this down, it will say Highland Village and then Willowbrook and then The Woodlands Mall. I need a single dollar sign there before the D.
To parameterise the rest of this, I take January. I go click on January like this.
And I Press F4.
Twice to lock down the room and then type actual. Uh we will come here and click on actual and again press F4 two times to lock down the road like that.
Right now that form a copy it and paste special formulas someplace special eschews formulas because I have nice formatting set up here with currency on the 1st row, but not in the other rows and then same thing here. I want a special formulas awesome. Trickier not a pivot table trick. But just a great trick Excel F4F4 key does exactly what you just did I just did all ESF enter at 4 will do it again and then right here do it, one more time to use all these cells press F4 and bam.
It's beautiful now that process becomes we add more data to the bottom here we refresh. The pivot table. No one ever sees this pivot table except for us and then here in the report. We have to change. This word plan to actually have a little formula, there to do that, so I changed to 5:31 when I press enter you'll see. These numbers change from plan to actual like that beautiful. It's a report a nice report. That's actually using data to pull data from a pivot table now of course. If someone adds a new store in you're going to have to manually add that to the reports. You want to have a check cell down here. Make sure this grand total matches the other grand total, but cool cool way to go alright.
You breath there all right now, let's just do a quick recap of what we talked about so we started out with traditional pivot tables.
Do a year over year report, both with grouping dates and then getting the getpivotdata problem or a second solution with percentage change from previous Top 10. You can auto filter a pivot table from the Magic cell or? You can just hang on that's that's it. That was the solution. Back then because we couldn't turn on include subtitles and totals.
And then here using the filter field or the slicers slices are much better way to filter. But if you have to replicate a pivot table than the old filters will let you do that and then moving on to add this data to the data model, matching up data from 2 different datasets count distinct a true Top 10 report because we can show the totals.
The filtered items entails medians or text slicers for 2 different data sets have a table on a map and then finally just closing with an example of when you might actually use getpivotdata. There you are right so a lot of pivot table information for you. Check out my YouTube channel. On on YouTube. Mister Excel com. There is a whole pivot table playlist there. Just go to the most recent video. I have out there and type, a comment and I'll get an email there. If you have any questions. I'll be happy to help really again, thanks to the Excel team at Microsoft for giving us this great opportunity for the MVP is to share their knowledge be sure to check next month November. 2019 when my good friend Liam Bastic, who is hilarious by the way smartest guy. I know and also the funniest guy know can't wait to hear his web and our next month thanks so much for joining me, this is Bill Jelen for Mister Excel thanks.