Learn Dashboards, Excel, Power BI, Power Query, Power Pivot
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 2019 onward and Microsoft 365 is the ability to set a PivotTable default layout, which can save a load of time. 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 …
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 …
Unfortunately there’s no built in way to calculate the median in a PivotTable. The approach is different depending on whether you’re using a regular PivotTable or a Power Pivot PivotTable. We’ll look at both options in this tutorial. The Data The data we’ll be using in this example is in an Excel Table called Table1: …
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 Enter …
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, …
