Visuals in Pivot

Pivot table offers you the following visualization options to enhance data representation.

• Sparkline Charts
• Data Bars
• Gantt

Sparkline Charts

Sparklines are compact, multi-point visual charts that show the trends and variations in the data, without taking up much space. They are widely used alongside pivot tables and help grasp trends and insights quickly.

For instance, spark lines can be used to visualize the trend of sales figures, expenses, or other metrics across different categories or time periods.

Zoho Analytics supports three types of sparkline charts:

• Line
• Bar
• Area

Adding Sparkline Charts to the Pivot Table

1. Drag- and-drop the required fields to the columns, rows and data shelves.
2. From the toolbar, click the Sparkline icon or open the Moreoptions(⋮) menu.
3. Choose any one of the below options from the drop-down menu.
   • Only Data - This is the default case, where only the data is generated according to the columns dropped in the designer shelf.
   • Only Sparkline - The data trends of the selected columns alone are shown in the report.
   • Sparkline with Data - The data trend and the data are shown in the report.

A pivot table with a sparkline chart will be created, and a tooltip will be displayed on hover to view the datapoints.

Customizing a Sparkline Chart

Follow the steps mentioned below to customize the sparkline chart

1. Click the Settings icon at the top right corner.
   
2. Open the Sparkline tab.
3. In the Sparkline section, choose the Chart Type to apply to the data column.
4. Choose the Color for the sparkline charts.
5. Specify the Sparkline Cell Height.
   

After you apply the changes, the pivot table updates to reflect the customization.

Data Bars

Data bars are single-point visual elements that help you quickly compare and analyze values within your data. These bars appear directly into the cells, providing an intuitive indication of relative value, making it easier to spot trends, outliers, and patterns.

Adding Data Bars to the Pivot Table

1. Drag- and-drop the required fields to the columns, rows, and data shelves.
2. From the toolbar, click the Data Bars icon or open the More options (⋮) menu.
3. Choose one of the following options from the drop-down.
   • Only Data - This is the default case, where only the data is generated according to the columns dropped in the designer shelf.
   • Only Data Bars - Displays only the Data Bars of the selected column without the values.
   • Data Bars With Data - Displays both the Data and the Data Bars of the selected column.
   • Data Bars with Sparkline - Displays data bars and sparklines together for the selected columns.

Formatting Data Bars

Zoho Analytics allows you to customize Data Bars appearance to match your data visualization needs better.

To format the Data Bars, follow the below steps.

1. At the top-right corner, click the Settings icon. Settings page will open.
2. Click Data Bars.
3. You can use the following settings to adjust the appearance of the Data and Data Bars.
   1. Type: Zoho Analytics offers you the below Data Bars type.
      • Bar: A standard horizontal bar representing the data with length proportional to the data value.
      • Waterfall: Displays bars with progressive values used to show changes over time/stages/categories.
      • Data: Displays only the data values.
   2. Color: Choose your preferred color for the Data Bars.
      
   3. Additional Formatting Options: Click on the Ellipsis icon to access the Additional Options window.
      
      • Data and Bar Sequence: Click the drop-down to choose the display sequence between Data and Bar. This option is only enabled when the Data Bars With Data option is selected. Zoho Analytics provides the following options:
        • Data and bar – Displays the data first, followed by the bar.
        • Bar and data – Displays the bar first, followed by the data.
        • Overlapped – Displays the data overlaid on the bar.
      • Bar alignment: When Bar type is selected, you can align the bar either left, right, or middle. When Waterfall is selected, you can align the data bar either left or right.
      • Data alignment: When the Data Bars With Data option is selected, you can set to align the data either left, right, or middle.
      • Preview: The Preview space allows you to instantly see how different Data and Bars alignment or sequence appear in your pivot table.
      • Negative bar color: Choose your preferred color for the data bars having negative values.
      • Data Bars shown in: Select checkboxes to show Data Bars in Data, Row Grand Total, and Column Grand Total.  
   4. Enter your preferred height value in pixels for the cells of Data Bars in the Data Bars Cell Height box.
      
   5. After customization, the data appears as follows:
      

Formatting Combined Visuals (Data Bars with Sparkline)

When you select Data Bars with Sparkline, the formatting options are available under the Visuals section in the Settings panel.

1. Click Settings icon at the top-right corner.
2. Open the Visuals tab.
3. For each data column, choose the required visual.
4. Customize the color and cell height as needed.
   

The pivot table updates instantly to reflect the changes.

Conditional Formatting for Data Bars 

Zoho Analytics allows you to apply conditional formatting directly on Data Bars. This lets you highlight bars that meet specific conditions, making it easier to track outliers or key thresholds at a glance.

To apply conditional formatting:

1. Right-click on any data cell and select Conditional Formatting.
   
2. On the Conditional Formatting dialog, define the rules based on your criteria.
3. Choose a color for the Data Bars that meet the condition.
   
4. Click OK to save the changes.

The Data Bars in your pivot will now change color dynamically based on the conditions you set, giving you deeper insight without losing the visual comparison of bar lengths.

Gantt

Gantt charts show date ranges as horizontal bars across a timeline, one bar for each row in the pivot table. Use them to track project timelines, task schedules, or any data that has a start date and an end date.

Gantt charts cannot be combined with Sparkline charts or Data Bars.

Data Requirements

To add a Gantt chart, the pivot table must have:

• At least one column in Rows
• A start date column aggregated as Min and an end date column aggregated as Max in Data
• An empty Columns shelf

Gantt option will be available only if these conditions are met.

Optionally, add a column with the completion percentage of each row to Data. This shows the progress on each bar, colors completed and in-progress items separately, and makes the Bullet type available.

Adding a Gantt Chart to the Pivot Table

1. Drag and drop the required columns to the Rows and Data shelves, as listed in Data Requirements.

   

2. From the toolbar, click the More Visuals (⋮) icon.

3. Select Gantt.

   

The pivot table shows a bar for each row across a timeline. Hover over a bar to see its start and end dates.

Customizing a Gantt Chart

1. Click the Settings icon at the top-right corner.

   

2. On the General tab, expand Gantt.
3. Set the following options.
   • Gantt Type: Select how the bars are drawn.
     • Bar: A solid bar from the start date to the end date.
     • Dumbbell: Two points at the start date and the end date, joined by a line.
     • Bullet: A narrow bar showing progress, drawn over a wider bar that spans the full date range. Available only when the pivot table has a completion percentage column. If you remove that column, a chart set to Bullet switches to Bar.
   • Default Value: Select the period the timeline shows when the pivot table opens.
   • Cell Height: Enter the height of each row, in pixels.
   • Chart Effect: Select the drawing style of the bars.

   • Bar Color: Select a color for In Progress and Completed items. Without a completion percentage column, a single Gantt Color is shown instead.

     

The pivot table updates to reflect the changes.

Note: Conditional formatting cannot be applied to Gantt bars.

Navigating the Timeline

Use the navigation bar above the pivot table to move through the timeline. The period label in the middle shows the dates currently displayed.

• Click < or > to move one period backward or forward. The period matches the one selected, such as a week or a month.
• Click << or >> to move one year backward or forward.
• Click the Reset icon to return to the period set in Default Value.

To change the period, click the period label, then do one of the following.

• Select Today, This Week, This Month, This Quarter, This Year, or All.
• To show a single date, select Specific Date, click the date in the calendar, then click OK.
• To show a date range, select Custom Range, click the start date and the end date in the calendar, then click OK.