In Beta
This feature is in beta. Contact your CSM to learn more.
The R365.GL formula pulls general ledger account balances from R365 into Excel. It builds profit and loss statements, tracks account activity by period, and compares results across locations inside a workbook the user controls, instead of exporting a canned report each time the user needs updated numbers. Because it is a formula, it can be combined with other Excel calculations, placed into an existing template, or refreshed for the whole workbook with one click. See Install Excel Bridge before writing a formula for the first time.
Enter a Formula
Click a cell and type
=R365.GL(.
Excel lists R365.GL and R365.BUD under User Defined Functions while typing.Review the argument hints.
Excel showsR365.GL(accounts, [locations], [startDate], [endDate], [approvedOnly]). Arguments in brackets are optional.Complete the formula and press Enter. The cell shows an hourglass.
In the task pane, click Refresh Sheet or Refresh Workbook.
The cell fills in, and Sheet/Workbook last updated shows the refresh time.

The task pane must stay open and signed in while working. Formulas get data only after the user clicks Refresh Sheet or Refresh Workbook; the task pane also adds quotes around unquoted dash numbers and dates automatically.
What R365.GL Returns
R365.GL returns the net activity (debits minus credits) for the accounts, locations, and date range specified, as a number. Data can be up to about 15 minutes behind R365; the task pane shows Sheet / Workbook last updated after each refresh. A formula returns data only for the accounts and locations the user's role can access.
Note About Retained Earnings Balances
R365.GLdoes not include the year-end net income flip, so results will not match My Reports or the Balance Sheet for Retained Earnings accounts. Use My Reports as the source of truth for Retained Earnings.To tie out Retained Earnings in Excel:
Use
R365.GLon the Retained Earnings account.Use
R365.GLacross all P&L accounts for all prior fiscal years through the last year-end.Sum the results.
R365.GLsupports up to 350 accounts per formula. If your chart of accounts has a large P&L range, split it across multipleR365.GLformulas and sum the totals.
Syntax
=R365.GL(accounts, [locations], [startDate], [endDate], [approvedOnly])
Argument | Required | What to enter | Examples |
|---|---|---|---|
accounts | Yes | One account number, a comma-separated list, or a range using a colon. Up to 350 accounts per formula. |
|
locations | No | One location number, a list, or a range. Leave blank for all locations the role can access. |
|
startDate | No | A date. Leave blank for no start limit. |
|
endDate | No | A date. Leave blank for no end limit. |
|
approvedOnly | No | TRUE to include approved transactions only. FALSE or blank includes all. |
|
Important: Argument Order Is Fixed
Accounts first, then locations, start date, end date, and approved only.
R365 Tip
To see a description of each argument while building a formula, click Insert Function to open the Function Arguments dialog (Windows) or Formula Builder (Mac). Arguments can be entered on this dialog as well as directly in the cell.
Quotation Marks
A single value does not require quotes: R365.GL(5000, 1, ...) works as written. A list or range requires quotes, such as "5000:5999" or "1,2,3". Without quotes, Excel reads 5000:5999 as a cell reference and 1,2,3 as separate arguments.
A number with a dash or leading zeros, such as 1900-002 or 001, needs quotes so R365 matches the number exactly. Without quotes, Excel drops leading zeros (1900-002 becomes 1900-2). The task pane adds quotes around what Excel passes, so R365 matches 1900-2 to 1900-002. This works only while the task pane is open and signed in.
A zero-padded location number with no dash, such as 001, needs quotes. Without quotes, Excel passes 1, which does not match 001.
When two accounts could both match an unquoted number (for example,
1900-002and1900-02both match1900-2), R365 returns a "no matching GL Codes" error. Quoting the full number resolves the ambiguity.
Enter Dates
A date can be DATE(2026,1,1), a cell that contains a date, a quoted string such as "01/01/2026" or "01-01-2026" (mm/dd/yyyy or mm-dd-yyyy), or a typed value such as 6/1/2026. The task pane adds quotes automatically. Typed dates require a current version of Excel. They work only when the formula starts with =R365.GL( directly, not nested inside another function such as IF.
With no dates, a formula returns all dates. With only an end date, it returns everything up to that date. With only a start date, it returns that single day, not everything from the start date onward. A range needs both dates. A start date cannot be after the end date.
Examples
Goal | Formula |
|---|---|
One account, one location, one month, approved only |
|
Account range, all locations, a quarter |
|
Several accounts and locations, dates from cells |
|
Typed dates |
|
Limits
A formula can cover up to 350 accounts by default. A formula that requests more than that returns "Error: This formula requests more than 350 GL accounts. Split it into several formulas."
Each refresh can calculate up to 1,000 Excel Bridge formulas (R365.GL and R365.BUD combined). Refresh Sheet counts the formulas on the sheet in view, and Refresh Workbook counts the formulas on every sheet. When a refresh is over the limit, the task pane shows a message with the number of formulas. The message suggests moving some to another sheet or workbook. If your organization needs a higher limit, reach out to R365 Support.
Cell Errors
When a formula cannot return a number, the cell shows an error message as text. SUM skips these cells.
Formula Problems
Message | What to do |
|---|---|
Error: Account numbers are required | Enter at least one account in the first argument. |
Error: Invalid account range notation | Use the form |
Error: Account range start must not be greater than end | Put the lower account number first. |
Error: Invalid location range notation | Use the form |
Error: Location range start must not be greater than end | Put the lower location number first. |
Error: Start date must not be after end date | Swap the dates. |
Error: Start date is not a valid date. Use "mm/dd/yyyy" or "mm-dd-yyyy". | Use |
Error: Invalid start date / Error: Invalid end date | A quoted date was not read as a valid date when the formula refreshed. Use |
Error: This formula requests more than 350 GL accounts. Split it into several formulas. | Split the formula into smaller account sets. |
No Matching Data
Message | What to do |
|---|---|
Error: The formula includes a value/range with no matching GL Codes | Check each account number and range. Every value must exist, and the user's role must be able to view it. A range must contain at least one accessible account. |
Error: The formula includes a value/range with no matching locations | Check each location the same way. |
Error: No account data available for the specified account number(s). Verify the account numbers and try again. | Check the account numbers. |
Error: No location data available for the specified location number(s). Verify the location numbers and try again. | Check the location numbers. |
These messages appear the same way when an account is missing and when the user's role cannot view it.
See Excel Bridge Troubleshooting for errors that are not specific to this formula, such as task pane messages and connection errors.
FAQ
Will two people see the same numbers if they open the same workbook?
No. R365.GL returns data only for the accounts and locations that the current user's role can access. Two people with different role permissions can see different numbers from the same formula. This also means the numbers in a shared workbook can change depending on who last clicked Refresh Sheet or Refresh Workbook.
Why do I need to quote account or location numbers sometimes but not always?
A single account, location, or date does not need quotes, but a list or a range does. Without quotes, Excel reads a range like 5000:5999 as a cell reference instead of text. A number with a dash or a leading zero, such as 1900-002 or 001, also needs quotes. The quotes make R365 match the number exactly instead of dropping characters.
My formula cell is stuck on the hourglass and never fills in. What is wrong?
A cell that has never had a value shows an hourglass until a refresh. It does not update on its own. If the cell still shows the hourglass after clicking Refresh Sheet or Refresh Workbook, the issue is usually a task pane connection or sign-in problem rather than the formula itself. See Excel Bridge Troubleshooting for connection and sign-in errors.
Can I use R365.GL to pull budgeted amounts instead of actuals?
No. R365.GL returns general ledger account activity. R365.BUD returns budgeted amounts for an account, location, and date range. See Write Budget Formulas for the budget formula's syntax.
