Applies to Excel 2013 onward for Power Pivot/Data Model or OLAP PivotTables. Excel PivotTable Quick Explore allows us to filter and drill down in a PivotTable: And Pivot Charts: Excel PivotTable Quick Explore requires the source data to come from the Data Model (aka Power Pivot), or an OLAP (Online Analytical Processing) cube. In other […]

Inserting PivotTables in Excel Online is now possible. It’s still in its infancy with many features you may be used to in the Desktop version of Excel not yet available, but it’s a start. Inserting Excel Online PivotTables As with any PivotTable, you need to begin with some Tabular Data. I recommend storing your data […]

Obviously, we don’t want our PivotTables littered with #DIV/0! and #NULL! errors if we’re presenting them in a report (like the one below), that would just create unnecessary questions and we’re busy enough. Thankfully Excel PivotTable error handling is easy to control via the PivotTable Options; right-click the PivotTable > PivotTable Options > On the […]

PivotTables can make quick work of summarising and analysing data and they have some handy built in percentage calculations available via the Show Values As menu. The Excel PivotTable Percentage Change calculation is achieved with the % Difference From option and is useful for quickly identifying if this month/quarter/year is better or worse than last […]

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 […]