Back to Excel Newsletter Archives

OzGrid Excel and VBA Newsletter May 2007

Add to GoogleSearch Tips Excel Data Manipulation and Analysis

Excel Newsletter Index


What's New at Ozgrid

Excel Help Forum getsKeyword Auto-links: Specified keywords in the forum our now auto-linked to relevant page(s) and/or non-intrusive small pop-ups (only if Auto-link hovered over) text that helps you immediately. This, combined with our immediate Possible Answers link (seen after submitting a question) means you can get your answer before anyone even replies!

Forums are great, but there searching ability is always quite poor, hence the same questions being asked over. Our solution?Excel Help Forum searches are now using Google Custom Search.

See the forums mostRecent Posts. These are grouped by category and show Today's 10 most recent posts.

Ozgrid Forum members can now add their own personal stickies to the forums.....IF they want. If in a Thread that appeals to you in anyway, go to Thread Tools>Stick this Thread (only you will see it). The Thread will thenONLY be seen as a sticky when logged in under your user name. To un-stick open the Thread go to Thread Tools>Unstick this personal thread.

If you wish to receive emails when anyone replies to a Thread you can still use Thread Tools>Subscribe to this thread.

What's a "sticky"?

I'm glad you asked :) A sticky is a Thread in a forum that always stays at the top. Normally, as new Threads are added, older Threads get shunted down the list. It is normally only when another member replies (via Post Reply) that the Thread is moved back to the top. That rise back to the top can be short lived however on busy forum like ours.


PivotTables are used to display and extract a variety of information from a table of data that resides within either Microsoft Excel or another compatible database type. PivotTables are frequently used to extract statistical information from raw data. You can drag around the different fields within a PivotTable to view its data from different perspectives.

This month I thought we would look at a really nifty feature of Pivot Tables – the ability to insert a calculated field. For example, if you had a pivot table set up, with an amount displayed in the Data Items area, you may wish to display the GST (tax) component of the Amount Owing so you can see it at a glance. 

First you need to create the pivot table.  In our list, we have Names in column A and Amount Owing in Column B.  We created a pivot table and moved the Names field to the Row area and the Amount Owing to the Data area.

Download Pre Example

With our PivotTable created, it would be nice if we could see at a glance the GST component of each of the amounts owing.  We can do this by inserting what is known as a calculated field.  To do this easily, select PivotTable on the PivotTable toolbar (if this toolbar is not displayed, go to View>Toolbars and select PivotTable, or right click in the toolbar area of your screen and select PivotTable) then select Formulas>Calculated Field.  This will display the Insert Calculated Field dialog box.  The first thing we need to do is give our new field a Name.  We will call ours GST.  Now move to the Formula area and type in the following formula =’Amount Owing’/10, select Add, then Close.  This will insert a column to the right of the Sum of Amount Owing with the GST Amount displayed.  You can of course use any formula you like in a calculated field, however one important thing to remember about using formulas in calculated fields is that you cannot use Excel formulas that REQUIRE a range reference.

Download Pre Example

Pivot Table Products

O2OLAP for Excel

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only $145.00. $59.95 InstantBuy/Download, 30 Day Money Back Guarantee & Free Excel Help for LIFE!


Download Example

The code below uses anInputBox to collect the users criteria so they can quickly and easily hide PivotTable Field Items by a criteria they specify.

The raw data that the Pivot Table is based on is 3 columns consisting of the Fields;

  1. Department (Row Field)

  2. Employee (Row Field & Data Field)

  3. Age  (Row Field & Data Field. Also the items that are hidden by criteria)

The Code to Hide PivotTable Fields (Age) Items by Criteria

Sub HideByCriteria()'Declare variables'SEE:'SEE: pt As PivotTable, pi As PivotItemDim lAge As LongDim strCri As String, strCri1 As String, strCri2 As StringDim bHide As BooleanDim xlCalc As XlCalculation    Set pt = Sheet4.PivotTables("PivotTable1")    'SEE:           strCri = InputBox("Enter your criteria for hiding employees by age." _            & Chr(13) & "Valid Criteria Examples:" _            & Chr(13) & "'>20' for ages above 20." _            & Chr(13) & "'>=30  <40' for ages equal to or above 30 but below 40.", "HIDE AGE")            'SEE:    'They Cancelled.     If strCri = vbNullString Then Exit Sub         'Remove any *excess* spacing     strCri = Trim(strCri)          'Speed up code.     'SEE:     'SEE:         'Set PT to manual update.     pt.ManualUpdate = True    'SEE:        'Get users calculation mode, go to manual & stop screen updating     With Application            xlCalc = .Calculation            .Calculation = xlCalculationManual            '.ScreenUpdating = False     End With        'Error trap for non valid criteria    On Error GoTo NonValidCriteria:    'SEE:        'Find out if between or single criteria.    If InStr(1, strCri, " ") = 0 Then 'Single        For Each pi In pt.PivotFields("Age").PivotItems        'SEE:        'SEE:            lAge = pi            bHide = Evaluate(lAge & strCri)            pi.Visible = bHide        Next pi    Else 'Between            'Get 1st criteria             strCri1 = Mid(strCri, 1, InStr(1, strCri, " ") - 1)            'Get 2nd criteria             strCri2 = Mid(strCri, InStr(1, strCri, " ") + 1, 256)        For Each pi In pt.PivotFields("Age").PivotItems            lAge = pi            bHide = Evaluate(lAge & strCri1) And Evaluate(lAge & strCri2)            pi.Visible = bHide        Next pi    End If        pt.ManualUpdate = False    With Application            .Calculation = xlCalc            .ScreenUpdating = True     End With        Exit SubNonValidCriteria:MsgBox "Your criteria is not valid", vbCritical     pt.ManualUpdate = False     With Application            .Calculation = xlCalc            .ScreenUpdating = True     End WithEnd Sub

The Code to Show All PivotTable Fields (Age) Items

Sub ShowAll()Dim pt As PivotTable, pi As PivotItemDim xlCalc As XlCalculation    Set pt = Sheet4.PivotTables("PivotTable1")    pt.ManualUpdate = True        With Application            xlCalc = .Calculation            .Calculation = xlCalculationManual            .ScreenUpdating = False '    End With            On Error Resume Next        For Each pi In pt.PivotFields("Age").PivotItems                    pi.Visible = True        Next pi                On Error GoTo 0  pt.ManualUpdate = False    With Application            .Calculation = xlCalc            .ScreenUpdating = True    End WithEnd Sub

Download Example

Got any Excel Questions? Free Excel Help

Complete Excel Training Course Special!
We don't teach Excel from a manual, we teach Excel from experience!
Free Help Forum, not just Excel!

Instant Download and Money Back Guarantee on Most Software

Add to Google Search Tips

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft

FREE Excel Help