In this article:Style
XLReporting has a powerful built-in spreadsheet, fully compatible with Microsoft Excel, in which
you can build your models. You will find familiar tasks, such as sheets, columns, rows, cells,
styling, functions, and a toolbar. You can create models in the same way you would build
spreadsheets, with one major difference: models only contain logic and layout, they do not
contain data. The data is retrieved in realtime through queries and functions.
This group of buttons enables you to style a cell or cell range:
- font type
- font size
- cell and text color
- cell borders
- cell alignment
- date and number format
- conditional formats
Please note that not all fonts may exist on every computer or browser. There are significant
differences between Windows and Apple with regards to available fonts. This means that a
selected font may not exist on your computer in which case your browser will use its default
font instead. For a complete overview, see www.cssfontstack.com
This group of buttons enables you to align a cell or cell range:
- horizontal alignment
- horizontal indentation
- vertical alignment
- merge a cell range
- wrap the cell content
This group of buttons enables you to set the visual format of numbers and
dates in a cell or cell range:
Please note that this applies ONLY to cells that contain numbers or dates,
and affects only the visual display, not the actual contents of the cell. Numbers, dates, and
periods will be displayed as per the user settings set in Manage - Profile.
The masked format will hide sensitive numbers and dates on screen (and thus in prints and PDF
exports), and they will only be visible while editing the cell.
In addition to the standard cell formats, you can also define your own custom format:
A custom format may consist of several code sections, separated by semicolons (;). Each code
section can include:
- [condition] to compare the cell value, within square brackets e.g. [>1000]
- [color] within square brackets e.g. [blue]
- format as a set of zeros or digits e.g. 0.0 or #.0
- text that should be displayed in this cell (within "" to ensure it will be interpreted
The code sections of the custom number format are set according to the rules below:
- Condition - to apply a format to a number if it meets a given condition, specify
a condition within square brackets. It should contain a comparison operator and a value:
- Color - to specify a color for a section of the format, type a color name (e.g. green,
red, blue, orange, black, violet or magenta) within square brackets. The color is optional,
but when used it must be the second item after the condition: [>1000][red];[>100][green]0.0
- Format - to apply a custom number format to the value of a cell, define the format as a
set of zeros or digits (e.g. 0.0) which are separated by decimal or thousand separators have
additional 0 after the separator to display insignificant zeros or # to ignore them:
- Text - if you want to add text, you can type it as it is. This is optional, but when
used it must be the last section: [>1000][red];[>100][green]0.0;[blue] "Small"
The number format is defined as follows:
0 - the digit placeholder to display insignificant zeros, if a number has fewer
digits than there are zeros in the format. For example, to display 4 as 4.0, use the format
# - the digit placeholder to display only significant numbers (not to display extra
zeros when a number has fewer digits than there are # symbols in the format).
? - works the same as 0 (zero), but adds a space for insignificant zeros on either side
of the decimal point so that decimal points are aligned in the column.
The decimal separator is a , (comma) and the thousands separator is a . (period)
This group of buttons enables you to edit a cell or cell range:
- sort a cell range up or down
- set a filter on a cell range
- create named cell ranges
- lock cells against editing
- set conditional cell formatting
- clear cells or cell styles
- insert new sheets
- undo or redo changes
You can insert cell objects into a model to create advanced functionality in a model.
You can choose from various types of objects:
- Icons and images - add icons
or external images into your model.
- Sparklines and charts - add sparklines
(mini-charts) into a cell, or add embedded charts to your model. Sparklines and charts
visualize a given cell range in your model. This cell range can be populated by a query, by
data entry, or by calculations.
- Links - add navigation links to other objects (e.g. for drilldown into
another report or model) or to external documentation.
- Cell editor - create editable cells
to make your model interactive (e.g. planning models).
- Row actions - buttons to insert
and delete rows (use this to build planning models).
- Comment - add comments to cell, which will popup when the user hovers
over the cell.
Once you click the toolbar icon, a popup window will guide you through the process of
defining the object.
This group of buttons enables you to set the format of a cell or cell range:
- insert, delete, hide, show, or resize rows
- insert, delete, hide, show, or resize columns
- hide or show gridlines
- hide or show headers
- freeze or unfreeze rows
- freeze or unfreeze columns
- show or hide cell formulas (instead of cell values)
- show or hide a page ruler (with column widths)
Recommended reading: Back to top