PivotTable New Features (Spill Error & Auto Refresh) and PivotTable Basics Revealed. EMT 1914

Welcome to Excel Magic Trick 1914. And in this video, well, check out this. This is the new Excel logo. And guess what? The old Excel pivot table is better than ever. And we're going to see two amazing new features, plus check out some basics that make pivot tables awesome. Now, as many of you know, I race BMX. Here's a picture of me swooping on the inside. And this year, I've been tracking all the different tracks I've been to. Control down arrow. As of November 2nd, I've raced 84 times. And the goal is to go from this data set and get a unique list of tracks, then count, figure out the percent of count total, and then I want the total district points that I earned from each track, and then the percent of point total. All right, we're on the sheet 1914. Here's our data set. I'm going to click in a single cell. I'm keeping all the data in an Excel table. So, if we add data later, anything pointing to the Excel table will update. I go to insert, tables, pivot table, drop down from table range, or I use the keyboard alt NVT. That's the name of the table. I want it on the existing sheet. Let's put it in H3. Click okay. And bam, there's improvement number one. It looks like the spill error from dynamic spill array formulas, but that's coming from a pivot table. It's polite. It's saying, "Hey, I can't display the full report until you make sure that there's nothing in the way. " Now, I have this note here, which you should never have off to the side. It should be incorporated in a separate column called notes. But for the moment, I'm going to use my move cursor, click and drag. And just like that, no more spill error. Now, we drag track down to rows. Ever since the beginning of pivot tables, just that feature alone is amazing. Instantly, we get a sorted unique list. Now, I'm going to drag track, and notice that is a text field. And when you drag a text field down to values, bam, it defaults to count, not sum. Now, let's drag track a second time. And now, this is an amazing feature. Right click. Well, summarize values by, there's 11 aggregate functions. And we can see that we have count. But once you've determined whatever the aggregate is, you can add a calculation on top. Show values as, and we want percent of column total. And just like that, it took each one of these individual numbers and divided it by 82. And there are the percentages. Now, I don't like that label. Something like percent of count total. And then right click. Do not choose format cells if you want to add number formatting. That is for the cells. Right click. Notice we have format cells, but there's no number. But when you're in a pivot table, right click, there it is, number format. Now, this is a mistake all the way back to the beginning. This looks like the format cells dialog box, but if you look closely, there's only one tab. That title, format cells, should be add formatting to the field in the values area of the pivot table. I just want to show one decimal. Click okay. So, SeaTac definitely has the most, 22 races. That's my local track. 26. 8% of the time. Port Orchard, 15. Sumner, right down the street, raced 17 times. That's 20. 7% of the time. Now, let's drag district points, and notice that is a number field. I'm going to drag it down to values, and it defaults to sum. And that's exactly what we want. Anytime the field is a number, it defaults to sum. Right click, number formatting. I just want to add a comma, and no decimals. Now, let's drag district points down a second time. Right click, down to summarize values by. You always want to check if the aggregate calculation is correct. So, sum is correct. That's the aggregate. Now, I want percent of column total. One decimal. Click okay. Percent of total points. And now, we have count and a percent of total, sum total. And you know what? Most of the time when you're creating reports, you're counting, summing, or doing some sort of percentages. And that's what a pivot table makes so easy to do when you're creating reports. Now

I would like to sort by district points. So, I'm going to come over, drop down on the track field, more sort options. Descending, I want biggest at top, and I want sum of district points. Click okay. And there's our report. Now, at the SeaTac track, I raced 22 times. That was 26. 8% of my races. And I got a total of 2,343 points, which represented 27. 6% of my total district points from SeaTac. Sumner, River Valley, 20. 7% of the actual count of races, but only 15% of the points came from that track. Now, let's notice Richland. Two. I only raced there two times, but last weekend, I raced Saturday and Sunday at Richland. So, the second amazing feature in pivot tables is with the pivot table selected, go up to pivot table analyze. I've already selected auto refresh because I've changed my default, which is important. I'll show you how to universally change that in just a second. Control down arrow. Remember, the count was two. I'm going to incorporate these into the Excel table. Excel table expands. Control home to jump back to cell A1, and sure enough, automatically updating. There's four. If you want to change this amazing new setting universally, file, options, data. Make changes to default layout of pivot table, edit default layout. Always change it to tabular. That way, you get the field names at the top of the report. But now, we can go to pivot table options, data. And I already have this checked. So, all pivot tables will automatically refresh when source data changes. Click okay. Okay. Now, here's your bonus. We have the same data set. I'm going to create the same pivot table in H3. Click okay. I don't see anything anywhere. But guess what? Let's select the cell and look at the error message. Well, it tells me what's going on. Spill range isn't blank. And this is an awesome option. Select obstructing cells. And sure enough, just like all of us have done throughout the years with pivot tables and dynamic spill array formulas, F2, there's a rogue space. So, now when I backspace, enter, now I can create my pivot table. All right, spill error, auto refresh, pivot tables rule, and we'll see you next Excel Magic Trick.