In this article:
Style | Align | Number | Edit | Insert | View

Toolbar

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.

screenshot define models toolbar

Style

This group of buttons enables you to style a cell or cell range:

  • font type
  • font size
  • style
  • cell and text color
  • cell borders
  • cell alignment
  • date and number format
  • conditional formats

Align

This group of buttons enables you to align a cell or cell range:

  • horizontal alignment
  • vertical alignment
  • merge a cell range
  • wrap the cell content

Number

This group of buttons enables you to set the format of a cell or cell range:

  • currency
  • number
  • integer
  • percent
  • date
  • period
  • text
  • custom

In addition to the standard cell formats, you can also define your own custom format:

screenshot define models formats

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 as text)

[>1000][red];[>100][green]0.0;[blue] "Small"

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: [>1000]
  • 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: [>100]0,000.00
  • 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 0.0.
# - 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)

Edit

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

Insert

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.

View

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
Recommended reading:
Back to top | Models | Videos