Table Properties

Revision as of 08:49, 11 June 2019 by Antonio.remedios (talk | contribs) (Formatting)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

Behaviour

General

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

Formatting

Resize columns/rows after refresh
Automatically adjust the columns and rows to fit the data.
Apply formatting
Override formats on the Table, or let you manage them manually.
Fill formulae next to Table
Detect and fill down any formulae directly adjacent to the Table as rows are added or removed.
Merge repeating cells
When data is repeated, merge the repeated cells for the outer values
With merged repeating cells
With merged repeating cells
With separate repeating cells
With separate repeating cells
Transpose
Changes the position of table rows and columns by swapping them over (new in v8.1)
Insert/delete columns/rows when Table size changes
Determines the behaviour when rows are added or removed.
No Shift XLCubed overwrites neighbouring cells.
Shift Range XLCubed inserts the required number of cells only.
Shift Entire XLCubed inserts and deletes entire columns or rows to accommodate the new data.
Fill formatting when expanding
Controls 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

Refresh Table on open
Causes the Table to update itself when the workbook is opened.
Refresh when driving cells change
Causes the Table to update itself when cells driving the query are updated.

Appearance

Pivoting

These options apply when pivoting data

Group by pivoted value
Pivoted values are kept together, e.g. all columns for January will be together
Nest by pivoted value
Numeric columns are kept together, e.g. all columns for Sales Value will be together
Show in-cell charts
Useful when pivoting on a date, automatically displays a chart of the data.
PivotGroupingExamples.png
Pivot fixed on column name
By default XLCubed stores the pivoted column index. You can instead switch so the column name is used. This is useful if the query can vary the columns it returns, e.g. from a stored procedure.
When using named query for pivot
For Sql queries you can specify the order for pivoted columns to be displayed (by default alphabetical order is used). If you specify an order query, you can also decide whether to emit all columns in the order query, or just those which have 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 only affects the final displayed results.

Query

Auto-generate Query (default)
XLCubed 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

XLCubed can create named ranges based on Table elements that can then be used by other Excel objects, such as formulae and charts. See Named Ranges for more information.

Performance tips for large tables

If you have very large tables, the following options will speed up data refreshes:

Turn off

  • Apply Formatting
  • Merge repeating cells
  • Resize Columns

See Also