What can the PivotTable Report do further to the basic?

I have already written the PivotTable report four times. All of them have involved much the basics to create the report with the designed layout (design). However, to make our report more interesting, we need to use more abilities available in the PivotTable tools (Options) and the conditional formatting. The conditional formatting forms a part of Home tab instead of the PivotTable tools but it can be used to highlight the number on the PivotTable report.

The report, our readers have required today, is not just the report with the sum of values. We all know that they require not only the timely basic report but also the capable report for analysing.  PivotTable report can answer these questions reasonably although the answer is not absolutely complete due to the concern of calculation’s accuracy. Tools (Options) of PivotTable can make the analytical report of which the number can be highlighted with the conditional formatting of Home tab.

Work Flows of Conditional Formatting and Options of PivotTable Tools

Start with the conditional formatting which parts of the Ribbon of Home:

Home > Conditional Formatting > Icon Sets > Ratings with Stars > Apply formatting rule to all selected cells on the Sheet4_CondFormat

Home > Conditional Formatting > Data Bars > Select colour > Apply formatting rule to all selected cells on the Sheet4_CondFormat

Click within the area of PivotTable report to show PivotTable Tools and their two Ribbons i.e. Options and Design

What we can do via the Ribbon of Options (Alternative: the right click)

Group Date to see the values by monthly and yearly

PivotTable Tools > Options > Date (field label) > Group Selection (Start & End date by Month & Year to group items under this field label)

Group text is grouping each customer with the tier of each group.

PivotTable Tools > Options > CustomerName (field label)> Dropdown menu > More Sort Option > A to Z by Sum of Total > Select items in CustomerName for Group1 (default name) > Group Selection > Group1 under CustomerName2 (automatically increased to PivotTable report and Field list) > Dropdown menu for CustomerName2> More sort option > A to Z by Sum of Total > Select items in CustomerName2 for Group2

We will use this flow to finish our text grouping. We can change the group name by clicking its name and changing it on the formula bar.

We can group the amount to see how many counts are on each range of values. Nevertheless, we ungroup the amount or Total to move towards the calculations on PivotTable report.

Summarize Values by and Show Values as

I have already changed the name on the field list from Total to Amount (Bht). Notice that box of “Total” on the field list area has been checked but it shows Amount (Bht) on the PivotTable report due to my action of changing.

Normally the total value is summarized by Sum while “Show Values as” appears “No Calculation”. So we will see Amount (Bht) in the sum number attributed to these Summarize Values and Show Values.

In addition to the value in the number, we can make it seen as % on the same PivotTable report by dragging the duplicate Total to the area of Zigma Value of the field list and changing its name to Amount (%).

However “Summarize Values by” and “Show Values as” have to be set up as follows in order to show Amount in Percentage.

PivotTable Tools > Options > Summarize Values by > Sum > Show Values as > % of Grand Total

Fields, Items and Sets

Other than “Summarize Values by” and “Show Values as”, we can put our own formula onto the function of Fields, Items and Sets to let it calculate as it works on the normal report to make the report more useful.

However, we have to be careful about the value of the grand total amount resulting from the price per all units time to units rather than the price per unit time to units.

As early mentioned, ungroup Amount (Bht) (Total) value just allows the function of Fields, Items and Sets to calculate. Group Selection will return the zero value to Total (field label) and then it causes zero to our formula set with Total time to anything.

PivotTable Tools > Options > Fields, Items and Sets> Calculated Field > Name formula : AmtVat+ Insert field onto Formula = Total * 1.07 > Add and OK

There are more functions waiting for us to do our own PivotTable report in details for analysing. Please try it for your real numbers and requirements and it will give you more understanding and more practical reports.

There are the links among Spread Sheet, Access and PivotTable report for the advance function. And Access represents the higher storage of the database that makes me think about the database in the other system’s storage.

Hope that you will enjoy the reading and find the linked file of spreadsheet useful to you.