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

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

Align

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

Number

This group of buttons enables you to set the visual format of numbers and dates in a cell or cell range:

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

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:

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 series with # or 0 signs and optional decimal or thousand separator. A # sign will hide zero digits, a single 0 will show zero digits, and additional 0's will enforce insignificant zeros to be displayed. Examples: [>100]# or [>100]0.00 or [>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.
You can use the , (comma) and . (period) as placeholders for the thousands and decimal separator. This will be replaced by the user's own personal setting for number format.

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
  • show or hide cell formulas (instead of cell values)
  • show or hide a page ruler (with column widths)
Recommended reading:
Back to top | Models | Videos

We value your privacy

We use cookies to enhance your browsing experience and analyze our traffic.
By accepting, you consent to our use of cookies.

Accept Reject Cookie Policy