With the Excel add-in Invantive Control for Excel you can use Excel formulas to process Exact Online data into your own Excel reports. This applies both to Exact administrations of an accountant and of businesses. Excel is linked directly to Exact Online for all your administrations. The data is therefore retrieved in real time by the Excel functions from the Exact Online database and shown in Excel reports.
There are hundreds of standard formulas available via the menu, and you can also add your own formulas, even with SQL statements on the Exact database.
In addition, models can also be built using so-called “blocks”. An example of this is the Excel liquiditeitsbegroting voor Exact Online (cash flow met consolidatie).
The direct link between Excel and Exact Online is available in all Excel versions from 2013 onwards on Microsoft Windows. For Mac versions we recommend using Get My Report or Invantive Cloud, or running Parallels Desktop on the Mac (see also Itgenuty347 Deze versie 2010 van Microsoft Office wordt niet ondersteund. Gelieve te upgraden naar Microsoft Office 2013 of nieuwer).
Installing the Excel add-in
Follow these steps to install the Excel add-in:
- First check that you have an up-to-date version of Microsoft Excel. This should be Excel 2013 or newer, although it also works with Excel 2010. It doesn’t matter whether you have a 32-bit or 64-bit version of Excel.
- You install the add-in from the download.
- Then close Microsoft Excel completely and, if in doubt, check in Task Manager that no EXCEL.EXE is still running.
- Start Microsoft Excel again afterwards. The add-ins will then be loaded, and you’ll briefly see “Invantive Control” flash by on the Excel “splash screen”.
- The first time, you’ll need to fill in your personal details. These are also used if you request online assistance via the button on the ribbon, but no one happens to be available at that moment.
- When asked for a licence key, choose “Try all features”, after which you can try it out for free and without obligation for 180 days.
- During this trial period you can also use the Exact Online Power BI connector.
The next time, you won’t need to fill in anything. After starting Excel, it may take up to 20 seconds before the add-in becomes visible on the ribbon (menu at the top) as two new ribbons in Excel: “Invantive Control” and “Modeller”. You can already use Excel in the meantime.
How do you create Exact Online reports with the Excel add-in?
Most users of the Excel add-in for Exact Online only use the standard Invantive Excel formulas for Exact Online in their reports. SQL usage is less common and is only available to accountants with an accountancy subscription for large firms (1,000+ administrations).
You can retrieve data from one or more Exact administrations at the same time and process it in a consolidated way in your reports. Even within a single cell, figures from multiple administrations can be combined.
Invantive Control is standard software and comparable to the Exact Globe Excel add-in. The Invantive Excel add-in adds extra capabilities compared to the Exact Globe Excel add-in. Unlike the add-in for Exact Globe, you can, for example, also create queries in SQL, upload data into your administration(s), and exchange data with over 70 other data sources such as AFAS Profit or several Visma software products.
In this topic I’ll limit myself to the Exact Online Excel functions. There are many more possibilities, and the Invantive add-ins offer sufficient capabilities even for multi-billion-euro companies.
The easiest way to start is with an Excel report that already contains figures from Exact Online. Make a copy of this and open the Excel file. First, we’ll connect to the accounts from Excel:
- Click on “Sign in” in the “Invantive Control” menu.
- Open the “Exact Online” group and choose the country.
- Enter your login code and password, the same ones you use in the browser.
- Click on “Connect”:
- Enter the verification code.
- Allow the app to connect to a specific administration or all of them.
- Check the selected administration(s) in the “Invantive Control” menu and choose other administrations if needed.
- Enable the use of Exact Online formulas by switching the leftmost toggle in the “Modeller” ribbon so that it turns green. But…
- If an Excel file was already open, you will automatically be asked about this:
The software is now also connected to one or more administrations. Next, we’ll replace the figures with Excel formulas.
Every Exact Online formula has the administration as its first argument, named “DivisionCode”. You don’t need to specify this if you’ve only selected 1 administration; the software will then automatically use the selected administration.
Otherwise, you’ll need to provide a value. However, this value is NOT the administration number (often “1” or “2”), but the actual unique number that Exact Online uses internally. This is the so-called “DivisionCode”, and you’ll often also see it in your browser’s URL when you switch administrations.
This number determines where the data is retrieved from. You can find a list of these numbers, for example, in the administration selection menu. The division codes are shown in brackets after the administration name. In this image, for example, that’s 868043 for the administration “13 - Vereniging”:
For this example, I’ll assume you’ve selected exactly 1 administration and that no value needs to be specified in the function.
Now look for a cell containing figures from Exact.
In 80% of cases these are figures from the general ledger, and almost always balances. Invantive Control offers formulas that let you easily retrieve a balance sheet total up to a certain period, or a movement on a cost centre/cost carrier (analytical accounting). Despite the fact that hundreds of formulas are available, it turns out that the “most popular Exact Online Excel formulas” from the title are really only a handful.
Popular Formulas
Note the current result of the cell and, where feasible, replace the figures with:
I_EOL_GLACT_DESC: name of a general ledger account from the stated Exact division.I_EOL_BAL_YEAR_OPEN: opening balance of a general ledger account.I_EOL_BAL_PDE_CLOSE: realised balance at the end of a period for a general ledger account.I_EOL_BGT_PDER_LINE_AMOUNT: budget over a range of periods for a general ledger account.
All Exact Online Excel formulas can be found in the “Modeller” ribbon under Building Blocks:
There are also variants for use with a reporting scheme/classification structure. Here, the balance of multiple general ledger accounts that fall under a classification is added together. Usually this is done for all underlying general ledger accounts, but you can also limit the number of levels included. The most commonly used variant with a reporting scheme/classification structure is the period total across all general ledger accounts under the classification code. An example:
=I_EOL_BAL_PDER_CLASSIFICATION(divisioncode;financialyear;1;4;;“01”)
gives the cumulative movements over periods 1 to 4 of classification “01” (which is usually the full balance sheet). Accountants using RGS can also use the RGS classification schemes.
In Belgium, for example, analytical accounting with cost centre and cost carrier is often used. The Excel formulas for variants with cost centre/cost carrier can be recognised by the abbreviations “CCE” and “CUT” directly after “EOL” in the Excel formula name, such as I_EOL_CUT_CCE_BAL_PDE_CLOSE. Here, “CCE” stands for “cost centre” and “CUT” for “cost carrier”.
For complex consolidations, you can also use, for example, ranges of general ledger accounts as described in Nieuwe Excel consolidatiemogelijkheden voor Exact Online bedrijven en grootboekrekeningen.
The calculated figures are shown immediately. For the first formula, this may take a few seconds. After that, it’s still immediate — but truly immediate: lightning fast, even for thousands of cells.
You can repeat this process of replacing figures with formulas for all your reports. The end result might then look something like this:
It’s useful to work with named ranges. In this example, I’ve used the names “financialyear” and “divisioncode” in Excel:
If you ever need a flat copy of your Excel report, use the “Publish” button. The recipient will then only receive the figures, not the formulas.
There are countless other clever tricks to limit the number of reports, for example by creating reports that give the correct results for each administration or customer cluster. If you have any questions about this, please ask them on the forums. We’ll then try to help you further together. After reading this text, are you thinking “help”? Please leave a comment below.
Loading entries via formulas
It’s also possible to add entries, for example in the memorial or sales ledger, to one or more Exact Online administrations via Excel formulas. For this, read: Massaal Exact Online boekingen importeren direct vanuit Excel.






