Advanced Spreadsheets and Reporting
Cheatsheet for Excel Users
Works exactly like Excel
Your muscle memory carries over — these behave the way you expect.
| In Excel | In BC Spreadsheet |
|---|---|
=SUM(A1:A10) | The same — plus AVERAGE, COUNT, PRODUCT, with mixed arguments: =SUM(A1:A3, B5, 10) |
=XLOOKUP(B1, A2:A20, C2:C20) | The same, including the optional if-not-found fourth argument |
A1, B2:D13, $A$1, A$1 | The same — $ pins a reference through fill, copy and paste |
| Fill handle — drag the selection’s corner square | The same: formulas fill with relative references shifted per cell |
Ctrl+C / Ctrl+X / Ctrl+V | The same — the clipboard is tab-separated text, so copy and paste to and from Excel works |
F2 edits, Enter/Tab commit, Esc cancels | The same (Shift+F2 opens a larger source editor) |
Ctrl+B / Ctrl+I / Ctrl+U, Ctrl+Z / Ctrl+Y | The same — bold, italic, underline, undo, redo |
Ctrl+1 opens Format Cells | The same — number, percent, currency and date formats with a live preview |
Alt+Down opens an in-cell dropdown | The same, on slicer cells — a dropdown, a calendar, or the table’s lookup page, depending on the slicer kind (see below) |
| Conditional formatting | The same ideas — cell-value rules, data bars, color scales: Sheet menu → Conditional Formatting |
| Long text spills over empty cells to the right | The same |
| Right-click a row/column header to insert or delete | The same — formula references adjust automatically |
Formulas: AL expressions instead of Excel functions
A formula still starts with
= and references cells the same way — but what follows is a Business Central AL expression. Most Excel functions have a direct AL twin.| In Excel | In BC Spreadsheet |
|---|---|
=IF(A1>10, "High", "Low") | =A1>10 ? 'High' : 'Low' |
"Text in double quotes" | 'Text in single quotes' |
=A1 & " pcs" | =Format(A1) + ' pcs' — texts join with +; Format() turns a number into text |
=TODAY() / =NOW() | =Today / =CurrentDateTime |
=ROUND(A1, 2) | =Round(A1, 0.01) — a precision, not a digit count |
=ABS(A1), =A1^2 | =Abs(A1), =Power(A1, 2) |
=LEN(A1) | =StrLen(A1) |
=LEFT(A1, 3) / =MID(A1, 2, 3) | =CopyStr(A1, 1, 3) / =CopyStr(A1, 2, 3) |
=UPPER(A1) / =LOWER(A1) | =UpperCase(A1) / =LowerCase(A1) |
=EDATE(A1, 1) | =CalcDate('<1M>', A1) — date formulas like <1M>, <-1Y> |
=CONCAT(...) / =TEXTJOIN(...) | =StrSubstNo('%1 of %2', A1, B1) — %1, %2 are the arguments |
=7/2 gives 3.5 | =7/2 gives 4 — the left operand decides the type (integer math); write =7.0/2 to get 3.5 |
="5"=5 coerces the types | Mixed-type comparisons are #ERROR — make both sides the same type first, e.g. =Format(A1) = '5' |
Excel features that live elsewhere here
| In Excel | In BC Spreadsheet |
|---|---|
| PivotTable | A Pivot Table region: group any Business Central table by row and column fields, aggregate a value per intersection (sum, count, average, min, max), date buckets, subtotals and drill-down |
| Get Data from a database | New Region (toolbar): bind an area to any Business Central table, filtered and sorted — re-queried on every recalculation |
Fill handle date series / =EDATE runs | A Date Range region: a run of dates from a start date and an interval (<1M>, <1W>, <1Q>) — one date per cell, start/end pairs, or ready-made 2026-01-01..2026-01-31 filter texts that region filters and GL.GLSum can consume |
A column of =SUMIFS(...) per period | A FlowField region: one table field (G/L Account Net Change, Customer Balance (LCY)) calculated once per cell, its [B6] filter references shifting one row per cell like a filled-down formula — put it beside a Date Range region for a balance per period; [$C$2] pins a reference |
| Power Query | A Query region: a BC query object fills the area, read-only, with paging |
| Insert → Chart | A Chart region: column, bar, line, area, pie, donut or waterfall over a data range |
| Insert → Picture | An Image region: the picture a Business Central record holds (Item Picture, Company Information Picture, Employee Image) drawn over a rectangle — filter it with a [C2] reference to a ?FIELD slicer and the picture follows the pick |
| Data validation dropdown list | A slicer cell: ?LIST(Jan;Feb;Mar)->Feb |
| PivotTable slicers and timelines | ?FIELD(Customer, No.) opens the table’s own lookup page (add a display field to show it in the cell: ?FIELD(Customer, No., Name)), ?DIMENSION(AREA), ?DATE and ?RANGE cells — a pick recalculates the whole sheet |
VBA macros / LAMBDA | Sheet menu → Init Code: AL procedures callable from any cell, e.g. =Discount(B2, 10) |
| Scenario Manager | Sheet menu → Scenarios: named versions of the sheet’s cell contents |
=SUMIFS(...) over exported ledger data | =GL.GLSum('40000..49999', B1) — G/L sums straight from the database; GL.GLBudget for budgets |
| Worksheet tabs | Page regions mark out areas; Preview shows them as tabs |
F9 recalculates | Sheet → Recalculate in the toolbar. Table regions re-query live; the computed kinds (pivot, budget, financial report, query, report, SQL) keep their result until a setting or slicer changes — the ↻ beside the region badge fetches fresh data |
| Workbook templates | Sheet menu → Insert Template… and Save as Template… |
No Excel equivalent — worth knowing
| In Excel | In BC Spreadsheet |
|---|---|
| — | Ctrl+click a region cell drills down to the record or ledger entries behind it |
| — | Allow Table Editing writes region-cell edits back to the record — validated like on a BC page |
| — | Sheet menu → Spread Total: distribute a total over the selected cells, evenly or by weights |
| — | A Budget region: an editable G/L budget matrix with actuals comparison and write-back |