Learn Dashboards, Excel, Power BI, Power Query, Power Pivot
One of the downsides of PivotTables is they have a very distinctive look. Some might even say they’re ugly. In this tutorial I’m going to cover my PivotTable formatting tricks that will transform their look and feel. Table of Contents Custom Sort Order Column Labels Inserting Blank Rows Subtotal Position Custom Styles Conditional Formatting Expand/Collapse …
With Microsoft 365 we can now include images in Excel PivotTables, as well as built in data types like Stock and Geography, Power Query custom data types and Power BI Organisational Data Types. This opens up the ability to create dynamic and visually rich PivotTables like never before. When combined with some slick formatting we …
In this tutorial we’re going to look at the options we have available to auto refresh PivotTables in Excel without using VBA. The process differs depending on whether you use Power Query to get the data or not. I’ll also show you the VBA method required for regular PivotTables. Beware because not all PivotTables based …
Show Items with no Data in PivotTables is a handy setting that allows you to show items in the PivotTable even if there isn’t any data for them. For example, the PivotTable and chart below includes the East and West regions even though the Sum of Count is blank: Watch the Video Download Workbook Enter …
There’s no built-in way we can force Excel Slicers to single select, but we can use some crafty tricks to prompt users to only select one item, and even hide the data if they try to select multiple items! Watch the Video Download Workbook Enter your email address below to download the sample workbook. Get …
Creating an Excel PivotTable Profit and Loss statement is surprisingly easy. And because it’s a PivotTable you can team it with Slicers to make it interactive. While you’re at it you might as well add some conditional formatting to make reading, what is usually a drab report, quick and easy. Watch the Video Download Template …
The Excel PivotTable field list automatically appears in the task pane on the right-hand side of the Excel window when any cell or cells in the PivotTable are selected: Tip: If you can’t see the field list > right-click any cell in the PivotTable > Show Field List: Or select any cell in the PivotTable …
There are a couple of ways you can hide blanks in Excel PivotTables. To be clear, the ‘blanks’ I’m referring to are those shown below where the text, (blank), is inserted as a placeholder for empty cells in your source data: It’s important to point out that these (blank) placeholders only occur in row or …
Excel Slicers are great, but they’re a bit on the chunky side and that can be a pain when you’re building reports like Dashboards where space is limited. Unfortunately, the Excel Slicer Formatting available on the Slicer contextual tool tab is limited because you can’t adjust the font size (see below): I’m going to show …
With PivotTable Quick Explore we can filter and drill down in PivotTables and Pivot Charts without the field list exposed. This means our users can customise their reports without needing to know how to build a PivotTable: Quick Explore is also available in Pivot Charts: Note: Applies to Excel 2013 onward for Power Pivot/Data Model …
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 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 …
Updated 16 May 2023 Note: This does not apply to Power Pivot aka Data Model 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 …
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, …
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 …
There’s no built-in way to create Excel Slicers for fiscal years, however the solution is easily achieved by adding an extra column to your source data to classify each date into its relevant fiscal year. Download the Workbook Enter your email address below to download the sample workbook. Get Workbook By submitting your email address …
Excel Slicers for rolling periods, for example; the last 12 months, requires a column in your source data that looks up the date and compares it to today’s date to see if it falls into the current period. Download the Workbook Enter your email address below to download the sample workbook. Get Workbook By submitting …
In this tutorial we’re going to look at how we can create a single Excel Slicer for Year and Month, as opposed to the default of having the Year and Month in separate Slicers. Let’s rewind a tad and look at how we got here in the first place. Below is an extract of my …
After having thousands of people recently attend our free PivotTable webinar we found there were 3 questions that were asked again and again, which are: How do I format my data in an Excel Table so I can refresh my PivotTable and it automatically picks up any new data added? How do I change the …
Excel PivotTables are a treasure trove of features. One of my favourites is the ability to expand/collapse and drill down into the data. Let’s look at an example; the first column of the PivotTable below lists Categories, which group and summarise Products. We can click on the + symbols to the left of the Category …
Excel PivotTable Calculated Fields are easy to add but there are a couple of ‘gotchas’ you should be aware of. Calculated Field Definition Microsoft Definition: A Calculated Field is a user-defined field in a PivotTable that can perform calculations using the contents of other fields in the PivotTable. Calculated field formulas can refer to one …
In its simplest form the Excel GETPIVOTDATA function enables you to extract values from a PivotTable report, but if you’re like me when you first tried to figure out how to use GETPIVOTDATA, you were less than pleased with the results. Understandably so, because in its default form it’s quite inflexible. However, the benefit in …
