In Excel, this is no built-in summary function that calculates a distinct count or unique count. In the pivot table shown below, we’d like to see the number of stores in Boston, where each product category was sold. Instead of a unique count, the pivot table is counting each record that has a store number. [...]

When you create a pivot table in Excel 2010 or Excel 2007, the row labels are in a compact layout – all the headings are listed in column A. You can change the Report Layout setting, to either Outline form or Tabular form, so each row field is in a separate column. Here’s the same [...]

Happy Spreadsheet Day! We celebrate on October 17th, because this is the date that VisiCalc was first released to customers, in 1979. This year’s theme is Spreadsheets for Students, and what better way to help students, than show them a great way to use pivot tables? Student Budget Workbook Most students have limited funds, so [...]

When you update a pivot table’s source data in Excel, the pivot table does not update automatically. You can set the pivot table to update when the Excel file opens, but that doesn’t help if you’re making changes after the file opens. If you can use macros in your Excel file, you can use an [...]

Last year, I posted Excel VBA code for removing a calculated field from a pivot table. The code works well if there is only one pivot table based on that pivot cache. However, a couple of comments mentioned that there were problems if multiple pivot tables shared the pivot cache. Here is a revised version [...]

Last week, you saw a technique for changing monthly data into a better layout, when creating an Excel pivot table. We used a multiple consolidation ranges pivot table to change this 13-column table into a 3-column data source. Today we’ll look at a different approach to using multi-column data in a pivot table. Bi-Weekly Data [...]

It’s Friday, and the weather has been unbearably hot all week. On top of that, your boss and/or Excel are making you crazy. If you’re on your last nerve, you can vent your rage in a comic strip, by using Rage Builder. I made this pivot table themed comic, and I’m sure you can do [...]

When you are setting up your source data for an Excel pivot table, there are a few data layout guidelines that will help you create the best possible pivot table. Sometimes you get data that isn’t well suited for creating a pivot table, like the example shown below. It has a column for each month, [...]

It’s easy to change the summary function for one data field in a pivot table. For example, if a field shows the Sum of Units, you can right-click on that field, and change it to Count of Units. However, if you have several data fields in a pivot table, it can take quite a bit [...]

Double-clicking on a number cell in a pivot table creates a new sheet in the workbook, showing the records included in that number. Sometimes the list doesn’t look the way you’d like it to, and the numbers aren’t formatted the way they are in the source data. For example, the source data might have sales [...]