Customizing a pivot table

Customizing a Pivot Table

When designing a Pivot Table, ManageEngine Analytics Plus offers 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 ManageEngine Analytics Plus to customize a Pivot Table that you create.

General Settings

The General Settings tab helps you modify the Title, add a short description of the data, and configure the settings for the Missing values. Follow the below steps to customize the settings,

  • Click the Settings icon in the top left corner.

  • The General Settings Tab will open.

  • Modify the settings as required and click Apply.

Show Missing Values

Show missing values feature is used to display the missing/empty values in a Pivot. This can be applied to a Date or a Category column. When creating a Pivot, if a particular data point does not have any value, then the Pivot will skip displaying that data point. With this option, you can choose to display the record even if the point does not contain a value.

  • For columns in the 'Columns' shelf: You can choose to display the filtered columns in the 'Columns' shelf by selecting/deselecting the check box. By default, this value will not be selected.

  • For columns in the 'Rows' shelf: You can choose to display the filtered columns in the 'Rows' shelf by selecting/deselecting the check box. By default, this value will not be selected.

Layout

Analytics Plus allows you to choose one of two layout options for your pivot tables. To customize these layout options, click the Settings icon on the top right corner of your pivot table, and navigate to the Layout tab. To learn more about the layout options, click here

The following modification can be made to the Tabular layout.

  • Repeat group level value in each row: Select this option to display the group label for every row listed in your pivot table. 

The following modification can be made to the Compact layout.

  • Indent Level: You can change the indent level of the data displayed in the compact Row Labels column, that groups and displays the values dropped in the Row shelf. By default, indent level 1 will be applied.

The following modifications can be made to the way data is displayed in the pivot table.

  • Hide row numbers: Select this to hide the row numbers displayed in the pivot table.

  • Horizontal Line Between Each Row: You can separate each row of the pivot table using a horizontal line, by selecting this checkbox.

  • Wrap text in Column headings: Select this option to wrap column headers in the pivot table and display them in multiple lines within the same cell. 

  • Column Width: The Column Width under Settings -> Layout tab enables you to set a specific width to columns in the pivot table. You can use the options available to set uniform, compact or varied widths across all columns in a pivot table.

    • Compact: This option tightly packs the columns in the pivot table, thereby helping to save horizontal space and improve readability without the need to scroll sideways.
    • Fit to Screen: This option adjusts column width to fit the table across the entire screen. In case of adding/removing columns, applying filters, etc in the future, the column width will always adapt to the screen size. The Fit to Screen option can also be applied to all pivot and summary views on the dashboard, by enabling the Fit the Pivot/Summary view to card width from the Settings menu in dashboard.
    • Equal: This option allows you to uniformly resize all the columns to an equal width. Once selected, specify the required width (in pixel) in the provided field.
    • Apply to manually resized columns: Selecting this checkbox will resize all the columns - including columns whose width has been set manually - to the specified uniform width.
  • Show Expand/Collapse Icons: You can choose to show/hide the Expand/Collapse (+/-) icons by selecting/unselecting the check-box. By default, the +/- icons will be displayed inline with the cell name when you perform the Expand/Collapse operation on your Pivot Table. To learn more about this option, refer to Working with Pivot Table document.
  • Display 'Unknown' value as: Specify the value that needs to be displayed when the underlying data in the pivot contains empty values. By default, -No Value-  will be displayed.

  • Sub Total Label: By default, Analytics Plus will display the subgroup names in the row header. You can add a prefix or suffix to the Sub-total Label, or you can customize the label as needed.

Applying Themes

Analytics Plus allows you to customize the look and feel of your Pivot table using colorful and attractive themes. You can customize your Pivot Table using the options provided. 

Note: This option is available only as a part of the new charting library that was released recently.

Watch the following GIF or read the below steps to learn about changing the Pivot Theme. 

  • Open the Pivot Table.
  • Click the Themes button. The Themes dialog will open as shown below.

  • You can select an appropriate theme to suit your needs and customize it using the options available. The Themes dialog allows you to select the,
    • Theme Layout: You can choose a layout from the available set of seven layouts.
    • Theme Color: Select a color that you wish to apply.
    • Font: Select the font for the text in your Pivot.
    • Zoom: You can Zoom in or Zoom out. This will increase or decrease the size of your Pivot Table.
    • Row spacing: You can alter the row spacing using the predefined options available.
  • As you choose the themes, the changes will be dynamically applied in the background.
  • If you wish to undo the changes click Reset.
  • If you want to reset the theme to the default theme click the Reset to default option.
  • Save the Pivot Table.

Show Missing Values

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 the day. Our data 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.

settings-pivot-dialog

 

 

 

 

 

 

 

 

 

 

 

 

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.

  • Click Settings in the tool bar and in the Settings tab that appears, click Choose Columns link next to For columns in the "Rows" shelf (Under Show Missing Values). 
  • Select the column (in our case Location)
  • Check Apply hierarchy while listing missing values
  • Click Apply

The Pivot will now look as shown above.

Formatting Options

To format the columns in the Pivot, follow the steps below:

  • Open the Pivot Table you would like to customize.

  • Select Settings option in the toolbar. This will open up Settings dialog box. You will notice that this dialog contains two tabs - General and Format.

  • In the Format tab, you can change the display Labels for the columns as needed.

  • You can set the display format of each column clicking the Format link. The options in the Format column dialog will vary depending the data type of the column. These options will be similar to that of the table column formatting.

Show/Hide

By default, subtotals of individual rows and columns, and the grand total of all the rows and columns will be displayed in the Pivot table. You can choose to change the position of these totals, or turn them off. You can choose to hide specific columns in a pivot table, and display only the required columns.

Show/Hide columns

When you create a pivot table, every column will be displayed automatically. However, you can choose to hide certain columns and display only the rest as required. Follow the steps below to do this: 

  • Open the required pivot table, click the Show/Hide option in the toolbar and select Columns

  • The Show/Hide Columns popup appears, listing all the columns on the pivot table. Select the columns you wish to hide.

  • The Empty Columns checkbox allows you to show or hide the empty columns in the Pivot. Select this checkbox to display the empty columns, and unselect this checkbox to hide the empty columns. Click OK

  • You can also right-click a particular cell, and select the Show/Hide Columns option. The following options are available:

    • Hide column-name: This option allows you to hide the selected column.

    • Hide all column-name: This option is applicable only when repetitive columns are present in the pivot table and allows you to hide all columns with the same name. 

    • More: Select this option to access the Show/Hide Columns screen, which displays every column in the pivot view. 

 

Show/Hide Totals

Follow the steps below to customize the subtotals and the grand totals in your Pivot table.

  1. Click the Edit Design button in the Pivot Table.
  2. Click the Show/Hide Totals button in the toolbar. You can also right click a column and choose the More... option under the Show/Hide Totals option. 
  3. The Show/Hide Totals dialog appears.

The Subtotal and Grand total sections list the following options to customize the row and column subtotals and grand totals.

  • Rows: Select Right or Left to display the subtotal in the corresponding position. You can also choose to hide the row subtotal by selecting Hide.

  • Columns: Select Bottom or Top to display the subtotal in the corresponding position. You can also choose to hide the column subtotal by selecting Hide.

Show Total As

By default, sub-totals and grand totals are displayed for relevant columns in pivot tables. You can customize this to apply other summary functions such as sum, average, minimum and maximum in the sub-total and the grand total of rows and columns. 

To customize summary function, right-click on any column in the pivot table and select Show Total As and then select the function you wish to apply. 

Note: The summary function is customizable for each data column in the pivot. 

Sorting a Pivot Table

In ManageEngine Analytics Plus, 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. ManageEngine Analytics Plus 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:

  • Right-click the column header or on any cell of the corresponding pivot table column whose values has to be sorted.
  • In the pop up menu, select the required sort order and then By Column (column specific) option.

For example if a pivot table has Product category and Product columns 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 shot 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:

  • Right-click the data value column header or on any data value cell corresponding to a Pivot Table column value.
  • In the pop up menu, select the required sort order and then select
    the column based on which you want to sort data values as shown below.

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.

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:

  • Right-click the summary column's header.
  • In the pop up menu, select the required sort order and then select
    the column based on which you want to sort summary values as shown
    below.

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

You can highlight various cells in a pivot table based on specific conditions using the Conditional Formatting feature. This option allows you to apply different styles and colors, to categorize the data in your pivot table for easier analysis. 

Follow the below steps to apply conditional formatting to your pivot table:

  • Right-click on the data you wish to format in your pivot table, and select the Conditional Formatting option.

  • In the popup that appears, select the required condition from the Condition drop down. The available conditions vary based on the type of data that is being formatted. 

  • Enter the threshold value in the Value section. Every data cell that meets this condition will be highlighted. 

  • Select the required font and background color using the icons under the Format Options section.

  • You can format your cell further by clicking the Additional Formatting Options icon under the Format Options section. Note: The Icons options is currently supported only for Numeric values. 

  • If you wish to modify the data, rows and columns over which the conditional formatting should be applied, click the Edit link. To add more conditions, click the +Add Condition link. The specified conditions will be evaluated from top to bottom, and appropriate formatting options will be applied over the data cells that meet the condition.

  • Click OK to apply the specified formatting over the pivot table. 

You can also view and modify the conditional formats applied over the pivot view, from the Conditional Format tab of the chart's Settings page. Click the required data to view the corresponding conditional formats applied over it. Modify the conditions as required, and click Apply

Share this post : FacebookTwitter