Business Central, but inside a Spreadsheet (but inside BC!)

I have this idea, maybe more of a very loose concept, where Business Central and a spreadsheet melt together into a single very utilitarian tool. I have done a few videos on the topic over the last many years. Here’s a new approach; this one seems pretty cool. Check out the video:

https://youtu.be/oIqK8OISioQ

In this video, Erik explores a fascinating experimental concept: a spreadsheet control built from the ground up inside Business Central, where every cell formula is actually AL code. Inspired by a conversation with Jim Holcroft (of Jet Reports fame) about how users fundamentally just want to use Excel, Erik demonstrates a prototype that blends the low-friction appeal of spreadsheets with the power of Business Central’s data engine.

The Excel Problem in Business Central

Erik opens by reflecting on a conversation with Jim Holcroft, who made the case that given the choice, most users would simply say, “I can just do it in Excel.” Despite the industry spending enormous effort building custom pages and avoiding spreadsheet-based workflows, the appeal of Excel remains undeniable.

The current state of getting Business Central data into Excel is functional but tedious — you need APIs, the data becomes static once exported, and you can typically only open one thing at a time. Erik has been wrestling with this problem for years, even experimenting in earlier videos with shoehorning the AL compiler into a control add-in. This video represents his latest attempt to find the sweet spot between spreadsheets and Business Central.

A Spreadsheet That Speaks AL

The key distinction of this prototype is that it’s not embedded Excel — it’s a custom-built spreadsheet control running inside Business Central. And the formulas aren’t Excel formulas. They’re AL code.

Erik demonstrates this by entering a formula in a cell:

  • =Today() — returns the current date, just as it would in AL
  • =CalcDate('-CM', Today()) — uses AL’s CalcDate function with a date formula to calculate the first day of the current month
  • =CalcDate('CM', Today()) — returns the last day of the current month
  • =CalcDate('-89D', Today()) — subtracts 89 days from today

Every cell is essentially a small AL program. You can even write custom procedures in an “init code” section — Erik writes a simple procedure Test() : Text that returns “Hello World”, then references it in a cell with =Test() + Test(), producing “Hello WorldHello World”.

Regions: Bringing BC Data Into the Grid

The real power emerges with a feature called regions. A region binds a section of the spreadsheet to a Business Central table. Erik creates a region called “Customer” anchored at cell A3, pointed at table 18 (Customer), and selects fields: Number, Name, Address, City, Credit Limit, Balance, Sales, Profit, and Payments.

With a table view filter of ..50000, the spreadsheet instantly populates with customer data — effectively creating an ad-hoc report in seconds. Erik then adds a =SUM() across the data range, and it works both vertically and horizontally (the horizontal case being something he’d coded for but never actually tested until this moment).

Building a Customer Card in a Spreadsheet

Erik takes it further by building what amounts to a customer lookup card. He creates a region displayed as columns (rather than rows) showing a single customer, with the customer number driven by a cell reference — typing a customer number in cell B1 dynamically updates all the data below.

He then adds a second region for Customer Ledger Entries (table 21), also filtered by the customer number in B1, with fields like Posting Date, Document Type, Document Number, Description, Amount, Remaining Amount, and Open. Changing the customer number refreshes both the customer details and their ledger entries instantly.

Regions as AL Variables

The regions aren’t just display mechanisms — they become global record variables in the spreadsheet’s AL address space. Erik demonstrates:

  • =Ledger.Count — returns the number of ledger entries for the current customer (150 for one customer, 113 for another)
  • =Ledger.TableSum('Remaining Amount') — calculates the sum of remaining amounts within the current filter, matching what you’d get from the data displayed in the grid

This is AL syntax that Erik has extended specifically for this context, allowing aggregate functions to be called directly on the region’s underlying record variable.

The Source Code: An Earlier Experiment

The repository includes source from an earlier iteration of this concept — an “Excel Viewer” page that uses Business Central’s built-in Excel Buffer table to display uploaded Excel files in a matrix-style list page:

page 50410 "Excel Viewer"
{
    PageType = List;
    Caption = 'Excel Viewer';
    SourceTable = Integer;
    SourceTableView = where(Number = filter(1 ..));
    Editable = false;
    layout
    {
        area(Content)
        {
            repeater(Rep)
            {
                field(Number; Rec.Number)
                {
                    Caption = ' ';
                    ApplicationArea = all;
                }
                field(Col1; GetExcelCell(Rec.Number, LeftMostColumn))
                {
                    CaptionClass = '3,' + GetColumnHeading(LeftMostColumn);
                    ApplicationArea = all;
                }
                field(Col2; GetExcelCell(Rec.Number, LeftMostColumn + 1))
                {
                    CaptionClass = '3,' + GetColumnHeading(LeftMostColumn + 1);
                    ApplicationArea = all;
                }
                field(Col3; GetExcelCell(Rec.Number, LeftMostColumn + 2))
                {
                    CaptionClass = '3,' + GetColumnHeading(LeftMostColumn + 2);
                    ApplicationArea = all;
                }
                field(Col4; GetExcelCell(Rec.Number, LeftMostColumn + 3))
                {
                    CaptionClass = '3,' + GetColumnHeading(LeftMostColumn + 3);
                    ApplicationArea = all;
                }
                field(Col5; GetExcelCell(Rec.Number, LeftMostColumn + 4))
                {
                    CaptionClass = '3,' + GetColumnHeading(LeftMostColumn + 4);
                    ApplicationArea = all;
                }
            }
        }
    }
    // ...
}

This earlier approach uses the Integer virtual table as the row source and renders five columns at a time, with scroll-left and scroll-right actions to navigate horizontally. The column headings are dynamically generated using a procedure that converts column numbers to Excel-style letter headings (A, B, C, … Z, AA, AB, etc.):

local procedure GetColumnHeading(columnNumber: Integer): Text
var
    dividend: Integer;
    columnName: Text;
    modulo: Integer;
    c: Char;
begin
    dividend := columnNumber;
    while (dividend > 0) do begin
        modulo := (dividend - 1) mod 26;
        c := 65 + modulo;
        columnName := format(c) + columnName;
        dividend := (dividend - modulo) DIV 26;
    end;
    exit(columnName);
end;

Cell values are retrieved from the temporary Excel Buffer record:

local procedure GetExcelCell(row: Integer; column: Integer): Text
begin
    if ExcelBuffer.Get(row, column) then
        exit(ExcelBuffer."Cell Value as Text");
end;

While this earlier version was essentially a read-only Excel file viewer, the new prototype shown in the video goes dramatically further — it’s an interactive spreadsheet where the formulas are AL expressions and the data comes live from Business Central tables.

The Appeal: Low Friction

Erik articulates what makes this concept compelling: it’s the same thing that makes Excel so beloved — low friction. You just open it and start doing things. No need to check if there’s an API endpoint. No need to build a custom page. No dead data. Everything is live, integrated, and expressed in the language Business Central already speaks.

The ad-hoc nature is the key strength: “I want to investigate something that happened, so let me grab this data and that data and change it around.” That exploratory workflow is exactly what Excel excels at, and this prototype brings it natively into Business Central.

Bugs and Rough Edges

Erik doesn’t shy away from showing the prototype’s current limitations. There’s no IntelliSense or tooltips for the AL formulas yet. You can’t insert rows or columns (though that’s planned). And during the demo, a =SUM() call across a range triggered a recursive function error — an “insufficient memory” message caused by what Erik quickly identified as a bug. These are expected growing pains for an experimental project.

Conclusion

Erik describes this video as something of a “therapy session” — an honest exploration of whether this idea is genuinely useful or just a fascinating technical exercise. He admits he doesn’t yet see the killer use case, the clearly defined scenario where this tool makes users twice as productive. But he also can’t bring himself to kill the idea, because the intersection of spreadsheet ergonomics and live Business Central data feels like it has real potential. The low friction of being able to pull in any table, filter it with cell references, write AL formulas, and see results instantly — all without leaving Business Central — is something that current Excel integration simply can’t match. This is an idea Erik plans to keep scratching at.

Picture of hougaard

hougaard