By default, when you add Report Filters to a pivot table, they are shown in a single vertical list, above the body.

If you add several filters, that list can get rather long, and it pushes the data down the worksheet.

To save space at the top of the worksheet, you can change the Report Filter layout. You can either:

• Set a limit for the rows in the vertical list, and create more vertical lists, if necessary
• Switch to a horizontal layout, with the Report Filters across the row

Change the Layout

1. To change the Report Filter layout
2. Right-click a cell in the pivot table, and click Pivot Table Options
3. Click the Layout & Format tab

Set Limit for Rows in the Vertical List

1. The 'Display Fields in Report Filter Area' is set for 'Down, Then Over'
2. In the 'Report filter fields per column' box, select the number of filters to go in each column.
   NOTE: The default setting is zero, which means "No limit"
3. Click OK

In the screen shot below, the number of fields per column is set at 4. The 5th field – Product -- is at the top of the second set of filters.

Switch to Horizontal Layout

1. In the 'Display Fields in Report Filter Area' drop down list, click 'Over, Then Down'
2. In the 'Report filter fields per row' box, select the number of filters to go across each row.
   NOTE: If the number is set at zero, all the filters will be shown in one row.
3. Click OK

In the screen shot below, the number of fields per row is set at 3. The 4th field – Category – begins the second row of filters.

Watch the Video

To see the steps for changing the Report Filter layout, please watch this short video tutorial.

_______________

Share and Enjoy

Besides using the fields from a pivot table’s source data, you can create calculated fields. These fields are formulas that can refer to other fields in the pivot table, to perform calculations on the summarized amounts.

In this example, the RepBonus calculated field is added to the pivot table, to show the bonus amounts paid on product sales.

In the formula, the Total field is multiplied by 3%, to give the bonus on each product, for each sales rep. The 3% amount is typed into the formula, because a calculated field cannot refer to a worksheet cell, or to a named range or named formula.

Watch the Video

To see the steps for creating a simple calculated field, you can watch this short video tutorial. The written instructions are on my Contextures website.

_______________

Share and Enjoy

Sometimes you have to move a pivot table, after you’ve created it. That’s not too difficult if the pivot table is small – just select all the cells, cut them, and paste in a new location.

With a very large pivot table, it’s a bit harder – you have to scroll through the worksheet, and try to select all the cells, including any report filters.

Fortunately, there is a Ribbon command that makes the job much easier.

Use the Move PivotTable Command

To quickly move a pivot table, follow these steps:

1. Select any cell in the pivot table
2. On the Ribbon, under the Pivot Table Tools tab, click the Options tab
3. In the Actions group, click the Move PivotTable command
4. In the Move PivotTable dialog box, select New Worksheet, or select a location on an existing sheet.
5. Click OK, to move the pivot table.

Watch the Video

To see the steps for moving a pivot table, please watch this short video tutorial.

______________

Share and Enjoy

To pull data from a cell in a pivot table, you can use a normal cell reference, such as =B5, or you can use the GetPivotData function, which is specially designed to extract data from a pivot table.

The advantage of the GetPivotData function is that it uses criteria, so the correct data will be returned, even if the pivot table layout is changed.

Get the Subtotal Amount

Usually, the GetPivotData function works well, and returns the correct result. In the screen shot below, I typed an equal sign in cell B1, and then clicked on cell B8, where the Bars subtotal is located.

A GetPivotData formula was automatically created, and it returns the quantity of Bars sold.

=GETPIVOTDATA("Quantity",$A$3,"Category","Bars")

Create Custom Subtotals

One of the features of a pivot table is that you can create multiple subtotals for a field. For example, we could show both the Sum and an Average for the quantity of units sold.

However, when you use these Custom Subtotals, the GetPivotData formula might show an error.

Problems with Custom Subtotals

The sample pivot table now has custom subtotals – Sum and Average. Now, if I type an equal sign and click on either of the Bars subtotal cells, the result is a #REF! error. The GetPivotData formula looks different too, with square brackets in it.

=GETPIVOTDATA($A$3,"Category[Bars;Data,Sum]")

Fix the GetPivotData Formula

Fortunately, it’s easy to fix the #REF! error – you just remove the "Data," from the GetPivotData formula. In this example, the corrected formula is:

=GETPIVOTDATA($A$3,"Category[Bars;Sum]")

With that simple change to the formula, the correct result is returned.

Top or Bottom Subtotals

These list-style GetPivotData formulas are only created if there are Custom Subtotals, at the bottom of the pivot field group. For Automatic Subtotals, or Custom Subtotals at the top of the group, a normal GetPivotData formula is inserted.

Note: If there are multiple subtotals for a field, the subtotals cannot be shown at the top of the group, so they would automatically move to the bottom.

For more information on the GetPivotData formula, please click here to visit my Contextures website.

_______________

Share and Enjoy

Slicers were introduced in Excel 2010, and they make it easy to filter one or more pivot tables with a single click. In the screen shot below, “Bars” is selected in the product type Slicer. In the Product Slicer, the bars are shown at the top, and the other products are listed below the bars.

Update Old Excel Files

It’s easy to create pivot tables in Excel 2010, and connect them to Slicers, but if you want to use Excel 2003 files, you’ll have to update them first.

• Open the older files, and convert the files to the newer Excel formats -- xlsx, xlsb or xlsm.
• Then, close and reopen the file, and you should be able to connect any existing pivot table to a Slicer.

Problem Updating Old Pivot Tables

Sometime I find a pivot table that isn't recognized by the Slicers, even after converting to the new format.  To fix the problem, you can create a new pivot table, or try to repair the old one.

I’ve had some luck with saving the file back to the old format, then re-saving it in the new format. During the process, Excel makes minor repairs to the pivot table, and it is able to connect to a Slicer.

There are a few steps to the process, but it doesn’t take too long. Try it on a copy of your file, and you might be able to avoid starting from scratch.

See the Steps for Fixing Problem Pivot Tables

Watch this video to see how to update the files, connect to Slicers, and repair a problem pivot table so it works with the Slicers.

More Slicer Information

To see the written instructions, and more Slicer videos, please visit the Excel Pivot Table Slicers page on my Contextures website.

Excel Dashboard Course Recommendation

For a limited time, Mynda Treacy from My Online training Hub is opening her Excel Dashboard course, and if you sign up by 8 pm Pacific time on May 1st, you can get the course for 20% off. Use the Discount code: DASH

____________

Share and Enjoy

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.

However, if you change the pivot table layout, or add new data, the correct cells might not be formatted. In the screen shot below, a new month’s data was added, and those cells are not formatted.

Change the Formatting Range

To prevent this problem, you can adjust the formatting rules, so they refer to the pivot fields, instead of a specific range of cells.

1. Select a pivot table cell, and on the Ribbon's Home tab, click Conditional Formatting, then click Manage Rules
2. Select your pivot table rule, and click Edit Rule, to open the Edit Formatting Rule window.
3. In the Apply Rule To section, there are 3 options, and the Selected cells option is selected.
   • The Selected cells option works in many cases, but may not adjust correctly if the layout changes
   • All Cells Showing "Sum of Sales" Values might include too many cells, such as subtotals, and Grand Totals
   • All Cells Showing "Sum of Sales" Values for "Date" and"Territory," is the best option for this pivot table. It formats cells where the Date and Territory values appear, and excludes subtotals and Grand Totals
4. Click on the 3rd option -- All cells showing "Sum of Sales" values for "Date" and "Territory", then click OK

The conditional formatting automatically adjusts, to include the new month’s data.

Watch the Video

To see the steps for applying the conditional formatting and changing the data range setting, please watch this short video tutorial.

Download the Sample File

To download the sample file, and to see detailed instructions, please visit my Contextures website: Pivot Table Conditional Formatting.

_________________

Share and Enjoy

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.

_______________________

Share and Enjoy

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.

1. First, select any cell in the pivot table.
2. 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:

1. Select any cell in the pivot table
2. 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.

_________________

Share and Enjoy

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.

1. Click the File tab on the Ribbon, then click Options.
2. In the list of categories, click Advanced, and in the General section, click Edit Custom Lists.
3. In the Custom Lists dialog box, under Custom Lists, select NEW LIST
4. 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
5. Click the Add button to add your list to the Custom Lists area
6. 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:

1. 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:

1. Right-click on one of the region names in the pivot table.
2. Click Sort, and then click Sort A to Z

__________________

Share and Enjoy

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.

1. Right-click any cell in the pivot table, and click PivotTable Options
• 
2. In the PivotTable Options window, click the Format tab
3. In the Format section, remove the check mark from Autofit column widths on update
• 
4. 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.

PivotPower Premium Add-In

If you have a copy of my PivotPower Premium add-in, you can quickly change the Autofit setting by clicking the command on the PivotPower Ribbon tab.

It’s also one of the settings that you can store in your PivotPower Premium Defaults, and then apply all your defaults to any pivot table, with one click.

____________

Share and Enjoy