Creating a basic pivot table

Using slicers to filter your report

With Excel 2010, Microsoft introduced a feature called slicers. Slicers enable you to filter your pivot table in much the same way as Filter fields filter a pivot table. The difference is that slicers offer a user-friendly interface that enables you to easily see the current filter state, even when multiple items are selected.

Creating a standard slicer

To understand the concept behind slicers, place your cursor anywhere inside your pivot table and then select the Insert tab on the ribbon. Click the Slicer icon (see Figure 2-24).

FIGURE 2.24 Inserting a slicer.

The Insert Slicers dialog, shown in Figure 2-25, opens. The idea is to select the dimensions you want to filter. In this example, the Region and Market slicers are selected.

FIGURE 2.25 Select the dimensions for which you want to create slicers.

After the slicers are created, you can simply click the filter values to filter your pivot table. As you can see in Figure 2-26, clicking Midwest in the Region slicer filters your pivot table, and also the Market slicer responds by highlighting the markets that belong to the Midwest region.

FIGURE 2.26 Select the dimensions you want to filter using slicers.

In Figure 2-27, the Multi-Select toggle was enabled and then Baltimore, California, Charlotte, and Chicago were selected. Note that Excel highlights the selected markets in the Market slicer and also highlights their associated regions in the Region slicer.

FIGURE 2.27 The fact that they enable you to see the current filter state gives slicers a unique advantage over the Filter field.

Another advantage you gain with slicers is that you can tie each slicer to more than one pivot table. In other words, any filter you apply to your slicer can be applied to multiple pivot tables.

To connect a slicer to more than one pivot table, simply right-click the slicer and select Report Connections. The Report Connections dialog, shown in Figure 2-28, opens. Select the checkbox next to any pivot table that you want to filter using the current slicer.

FIGURE 2.28 Choose the pivot tables you want to filter using this slicer.

At this point, any filter applied via the slicer is applied to all the connected pivot tables. Again, slicers have a unique advantage over Filter fields in that they can control the filter state of multiple pivot tables. Filter fields can control only the pivot table in which they live.

Note that the choices in Figure 2-28 are the pivot tables that rely on the same pivot table cache. If you want to use a slicer to control data coming from two different data sets, see “Tip 22 – Slicer to control data from two different data sets” in Chapter 14.

Creating a Timeline slicer

The Timeline slicer (introduced with Excel 2013) works in the same way as a standard slicer in that it lets you filter a pivot table using a visual selection mechanism instead of the old Filter fields. The difference is that the Timeline slicer is designed to work exclusively with date fields, and it provides an excellent visual method to filter and group the dates in a pivot table.

To create a Timeline slicer, place your cursor anywhere inside your pivot table, select the Insert tab on the ribbon, and then click the Timeline icon.

The Insert Timelines dialog opens, showing you all the available date fields in the chosen pivot table. Here, you select the date fields for which you want to create slicers.

After your Timeline slicer is created, you can filter the data in your pivot table by using this dynamic data-selection mechanism. As you can see in Figure 2-29, clicking the April slicer filters the data in the pivot table to show only April data.

FIGURE 2.29 Click a date selection to filter your pivot table.

Figure 2-30 demonstrates how you can expand the slicer range with the mouse to include a wider range of dates in your filtered numbers.

FIGURE 2.30 You can expand the range on the Timeline slicer to include more data in the filtered numbers.

Want to quickly filter your pivot table by quarters? Well, you can easily do it with a Timeline slicer. Click the time period dropdown and select Quarters. As you can see in Figure 2-31, you also can select Years, Months, or Days, if needed.

FIGURE 2.31 Quickly switch between filtering by years, quarters, months, and days.

CASE STUDY: ANALYZING ACTIVITY BY MARKET

Your organization has 18 markets that sell 7 types of products. You have been asked to build a report that breaks out each market and highlights the dollar sales for each product. You are starting with an intimidating transaction table that contains more than 91,000 rows of data. To start your report, do the following:

1. Place your cursor inside the data set, select the Insert tab, and click PivotTable.

2. When the Create PivotTable dialog appears, click the OK button. At this point, you should see an empty pivot table with the PivotTable Fields list.

3. Find the Market field in the PivotTable Fields list and select the checkbox next to it.

4. Find the Sale Amount field in the PivotTable Fields list and select the checkbox next to it.

5. To get the product breakouts, find the Product Category field in the PivotTable Fields list and drag it into the Columns area.

In five easy steps, you have calculated and designed a report that satisfies the requirements. After a little formatting, your pivot table report should look similar to the one shown in Figure 2-32.

FIGURE 2.32 This summary can be created in less than a minute.

Lest you lose sight of the analytical power you just harnessed, keep in mind that your data source has more than 91,000 rows and 14 columns, which is a hefty set of data by Excel standards. Despite the amount of data, you produced a relatively robust analysis in a matter of minutes.