It’s easy to create a running total in a pivot table, and it’s usually used to show how quantities accumulate over time.
In this example, there are three Value fields in the pivot table, showing the number of units sold on each date.
In column B, the Sum of Units is shown, with no calculation. This is the number of units sold on each date listed.
In column C, the Sum of Units is shown, as a Running Total for Date. This is the total units sold, up to and including each date.
In column D, the Sum of Units is shown, as % Running Total for Date (new in Excel 2010). This is the total units sold, up to and including each date, divided by the grand total of units sold.
Running Total for Date
When you select either Running Total In or % Running Total In, you have to select a Base Field. The running totals will be accumulated at each change in that Base Field.
We want a running total down the list of dates, so Date is the Base field in this example.
By November 1st, a running total of 399 units have been sold, and the % Running Total is 18.8% of the 2121 overall total units sold.
End of Year Problems
The running total works for a continuous list of dates, but doesn’t flow down the whole list if you group the dates by years and months. In the pivot table shown below, the date has been grouped by Years and Months.
Now the running totals stop at December 2012, and start again in January 2013.
It can be a little difficult to understand the running totals in this layout, so be sure to label the pivot table headings, or add a title to explain it.
It’s easy to count things with a pivot table – just drop a field into the pivot table’s Values area, and change the summary function to count.
Sometimes you don’t even need to change the function to Count – it automatically uses the Count function if the field contains text cells or blank cells.
Watch for Blanks
If you’re using a pivot table to count items, be careful to use a field in which there is an entry in every row in the source data. Excel doesn’t count empty cells when summarizing in a pivot table, so you might not get the result that you expected.
For example, if the source data has the District name missing in some records, we can use a pivot table to count those records. Put the District field in the Row area, and another copy of the District field in the Values area.
The pivot table correctly counts the records where there is a District name, but can’t count the records where the District name field is blank.
Fix the Blanks Problem
To correctly count records where the District field is blank, use another field in the Values area. In this example, there is a date field in the source data, and it has an entry in every row.
When you add the Date field to the Values area, you can see that it counts correctly, and shows how many records have a blank District field.
Next, we can remove the District field from the Values area, and change the heading for the Count of Date field.
The pivot table shows the correct counts, and the headings are easy to understand.
Instead of showing a long list of items in a pivot table, you can group dates or numbers, and even text, to create a shorter list.
In this example, the units have been grouped by tens, so there are only 5 columns of summarized data, instead of 50.
The starting number for the grouping has been set at 1, and the upper limit has not been set. If higher number are added to the source data, those number would appear in new groupings.
Watch the Grouping Video
To see the steps for grouping numbers, you can watch this short video tutorial.
__
Download the Sample File
For more information on grouping data in a pivot table, and to download the sample file, please visit my Contextures website: Pivot Table Grouping
With a pivot table’s Report Filters, you can select one or more items, and see the summarized results for those items only. For example, you might want to see the sales in one region, or on a specific date.
You can also click on “(All)”, at the top of the items list, to see the overall results.
Block Selection of “(All)”
In some pivot tables, you might want to prevent people from selecting the "(All)" option in a Report Filter.
In this example, the worksheet shown below has GetPivotData formulas that refer to the Report Filter selection in cell B1. If (All) is selected in cell B1, the formulas show a message, instead of the dollar amounts.
Use Programming to Block “(All)”
Unfortunately, you can't remove the (All) option from the report filter's drop-down list, but you can use Excel VBA to block users from selecting it.
The code shown below will undo the report filter change, if (All) is selected or if someone checks multiple items in the filter. They’ll also see a message that explains what to do -- "Please select a single date."
This code is stored on the pivot table’s worksheet module, and can be activated by selecting from the filter drop down list.
If someone selects “(All)” from the OrderDate report filter's drop-down list, they’ll see the warning message.
Download the Sample File
To download the sample file, which contains the pivot table and VBA code, please visit my Contextures website: Pivot Table Report Filters VBA. The zipped file is in Excel 2007/2010 format, and you will have to enable macros when you open the file.
When you protect a worksheet in Excel, you’ll see a list of items that you can allow people to use, while the sheet is protected.
One of those options is ‘Use PivotTable reports’, and if you add a check mark to that option, things might not work exactly as you expected.
Limited Use of Pivot Tables
When the sheet is protected, even if you have allowed pivot table use, you can’t create a new pivot table. Another restriction is that you won't be able to refresh a pivot table on the worksheet. If you right-click in a pivot table cell, the Refresh command is disabled.
Refresh on Protected Sheet
If you need to refresh a pivot table on a protected sheet, you could
manually unprotect the worksheet
refresh the pivot table
protect the sheet again
Another option, if you would prefer to refresh the pivot table programmatically, is to create a macro that does these steps for you. The following macro will refresh the first pivot table on the active sheet, which has a password – “mypassword”
Sub UnprotectRefresh()
On Error Resume Next
With Activesheet
.Unprotect Password:="mypassword"
.PivotTables(1).RefreshTable
.Protect Password:="mypassword"
End With
End Sub
Connected Pivot Tables
If other pivot tables are connected to the same pivot cache are on protected sheets, you won’t be able to refresh a pivot table, even if it is on an unprotected sheet.
In this situation you will have to unprotect the sheet(s) where those other pivot tables are located, and then do the refresh.
You might have used one of the Custom Calculations in a pivot table, such as % of Column or Running Total. There’s another Custom Calculation – Index -- that isn’t used very often, but provides an interesting look at the pivot table values.
In the screen shot below you can see the original data in the pivot table, and the same date using the Index custom calculation. Even though Central Auto is the highest value in the table at the left, East Property has the highest Index value.
Using the Index custom calculation gives you a picture of each value's importance in its row and column context.
If all values in the pivot table were equal, each value would have an index of 1.
If an index is less than 1, it's of less importance in its row and column
If an index is greater than 1, it's of greater importance in its row and column.
The Index Formula
Even if two cells have the same value, they may have a different index. The Index formula is:
So, in this example, in the West region, the values for Auto and Property are almost equal, but the index for the Auto is 1.02 and Property is 0.98.
Because the grand total is higher for the Property column, the Grand Column Total in the Index formula is larger. The West Property amount is divided by this larger number, and its resulting index is smaller.
When you add a field to the Row Label or Column Label area of the pivot table, its heading cell shows a drop down arrow. When you click the arrow, you’ll see Sort and Filter options, for the selected field.
In the screen shot below, you can see the sort and filter options for the Product field, and the check boxes for manually filtering the list.
Hide the Arrows
If you want to prevent manual filtering, you can hide the drop down arrows, by using a bit of Excel VBA programming.
The following code will hide the drop down arrow for every field in the first pivot table on the active worksheet.
Sub DisableSelection()
Dim pt As PivotTable
Dim pf As PivotField
Set pt = ActiveSheet.PivotTables(1)
For Each pf In pt.PivotFields
pf.EnableItemSelection = False
Next
End Sub
Sorting and Filtering Still Available
After you hide the arrows, you will still be able to sort the pivot items, by using the commands on the Ribbon.
You’ll also be able to use the Label Filters and Value Filters, if you right-click on an item in the disabled field.
Video: Apply Multiple Filters to Pivot Field
To see the different types of filters – label, value and manual – please watch this short video tutorial.
If you’re creating workbooks with pivot table reports, you might have macros in those files, to make it easier to work with the pivot tables, or to navigate through the workbook sheets.
Instead of creating buttons on the worksheets, to run the macros, you can create a custom tab on the Excel Ribbon, with buttons to run the macros.
For example, this sample file lets users enter parts data in a UserForm, and summarizes the current inventory in a pivot table. The “Db Macros” tab appears when this workbook is active, and disappears when it’s not active.
Getting Started with Custom Ribbon Tabs
In the following video, you can see how to view the code for a custom Ribbon tab, and make a minor change. The written instructions are on my Contextures website, where you can also download the sample file: Excel Ribbon – Getting Started.
Create Your Own Custom Ribbon Tab
In the next video, you’ll see how to start from scratch – add a custom tab, create the code, and modify it. Then, make a slight change to the macros in Excel, and the Ribbon buttons will run your macros. The written instructions are on my Contextures website, where you can also download the sample file: Excel Ribbon – Add a Custom Tab.
When you create a pivot table, it is based on a pivot cache, with all of the records from the source data table. You can’t see the pivot cache, but with some programming, you can get information about the cache.
In the pivot table shown above, you can see the total quantities for all the records in the source data. To see how many records are in that source data, you can create a User Defined Function.
Function GetRecords(rngPT As Range) As Long
'pivot table tutorial by contextures.com
Dim pt As PivotTable
Set pt = rngPT.PivotTable
GetRecords = ActiveWorkbook _
.PivotCaches(pt.CacheIndex).RecordCount
End Function
This creates a function named GetRecords, and it requires a cell reference as its argument. If the referenced cell is in a pivot table, the function will show the record count for that pivot table’s pivot cache.
Note: If you save this file, make it a macro-enabled file type.
User a User Defined Function
Then, on a worksheet in that workbook, enter a formula that uses the function name, and refers to a cell in the pivot table. For example, enter the following formula in cell B1, referring to the pivot table in cell A3:
=GetRecords(A3)
The formula result is 825, which is the number of records in the source data. You can see the last record in the screen shot below. It’s in row 826, and if you subtract 1 for the heading row, that is record number 825.
With a GetPivotData formula, you can pull data from a pivot table. For example, how many file folders were sold:
The formula works well with text fields, but you might get errors if you create a formula using dates.
In the example shown below, the formula in cell E4 refers to the date "1/1/13", but the result is a #REF! error, even though that date is in the pivot table.
