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.
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.
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:
- Category
- Product
- 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.
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
In the Value Filter window, enter zero as the value, and click OK
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.
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
_____________________________