Find the Source Data for Your Pivot Table

After you create a pivot table, you might add new data, or change the existing data. When you refresh the pivot table, it might not show all the new records, or pick up the changes.

To find the problem, you can check the pivot table’s data source. It might not include all the rows, and you’ll have to adjust it.

Find the Source Data

Follow these steps, to find the source data for a pivot table:

Continue reading

Share and Enjoy

  • Facebook
  • Twitter
  • Google Plus
  • LinkedIn
Posted in Source Data | Leave a comment

Turn Repeating Item Labels On and Off

When you set up a pivot table, the outer field names each appear once, at the top of the group. In the screen shot below, The category names are in the left column, and the products for each category are listed below the headings.

itemlabelsrepeat01

Show Repeating Labels

In Excel 2010, and later versions, you can change a pivot field setting, to show the field names in every row, instead of just once. This is useful if the the lists are long, and you can’t see the headings as you scroll down. You can also do lookups from the pivot table, if the names are filled in.

Continue reading

Share and Enjoy

  • Facebook
  • Twitter
  • Google Plus
  • LinkedIn
Posted in pivot table formatting | Leave a comment

Sort Pivot Table Left to Right

When you create a pivot table, the items in each field are sorted alphabetically, in ascending order, like the products and cities shown below.

pivotsortleftright01

The only exception to this is for items that appear in a Custom List, such as month and weekday names, as you can see in the next pivot table. If you have the Custom Lists setting turned on, this sort order will take precedence over the alphabetical order.

pivotsortleftright02

Change the Sort Order

Instead of alphabetical order, you might need to show the results based on the values. In the products pivot table, you can quickly sort by the values in the Grand Total column or the Grand Total row.

  • Select one of the Grand Total cells (row or column)
  • On the Ribbon, click the Data tab
  • Click A-Z (Smallest to Largest) or Z-A (Largest to Smallest)

pivotsortleftright03

In the next screen shot, both the Grand Total column and the Grand Total row have been sorted Largest to Smallest.

pivotsortleftright04

Sort an Item Row

Although you can easily sort the Grand Total row in ascending or descending order, left to right, you can’t do the same thing in the row for one of the Products.

If you select a cell in the Oatmeal Raisin row, and sort Z-A, it sorts by the values in the Grand Total column, not the Oatmeal Raisin row.

It’s possible to sort the row’s values though – it just takes a few extra steps.

To sort the Oatmeal Raisin row in descending order:

  1. In the pivot table, right-click a value cell in the Oatmeal Raisin row.
  2. Click Sort, and then click More Sort Options
  3. In the Sort By Value dialog box, under Sort Options, select Largest to Smallest.
  4. Under Sort direction, select Left to Right.
  5. In the Summary section, you can see a description of the sort settings.
  6. Click OK to close the dialog box.

pivotsortleftright05

After you sort the Oatmeal Raisin row, left to right, the values in the Oatmeal Raisin row are sorted largest to smallest, from left to right.

The City column order has changed:

  • Seattle, which has the highest Oatmeal Raisin sales, is at the left.
  • Dallas, which has the highest Grand Total, is in second place.

pivotsortleftright06

Please visit my Contextures website for more information on pivot table sorting.

__________________

Share and Enjoy

  • Facebook
  • Twitter
  • Google Plus
  • LinkedIn
Posted in pivot table sort | 1 Comment

Apply Multiple Filters on a Pivot Field

Instead of looking at all the data in a pivot table, you can use filters to narrow your focus. The most noticeable ones are the Report Filters at the top of the pivot table. You can use those to select one item, or multiple items, to show in the results.

In the screen shot below, the Report Filter has been set to show only New York City. In the Row area, the Product and OrderMth fields have been added, and Total Price is in the Values area.

multiplefilterspivot02

Filter the Row Fields

You can use filters in the Row and Column fields too. There are 3 types of field filters:

  • Label
  • Value
  • Manual

We’ll see how they work individually, and then how they can be used together.

Apply a Label Filter

In this example, the pivot table has data from 2013-01 to 2013-12. To show only the last six months of the year, you can use a Label Filter on the Order month field.

  • Click the arrow in the OrderMth heading
  • Point to Label Filters
  • Click Greater Than
  • In the Label Filter window, type 2013-06 in the second box, and click OK

multiplefilterspivot03

Now, only the data from July to December is visible.

multiplefilterspivot04

Apply a Value Filter

Next, we’ll apply a value filter on the OrderMth field, to show the top 2 months for each product. To do this, we’ll apply a Top 10 filter on the field.

  • Click the arrow in the OrderMth heading
  • Point to Value Filters
  • Click Top 10
  • In the Top 10 Filter window, type 2 in the middle box, and click OK

multiplefilterspivot05

The pivot table now shows the 2 months with the highest sales, but the Label filter was removed. In the screen shot below, month 2013-05 is included in the Bran results. For Chocolate Chip, both months are in the first half of the year.

multiplefilterspivot06

So, when you add a different type of row filter, the first filter is removed.

Add a Manual Filter

Finally, we’ll try a Manual Filter. For this, you add or remove check marks in the list of pivot items for the field.

multiplefilterspivot07

Again, as soon as a new filter is applied, the old filter is removed. Now only the sales from the first 3 months are shown.

multiplefilterspivot08

Change the Pivot Table Filter Options

By default, a pivot table is set up to allow only one filter per field, as we saw in the examples above. However, if you want to use more than one filter per field, you can change one of the Pivot Table options.

  • Right-click any cell in the pivot table, and click PivotTable Options.
  • Click the Totals & Filters tab
  • Under Filters, add a check mark to ‘Allow multiple filters per field.’
  • Click OK

multiplefilterspivot09

Now you can apply both a Label filter and a Value filter to the OrderMth field, and both will be retained. In the screen shot below, both the Label filter (Greater Than 2013-06) and the Value filter (Top 2) have been applied, and both are in effect, as you can see in the popup message.

NOTE: You’re limited to one of each filter type per pivot field.

multiplefilterspivot10

Use PivotPower Premium Add-In

If you’ve bought a copy of my PivotPower Premium add-in, you can quickly turn the “Allow Multiple Filters” setting on and off with a command on the toolbar.

  • On the PivotPower tab, in the Filters group, click Filters
  • Click Allow Multiple Filters – On, or Allow Multiple Filters – Off

multiplefilterspivotpower

It’s also one of the Default Settings that you can store, so it will be automatically set when you use the Apply Defaults command.

multiplefilterspivotpower2

Watch the Pivot Table Filters Video Tutorial

To see the steps for using multiple filters on the same pivot field, please watch this short Excel video tutorial. It shows the steps in Excel 2010

____________

Share and Enjoy

  • Facebook
  • Twitter
  • Google Plus
  • LinkedIn
Posted in pivot table filters | 1 Comment

Pivot Table Shows Customers With No Purchases

A pivot table is a great way to see a summary of sales to your customers. You can see how much they bought, and how much they spent, on each product. In the pivot table below, each customer is listed, and below the customer name is a list of products and quantities.

This pivot table is in Tabular Report Layout, with Customer, Category and Product in the Row area, and Quantity in the Values area.

pivotcustomersales01

Find Missing Product Sales

If you carefully study the above pivot table, you can figure out which products each customer has NOT purchased.

  • The first customer, Bulk Bins Inc., only has 2 Bars listed – Bran and Carrot.
  • The next customer, Food Co, shows 3 Bars – Banana, Bran and Carrot.

So, you can see that Bulk Bins Inc. didn’t buy any Banana Bars, but this isn’t a very efficient way to analyze the data.

By making changes to the pivot table, you can easily spot the missing products for each customer.

Option 1: Change the Pivot Table Layout

One way to see which customers have not bought specific products is to change the pivot table layout. Instead of listing all the customers down the side of the table, put the Customer field into the Column area. This creates a heading for each customer, and you can quickly see any gaps in the data.

With this layout, you can see at a glance that Bulk Bins Inc. didn’t buy any Banana Bars. You don’t have to carefully read through a long list.

pivotcustomersales02

Option 2: Show All Items

Another way to see which customers have not bought specific products is to change a setting in the customer field, so product shows all customers. We saw this technique for showing missing items, a couple of months ago.

With this setting, you can use a vertical layout, similar to the first one, with Customer in the Row area. We’ll change the field order though, putting them in this order:

  1. Category
  2. Product
  3. Customer

To set the Customer field, follow these steps:

  • Right-click an item in the Customer field, and click Field Settings
  • On the Layout & Print tab, add a check mark in the ‘Show items with no data’ box.
  • Click OK

Now, for each product, you can see the full list of customers. If a customer has purchased the product, the quantity will show in the pivot table. Otherwise, the quantity cell will be empty.

In the screen shot below, it’s easy to see which 4 customers have not bought Banana Bars.

pivotcustomersales03

Option 3: Show Only the Customers With No Purchases

If you want to focus a sales campaign on only the customers who have not bought specific products, you can filter the Customer field.

First, set up the pivot table as described in Option 2. Then, to filter the table:

  • Click the drop down arrow in the Customer field heading
  • Point to Value Filters, and click Equals

pivotcustomersales04

In the Value Filter window, enter zero as the value, and click OK

pivotcustomersales05

The pivot table changes, to show only the customers who have not bought specific products. That gives you a short, targeted list of customers to work with.

pivotcustomersales06

Download the Sample File

To see the sample data used in this tutorial, and to experiment with the pivot tables, you can download the sample file from my Contextures website. On the Sample Files page, go to the Pivot Tables section. Look for PT0035 – Pivot Table Shows Customers With No Purchases.

The zipped file is in xlsx format, with no macros.

Video: Show Items With No Data

To see the steps for showing pivot table items with no data, please watch this short video. And please visit my Contextures website, for more information on pivot table field settings.

Or watch on YouTube: Show Missing Items in Excel Pivot Table

_____________________________

Share and Enjoy

  • Facebook
  • Twitter
  • Google Plus
  • LinkedIn
Posted in pivot table formatting | Leave a comment

Dynamic Date Range Filters in Pivot Table

In the source data for your pivot table, you can use the drop down filters in the heading cells, to show only the records for a specific date range. For example, in the table shown below, you could show just the orders that were shipped this week.

filterdatedynamic01

However, if you put a date field into a pivot table’s Report Filter area, those dynamic date ranges – Today, Next Month, etc. – aren’t available. You can only select specific dates by checking the boxes.

filterdatedynamic02

Move Date to Row Area

If you’d like to use dynamic date filters in a pivot table, move the date field to the last position in the Row Labels or Column Labels area, instead of the Report Filters area.

Then, click the drop down arrow, and click Date Filters, then the date range, such as This Week.

filterdatedynamic03

After applying the filter, the pivot table only shows the orders that have been shipped this week.

filterdatedynamic04

Hide the Date Details

If you don’t want to see a row for each shipping date, you can collapse the pivot table field.

  • Right-click on the ShipDate heading, and click Expand/Collapse
  • Click on “Collapse Entire Field”

filterdatedynamic05

After collapsing the date field, the Region and Product names show, but the dates are hidden. The ShipDate heading is still visible, and the filter icon shows that the date range filter is still applied.

filterdatedynamic06

To see the dates again,

  • Right-click on the ShipDate heading, and click Expand/Collapse
  • Click on “Expand Entire Field”

To see dates for a specific product

  • Click the + sign at the left of the product name.

Video: Dynamic Date Filters

To see the steps for applying a dynamic date filter, and hiding the dates, please watch this short video tutorial.

Or, watch on YouTube: Select Dynamic Date Range in Pivot Table Filter

Download the Sample File

To download the sample file for this tutorial, please visit my Contextures website: Pivot Table Date Filters

________________________

Share and Enjoy

  • Facebook
  • Twitter
  • Google Plus
  • LinkedIn
Posted in pivot table filters | Leave a comment

Show New Pivot Table Items in Alphabetical Order

When you add new items to a pivot table, they might not appear in alphabetical order. In the screen shot below, Binders is a new item, and it’s at the end of the list, instead of at the top.

newitemsort01

Another problem with new items is that sometimes, if the pivot field is filtered, those items don’t appear at all. In the next screen shot, Staplers was added to the source data, but it didn’t appear after the pivot table was refreshed.

There is a manual filter on the field, and new items aren’t appearing automatically.

newitemsort02

Solve the New Item Problems

Fortunately, both of these problems are easy to fix. Watch this short video to see how to sort the items alphabetically. Also see how to change a pivot field setting, so new items automatically appear, even if the field is filtered.

Or watch on YouTube: Show New Pivot Table Items in Alphabetical Order

More on Pivot Table Sorting

Visit this page for more information on pivot table sorting. For sorting instructions for Excel 2003, see this article: New Items at End of Pivot Table Drop Down Lists

___________________________________

Share and Enjoy

  • Facebook
  • Twitter
  • Google Plus
  • LinkedIn
Posted in pivot table sort | 1 Comment

Quickly Clear Filters in Excel

If you frequently use filters in your pivot tables, here is a quick way to clear them. Thanks to AlexJ, who shared this tip. You can find more of AlexJ’s Excel sample files on my Contextures site.

If you use the Ribbon commands to clear a pivot table’s filters, it takes several clicks. First, you go to the Analyze tab under PivotTable Tools, then click Actions, and then click Clear Filters.

clearfilterqat02

Add a Clear Button

To make this a one-click process, you can add a Clear button to the Quick Access Toolbar (QT). Then, select a cell in a pivot table, or an Excel table, click the button, and all the filters are cleared.

clearfilterqat01

Video: Quickly Clear Pivot Table Filters

Watch this short video to see the steps for setting up the button, and clearing the filters.

Or watch on YouTube: Quickly Clear Filters in Excel Tables and Pivot Tables

More on Pivot Filters

For more information on pivot table filters, please click here to visit my Contextures website.

______________________

Share and Enjoy

  • Facebook
  • Twitter
  • Google Plus
  • LinkedIn
Posted in pivot table filters | Leave a comment

Count Unique Items in Pivot Table With Excel 2013 PowerPivot

Last year, I described how to create a unique count in an Excel 2010 pivot table, by using the PowerPivot add-in. In that version, you can right-click on the table name in the field list, to add a new measure.

countdistinct00a

Missing PowerPivot Tab

I decided to make a video to show the steps, using Excel 2013, instead of Excel 2010. Things didn’t go too well, at first. For unknown reasons, the PowerPivot tab wasn’t showing up on the Excel 2013 Ribbon, even though the add-in was installed, and checked off as active.

A long and frustrating Google search didn’t turn up any solutions, at first. Many articles said the add-in had to be checked in the list of COM add-ins. That didn’t help, because it was checked.

Finally, I stumbled onto this Microsoft article that suggested a change to the Windows Registry. The instructions are in the last section – “Troubleshooting: Power Pivot ribbon disappears”.

As suggested, I deleted the PowerPivotExcelAddin item in the User Settings section in the registry, but didn’t see a PowerPivotExcelClientAddIn.NativeEntry.1 item in the Addins section, so I skipped that step. Fortunately, that worked, and the PowerPivot tab showed up again, in Excel 2013.

Connect to the Data

Once the PowerPivot tab was visible, I connected to the Excel file that had the sample data, without any problems. The next step was to build a pivot table in the PowerPivot window, and that went well too.

In the pivot table, I right-clicked on the table name, to add the new measure, but that command didn’t appear. Uh-oh!

powerpivotunique2010_14

Create a Unique Count in Excel 2013

Fortunately, I have a copy of Rob Collie’s book, DAX Formulas for PowerPivot. The book’s “Add a Measure” section showed a screen shot from Excel 2013, where the name has changed to Calculated Fields.

powerpivotunique2013_10

So, I used the New Calculated Field command on the Ribbon, and created a formula, using the DISTINCTCOUNT function.

powerpivotunique2013_11

The result shows the number of distinct stores, or unique stores, in each region and city.

powerpivotunique2013_15

Video: Show Distinct Count in Excel 2013 With PowerPivot

To see the steps for setting up the pivot table, and creating the formula, please watch this video.

Or watch on YouTube: Show Distinct Count in Excel Pivot Table with PowerPivot

________________________

daxformulas01

Share and Enjoy

  • Facebook
  • Twitter
  • Google Plus
  • LinkedIn
Posted in PowerPivot | 7 Comments

How to Show Missing Items in Pivot Table

When you create a pivot table from your source data, it only shows the items that have data. For example, if you put Customers and Products in the Row area, it only lists the items that each customer has bought.

pivotmissingitems02

Show Items With No Data

In the pivot table shown above, some items have bought several different products, and some customers only bought one type of product.

If you’d like to see each product listed for each customer, you can change a setting for the product field.

  1. Right-click an item in the pivot table field, and click Field Settings
  2. In the Field Settings window, click on the Layout & Print tab.
  3. Add a check mark in the ‘Show items with no data’ box.
  4. Click OK

After you’ve changed that setting, all the products are listed under each customer name, showing the number of units sold.

pivotmissingitems04

Show Zeros in Empty Cells

For the products that a customer hasn’t bought, the Units column shows a blank cell. If you’d like to see a zero there, you can change a pivot table setting.

  1. Right-click a pivot table cell, and click PivotTable Options
  2. On the Layout & Format tab, add a check mark to “For empty cells show:”
  3. In the text box for that setting, type a zero, then click OK

pivotmissingitems05

After you change the setting, the empty cells show a zero.

pivotmissingitems06

Show Missing Data

The “Show Items With No Data” setting can only show items that are included in the source data at least once. If you start selling a new product – Markers – but no customers have ordered it yet, it won’t appear in the pivot table.

If you’d like it to appear, you can add a fake record to the source data:

  1. In the source data, add a record with Markers as the product, and 0 as the quantity
  2. Refresh the pivot table, to update it with the new data
  3. Right-click a cell in the Product field, and click Field Settings.
  4. On the Layout & Print tab, add a check mark in the ‘Show items with no data’ box.
  5. Click OK

pivotmissingitems07

After you have added the fake record, refresh the pivot table, so the new data appears.

pivotmissingitems08

Video: Show Items With No Data

To see the steps for showing pivot table items with no data, please watch this short video. And please visit my Contextures website, for more information on pivot table field settings.

Or watch on YouTube: Show Missing Items in Excel Pivot Table

_____________________________

Share and Enjoy

  • Facebook
  • Twitter
  • Google Plus
  • LinkedIn
Posted in pivot fields | 1 Comment