You can apply conditional formatting rules to a pivot table, just as you would in other cells on a worksheet. In the screen shot below, cells with amounts above average are filled with light green.
Author: Debra
Show Multiple Grand Totals in an Excel Pivot Table
In a pivot table, you can show multiple subtotals for a field, such as Sum, Count and Average. However, there isn’t a setting that lets you show multiple Grand Totals.
With a simple workaround, you can replace the Grand Total with a new field, and show two or more Grand Total calculations. In the screen shot below, the Sum, Average and Max are being calculated.
Add a New Field in the Source Data
This solution uses a new field in the pivot table’s source data, with a heading cell, and nothing else in the column.
That field is added to the pivot table, and subtotals are moved to the bottom of each group.
For written instructions, please visit my Contextures website: Pivot Table Grand Totals
Watch the Video
To see the steps for showing multiple grand totals, please watch this short video tutorial.
_______________________
Create Pivot Chart with Shortcut Keys
The quickest way to create a chart in Excel is by using a keyboard shortcut. With the following shortcuts, you can create a chart on a separate sheet, or place it on the same sheet as the data.
Create a Chart Sheet
These shortcuts work for worksheet data, or pivot table data. In this example we’ll create quick Pivot Charts from a pivot table.
- First, select any cell in the pivot table.
- On the keyboard, press the F11 key, to insert a pivot chart on a new chart sheet.
Create a Pivot Chart on the Data Sheet
To create an embedded pivot chart, on the same sheet as the pivot table, follow these steps:
- Select any cell in the pivot table
- On the keyboard, press the Alt key, then tap the F1 key.
Watch the Chart Shortcuts Video
To see the steps for creating a pivot chart with shortcuts, please watch this short video tutorial.
_________________
Create a Custom List for Pivot Table Sorting
Usually you sort a pivot table’s items alphabetically, or numerically. Here is our current pivot table, with the regions in alphabetical order.
Sometimes you might want another sort order though, such as listing cities or regions in geographical order. In this example, we’d like our company’s regions listed in this order in the pivot table reports – from East to West:
- East
- Central
- West
Creating a Custom List
To show the regions in geographical order, you can create a custom list of regions, in the order in which you want them sorted automatically.
- Click the File tab on the Ribbon, then click Options.
- In the list of categories, click Advanced, and in the General section, click Edit Custom Lists.
- In the Custom Lists dialog box, under Custom Lists, select NEW LIST
- Click in the List Entries section, and type the list of regions, in the order that you want them sorted, pressing the Enter key after each item, to separate the list items
- Click the Add button to add your list to the Custom Lists area
- Click OK twice, to close the dialog boxes.
Sorting with a Custom List
After creating a custom list, the custom sort order isn’t automatically applied to fields that are already in the pivot table layout. You’ll refresh the pivot table to apply the custom list sort order:
- Right-click any cell in the pivot table, and click Refresh.
The regions are then listed in the custom list order.
If the regions don’t automatically change to the custom list order, the field is probably set for Manual Sort. To change the field to Automatic Sort:
- Right-click on one of the region names in the pivot table.
- Click Sort, and then click Sort A to Z
__________________
Stop Pivot Table Column Widths From Changing
It’s a little thing, but it can be frustrating if your pivot table column widths keep changing. In the screen shot below, I’ve made column B narrow, so the pivot table takes up less room across the worksheet.
But, when I select a customer name, instead of showing all the customers, the column width adjusts to fit the selected name.
Now, column B is much wider, and it’s hard to read the numbers in that column, because they’re off to the right of the heading.
This automatic adjustment of the column width is useful sometimes, but for other pivot tables, where the column layout is fairly static, it’s not so helpful!
Change the Autofit Setting
If you have adjusted your pivot table column widths, and you want them to stay that way, you can change a setting in the pivot table options.
- Right-click any cell in the pivot table, and click PivotTable Options
- In the PivotTable Options window, click the Format tab
- In the Format section, remove the check mark from Autofit column widths on update
- Click OK, to close the PivotTable Options window.
With the Autofit setting turned off, the column widths will stay the where you set them.
If you want the Autofit feature back on at any point, go back to the PivotTable Options, and add a check mark for Autofit columns.
____________
Pivot Table Percent Running Total
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.
______________
Count Blank Entries in Pivot Table
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.
Video: Count Blank Entries in Pivot Table
This 3-minute video shows the steps for correctly counting blank items from the source data for a pivot table.
Get the sample Excel file, and written steps, on the Count Blank Cells in Pivot Table page on my Contextures site.
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.
_________________
Group Pivot Table Numbers by Tens
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
_________
Block Selection of All in Report Filter
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.
_________________
Refresh Pivot Table on Protected Sheet
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.
There is sample code to unprotect all the sheets, and do a refresh all, on my Contextures website: Refresh Pivot Table Connected to Protected Sheet
_______________________