# 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](#pivot-settings)
  - [General](#general-settings)
    - [Show missing values](#show-missing-values)
  - [Format](#format-settings)
  - [Layout](#layout-settings)
    - [Layout options](#layout-options)
    - [Display options](#display-options)
- [Show/Hide](#show-hide)
- [Show total as](#show-totalas)
- [Apply themes](#apply-themes)
- [Sort a pivot table](#sorting)
- [Freezing Column](#freezing-columns)
- [Conditional formatting](#conditional-formatting)
  - [Rule Based](#rule-based)
  - [Color Band](#color-band)
  - [Icon 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**.

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-plus-pivot-general-settings.png)

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.

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-plus-pivot-general-settings-with-show-missing-values-option.png)

#### 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**.

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-plus-pivot-general-settings-with-show-missing-values-enabled.png)

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

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-plus-show-missing-values-table-ex.png)

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.

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-plus-pivot-table-without-missing-values-ex.png)

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.

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-plus-pivot-table-with-missing-values-ex.png)

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*.

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-plus-pivot-table-with-missing-values-and-heirarchy-ex.png)

### 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](https://www.manageengine.com/analytics-plus/help/table/working-with-tables.html#format_column) to learn more about formatting a table column.

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/format-column.png)

4. The **Alignment** option allows you to align the **Column Headers** horizontally to the Left, Right, or Center positions.

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/column-header-alignment.png)

### Layout settings

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

- [Layout options](#layout-options)
- [Display 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.

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-plus-pivot-tabular-layout.png)

- **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.

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-plus-pivot-compact-layout.png)

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.

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-plus-pivot-layout-icons.png)

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**.

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-plus-pivot-compact-layout-edit-rowlabel.png)

**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.

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-plus-pivot-layout-settings-tabular.png)

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

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-plus-pivot-layout-settings-compact.png)

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.
3. Click **Apply**.

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-pivot-table-display-settings.png)

The following are the available display options:

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

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-plus-pivot-display-settings-row-number.png)

- **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.

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-show-vertical-line-pivot-table.png)

- **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.

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-plus-pivot-display-settings-line-separator.png)

- **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.

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-plus-pivot-display-settings-wrap-text.png)

- **Show Expand/Collapse icons**: This option allows you to show the Expand/Collapse (+/-) icons in the pivot table.

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-plus-pivot-display-settings-expandorcollapse-icon.png)

- **Column Width**: Choose one of the following options:
  - **Compact**: Tightly packs the columns in the pivot table.

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-plus-pivot-display-settings-column-width-compact.png)

  - **Fit to Screen**: Scales up the pivot table to fit the entire screen.

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-plus-pivot-display-settings-column-width-fit-to-screen.png)

  - **Equal**: Sets a uniform width to all the columns.

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-plus-pivot-display-settings-column-width-equal.png)

- **Display 'Unknown' value as**: 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-**.

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-plus-pivot-display-settings-empty-values.png)

- **Sub-total Label**: Specify a label for the row displaying the subtotal in the pivot table.

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-plus-pivot-display-settings-subtotal-label.png)

## 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-columns)
- [Show/Hide totals](#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.

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-plus-pivot-showhide-columns-option.png)

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.
5. Click **OK**.

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-plus-pivot-showhide-columns-dialog.png)

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>**: Hide the selected column.
- **Hide All <column-name>**: Hide all columns with the same name.
- **More**: Access the **Show/Hide Columns** pop-up.

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-plus-pivot-showhide-columns-option2.png)

### 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](https://www.manageengine.com/analytics-plus/help/pivot-table/creating-a-new-pivot-table.html#data-as-row) or [Data as column](https://www.manageengine.com/analytics-plus/help/pivot-table/creating-a-new-pivot-table.html#data-as-column) format.

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

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-plus-pivot-showhide-totals-option.png)

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**: Select **Right**, **Left**, or **Hide**.
   - **Columns**: Select **Bottom**, **Top**, or **Hide**.
4. Click **OK**.

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-plus-pivot-showhide-totals-dialog.png)

Default alignment:

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-plus-pivot-showhide-totals-default-alignment.png)

Modified alignment:

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-plus-pivot-showhide-totals-modified-alignment.png)

Totals hidden:

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-plus-pivot-showhide-totals-hidden.png)

## 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.

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

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-plus-pivot-show-total-as-option.png)

Example with **Average** function applied:

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-plus-pivot-show-total-as-ex.png)

## Apply themes

Analytics Plus allows you to customize the look and feel of your Pivot table using colorful and attractive themes.

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/v1_15.gif)

- Open the **Pivot Table**.
- Click the **Themes** button.

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/s59.png)

You can customize:

- **Theme Layout**
- **Theme Color**
- **Font**
- **Zoom**
- **Row spacing**

- Click **Reset** to undo changes.
- Click **Reset to default** to revert to default theme.
- **Save** the Pivot Table.

## Sort a pivot table

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.

### Sorting a Pivot column by its values (Row shelf)

- Right-click the column header or any cell.
- Select the required sort order and then **By Column**.

![](https://www.manageengine.com/analytics-plus/images/sorting-pivot.jpg)

Example:

![](https://www.manageengine.com/analytics-plus/images/sorted-pivot.png)

### Sorting by corresponding data values (Data shelf)

- Right-click the data value column header.
- Select the required sort order and choose the column.

![](https://www.manageengine.com/analytics-plus/images/sort-by-column.png)

### Sorting by summary values

- Right-click the summary column header.
- Select the required sort order and column.

![](https://www.manageengine.com/analytics-plus/images/sorting-menu.jpg)

![](https://www.manageengine.com/analytics-plus/images/sort-summary-column.png)

![](https://www.manageengine.com/analytics-plus/images/column-sorted.png)

You can also sort rows by clicking the arrow icon in the column header.

## Sorting of Values across Groups

**Sort across group** allows you to apply sorting across all groups instead of sorting inside each group.

**Before enabling Sort across Group**

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/sorting2.png)

**After enabling Sort across Group**

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/sorting1.png)

To enable Sort Across Groups:

1. Go to **Settings** and select **Display**.
2. Enable **Sort across Group**.

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/sort-settings.png)

**Note:** This option will be disabled if:

- Subtotal or Grand Total rows are enabled.
- Expand/Collapse or Compact View is enabled.
- Columns are placed under the Columns shelf.
- Columns are hidden.
- Data as row is enabled.
- Missing values are shown.
- Custom sorting is applied.
- Sorting is done by Subtotals.
- Advanced conditional formatting is applied.
- Window functions or view formulas are used.

## Freezing Columns

When working with a wide pivot, you can freeze specific columns to keep them visible while scrolling horizontally.

**To freeze a column,**

1. Right-click the column header.
2. Select **Freeze Column**.

**To unfreeze a column,**

- Click the pin icon, or
- Right-click and select **Unfreeze Column**.

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/freeze-ezgif.com-crop.gif)

**Note:**

- The **Row** section is frozen by default.
- Freeze behavior depends on screen size.
- The freeze limit is based on the number of columns frozen.

## Conditional formatting

Analytics Plus allows you to highlight cells in pivot tables based on specific conditions.

**Note:** To utilize a secondary column for conditional formatting, both columns must be numeric.

- [Rule Based](#rule-based)
- [Color Band](#color-band)
- [Icon Band](#icon-band)

**Note:** The Color Band and Icon Band options apply only to numeric data types.

### Conditional formatting - Rule Based

1. Open the pivot view.
2. Right-click the data and select **Conditional Formatting**.

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-pivot-rule-based-conditional-formatting-option.png)

3. Choose the column in **Based on**.
4. Select the required **Condition**.

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-pivot-rule-based-condition.png)

5. Enter the threshold value.
6. Choose **Format Options**.
7. Click **+Add Condition** to add more rules.
8. Click **OK**.

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-pivot-rule-based-ex.png)

#### Formatting options

- **Font Color**
- **Background Color**

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-pivot-rule-based-text-color-options.png)

- **Text**: Add prefix/suffix or replace text.

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-pivot-rule-based-additional-text-formatting-options.png)

- **Icon**

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-pivot-rule-based-additional-icon-formatting-options.png)

### Conditional formatting - Color Band

1. Open the pivot view.
2. Right-click and select **Conditional Formatting**.

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-pivot-color-band-conditional-formatting-option.png)

3. Choose column in **Based on**.
4. Select **Monochrome** or **Gradient**.

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-pivot-color-band-type-monochrome.png)

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-pivot-color-band-type-gradient.png)

5. Use **Auto MinMax** or define range.
6. Click **OK**.

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-pivot-color-band-ex.png)

### Conditional formatting - Icon Band

1. Open the pivot view.
2. Right-click and select **Conditional Formatting**.

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-pivot-rule-based-conditional-formatting-option.png)

3. Choose column in **Based on**.
4. Select icons and ranges.

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-pivot-icon-band-settings.png)

5. Choose **Value With Icon** or **Icon Only**.

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-pivot-icon-band-display-icon-with-value.png)

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-pivot-icon-band-display-icon-only.png)

6. Use **Auto MinMax** or define range.
7. Click **OK**.

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-pivot-icon-band-ex.png)

#### From settings

You can view and modify the conditional formats from the **Conditional Format** tab in the Pivot's **Settings** page.

![](https://cdn.manageengine.com/sites/meweb/images/analytics-plus/analytics-pivot-settings-conditional-formatting.png)