| XLCubed is now FluenceXL. The new wiki can be found here: https://help.fluencexl.com/ |
Difference between revisions of "Table Properties"
(→Performance tips for large tables) |
(→Behaviour) |
||
| (16 intermediate revisions by 4 users not shown) | |||
| Line 1: | Line 1: | ||
| − | ==Behaviour | + | ==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 | ; Resize columns/rows after refresh | ||
: Automatically adjust the columns and rows to fit the data. | : Automatically adjust the columns and rows to fit the data. | ||
| − | ; Insert/delete columns/rows when | + | ; 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 | ||
| + | {| align="center" | ||
| + | | [[File:TableMergeRepeatingCellsOn.png|thumb|250px|alt=With merged repeating cells|With merged repeating cells]] | ||
| + | | [[File:TableMergeRepeatingCellsOff.png|thumb|250px|alt=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. | : Determines the behaviour when rows are added or removed. | ||
{{TableHeader}} | {{TableHeader}} | ||
| Line 15: | Line 31: | ||
| XLCubed inserts and deletes entire columns or rows to accommodate the new data. | | XLCubed inserts and deletes entire columns or rows to accommodate the new data. | ||
{{TableFooter}} | {{TableFooter}} | ||
| − | ; Fill | + | |
| − | : | + | ; 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=== | ||
; Refresh Table on open | ; Refresh Table on open | ||
: Causes the Table to update itself when the workbook is opened. | : 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. | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | + | ; Allow formulae | |
| − | ; | + | : Controls whether text returned from the query is inserted as plain text, or whether it allows Excel formulae to also be inserted |
| − | : | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | == | + | ==Appearance== |
| − | === | + | ===Pivoting=== |
These options apply when pivoting data | These options apply when pivoting data | ||
| − | ; Group by value | + | ; Group by pivoted value |
: Pivoted values are kept together, e.g. all columns for January will be together | : 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 | : Numeric columns are kept together, e.g. all columns for Sales Value will be together | ||
; Show in-cell charts | ; Show in-cell charts | ||
: Useful when pivoting on a date, automatically displays a chart of the data. | : Useful when pivoting on a date, automatically displays a chart of the data. | ||
| + | |||
| + | [[Image:PivotGroupingExamples.png|thumb|center|300px]] | ||
; Pivot fixed on column name | ; 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 | + | : 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 | ;When using named query for pivot | ||
| Line 56: | Line 66: | ||
This is used to configure the columns used as section headers, as described in the [[Tables#Sections|section]] help. | This is used to configure the columns used as section headers, as described in the [[Tables#Sections|section]] help. | ||
| − | ==Query | + | ==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) | ; Auto-generate Query (default) | ||
: XLCubed controls the query for the Table given the selected options. | : XLCubed controls the query for the Table given the selected options. | ||
| Line 63: | Line 78: | ||
; Get Query from Excel range | ; Get Query from Excel range | ||
: The entire query statement for the Table is taken from a cell on the worksheet. | : 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 == | == Performance tips for large tables == | ||
| Line 70: | Line 88: | ||
Turn off | Turn off | ||
* Apply Formatting | * Apply Formatting | ||
| + | * Merge repeating cells | ||
* Resize Columns | * Resize Columns | ||
| − | * | + | |
| + | ==See Also== | ||
| + | * [[Tables]] | ||
| + | |||
| + | [[Category:Tabular Reporting]] | ||
| + | [[Category:Relational Reporting]] | ||
Latest revision as of 11:13, 29 July 2020
Contents
[hide]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
- 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.
- Allow formulae
- Controls whether text returned from the query is inserted as plain text, or whether it allows Excel formulae to also be inserted
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.
- 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
