Customizing a pivot table

Analytics Plus offers a wide range of options to customize and personalize the overall appearance of the pivot table. The following are the available options.

• Customizing with settings
  • General
    • Show missing values
  • Format
  • Layout
    • Layout options
    • Display options
• Show/Hide
• Show total as
• Apply themes
• Sort a pivot table
• Conditional formatting
  • Rule Based
  • Color Band
  • Icon Band

Customizing with settings

Analytics Plus allows you to customize various elements in the pivot table, including the addition of title, description, hiding row numbers, etc. Refer to the following sections to learn about each option in detail.

General settings

Analytics Plus allows you to modify the title and description of the pivot table. To do this, follow the steps below.

• Open the required pivot table, and click the Settings icon.
• In the page that appears, add or modify the title and description of the pivot table.
• Click Apply.

You can also choose to display the filtered or missing values in the pivot table from the General settings page. Refer to the following section to learn more about displaying missing values.

Show missing values

The show missing values option allows you to display the data points with missing values in the pivot table. Please note that this option is displayed only, when the pivot table has missing data points. To do this,

1. Open the required pivot table with missing values, and click the Settings icon.
2. In the page that appears, head to the Show Missing Values section, and choose the corresponding checkbox as required.
   • For columns in 'Columns' shelf: Select the checkbox to display the filtered columns in the Columns shelf.
   • For columns in the 'Rows' shelf: Select the checkbox to display the filtered columns in the Rows shelf.
3. Click the Choose Columns link and select the required column for which the missing values should be displayed.
4. Select the Apply hierarchy while listing missing values checkbox to apply the hierarchy function when displaying missing values in the pivot table.
5. Click Apply.

For illustration, let's create a pivot table to view employee's attendance details every week using the employee attendance table given below.

To generate the required pivot table, drag and drop the Date, Employee Name, and Location column in the Rows shelf and the Clock-in hours column in the Data shelf. If an employee is unavailable for a particular day, his/her data will not be available for that day, and the employee's name will not be displayed in the generated pivot table.

To display the missing employee details,

• Click the Settings icon, and head to the Show Missing Values section.
• Select the For columns in the "Rows" shelf checkbox and click the Choose Column link.
• Choose the column (Employee Name), and click Apply.

The generated pivot table displays all the employee attendance irrespective of the missing Clock-in hours and Location to which the employee belongs.

Let's apply hierarchy to the above example to view all the employee attendance based on their Location regardless of the missing Clock-in hours.

Format settings

Analytics Plus enables you to change the display label and format of the data columns used in the pivot table. To do this, follow the steps below:

1. Open the required pivot table, and click the Settings icon.
2. Head to the Format tab and change the column display Labels as required.
3. Click the Format link adjacent to each label to change the format of the data in the pivot table. The options displayed in the Format Column dialog vary depending on the data type of the column and are similar to that of the table column formatting. Click here to learn more about formatting a table column.
   
   
4. The Alignment option allows you to align the Column Headers horizontally to the Left, Right, or Center positions.
   
   ​

Layout settings

Analytics Plus allows you to customize the layout and various display options in the pivot table.

• Layout options
• Display options

Layout options

Analytics Plus supports the following two layouts.

• Tabular: The tabular layout is the default layout of the pivot table. It displays the values in the Rows shelf as separate columns in the pivot table.

  

• Compact: This is the close-packed view of the pivot table. This layout groups and displays the values in the Rows shelf as a single column in the pivot table.

  

You can switch between layouts in two ways, either from Edit Design/View mode or Settings.

From Edit Design or View mode

1. Open the required pivot table.
2. Click the corresponding Tabular or Compact icon in the toolbar.

   

   For Compact layout, the values dropped in the Rows shelf are named Row Labels. To modify this, open the pivot table in Edit Design mode, head to the Row Label column, and click the Edit icon that appears on mouse over the column name. Modify the name as needed, and press Enter.

   

From settings

1. Open the required pivot table, and click the Settings icon.
2. Head to the Layout tab and choose the required layout.
   • For tabular layout, you can select the Repeat group label value in each row checkbox to display the group label for each row listed in the pivot table.

     

   • For compact layout, you can change the Indent Level of the data displayed. By default, the pivot table uses indent level 1.

     

3. Click Apply.

Display options

Analytics Plus lets you specify how to display the data in the pivot table. Follow the steps below to modify the display options.

1. Open the required pivot table, and click the Settings icon.
2. Head to the Layout tab > Display section, and choose the required options. Refer to the following section to learn about each display option in detail.
3. Click Apply.

The following are the available display options:

• Show row numbers: This option allows you to display row numbers in the pivot table.

  

• Show Vertical Line between Each Column: Enable this option to add a vertical line between each column. This option is enabled by default. Removing vertical lines can be helpful while preparing financial statements.

  ​

• Show Horizontal Line between Each Row: Enable this option to add a horizontal line between each row that helps you to separate rows in the pivot table.

  

• Wrap text in Column Headings: This option allows you to wrap column headers and display them in multiple lines within the same cell in the pivot table.

  

• Show Expand/Collapse icons: This option allows you to show the Expand/Collapse (+/-) icons in the pivot table. By default, the +/- icons are displayed in line with the cell name when you perform the expand or collapse operation using the Expand/Collapse icon in the toolbar.

  

• Column Width: Choose one of the following options:
  • Compact: This option allows you to tightly pack the columns in the pivot table, to save horizontal space and improve readability without the need to scroll sideways.

    

  • Fit to Screen: This option enables you to scale up the pivot table that fits the entire screen on which you are viewing it.

    

  • Equal: This option allows you to set a uniform width to all the columns in the pivot table.

    

• Display 'Unknown' value as: This option allows you to specify a label to be displayed when the columns dropped in the Rows and Columns shelf have empty values. By default, the pivot table displays -No Value-.

  

• Sub-total Label: This option enables you to specify a label for the row displaying the subtotal in the pivot table.

  

Show/Hide

Analytics Plus lets you show or hide specific columns and totals in the pivot table.

By default, Analytics Plus displays the subtotals and grand total of all the rows and columns. However, you can choose to change the position of these totals or turn them off and hide specific columns in the pivot table.

• Show/Hide columns
• Show/Hide totals

Show/Hide columns

Analytics Plus enables you to hide certain columns and display specific ones as required in the pivot table. Follow the steps below to do this.

1. Open the required pivot table.
2. Click the Show/Hide option in the toolbar and choose Columns from the drop-down.

   

3. In the pop-up that appears, select the required columns you wish to hide. All the columns are selected by default.
4. Disable the Empty Columns checkbox to hide empty columns in the pivot table. By default, this option is selected, and the pivot table displays all the empty columns.
5. Click OK.

   

You can also right-click a particular cell in the pivot table 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 if repetitive columns are available 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 pop-up, which displays all the columns present in the pivot view. You can select the columns as required.

Show/Hide totals

When creating pivot tables, Analytics Plus automatically adds the sub-totals of individual columns and the grand total of all the rows and columns. However, you can turn off these totals or change their positions. Analytics Plus allows you to hide the totals when they are displayed in Data as row or Data as column format. To do this, follow the steps below.

1. Open the required pivot table.
2. Click the Show/Hide option in the toolbar and choose Totals from the drop-down. You can also right-click any column and choose the Show/Hide Totals > More option.

   

3. In the pop-up that appears, choose the corresponding option to customize the rows and columns in Sub-total and Grand-total sections.
   • Rows: This option allows you to select the Right or Left position to display the sub-total or grand-total. You can also choose Hide to hide the row displaying the sub-total or grand-total.
   • Columns: This option allows you to select the Bottom or Top position to display the sub-total or grand-total. You can also choose Hide to hide the column displaying the sub-total or grand-total.
4. Click OK.

   

The following image shows the default alignment of the Sub-total (Columns at the bottom) and Grand-total (Rows at the right and Columns at the bottom).

The following image displays the modified alignment of the Sub-total (Columns at the top) and Grand-total (Rows at the left and Columns at the top) in the pivot table.

The Sub-total and Grand-total are hidden in the following pivot table.

Show total as

Analytics Plus enables you to customize and apply other summary functions, such as sum, average, minimum, and maximum, in the sub-total and the grand total sections of the pivot table. To customize the summary function, follow the steps given below.

• Open a pivot table, right-click any column and select the Show Total As option.
• Choose the required function you wish to apply.

  

The below image is an example pivot table with the Average function applied to the Subtotal and Grand Total.

Apply 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. Please note that 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.

Sort 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 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 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

Analytics Plus allows you to highlight cells in pivot tables based on specific conditions. You can apply different styles and colors to highlight and emphasize critical information such as deadlines, targets, stock levels, and task completion. Conditional formatting can be applied based on the data values of the same or other columns used in the pivot view.

Analytics Plus allows you to visually highlight columns that match the conditional formatting criteria in the following ways:

• Rule Based
• Color Band
• Icon Band

Conditional formatting - Rule Based

Follow the steps below to apply rule-based conditional formatting,

1. Open the desired pivot view in View mode or Edit Design mode.
2. Right-click the data you wish to format in your pivot table and select the Conditional Formatting option.

   

3. In the popup that appears, you can format the data values based on the current selected column or choose another column from the Based on drop-drop menu.

   

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

   

5. Enter the threshold value in the Value section. Any data cell that meets this condition gets highlighted.

   

6. Select the desired Format Options to apply. Click here to learn more.
7. Click the Edit link if you want to modify the data, rows, or columns over which the conditional formatting applies.
8. Click the +Add Condition link to add more conditions. The conditions are evaluated from top to bottom, and the appropriate formatting is applied to the data cells that meet each condition.

   

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

   

Formatting options

The following are the available formatting options:

• Font Color: This will apply the chosen color to data that meets the specified conditions.
• Background Color: This will apply the chosen color to the background of the cells that meet the specified condition.

  

• Text: This allows you to include Prefix or Suffix to the existing data value or change the existing data values with custom text using the Text Replacement option.

  

• Icon: This provides a quick visual representation to highlight trends in data, making it easier to identify areas of improvement and areas of excellence.
  • Choose the icon that represents the information in the data better.
  • Click the Icon color to choose the preferred color for the icon.
  • Select the way to display the icon, either Values with Icon or Only the Icon.
  • Choose the Icon Alignment, Align left, or Align right.
  • The Place Icon near the Value checkbox helps to position the icon closer to the corresponding value. This option is enabled by default. Disable it to position the icon farther from the value.

  

Conditional formatting - Color Band

The Color Band feature lets you apply a gradient of colors across your data range. It helps to highlight variations in data, such as a gradual increase or decrease in values.

1. Open the desired pivot view in View mode or Edit Design mode.
2. Right-click the data you wish to format in your pivot table and select the Conditional Formatting option.

   

3. In the popup that appears, you can format the data values based on the current selected column or choose another column from the Based on drop-drop menu.

   

4. Select the type of color band, either Monochrome or Gradient, from the Color Band Type drop-down.
   • Monochrome: It allows you to apply gradients using shades of a single color to represent the data range.

     Click the Reversed toggle button if you want to invert the color intensity across your data range, with the lightest shade becoming the darkest and vice versa.

     

   • Gradient: This feature enables you to create a smooth transition between two or three colors in your pivot table.
     • Start color: Applied to the minimum data values.
     • End color: Applied to the maximum data values.
     • Middle color (Optional): Click the + icon to add a third color, which applies to the middle of the gradient. You can adjust the position using a slider that ranges from 0% to 100%.
     • Delete Colors: Click the link to remove any of the selected colors. This option applies only when you choose a third color.
     • Merge Colors: This option blends two selected colors into a smooth, unified gradient. Unchecking it will give you a separate gradient for each color.

       Note: The Merge Color option is applicable only if two colors are selected. It is not available if a third color is added to the gradient.

     

5. The Auto MinMax checkbox allows the minimum and maximum values for the color band to be set automatically. Unchecking this box lets you manually define the minimum and maximum values, giving you control over the range that the gradient spans.
6. Click OK.

   

Conditional formatting - Icon Band

The Icon Band feature allows you to represent your data with icons such as arrows, emojis, circles, hearts, stars, etc. It helps to identify high and low values or performance indicators.

1. Open the desired pivot view in View mode or Edit Design mode.
2. Right-click the data you wish to format in your pivot table and select the Conditional Formatting option.

   

3. In the popup that appears, you can format the data values based on the current selected column or choose another column from the Based on drop-drop menu.

   

4. Choose the icons and their colors. You can choose two to five icons. The middle three icons are optional, and their positions can be adjusted using a range slider that spans from 0 to 100%.

   Note: Analytics Plus automatically adjusts the ranges as you add or remove icons.

   

5. Select desired format settings to adjust the icon display options:
   • Value With Icon: This option displays both the data value and the corresponding icon. You can align the icon either to the left or right of the value. The Place Icon near the Value checkbox helps to position the icon closer to the corresponding value. This option is enabled by default. Disable it to position the icon farther from the value.

     

   • Icon Only: This option displays only the icon without the data value. You can align the icon to the left, middle, or right within the cell.

     

6. The Auto MinMax checkbox allows the minimum and maximum values for the color band to be set automatically. Unchecking this box lets you manually define the minimum and maximum values, giving you control over the range that the gradient spans.
7. Click OK.

   

From settings

Analytics Plus lets you view and modify the conditional formats applied over the pivot view, from the Conditional Format tab of the Pivot's Settings page. Click the required data to view the corresponding conditional formats applied over it. Modify the conditions as required, and click Apply.