≡ Menu

Amending Calculated Pivot Fields

Amending Calculated Pivot Fields Thumbnail

If you can create a user-defined calculated field for your pivot table you really need to know how to amend those calculated pivot fields as well: - Click on any pivot table field - Go to Analyse - Select Fields, Items & Sets - Click Calculated Field - Use the drop down to select your [...]

{ 0 comments }

Top 5 Excel Pivot Table Tips

Top 5 Excel Pivot Table Tips Thumbnail

Here are 5 very quick animated pivot table tips to increase your pivot table skills. Evey month I am sent a very large pivot table with approximately 1 gazillion value fields stretching across the landscape of my laptop screen. I am interested in only one of those values and it tends to be somewhere in [...]

{ 0 comments }

Macro to Repeat Item Labels in Excel Pivot and Flatten

Macro to Repeat Item Labels in Excel Pivot and Flatten Thumbnail

I often use pivot tables to summarise one data source so that I can use that pivot table output as the basis for further analysis. By working on summarised data I can reduce the overall file size by losing unnecessary detail and it is also a useful way to control the format of the data [...]

{ 0 comments }

Filter Pivot Table Based on Results of Another Pivot

Filter Pivot Table Based on Results of Another Pivot Thumbnail

This post demonstrates how to use VBA to alter the filters on a pivot table based on the values in another pivot table or list. I have two pivot tables that reveal detail about the costing of clinical services. Both pivot tables are based on different data sources but have a relationship that I want [...]

{ 3 comments }

The Best Way to Prepare Excel Pivot Table Source Data

The Best Way to Prepare Excel Pivot Table Source Data Thumbnail

This tutorial recommends some best practice for the preparation of source data that you intend to summarise by using Microsoft Excel Pivot Tables. There are some standard rules that should be adhered to, but as with most things in life it is best to follow the 5 P’s (Proper Preparation Prevents Poor Performance) and apply [...]

{ 0 comments }

How to Create a Pivot Table with Expanding Data Ranges

How to Create a Pivot Table with Expanding Data Ranges Thumbnail

This Excel tutorial shows you how to create pivot tables based on a dynamic named range that will expand as you add additional rows of data. This can be a huge time saver and helps to protect against inadvertent errors that result when pivot tables draw from only part of the data source. Typically, when [...]

{ 34 comments }

How to change multiple pivot table fields to Sum Function

How to change multiple pivot table fields to Sum Function Thumbnail

The Pivot table is an incredibly powerful tool for summarising data. Excel will either default to summing or counting the field data but you can choose from 11 different functions that include min, max and StdDev as well as the more common Sum, count and Average. If the data you are pivoting contains only numeric [...]

{ 13 comments }

Refresh all Pivot Tables

Refresh all Pivot Tables Thumbnail

I use pivot tables a lot. They drive multiple reports in my workbooks and it is not unusual for me to have 6 or so pivots in each spreadsheet. When the underlying data changes I spend quite a lot of time clicking through each pivot table to refresh it. I was happy to discover the [...]

{ 1 comment }