• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member
  • Login

Excel PivotTables

PivotTable Formatting

12 PivotTable Formatting Tips

July 6, 2023 by Mynda Treacy

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 …

Read more12 PivotTable Formatting Tips
Images in Excel Pivot Tables

Images in Excel PivotTables

June 27, 2023 by Mynda Treacy

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 …

Read moreImages in Excel PivotTables

Auto Refresh PivotTables

July 22, 2021 by Mynda Treacy

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 …

Read moreAuto Refresh PivotTables

Show Items with no Data in PivotTables

June 16, 2021 by Mynda Treacy

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 …

Read moreShow Items with no Data in PivotTables

Force Excel Slicers to Single Select

November 19, 2020 by Mynda Treacy

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 …

Read moreForce Excel Slicers to Single Select
excel pivottable p&L

Excel PivotTable Profit and Loss

July 30, 2020 by Mynda Treacy

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 …

Read moreExcel PivotTable Profit and Loss

Excel PivotTable Field List Tips

July 16, 2019 by Mynda Treacy

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 …

Read moreExcel PivotTable Field List Tips

Hide Blanks in Excel PivotTables

October 11, 2018 by Mynda Treacy

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 …

Read moreHide Blanks in Excel PivotTables
Excel Slicer Formatting

Excel Slicer Formatting

June 25, 2018 by Mynda Treacy

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 …

Read moreExcel Slicer Formatting
Excel PivotTable Quick Explore

Excel PivotTable Quick Explore

April 25, 2018 by Mynda Treacy

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 …

Read moreExcel PivotTable Quick Explore
excel online pivottables

Excel Online PivotTables

March 14, 2018 by Mynda Treacy

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 …

Read moreExcel Online PivotTables

Excel PivotTable Error Handling

November 6, 2017 by Mynda Treacy

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 …

Read moreExcel PivotTable Error Handling

Excel PivotTable Percentage Change

November 2, 2017 by Mynda Treacy

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 …

Read moreExcel PivotTable Percentage Change

Symbols in Excel Slicers

July 11, 2017 by Mynda Treacy

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 …

Read moreSymbols in Excel Slicers

Set Excel PivotTable Default Layout

June 13, 2017 by Mynda Treacy

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 …

Read moreSet Excel PivotTable Default Layout

Excel PivotTable Show Values As

September 7, 2016 by Mynda Treacy

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 …

Read moreExcel PivotTable Show Values As
PivotTable Classic Layout

Classic PivotTable Default Layout

April 11, 2016 by Mynda Treacy

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 …

Read moreClassic PivotTable Default Layout
Excel Tool Belt

Excel PivotTable Default to SUM instead of COUNT

April 11, 2016 by Mynda Treacy

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 …

Read moreExcel PivotTable Default to SUM instead of COUNT
Excel Pivot Cache

Excel Pivot Cache

November 17, 2015 by Mynda Treacy

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 …

Read moreExcel Pivot Cache
PivotTable Median

Calculating MEDIAN in PivotTables

October 27, 2015 by Mynda Treacy

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: …

Read moreCalculating MEDIAN in PivotTables
PivotTable Calculated Items by Position

Excel PivotTable Calculated Items by Position

July 28, 2015 by Mynda Treacy

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 …

Read moreExcel PivotTable Calculated Items by Position
pivot charts

Pivot Charts

May 26, 2015 by Mynda Treacy

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, …

Read morePivot Charts
Excel PivotTable Calculated Item

Excel PivotTable Calculated Items

May 12, 2015 by Mynda Treacy

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 …

Read moreExcel PivotTable Calculated Items
Excel Slicers for Fiscal Years

Excel Slicers for Fiscal Years

May 6, 2015 by Mynda Treacy

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 …

Read moreExcel Slicers for Fiscal Years
Excel Slicer rolling periods

Excel Slicers for Rolling Periods

May 6, 2015 by Mynda Treacy

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 …

Read moreExcel Slicers for Rolling Periods
Single Slicer for Year and Month

Create a Single Excel Slicer for Year and Month

April 28, 2015 by Mynda Treacy

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 …

Read moreCreate a Single Excel Slicer for Year and Month

Excel PivotTable FAQ’s

March 4, 2015 by Mynda Treacy

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 …

Read moreExcel PivotTable FAQ’s
Excel PivotTable Expand and Collapse options

Excel PivotTable Expand, Collapse and Show Details

February 25, 2015 by Mynda Treacy

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 …

Read moreExcel PivotTable Expand, Collapse and Show Details
Excel PivotTable Calculated Field

Excel PivotTable Calculated Fields

February 4, 2015 by Mynda Treacy

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 …

Read moreExcel PivotTable Calculated Fields
getpivotdata function

Excel GETPIVOTDATA Function

October 20, 2014 by Mynda Treacy

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 …

Read moreExcel GETPIVOTDATA Function
  • Go to page 1
  • Go to page 2
  • Next

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel Office Scripts
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook

Sign up to our newsletter and join over 400,000
others who learn Excel and Power BI with us.

 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate
  • Sponsor Our Newsletter

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.