In addition to the available choices, such as Sum, Min, Max, and Average, there is another set of pivot table options called the calculation options. These allow you to show a particular field as a percentage of the total, a percentage of the row, a percentage of the column, or as the percent difference from the previous or next item. All these settings are controlled through the The valid properties for ## Percentage of TotalTo get the percentage of the total, specify ' Set up a percentage of total With PT.PivotFields("Revenue") .Orientation = xlDataField .Caption = "PctOfTotal" .Function = xlSum .Position = 2 .NumberFormat = "#0.0%" .Calculation = xlPercentOfTotal End With ## Percentage Growth from Previous MonthWith ship months going down the columns, you might want to see the percentage of revenue growth from month to month. You can set this up with the ' Set up % change from prior month With PT.PivotFields("Revenue") .Orientation = xlDataField .Function = xlSum .Caption = "%Change" .Calculation = xlPercentDifferenceFrom .BaseField = "In Balance Date" .BaseItem = "(previous)" .Position = 3 .NumberFormat = "#0.0%" End With Note that with positional calculations, you cannot use the ## Percentage of a Specific ItemMany companies have a goal to have service revenue exceed a certain multiplier of copier sales. You can use the ' Show revenue as a percentage of hardware With PT.PivotFields("Revenue") .Orientation = xlDataField .Function = xlSum .Caption = "% of Copier" .Calculation = xlPercentDifferenceFrom .BaseField = "ProductLine" .BaseItem = "Copier Sale" .Position = 3 .NumberFormat = "#0.0%" End With ## Running TotalIt is not intuitive, but to set up a running total, you must define a ' Set up Running Total With PT.PivotFields("Revenue") .Orientation = xlDataField .Function = xlSum .Caption = "YTD Total" .Calculation = xlRunningTotal .Position = 4 .NumberFormat = "#,##0,K" .BaseField = "In Balance Date" End With Figure 12.21 shows the results of a pivot table with three custom calculation settings, as discussed earlier. ## Figure 12.21. This pivot table presents four views of Sum of Revenue. Column O is the normal calculation. Column P is % of Total. Column Q is % change from previous month. Column R is the running total.## CASE STUDY: Special Considerations for Excel 97Pivot tables and VBA took a radical turn in Excel 2000. In Excel 2000, Microsoft introduced the Officially, Microsoft quit supporting Excel 97 a few years ago. But, in practical terms, there are still many companies using Excel 97. If you need your code to work on a legacy platform, you should be aware of how pivot tables were created in Excel 97. In Excel 97, you would use the Sub PivotExcel97Compatible() ' Pivot Table Code for Excel 97 Users Dim WSD As Worksheet Dim PT As PivotTable Dim PRange As Range Dim FinalRow As Long Set WSD = Worksheets("PivotTable") ' Delete any prior pivot tables For Each PT In WSD.PivotTables PT.TableRange2.Clear Next PT ' Define input area FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row FinalCol = WSD.Cells(1, Application.Columns.Count). _ End(xlToLeft).Column Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol) ' Create pivot table using PivotTableWizard Set PT = WSD.PivotTableWizard(SourceType:=xlDatabase, _ SourceData:=PRange.Address, _ TableDestination:="R2C13", TableName:="PivotTable1") PT.ManualUpdate = True ' Set up the row fields PT.AddFields RowFields:="Region", ColumnFields:="Line of Business" ' Set up the data fields With PT.PivotFields("Revenue") .Orientation = xlDataField .Function = xlSum .Position = 1 .NumberFormat = "#,##0,K" .Name = "Total Revenue" End With PT.ManualUpdate = False PT.ManualUpdate = True End Sub |

Pivot Table Data Crunching for Microsoft Office Excel 2007

ISBN: 0789736012

EAN: 2147483647

EAN: 2147483647

Year: 2003

Pages: 140

Pages: 140

Authors: Bill Jelen, Michael Alexander

Similar book on Amazon

flylib.com © 2008-2017.

If you may any questions please contact us: flylib@qtcs.net

If you may any questions please contact us: flylib@qtcs.net