Back to Excel Newsletter Archives Back to Excel Newsletter Archives
EXCEL TIPS AND TRICKS | EXCEL VBA TIPS AND TRICKS
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.
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.
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!
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;
Department (Row Field)
Employee (Row Field & Data Field)
Age (Row Field & Data Field. Also the items that are hidden by criteria)
Sub HideByCriteria()'Declare variables'SEE: http://www.ozgrid.com/VBA/variables.htm'SEE: http://www.ozgrid.com/VBA/variable-scope-lifetime.htmDim 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: http://www.ozgrid.com/Excel/excel-pivot-tables.htm 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: http://www.ozgrid.com/VBA/inputbox.htm 'They Cancelled. If strCri = vbNullString Then Exit Sub 'Remove any *excess* spacing strCri = Trim(strCri) 'Speed up code. 'SEE: http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm 'SEE: http://www.ozgrid.com/VBA/calc-stop.htm 'Set PT to manual update. pt.ManualUpdate = True 'SEE: http://www.ozgrid.com/VBA/pivot-table-fields.htm '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: http://www.ozgrid.com/VBA/ExcelVBAErrors.htm 'Find out if between or single criteria. If InStr(1, strCri, " ") = 0 Then 'Single For Each pi In pt.PivotFields("Age").PivotItems 'SEE: http://www.ozgrid.com/VBA/loops.htm 'SEE: http://www.ozgrid.com/VBA/VBALoops.htm 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 SubSub 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 SubGot any Excel Questions? Free Excel Help
Complete Excel Training Course Special! 
Free Help Forum, not just Excel! Instant Download and Money Back Guarantee on Most Software
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
FREE Excel Help