Microsoft Excel: Pivot Tables — 3 Timesaving Productivity Tips

Pivot tables are one of the essential tools of Excel that make life easier when you spend time, like many of us do, analyzing information. However, as useful as they are, there are a few annoyances that we generally tolerate (or, honestly, don’t even realize there is an easier way…).

The following areas used to be “pain points” for me:

  1. Classic format rather than 2007/2010 default layout. Many times, it is preferable to have a side-by-side layout rather than indented underneath.
  2. Pivot table refresh does not occur when underlying data changes– when working on a pivot table, if the underlying data changes, the pivot table does not refresh automatically. This becomes tedious when you are working with a large analysis using multiple pivot tables.
  3. The source data changes and the pivot table data “source” needs to be manually updated to include the additional rows of data.

Each of these is addressed below:

Classic format rather than 2007/2010 default layout.

To switch layout, simply Right Click (when within the PT) and select “PivotTable Options”:

Finally, make sure to turn off “Subtotals” and the PT now shows the Classic layout format.

Pivot table refresh does not occur when underlying data changes

A small piece of VBA (Visual Basic for Applications) easily solves this problem. The code is as follows (and you can copy and paste right from here):

Private Sub Worksheet_Activate()
ActiveSheet.PivotTables(1).PivotCache.Refresh
End Sub

The “trick” is that this VBA should be placed into the “Sheet Code” for each sheet that contains a PT.

Here’s how:

Now, whenever you select that Sheet, the PT will automatically refresh.

The source data changes and the pivot table data “source” needs to be manually updated

The need to manually update a pivot table range each time the data changes can be eliminated with a dynamic named range (a range that automatically updates as data is added). This can be done two ways — in Excel 2007 or later by using the “Format as Table” capabilities or by creating a Dynamic Range Name using an Excel formula.

Create a Named Table — contextures.com has a nice overview of this on their website. A quick read of this will give you an understanding — it is very easy to do.

However, occasionally, it is preferred to not use a Table, in which case a Dynamic Named Range While certainly not as easy to create, it is straightforward once you understand the syntax and what the functions do. So here we go.

To create a dynamic named range (DNR), the OFFSET function is used.

The syntax of the OFFSET function is:

OFFSET(range,rows,columns,[height],[width])**

**NOTE — height and width are optional fields and are not used in creating a DNR.

So, to let’s create a Dynamic Named Range on a worksheet (named “Sheet1”) where the data starts in cell A1. The formula to create this range would be:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))

In simple terms, this says “start in cell A1”, “go down the rows in column A until there is no data”, then “go across the columns in row 1 until there is no data”

The arguments in the OFFSET function should be read as follows:

Reference cell: Sheet1!$A$1

Rows to offset: 0

Columns to offset: 0

Number of rows: COUNTA(Sheet1!$A$A)

Number of columns: COUNTA(Sheet1$1$1)

Use this approach a couple of times and you will not go back. Every pivot table I create uses a Named Table or a Dynamic Named Range.

Good luck!

About author:

Connect with Don!

LinkedInFlipboardTwitterSnapchat

Or, just Google me…I’m everywhere

Send Don an email

Course Categories

Quisque velit nisi, pretium ut lacinia in, elementum id enim. 

Connect with us