Excel PivotTables Made Easy in 2026 (+ What's Changed!)

Pivot tables are the single most powerful tool in Excel for analyzing data, and they're probably the one skill most Excel users keep putting off learning. With a pivot table, you can take thousands of rows of raw data and turn it into a clear, meaningful summary in under a minute. Whether you've never built a pivot table before or you use them regularly, this video has something for you. I'm covering the essentials, the mistakes that silently break pivot tables, and a couple of things that have changed recently that'll make you faster. And if you want to follow along, grab the practice file from the link in the description or pin comment. All right, let's get into it. I'll use this advertising campaign performance data set representing six months of weekly data across six channels, email, Google search, LinkedIn, Meta, Tik Tok and YouTube and covering 24 campaigns across five markets. Now each row represents a single campaign entry and across the columns we have metrics for budget, spend, impressions, clicks, click-through rate, conversions, revenue, return on ad spend, and cost per acquisition. Data sets like this naturally lead to questions like which channel is giving us the best return on ad spend, which campaigns are driving the most conversions, "Where is budget being wasted? How did performance vary monthtomonth? You could answer these questions using sum ifs, average ifs, and countifs formulas, but it's even faster with a pivot table because you can summarize the data instantly. Before we build our first pivot table, we need to talk about data layout because this is the number one reason pivot tables fail. The good news is that our campaign data is already set up correctly. Notice that each column contains one type of information. We've got one column for channel, spend in another, conversions in another, and so on. Every row is a single record. There's no subtotals, no grand totals, no blank rows, breaking up the data, and this is called a tabular layout, and it's a non-negotiable for pivot tables. Before inserting a pivot table, I always recommend formatting your data in an Excel table. You can do this with the keyboard shortcut CtrlT. My data is already formatted in an Excel table. And we can see on the table design tab, it's called campaign data. And this means when you add new data to the very next row below the table, I'll just cr to copy this down. You can see the table automatically expands to include it, which means your pivot table will pick up those new rows when you refresh without you having to update any references manually. It's a small step that saves a lot of headaches later. I also recommend formatting your number columns how you want them to appear in your report. For example, my monetary columns are formatted with comma separator and two decimal places. In modern versions of Excel, this number formatting will flow through to the pivot table, saving you loads of time. Okay. To insert a pivot table, click anywhere inside your data and then on the insert tab, pivot table. Excel will automatically detect the table range. You can see it's picked up campaign data and by default it's going to place it on a new worksheet. I'll keep it that way and click okay. Excel has a blank pivot table on the left and on the right is the field list which is simply a list of your column headers. Channel, campaign name, market, spend, conversions, revenue, and so on. These are the building blocks you'll drag into the four areas at the bottom. I'm going to leftclick and drag the field list out closer to my pivot table so that we don't have to pan left and right. Now, rows is where you put the categories that you want to group things by, like channel and campaign name. Notice I just checked the boxes and Excel was clever enough to know that I probably wanted these on the rows. Columns works in the same way, but this time I'm going to leftclick and drag it into the column area. And now we have the markets going across the columns. Now, values is typically where your numbers go. So, spend, revenue, budget, that type of thing. Let's check the box for spend. Again, it recognizes this as a numeric field and it puts it straight in the values area. And notice my formatting has come across from my source data. And lastly, filters lets you narrow down the entire pivot table to a specific segment without changing the layout. Let's look at only active campaigns. And just like that, we've built a pivot table that summarizes our active marketing spend by channel, campaign, and market. No formulas, no manual grouping, just a clean summary in seconds. Now, let's say your boss changed their mind and they want to see which channels are consuming the most budget and generating the most return. No problem. Let's add revenue to the values area. And we'll drag out campaign. In seconds, we've created a new view of our data. Whereas, if you created this report with formulas, you'd know you'd not be leaving on time today. By default, Excel sums your values

which is exactly what we want here. But you're not locked into that. Right click on any value and then summarize values by. We can choose from sum, count, average, max, min, product. And there's even more options. For example, let's say I want to see the average, CPA by channel, and market. We'll add CPA to the values area. Let's take some of spend and revenue out and we'll rightclick summarize values by average. Now I can see which channels are converting the most costefficiently and which ones aren't. I'm looking at you LinkedIn United States. Let me drag the field list back over and we'll add campaign name back in so we can dig down a bit deeper at the cause of the blowout. We can see the cause is webinar promo. This is a useful insight that would have taken several formulas to produce manually. One more thing worth knowing before we move on. If you go to the design tab, you can change the pivot table layout. By default, it's showing it in a compact form. But if we change it to tabular, it gives you a cleaner, more spreadsheet like look that's easier to read when you have multiple row fields. It's a personal preference, but worth knowing the options there. Now that you know how to build a pivot table from scratch, let me show you two features that can speed up the process and is especially useful if you're working with an unfamiliar data set and not sure where to start. The first is recommended pivot tables. Again, on the insert tab, recommended pivot tables. Make this screen a bit bigger. Excel has analyzed my data and suggested a handful of pre-built options based on what it found. Selecting each one gives you a preview on the right. When you find one that you like, click okay. It inserts it on a new sheet in your file. Now, the suggestions may not be exactly what you want, but they're a great starting point if you're new to pivot tables or if you just want a quick overview of your data before deciding how to dig in. Think of it as Excel giving you a head start. The second option is co-pilot agent. If you have a co-pilot license, you'll see the co-pilot button on the home tab. Clicking on it opens a pane where you can describe what you want in plain English and it will tell you how to create the pivot table. I can ask Copilot to show me the total revenue and spend by channel in a pivot table. Click the arrow and away it goes. And you [snorts] can see it's inserted a new sheet with the pivot table that I asked for. It's given me a little header as well. Very nice. You can also ask more specific questions like create a pivot table that shows which campaign has the highest return on ad spend. Let's see how it goes with that. Copilot interprets the question, identifies the right fields, and builds a pivot table, sorted in descending order by average rorowass. Plus, it's included the total revenue and spend for context and highlighted the highest result. It's gone the extra mile. If you have Copilot, try asking it to compare average CPA across markets in a pivot table. Keep in mind that C-Pilot won't always get it perfect, and in my experience, it's currently very slow. So, understanding how pivot tables work, which you now do, means you'll know exactly how to tweak the results or maybe even build them faster yourself. That's why it's important to learn the manual method first. With pivot table skills like these, you're well on your way to building interactive Excel dashboards. Dashboards are simply a smart combination of pivot tables, slices, and charts. If you're ready to level up, my Excel dashboard course shows you exactly how to build them from start to finish. The links in the description and pin comment. This next feature doesn't get talked about enough. And every time I show it to someone for the first time, it gets the same reaction. I didn't know you could do that. It's called show details, and it's one of the most useful things a pivot table can do. Let's say you're looking at your pivot table and you notice something unexpected. Tik Tok is showing a much lower average rorowass than every other channel. Before you go back to your manager and report that, wouldn't you want to know exactly what's behind that number? We can continue to segment the data, adding market to the columns. And now we can see that United States for Tik Tok appears to be the main contributor. So you want to investigate that further. Just double click the value and Excel creates a new worksheet containing only the rows that make up that figure. Every campaign entry, every date, every market filtered down to just the data behind that one cell. Now looking at the rows column, we can see the UCG contest was a failure, returning less revenue than it cost. Drill down is also one of the best data validation tools you have. Before you share any pivot table report, get into the habit of double clicking a few values, especially the ones that look surprisingly high or low. It takes 10 seconds and it's caught more reporting errors than I can count. And for those of you who inherited a data set from someone else and aren't totally sure what's in it, drill down is a brilliant way to get familiar with your data quickly. Spot something interesting in the summary, double click, and see exactly what's driving it. Okay, let's

talk about two features that make your pivot tables much more powerful for exploring data. Grouping and slices. Let's drag date into the columns. You'll notice Excel has automatically grouped the dates into months. Now you can undo this with control zed. And now we have the individual dates. You can always reapply the grouping again. Just rightclick one of the date cells. Group. Here I want to group by days, months, and years because I'm expecting to expand this data set to multiple years. Click okay. Now it's still in the expanded view, but we can see the groupings for months and years. You can rightclick on these groups and expand and collapse and then collapse entire field. And now we have the data back at the month level of detail. I can also individually expand months and years, etc. Next, let's talk about slices. This is one of my favorite pivot table features because it makes filtering quick and it gives your reports a professional finish. A slicer is a visual filter. Instead of opening drop-own menus inside your pivot table, you can simply click a button and the pivot table updates instantly. To clear the slicer, you click the red X in the top left. Let me select both of these and I'll delete them. And then we'll insert them from scratch. With the pivot table selected, on the insert tab, we've got slicer. You can choose the fields you want a slicer for. Here, I want channel and market. Click okay. There's my two slices. I'm just going to grab them and move them up here. They're objects that float on top of the grid. So, you can place them anywhere. Let's give them some more columns. And we'll resize them to fit in the space at the top. Now when I click on Google search in the channel slicer, the entire pivot table updates to show only Google search data. Likewise, if I choose United States, now I'm filtered for Google search in the United States. So the filters are additive. And to select multiple items, hold down control and select additional buttons. This is so much faster and more intuitive than drop- down filters, especially if you're sharing the file with someone who isn't familiar with pivot tables. They don't need to know how filters work. I just need to click the button. And if you have multiple pivot tables on the same sheet, as I do here, you can connect one slicer to both of them. Right click on the slicer, report connections. Here's a list of all the pivot tables I have in this file. This is the sheet. So, I want the other pivot table on my slices sheet. Click okay. And now this slices connected to this pivot table and this one. Let's repeat for this slicer. Report connections. Scroll down to pivot table 7 and click okay. And now both pivot tables react to the slicer at the same time. That's the foundation of an interactive dashboard and we've built it in minutes. Next, let's look at how to update your pivot tables if your source data changes or you add to it. We can get updates by rightclicking on the pivot table and selecting refresh. If you have multiple pivot tables from the same source data, you only need to refresh one and they'll all be updated. Alternatively, if you have multiple pivot tables from different source data, you can go to the data tab of the ribbon and refresh all. If you're sharing the file with others, it's a good idea to set the pivot table to refresh when the file is opened. You do that by right-clicking and going into pivot table options. Then on the data tab, check the box refresh data when opening the file. Now, every time the workbook is opened, the pivot table will refresh automatically, ensuring your pivot table's up to date. Users with Microsoft 365 will soon have access to set the pivot table to auto refresh when the data changes, just like a formula does. This new button was available a few months ago, but it's been removed recently while Microsoft fixed some issues. Hopefully, it'll be back soon, so keep your eye out for it when you next update Excel. All right, let's look at the mistakes that make pivot tables feel difficult. In each case, the real issue is the structure of the source data. Here's what to watch out for. Multiple rows of column headers. Your pivot table needs a single header row. And empty columns risk excluding some data. Merge cells. Only one cell actually contains a value. In this case, it's cell F1. Cell G1 is empty. In later versions of Excel, this results in unnamed fields in your field list. In earlier versions of Excel, it will simply refuse to insert the pivot table. Now, probably the worst mistake is using data that's already pivoted. Here, I've got the same marketing spend data, but instead of the spend values all in one column, they're spread across separate columns for each month. And notice there's also a column for the grand total of the rows. And if we go to the bottom of the table, we have a grand total for the columns. But notice some of the columns have no data in them. Makes sense. It's a grand total, but those empty cells will cause you problems, and we'll see in a moment. You

can insert a pivot table using the data in this format. Let's give it a go. Insert pivot table. It's detected all my data. Looks good so far. We'll pop it on a new worksheet. Let's put market in the rows. And then what do we do about the values? Well, we've got separate columns for each value. And we can use these fields in a pivot table, but you have to select them individually, which makes it super inefficient. Plus, it means you can't use some of the other pivot table tools available. For example, notice there's no automatic grand total for the rows, which means we need to add the grand total field rather than letting the pivot table calculate it for me. Notice I also have a row containing blank for the market. This is that grand total row in my source data. It's showing blank because there's no value for market on that row. You can see there it's empty. But worse than that, this blank row are the grand total values being double counted once here as its own row in the data set and again automatically by the pivot table. Another problem is if I want to change the aggregation, say I want to see the average, I right click summarize values by average. It only changes one column at a time, making it incredibly laborious to maintain rather than just once like I did earlier. If you build pivot tables using data in this type of format, it can be very frustrating because you're able to build what looks like a working pivot table, but it results in lots of annoying issues that can leave those new to pivot tables completely baffled to the point they end up thinking pivot tables are complicated. If your data has any of these, we'll call them features, Power Query is the fastest way to fix them. So, check out this video next and I'll see you there.