When designing a Pivot Table, Zoho Reports offers a wide range of options to customize it and improve the overall appearance in different ways. In this section we will discuss about various options provided by Zoho Reports to customize a Pivot Table that you create.
Zoho Reports allows you to customize the appearance of the Pivot (like adding description, hiding row numbers etc..).
To customize the appearance of the Pivot, follow the steps given below:
Show missing values feature is used to display the in-between missing values in a Pivot. This can be applied on a Date or a Category column. When creating a Pivot, if a particular data point does not have any value, then the Pivot would skip displaying that data. With this option you can choose to display the record even if a point does not contain a value.
Let us say that you are the manager of a team and would like to view your employee`s attendance details every week. In case an employee is not available for a particular day, his data will not be available for that day. Our database looks as shown below.
Lets now create a pivot as shown in the below snapshot to view the number of employees present in the given week. To do that, drag and drop the Date and Employee Name column in the Rows shelf and Clock-in hours column in the Data shelf.
This Pivot does not contain any record of the employees who were not available (absent). To view the name of the employees who were not available for a particular day, you can enable show missing value function for the Employee Name column.
You can either right click on the column name and choose Show Missing Values or click Settings option on the tool bar.
In the settings tab that appears, Under the Show Missing Values section, click Choose Columns link next to For columns in the "Rows" shelf. You can choose the columns for which you wish to show the missing values.
The Pivot that is generated now, will contain the data of the employees who were absent.
In case you wish to view the details of the employees based on their location, we will be making a small change in the existing pivot. Drag and drop the Location column under the Rows shelf.
Our Pivot will look as shown in the below snapshot. If you take a closer look, you will notice that this might not be the best way to display the data as it displays the name of all employees across all locations.
In this case, you can choose to apply hierarchy function while listing missing values.
The Pivot will now look as shown below.
To format the columns in the Pivot, follow the steps below:
In Zoho Reports, by default, sub totals of individual columns, and grand total of all the rows and columns will be automatically added to the Pivot Table. Zoho Reports also allows you to turn off these totals when they are not required.
To hide or show the subtotals and grand totals do the following:
Under this option the following sub-options are available:
Subtotal (column specific): Select/Deselect this option to show/hide subtotals of an individual column. This option will be available only on right clicking the corresponding column whose subtotal has to be shown/hidden.
Row Grand Total: Select/Deselect this option to display/hide the Row Grand Total column in the pivot table.
Column Grand Total: Select/Deselect this option to display/hide Column Grand Total row in the pivot table.
In Zoho Reports, by default, a pivot table data will be sorted in ascending order by the values of the columns from the source table that you assign to Row orientation in a Pivot Table. Zoho Reports allows you to change this default sort order in lot of different ways. Below is a brief description of various ways to sort a Pivot Table.
Sorting a Pivot column by its values (by the values of the columns in Row shelf): This option allows you to sort Pivot Table column data in ascending or descending order by its actual values.
To sort a pivot table by its column values:
For example if a pivot table has Product category and Productcolumns in Row shelf (Row Orientation), initially the Product Categories and Products will be ordered alphabetically in ascending order. When corresponding columns are sorted in descending order as described above, Pivot data will be rearranged as shown in the screen shots below.
Sorting a Pivot Table column by its corresponding data values(by values of the column in Data shelf): This option allows you to sort Pivot Table columns based on data values corresponding to each pivot column value.
To sort a pivot table based on its data values:
In the above example, when you right click Central region and select Sort Descending -> By Product Category, Sales values in Central region corresponding to Product Category column will be sorted in descending order as shown below.
When you select Sort Descending -> By Product, Sales values in Central region corresponding to Product column will be sorted in descending order as shown below.
Sorting Pivot Table columns by its corresponding summary values: This option allows you to sort Pivot Table columns based on summary values corresponding to pivot column values.
To sort a pivot table based on its summary values:
When you right click Summary Column and select Sort Descending -> By Product Category, Sales values in Summary column corresponding to Product Category column will be sorted in descending order as shown below.
When you select Sort Descending -> By Product, Sales values in Summary column corresponding to Product column will be sorted in descending order as shown below.
You can also sort rows by column values by clicking on the arrow icon() at the heading of the corresponding column. A down arrow indicates that the column is sorted in ascending order. An up arrow indicates the column is sorted in descending order.
Conditional formatting feature allows you to visually highlight data cells in a pivot table with different styles based on matching conditions. You have to specify the required conditions/criteria for formatting. When data in a cell meets the condition, Zoho Reports applies the corresponding formatting style that you have specified to the specific cell.
To apply conditional formatting: