This page provides you an overview of different table properties and its functions.

Behaviour

General

Title: The title of the table, displayed in dialogs and menus whenever the table is referred to.

Formatting

OptionDescription
Resize columns/rows after refresh Automatically adjust the columns and rows to fit the data.
Apply formatting Override formatting on the table, or let you manage them manually.
Fill formulas next to table Detect and fill down any formulas directly next to the table as rows are added or removed.
Merge repeating cells When data is repeated, merge the repeated cells for the outer values.
Transpose Changes the position of table rows and columns by swapping them over.
Insert/delete columns/rows when table size changes

 Determines the behavior when rows are added or removed.

  • No Shift - Anaplan XL overwrites neighboring cells
  • Shift Range - Anaplan XL inserts the required number of cells only
  • Shift Entire - Anaplan XL inserts and deletes entire columns or rows to accomodate the new data
Fill formatting when expandingControls whether cell formatting is copied into new rows when the table expands. Happens when a column is formatted and new rows are created when the table is refreshed.

Refresh

Option Description
Refresh Table on openCauses the table to update itself when the workbook is opened.
Refresh when cells driving the query changeCauses the table to update itself when cells driving the query are updated.
Allow formulasControls whether text from the query is entered as plain text or if it allows Excel formulas to be entered.

Appearance

Pivoting

These options apply when pivoting data.

OptionDescription
Group by pivoted valuePivoted values are kept together; for example, all columns for January will be together.
Nest by pivoted valueNumeric columns are kept together; for example, all columns for Sales Value will be together.
Show in-cell chartsUseful when pivoting on a date, it automatically displays a chart of the data.
Pivot fixed on column nameAnaplan XL stores the pivoted column index by default. You can instead switch so the column name is used. This is useful if the query can vary the columns it returns, for example, from a stored procedure.
When using named queries for pivotFor SQL queries, you can choose the order for the default alphabetical order to show (pivoted columns are used). If you specify an order query, you can decide whether to emit all columns in the order query or those with data.

Sections

This is used to configure the columns used as section headers, as described in the section help.

Column display

When enabled, this feature allows you to reorder the columns of the query before they are displayed, and to suppress any columns that aren't required.

This reordering is done after any pivoting has been done, so it only affects the final displayed results.

Query

  • Auto-generate Query (default): Anaplan XL controls the query for the table given the selected options.
  • Manual Query: You may type your own query in the edit field.
  • Get Query from Excel range: The entire query statement for the table is taken from a cell on the worksheet.

Interaction

Anaplan XL can create named ranges based on table elements that can then be used by other Excel objects, such as formulas and charts. See Named ranges in Anaplan XL Reporting for more information.

Performance tips for large tables

If you have large tables, these options will speed up data refreshes:

Turn off:

  • Apply Formatting
  • Merge repeating cells
  • Resize Columns