Did you know you can display symbols in Excel Slicers? That’s right, Slicers aren’t just limited to listing boring old items from your PivotTables or Tables. In the animated image below I’ve got a Slicer that displays currency symbols. As you click on a currency symbol in the Slicer, the chart converts to the selected […]

New in Excel 2016 for Office 365* users is the ability to set a default layout for your PivotTables. Finally! If you’re like many PivotTable users the first thing you do after inserting a PivotTable is waste a minute or so fixing the layout so it’s just how you want.  Waste no more, because now […]

The Excel PivotTable Show Values As menu has a load of handy instant calculations you can use. For example you can choose to show the values as: Percentages of the row, column or grand totals The difference from or % difference from Running totals Ranking And index Kevin emailed me with a vehicle log book […]

In Excel 2007 Microsoft thought they’d get clever and impose nested row labels in our PivotTables: If you’re a PivotTable old-timer like me then you’ll most likely wish you could have the classic PivotTable default layout back: Turn On Classic PivotTable Default Layout The retro Excel 2003 looking PivotTable above is still available in Excel […]

If you put a field in the values area of a PivotTable and it defaults to COUNT as opposed to SUM, the reason is that the column has at least one cell containing text, or one cell that is empty. Yes, one pesky cell that doesn’t contain a number is enough for a PivotTable to […]

Note: This does not apply to Power Pivot PivotTables. When you create a PivotTable Excel takes a copy of the source data and stores it in the Pivot Cache. The Pivot Cache is held in Excel’s memory. You can’t see it but that’s the data the PivotTable references when you build your PivotTable, or change […]

A while back I wrote about how to create Excel PivotTable Calculated Items using the conventional approach of referencing the item name in the formula like this: But did you know you can also refer to items by their position in the PivotTable relative to the column containing your Calculated Item? Download the Workbook Download […]

I’ve always thought after inserting a PivotTable that Excel should ask “would you like a PivotChart with that?”. I think PivotCharts are Microsoft’s equivalent of McDonald’s famous upsell – “Would you like fries with that?” I’m not saying you should have Excel Pivot Charts with every PivotTable meal but they do go nicely together. However, […]

Inserting Excel PivotTable Calculated Items is a great way to analyse your data and automatically incorporate that analysis in your PivotTables. Another way to think of them is the ability to add a new item to your report based on a formula which uses other items in the column. You can then include this new […]