You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Drill-down and Quick Explore

This part of the tutorial shows two ways in which you can analyse a
particular number further: by drilling-down to show the underlying numbers, or
by using Quick Explore to break it down by the values in a particular dimension.

If you're wondering why you haven't seen Quick Explore
before, it only shows up when you have a pivot table based on certain data
sources (of which a tabular model is one).

Double-clicking to drill down

The pivot table below shows that you've sold 364 amphibian products in retail
parks, but you want to know more:

We want to know the break-up of these 364 sales.

You can drill-down to show the items which constitute this figure by choosing
the following menu option (or you can just double-click on the cell):

You can right-click and choose this menu option, but it's much more fun just to double-click on the cell.

However, what you get isn't particularly helpful:

The
First 1000 rows title is misleading, as there are actually under 300 rows of data for this figure.

The best thing to do with the worksheet created is to delete it when you've
finished having a look at it:

This worksheet won't be dynamically updated when you refresh the underlying pivot table.

For normal pivot tables drill-down works well, but for pivot tables based on
tabular models the results aren't as easy to interpret.

Quick Explore

This feature is useful, if initially confusing. Suppose that you have the following pivot table:

The pivot table is already showing quantity broken down by centre type.

Suppose that the pivot table field list looks like this:

The other dimensions to this pivot table not currently displayed are
Region and Species,
so you can explore by these.

When you right-click on a value in the pivot table, you can choose to quick
explore it:

Here we're exploring the figure 4806.

You can now explore by any of the fields which are available in the pivot table
field list but not currently displayed as row or column headings in your pivot
table:

You can choose to explore in this case either by region or by species.

If you choose Species (as above), Excel will move the currently
displayed row labels (CentreType) to the filters section of the
pivot table, and show the species (as chosen) down the left side instead:

Since we clicked on a
Retail Park cell, Excel displays this as the chosen centre type in the filter section of the pivot table.

You could now repeat this trick to look at the sales of birds for retail parks
in more detail:

Right-click on the cell whose contents you want to break down.

Because you can't see the centre type on the left any more, this is offered up
as an option:

Despite this, however, we'll choose to break this figure down by region.

We now have two filter choices at the top of the pivot table - Retail
Park and Birds:

The figures shown sum to the total birds figure above of 1289.

Confused? I usually am. The idea's good, but the implementation
takes a bit of getting used to!

We're getting near the end of this (long) tutorial now, but there's one
important topic I haven't yet covered: slicers. Time to remedy that!