Spreadsheet

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.

Enter data

To enter data into SpreadSheet, click an empty cell and type a text or a number in a cell and then press Enter or Tab keys.

Select a cell range

You can select a range of multiple cells in one of 2 ways:

• By clicking the first cell and dragging the cursor to the last cell
• By clicking the first cell, then pressing Shift and clicking the last cell

To select all cells, click the triangle in the top left corner.

Keyboard shortcuts

As well as via the toolbar, commonly used functions can also be accessed via shortcut key combinations:

Windows

Enter	Open editor in a cell.
Esc	Close editor in a cell.
Arrow keys	Navigate across cells.
Tab	Move focus to the next cell in a sheet.
Shift+Tab	Move focus to the previous cell in a sheet.
Shift+Arrow key	Extend cells selection by on cell.
Ctrl+Shift+Arrow key	Extend cells selection to the last nonempty cell in the same column/row. If the closest cell is empty, selection is extended to the next nonempty cell.
Ctrl+C (Command+C on Mac)	Copy the content of a cell.
Ctrl+V (Command+V on Mac)	Paste the content to a cell.
Ctrl+X (Command+X on Mac)	Cuts the content of a cell.
Ctrl+A (Command+A on Mac)	Select all cells in a sheet.
Ctrl+Z (Command+Z on Mac)	Undo an action.
Ctrl+Y (Command+Y on Mac)	Redo an action.
Ctrl+B (Command+B on Mac)	Make the selected text bold.
Ctrl+I (Command+I on Mac)	Make the selected text italic.
Ctrl+U (Command+U on Mac)	Make the selected text underlined.
Ctrl+P (Command+P on Mac)	Call the print dialog window.
Delete	Remove the content of a cell, or the character to the right of the insertion point (in the editing mode).
Backspace	Remove the content of a cell, or the character to the left of the insertion point (in the editing mode).

Mac OS

Command+C	Copy the content of a cell.
Command+V	Paste the content of a cell.
Command+Z	Undo an action.
Command+Shift+Z	Redo an action.
Command+A	Select all cells in a sheet.
Command+B	Make the selected text bold.
Command+I	Make the selected text italic.
Command+U	Make the selected text underlined.
Command+Shift+X	Make the selected text strikethrough.
Command+P	Call the print dialog window.
Command+`	Show formulas in cells instead of values.

Undo/redo actions

To undo recent changes, click the Undo button. To reapply changes once again, click the Redo button.

Copy, cut, and paste cells

Copying cells

To copy a cell, you should take the following steps:

1. Select a cell or a range of cells you want to copy.
2. Right-click and select the Copy option in the context menu.

You can also copy the cell(s) via the Ctrl+C (Windows) or Command+C (Mac OS) shortcut keys.

Cutting cells

To cut a cell, you should take the following steps:

1. Select a cell or a range of cells you want to cut.
2. Right-click and select the Cut option in the context menu.

You can also cut a cell via the Ctrl+X (Windows) or Command+X (Mac OS) shortcut keys.

Pasting cells

To paste the copied/cut cell(s), you should take the following steps:

1. Select a cell or a range of cells you want to paste into.
2. Right-click and select the Paste option in the context menu.

You can also paste a cell via the Ctrl+V (Windows) or Command+V (Mac OS) shortcut keys. When you copy or move cell(s), you also copy or move styles, formulas, cell editors, and conditional formats.

Special paste

The widget allows you to perform a special paste, in which case you can paste only selected elements into the cell:

• values - paste only the values from the copied cell(s)
• formulas - paste only formulas, without values
• styles - paste only cell styles
• conditional formats - paste only the conditional formats.

To perform the special paste, you should take the following steps:

1. Copy a cell or a range of cells.
2. Select the cell you want to paste into.
3. Right-click and select the Special paste option in the context menu.
4. Select the paste option you need.

Sort data

You can sort data in a selected range of cells in both ascending and descending direction. There are two ways to do that:

• Select a range of cells that contain data to be sorted, and click one of the 2 sort buttons in the Edit section of the toolbar.
• Select a range of cells that contain data to be sorted, right-click, select Sort from the context menu, followed by selecting either Sort A to Z or Sort Z to A.

Add filters

You can add filters to a range of cells or a column. When selecting an option in the filter, the row data will be filtered according to the value of the selected option.

To add a filter, you should take the following steps:

• Select a cell, a range of cells, or an entire column.
• Choose one of the following options: Click the Create filter button in the Edit section of the toolbar, or right-click and select the Create filter option in the context menu.

Once added, a filter icon is shown above each column in the selected cell range. Click on an icon to filter data in that column.

To remove filters, you should select the cell range and click the Clear button in the Edit section of the toolbar, and then click Clear editors and filters.

Lock cells

You can lock a cell to prevent editing its content. Locked cells will be marked with an orange lock icon in the bottom right corner of the cell. To lock/unlock cells, you should select a cell or a range of cells that you want to lock, and click the Lock button in the Edit section of the toolbar.

Please note that cell locking is only relevant in Define models, as cells are automatically locked in Run models.

Formulas

XLReporting supports complex formulas and expressions and enables you to calculate and convert data with the help of Excel-like functions. The following elements are allowed in formulas:

• numbers
• basic mathematical operators, which are: +, -, /, *
• comparison operators: <, >, <=, >=, <>, =
• the & operator for connecting two or more text strings into a single text
• cell references
• Excel-like functions

To enter a formula into a cell, you should type the = sign in a cell, followed by a combination of numbers and math operators:

• + to add numbers
• - to subtract numbers
• * to multiply numbers
• / to divide numbers
• & to concatenate texts

Press the Enter button to get the result of your formula.
You can also include 280 Excel-like functions into your formulas.

Example:
= 8 + 5
=SUM(C2:D2)

In order to check your formulas, you can show the formulas in cells instead of their values. To do so, click on the Hide/show formulas button in the View section of the toolbar.