Excel Pivot Tables Reports - Excel pivot tables

How to create Pivot Tables Reports?

PivotTable is used for reporting purpose. After creating a pivot Table, search the data by arranging and rearranging the fields in its rows and columns, as it will be ready for the presentation among the group.
The option filters allows you to generate several required reports based on a single PivotTable while doing different summarizations.
It is known fact that a PivotTable report is interactive, as it enables you to make quick changes like data trends, data summarizations, etc. You can also provide visual cues such as report filters, slicers, timeline, PivotCharts, etc. to the audience so that they can visualize the details they want.
Here we explained about different types of creating PivotTable reports appealing with visual cues that enable quick exploration of the data.

Hierarchies

We learned about how to nest fields to form a hierarchy. Previous chapter explained about how to group / ungroup data in a PivotTable in the Chapter – Using PivotTable Tools. Following example discusses about how to produce interactive PivotTable reports with hierarchies.
Incase if you have an in-built structure for the fields in your data, then it will enable you to quickly expand/collapse fields to view the summarized values at the required level.
Here’s process to create a PivotTable as shown below.
hiracrchies
We find that, there is a comprehensive way of reporting the data using the nested fields as a hierarchy. To display the results only at the level of Quarters, you can quickly collapse the Quarter field.
create
Suppose you have a Date field in your data as shown below.
quarter field
In such a case, you can group the data by the Date field as follows −
your data

Create a PivotTable

group

As you can observe, this PivotTable is not convenient to highlight significant data.
  • Group the PivotTable by Date field. (You have learnt grouping in the Chapter – Exploring Data with PivotTable Tools in this tutorial).
  • Place the Salesperson field in Filters area.
  • Filter the Column labels to East Region.
east region

Report Filter

This option enables you to get the a report for each Salesperson separately. Here’s process −
  • Ensure that you have Salesperson field in Filters area.
  • Click on the PivotTable.
  • Click the ANALYZE tab on the Ribbon.
  • Click the arrow next to Options in the PivotTable group.
  • Select Show Report Filter Pages from the dropdown list.

report filter

The Show Report Filter Pages dialog box appears. Select the field Salesperson and click OK.
filter pages
A separate worksheet for each of the values of the Salesperson field is created, with the PivotTable filtered to that value.
seperate work sheet
The worksheet will be named by the value of the field, which is visible on the tab of the worksheet.

Slicers

Use slicer option to filter the fields visually.
  • Click on the PivotTable.
  • Click the ANALYZE tab.
  • Click Insert Slicer in the Filter group.
  • Click Order Date, Quarters and Years in the Insert Slicers dialog box. Three Slicers –Order Date, Quarters and Year will get created.
  • Adjust the sizes of the slicers, adding more columns for the buttons on the slicers.
  • Create Slicers for Salesperson and Region fields also.
  • Choose the Slicer Styles so that date fields are grouped to one color and the other two fields get different colors.
  • Deselect Gridlines.

slicers

As you can see, you have not only an interactive report, but also an appealing one, that can be understood easily.

Timeline in PivotTable

To create an aesthetic report it is mandatory to have a Date field in your PivotTable, inserting a Timeline also is an option to produce it.
  • Create a PivotTable with Salesperson in ROWS area and Region in COLUMNS area.
  • Insert a Timeline for the field Order Date.
  • Filter the Timeline to show 5 months data, from November 2015 to March 2016.
timeline

DESIGN Commands

The PIVOTTABLE TOOLS - DESIGN commands on the Ribbon provide you with the options to format a PivotTable, including the following −
  • Layout
  • PivotTable Style Options
  • PivotTable Styles

Layout

As per below mentioned preferences you can have PivotTable Layout:
  • Subtotals
  • Grand Totals
  • Report Layout
  • Blank Rows
layout

PivotTable Layout – Subtotals

The other option like displaying Subtotals is available but by default, Subtotals are displayed, at the top of the group.
subtotals
As you can observe the highlighted group – East, the subtotals are at the top of the group. You can change the position of subtotals as follows −
  • Click on the PivotTable.
  • Click the DESIGN tab on the Ribbon.
  • Click Subtotals in the Layout Options group.
  • Click Show all Subtotals at Bottom of Group.
click sub totals
The Subtotals will now appear at the bottom of each group.
bottom
Select Do Not Show Subtotals incase if you do not have to report the Subtotals.
show sub totals

Grand Totals

Here you can select either display Grand Totals or not. You have four possible combinations −
  • Off for Rows and Columns
  • On for Rows and Columns
  • On for Rows Only
  • On for Columns Only
By default, it is the second combination – On for Rows and Columns.

Report Layout

Select the best matching layout from the several Report Layouts.
  • Compact Form.
  • Outline Form.
  • Tabular Form.
If you find multiple occurrences then you can choose whether to repeat all the item labels or not.
report layout
The default Report Layout is the Compact form that you are familiar with.

Compact Form

compact form

The Compact form optimizes the PivotTable for readability. The other two forms display the field headers also.
Click on Show in Outline Form.
click show
Click Show in Tabular Form.
tabular form
Consider the following PivotTable Layout, wherein the field Month is nested under the field Region −
nested
As you can observe, the Month labels are repeated and this is the default.
Click Do Not Repeat Item Labels. The Month labels will be displayed only once and the PivotTable looks clear.
not repeat

Blank Rows

To make your PivotTable Report more distinct, you can insert a blank line after each item. You can remove these Blank Lines anytime later.
blank rows
Click Insert Blank Line after Each Item.
insert blank

PivotTable Style Options

You have the following PivotTable Style Options −
  • Row Headers
  • Column Headers
  • Banded Rows
  • Banded Columns
style option
By default, the boxes for Row Headers and Column Headers are checked. These options are for displaying special formatting for the first row and the first column respectively. Check the box Banded Rows.
banded rows
Check the box Banded Columns.
banded columns

PivotTable Styles

Select one PivotTable Styles from the given list. Select the one that suits your report. For example, if you choose Pivot Style Dark 5, you will get the following style for the PivotTable.
pivot tables

Conditional Formatting in PivotTable

You can set Conditional Formatting on the PivotTable cells by the values.
conditional format

PivotCharts

PivotCharts add a visual emphasis on your PivotTable reports. You can insert a PivotChart tied to the data of a PivotTable as follows −
  • Click on the PivotTable.
  • Click the ANALYZE tab on the Ribbon.
  • Click PivotChart.

pivocharts

The Insert Chart dialog box appears.
Click Column in the left pane and select Stacked Column. Click OK.
click columns
The stacked column chart is displayed.
  • Click on Month on the PivotChart.
  • Filter to February and click OK.
feb
As you can observe, the PivotTable is also filtered as per the PivotChart.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Excel pivot tables Topics