If you using grouping, you might run into a pivot table subtotal problem in Excel 2016. There was a change in a recent update, so you might see this problem if you have an Office 365 subscription. I just learned about this issue, and will show you how to fix the problem if it affects your workbooks.
If you’re rearranging a complex pivot table, it can take a while to manually remove each field. To make the job easier, you can remove Excel pivot fields with macros. There’s a sample macro below that remove all the column fields, and you’ll find more examples, and a free workbook, on my website.
Setting up a pivot table is a bit like news reporting – you can give a quick summary of the Who, What, When, Where and How (Much) of your data. After you’ve been using pivot tables for a while, it’s easy to create a new report, and drop the fields into the right locations. But, if you’re just starting out, it’s not clear what to put where. I’ve put together a short guide on how to plan a pivot table.
Thanks to Anne Walsh for suggesting today’s topic! Anne has led Excel classes for many years, and she knows that people struggle to get their pivot tables set up correctly. Anne recently published Your Excel Survival Kit, which is jam-packed with useful Excel tips, from her extensive experience.
When you double-click on a pivot table value cell, Excel creates a new sheet, with a list of all the records that make up that total value. The double-click runs Excel’s Show Details command – it’s a helpful troubleshooting feature, but can add clutter to a workbook, because of all the sheets that it creates.
To help you keep things tidy, I created a sample file with macros that label the Show Details sheets when you create them. Then, when the workbook closes, another macro will check for those sheets, and asks if you’d like to delete them. I updated the workbook this week, so take a look, if it’s something you need.
Before you can build a flexible pivot table, you might need to rearrange the data. For example, if the data has a separate column for each month’s sales, that won’t work well in a pivot table. You need to “unpivot” your data first. Here’s what that means, and 2 quick ways to unpivot Excel data.
If you’re setting up a big pivot table, it’s easy to lose track of what you’ve added, and what filters have been applied. To help you stay organized, I’ve created a macro to list all pivot fields and pivot items in the selected pivot table’s row, column and filter areas. You can download the sample file, and test the macro in your own files.
You don’t have to stick with the default formats for your pivot tables. You can create a pivot table style with your own colours, and other formatting options that you like. Here’s how to get started, and a video with a simple formatting change that you can make.
Have you ever recorded a macro to remove pivot table calculated fields? Just turn on the recorder, right-click on the field and hide it, and turn off the recorder. Then, if you try to run that macro later, Kaboom! You get an error message, “Run-time error ‘1004’: Unable to set the Orientation property of the PivotField class”.
Good news – you can download my sample file that has a macro that actually removes those pesky calculated fields, without creating an error message. The video shows how it works.
If you’re analyzing sales data from year to year, you can make a line chart that shows each month’s sales. That lets you see if there were any months with big differences, and shows how sales went up and down over the year. Another option is to use a Running Total to compare years in Excel. It’s quick and easy to set up with a pivot table and pivot chart.
It’s pivot table time! First, we’ll take a look two common problems with time values in pivot tables. Then I’ll show you a couple of ways to save time when working with pivot tables.