How to Report GL Account Groups in Excel

You can set up Excel formulas that easily report defined groups of GL accounts, product codes, sales office codes, etc.—and alert you to reporting errors. Here's how.

Charley Kyd

2688

The formulas in this Income Statement use named Account Groups to sum any number of accounts in one formula.

Believe it or not, this income statement is quite sophisticated. It’s not nearly as simple-minded as it looks.

In fact, this income statement illustrates a solution to a problem you’ve probably experienced when you work with accounting and other structured data in Excel Tables.

The problem is this: How can you set up formulas that easily report values for defined groups of GL accounts?

Every number in this Income Statement does exactly that.

If your version of Excel supports Dynamic Arrays—that is, if it includes the SORT worksheet function—you’ll use the FILTER and XLOOKUP functions to create this report.

Otherwise, if you still must enter arrays using Ctrl + Shift + Enter (the CSE version of Excel) you’ll have to set up your groups manually. But in either case, you’ll make extensive use of arrays.

The formulas in this income statement return data directly from the Table of data on the left. To illustrate, the value of 300 in cell F3 is the sum of cells C3 and C4 in the Table. Because the natural sign of these accounts is a Credit, the formula in cell F3 also changes the sign to a positive number for this report. — The formulas in this income statement return data directly from the Table of data on the left. To illustrate, the value of 300 in cell F3 is the sum of cells C3 and C4 in the Table. Because the natural sign of these accounts is a Credit—as specified in cell B3 in the gray table—the formula in cell F3 also changes the sign to a positive number for this report.

Let’s be clear about where we’re going. In the Excel Table above, accounts A4001 and A4002 are Sales accounts, and accounts A4101 and A4102 are Cost of Goods Sold (COGS) accounts. And now, we’re going to write formulas like the one used in cell F5 of the Income Statement, which returns the Gross Profit for the specified month.

Similarly, if your Table were to contain data by Sales Office, you could use the same strategy to write a formula that would summarize your data by Region, doing so by using the current list of which Sales Offices belong to which Region.

In this article, I’m going to explain the general strategy for setting up a report like this. Then, in the next three articles, I’ll explain the methods you can use to implement each step—depending on whether your version of Excel supports Dynamic Arrays or still requires you to enter arrays using Control + Shift + Enter.

(Generally speaking, if you use Excel 365 or if you have a version of Excel later than Excel 2019, you probably have a Dynamic Array (DA) version of Excel.)

The Data Table

This is the Table of data used for the Income Statement in Excel. In actual practice, it would have account balances or changes for all accounts, and often, for many years. — This is the Table of data used for the Income Statement. In actual practice, it would have account balances or changes for all accounts, and often, for many years.

This Excel Table contains changes to a selection of General Ledger accounts for every month. Alternately, the Table could contain year-to-date data. The formulas don’t care.

By my simple Chart of Accounts, accounts that begin with “A40” are sales accounts and accounts that begin with “A41” are cost of goods sold (COGS) accounts. I begin each account number with “A”—for GL accounts—for two reasons.

First, I find that it’s always easier to work with textual versions of ID codes like GL account numbers, rather than numeric codes. And second, there may be other similar codes used for other purposes. So “A” tells us that these codes belong to the Accounting Department’s Chart of Accounts.

Notice in this Table that the amounts for the sales accounts have negative values. This is because sales are Credit accounts, which, by convention, have a negative sign. And therefore, if this Table had the changes for all accounts in the General Ledger, the grand total of the Amt column always would equal zero. This is because, of course, total Debits (positive numbers) always equal total Credits (negative numbers).

The Chart of Accounts

An Excel Table with a Chart of Accounts that contains the data needed for the Income Statement report.

This Table contains the data we’ll need from the Chart of Accounts.

Here, the Acct and Name columns are about what you would expect.

The Sign column is necessary in this Table. It contains values of 1 for accounts with a natural Debit balance, and it contains values of -1 for accounts with a natural Credit balance. For clarity, I used a number format that I’ll explain in the next article in this series to display those values as Dr or Cr.

The Groups of Accounts

This is the key table that defines each group of accounts. It contains five named lists of account numbers, from ag.Sales through ag.Profit.

Each of the "ag" (Account Group) labels in column E of this Excel worksheet is the name assigned to the list of account numbers to the right of the labels. — Each of the “ag” (Account Group) labels in column E of this Excel worksheet is the name assigned to the list of account numbers to the right of the labels.

Each name in column E is a range name that defines the list of accounts to its right. For example, the name ag.OpEx was assigned to the range F6:H6.

By starting the name of each group with “ag” we group them together alphabetically in Excel’s Name Manager, which makes them easier to manage and to use with IntelliSense.

The period that follows “ag” makes the names more readable. And a period is the only special character that we can use in range names. That is, when you create range names, Excel essentially treats a period as part of the alphabet.

For fast performance, it’s important that the name of every group be only as long as the number of accounts its list contains. Otherwise, if “ag” names include empty cells, we’ll force our SUMIFS functions to waste calculation time to sum the values for many “accounts” with the account number equal to a null string, with each such sum returning zero.

We must assign the range names manually, rather than using dynamic range names. That’s because in the Income Statement shown below, we’d like our formulas in columns E and F to use Excel’s INDIRECT function to reference the range names listed in column A of that figure. But, unfortunately, the INDIRECT function doesn’t work with dynamic names.

However, if you use a version of Excel with dynamic arrays, and if your Chart of Accounts has a logical number pattern, you can use formulas to assemble most of the lists for you, as you’ll learn in Part 2 of this series.

(I’ve set up a request in Excel User Voice for Microsoft to update the INDIRECT function to allow it to reference dynamic range names. Please click the link and support this request. And add any additional reasons you think Microsoft should make this change.)

The Income Statement

Formulas in each row of the Excel Income Statement reference the name of its group account in column A.

In this figure, the only values entered directly into cells are in rows 1 and 2 and in column A. All other values you see here are returned by formulas.

And each row of formulas references the Account Group for its row in column A.

The Chart of Accounts includes the Account Groups, which work about like normal accounts in Excel formulas.

We can return the labels and the natural signs of each account group—which is defined by a list of accounts, remember—because we add those group names to our chart of accounts Table in the workbook, as shown here.

Notice in the Income Statement above that I’ve set up groups for the subtotal of Gross Profit and for Total Profit—even though they’re not actually needed for the report. I’ve done this for two reasons.

First, it’s because for other kinds of calculations, like the calculation of a Gross Profit Margin, we can divide a directly calculated Gross Profit value by a directly calculated Sales value, without maintaining side calculations.

Second, those names will help us to check for errors, as you’ll learn in Part 4 of this series.

Checking for Errors

In a report like this, it’s always best practice to check for errors automatically.

One type of error could be that your report isn’t internally consistent. That typically will occur when there’s a transaction against an account that hasn’t been added to a group.

That’s why it’s good practice for calculations like Gross Profit and Total Profit to use formulas like this in column C:

C5: =F3-F4<>F5

That is, we have an error if the value for Sales minus COGS doesn’t equal the value we directly calculated for Gross Profit in cell F5.

By using this approach, you’re continually testing the internal consistency of your groups.

You’ll probably think of other types of possible errors. As you do so, it’s best practice to maintain an Error Summary Table like this one.

The Error Summary Table summarizes all error tests in the worksheet. And the IsError cell returns TRUE if any error is found.

Here, cell F3 performs two tasks. First, if you enter a 1 in this cell, IsError will return TRUE. this allows you to check that your income statement properly alerts you to errors. And second, by having a constant in that cell, you keep Excel from trying to make all the formulas in column F the same. Those formulas check for errors that we’ll cover in Part 4 of this series.

Cell B3 above is named IsError, with a formula that returns TRUE if any value in the Summary Table’s IsErr column is TRUE.

If an error is found, the Income Statement title changes to "ERROR FOUND" and conditional formatting turns the background of the report a bright red. — If an error is found, the Income Statement title changes to “ERROR FOUND” and conditional formatting turns the background of the report a bright red.

And then, we use a formula for the Income Statement label, along with conditional formatting, to let us know when we have a problem, as shown here.

Unlike many Excel errors, we won’t have a problem identifying the error because the Error Summary Table will tell us the exact calculation that discovered each error.

How to Implement the Strategy

We now need to implement the strategy. The remaining three articles in this series—which I’ll post weekly—will show you how to do that.

Here are the names of all four articles in this series, with their links if they’ve been posted:

Part 1: How to Report GL Account Groups in Excel
Part 2: How to Define General Ledger Account Groups in Excel
Part 3: How to Use Array Formulas to Report Groups of Accounts
Part 4: How to Set Up an Automatic Error-Checking System in Excel Reports

Do you know of other Excel users who could find this article useful? Now would be a great time to send them a link to it or to post it to a social group.

How to Set Up an Automatic Error-Checking System in Excel Reports

How to Define General Ledger Account Groups in Excel