How to Define General Ledger Account Groups in Excel

In Part 1 of this series, How to Report GL Account Groups in Excel, we explored the strategy for creating financial reports that use GL Account Groups of detailed data, as shown by this Income Statement.

Now, in Part 2, we’ll look at the Excel methods for two alternate ways to set up the groups on which this report relies.

To follow along, you can download both workbooks at this link. Here are the names and short descriptions of the two workbooks with the alternate methods:

GroupAccts-DA.xlsx—for versions of Excel that support Dynamic Arrays.

GroupAccts-CSE.xlsx—for versions of Excel that require array formulas to be entered using Ctrl + Shift + Enter.

Before we get to the Groups, however, let’s take a quick look at two supporting Tables.

Monthly Account Changes

This Table of data in the Data sheet is the same in both GroupAccts workbooks and uses no formulas. I named the Table Data.

Chart of Accounts

The table below also is the same in both GroupAccts workbooks and uses no formulas. More generally, this is a Table with metadata—data about data. I’ve therefore named the Table Meta and you’ll find it in the Meta sheet.

I’ve hidden rows between the top and bottom of this figure to show you that this Table contains both normal accounts and named Account Groups. As you’ll see when we get to Part 3, the formulas that return data for accounts and Account Groups can be the same.

The Sign column with its Debit and Credit contents actually contains a column of 1 or -1 values and uses this custom number format:
” Dr”;” Cr”;

Custom number formats consist of three parts, one each for positive, negative, and zero numbers. So this format tells Excel to return ” Dr” for positive numbers, ” Cr” for negative numbers, and nothing at all for zeros.

Notice in this figure that I didn’t apply a similar format to column B. This is because if you accidentally enter a number like -5 for the ag.Sales sign in the Meta (Chart of Accounts) Table, the formulas in row 3 in this figure will multiply your sales by -5.

Therefore, it’s a good idea to see the actual entries here in column B. And also, Part 4 of this series explains how to set up a clever formula in the Error Summary Table to check for this issue.

I also should point out the best practice of using a control area as I did in the range A1:C8 of the figure above. By doing so, we make it very clear which accounts, account groups, and signs our formulas are using to return their data. We also, in column C, give ourselves the ability to check for errors, as you’ll see in Part 4 of this series.

Additionally, this practice allows us to set up our formulas much more quickly. Here, for example, I copied the formula in cell F3 to the range F3:G7.

Each Groups worksheet in the two GroupAccts workbooks has a slightly different design. Let’s start with the AcctGrp-CSE.xlsx workbook.

The AcctGrp-CSE.xlsx Workbook

The Groups sheet in the AcctGrp-CSE.xlsx workbook has a simple design:

I call this type of table a gray-cell table, for obvious reasons. We use this type when we can’t use an Excel Table. Here, using an Excel Table wouldn’t be practical because the number of columns in the table would need to be changed each time we add or remove accounts in the table. And besides, Tables are designed for vertical lists, not horizontal ones.

In general, the major rules for gray-cell tables are, first, you always insert new data between the gray rows. And second, when you set up formulas to read columns of data in this table, you anchor your formulas in the gray rows. That way, your formulas will adjust reliably and automatically as you change the number of rows in your table.

Specifically for this table, there should be no empty rows between the gray rows. And also, the accounts must be in a continuous list in each row.

As you look at the lists of accounts in each row of this table, you might notice that the ag.GrsPrft group is defined using the four accounts that we used to define the ag.Sales and ag.COGS groups—each of which have two accounts.

The reason we can’t do that is that Excel can’t (yet?) define an array of arrays. That is, although we can define the arrays {“A4001″,”A4002”} and {“A4101″,”A4102”}, we can’t yet define the array: {{“A4001″,”A4002”},{“A4101″,”A4102”}}

Therefore, I’ve set up a request in Excel.UserVoice to enhance arrays to support arrays of arrays. Please visit the link and add your approval to that request.

Here’s the only formula in this figure:

B3: =COUNTA($E$3:$E$9)

This formula returns the total number of group labels between the two gray border rows in column E. We need this number to support the conditional formatting formula that creates the gray rows starting in column F, which I’ll tell you about in a second.

To set up the accounts in this table, you copy and paste, or manually enter them. If you copy from a column of accounts, you can use the Paste Special dialog to paste the accounts in a row.

To do so, copy a column of accounts; select where you want the left-most cell in the row of accounts to be pasted; press Ctrl + Alt + V to launch the Paste Special dialog; press E to select Transpose; and then press Enter.

We now must assign the labels in column E as the names of ranges of accounts beginning in column F. The quickest way to do that is to first select the various ranges non-contiguously, as shown here.

To do that, select the first range with your mouse. Then hold down your Ctrl key and select each additional range with your mouse.

Alternately, after you select the first range, press Shift + F8 and then select the second range with your keyboard. Press Shift + F8 again and then select the third range, and so on.

And then, after your ranges have been selected, press Ctrl + Shift + F3 to launch the Insert Names dialog. Make sure that only Left column is checked. And then press Enter.

To test any of the range names you’ve just created, press the F5 key or Ctrl + G to launch the Go To dialog. Select the name you want to test in the Go To list in the GoTo dialog, and then press Enter or click OK. Excel should select the full list of accounts to the right of that name.

To set up the conditional formatting formula for the gray rows, select a cell in column F below the bottom gray border, and then temporarily enter this formula:

=COUNTA(OFFSET(F$3,1,0,NumRows))>0

The OFFSET function in this formula has this syntax:

=OFFSET( reference, rows, cols, [height], [width] )

Because you enter this formula in column F, and because there’s no dollar sign in front of the “F” in F$3, the formula returns a reference to the range that begins with the cell in row 3 of the current column, moves down 1 row, doesn’t change the column position, and is NumRows high. In the figure above, that is, it returns a reference to the range F4:F8.

And then the formula returns TRUE if the COUNTA of that range is greater than zero. That is, it returns TRUE if there are any accounts in the current column between the gray borders.

To set up the conditional formatting for the gray borders, first copy the temporary formula in column F from your formula bar to your clipboard. Make sure a cell in column F is still selected. And then, in the Home, Styles group, click Conditional Formatting, New Rule. In the New Formatting Rule Dialog, choose Use a formula to determine which cells to format.

Then, in the Format values where this formula is true edit box, paste your copied formula from your clipboard. And then click Format. In the Border tab of the Format Cells dialog, you should see an image like this.

Those initial gray borders indicate that Excel will make no change to the borders. That is exactly what we want for the left border…and I’ve found no way to set that gray border setting directly in the dialog.

Now set the top, bottom, and right borders as shown here. Be careful not to change the left border, because, if you do, you’ll have to start over again.

Next, in the Fill tab of the Format Cells dialog, choose a light gray fill color. I used the middle gray on the left side of the Background Color control.

And now, when you OK out of the dialogs, you’ll see that cell F3 has been formatted. Copy cell F3 and paste it to a range beginning in cell G3, and then to the right for as many cells as you think you’ll have accounts in your horizontal lists. Then copy the range F3 through that right-most cell to cell F9.

And now, as soon as you enter an account code anywhere within the range F4:F8, or in other cells to the right, the gray border rows will appear in rows 3 and 9.

Finally, delete the temporary formula in column F.

The horizontal line above Accounts uses a similar conditional format. You can see how it’s set up in your workbooks.

The AcctGrp-DA.xlsx Workbook

This workbook can be used only with versions of Excel that support Dynamic Arrays. And because it does so, you can use formulas to return lists of accounts based on the internal logic of your company’s chart of accounts.

(If your version of Excel has the SORT worksheet function, it supports Dynamic Arrays.)

The formulas rely on the setting and calculations shown here:

Here, you can use the GL Acct Patterns section—in columns E-H—to define your various groups. In cell E4, for example, we can define all Sales accounts to begin with A40 because that’s the pattern that our simplified Chart of Accounts has followed. And in row 7, all Gross Profit accounts begin with A40 or A41, because all COGS accounts begin with A41 in my Chart of Accounts.

(If your Chart of Accounts doesn’t support a logical pattern somewhat like that, you’ll need to use the CSE method, as discussed above.)

And then, the formulas in columns J and K base their calculations on patterns in their rows in the GL Acct Patterns section.

As you read in Part 1, How to Report GL Account Groups in Excel, we can’t assign dynamic names to the groups because Excel’s INDIRECT function doesn’t work with them. And therefore, we don’t want formulas to automatically change the lists of accounts, which would cause our range names to be incorrect.

Therefore, we set up each formula in column K to give us the list of accounts we need for its row. We’ll get to the specifics in a minute.

And also, because the list-generating formulas in columns J and K take a while to calculate, we enter 0 or FALSE in the ShowSpills cell, cell B2 as shown above and below. This setting causes those long, time-consuming formulas not to calculate at all—until we need them to do so. (However, you might want to leave a value of 1 or TRUE in the ShowSpills cell until calculation times become a problem.)

To calculate those formulas again, we just enter 1 or TRUE in the ShowSpills cell, cell B2 below.

Now, let’s see how the formulas work…

Cell B6, named NumAllAccts, counts all accounts in the Meta sheet. It uses this formula:

B6:  =COUNTA(Meta[Acct])

Cell I4 counts the number of accounts in the horizontal list beginning with cell M4. It uses this formula:

I4: =COUNTA(OFFSET(L4,0,1,,NumAllAccts))

It uses the NumAllAccts value to make sure that it references a wide-enough range so that all accounts in the row are counted.

Cell J4 counts the number of accounts that should be in your list—at least according to the patterns you’ve set up in the range E4 through H4. The heart of this formula is the FILTER function, which has this syntax…

=FILTER( array, include, [if_empty] )

…where:

• array is the range or array you want to filter.
• include is a column or array of Boolean values, where TRUE indicates the rows you want to keep and FALSE indicates the rows you want to exclude. Excel treats the value of 0 as FALSE in this column and all other numbers as TRUE. And the include array must have the same number of rows as the array array.
• The optional if_empty argument specifies the value to return if the array is empty. The default error value is #CALC!.

The formula in cell J4 has a complicating issue, however: How do we set up a FILTER formula to include accounts that begin with any of the four possible patterns that could be entered in the range E4:H4?  To illustrate, the similar formula in cell J7 needs the formula to filter for any accounts that begin with “A40” or “A41”.

The “obvious” solution is to perform those multiple tests by using the OR function. But unfortunately, that doesn’t work in this case.

As it turns out, there is an easy solution. Picture two Boolean arrays in single columns. One array contains TRUE and FALSE results, where the TRUE results indicate the accounts that begin with “A40”. And in the other array, the TRUE results indicate whether the accounts begin with “A41”.

When we sum arrays, Excel sums them row by row. And when it does so, the sum of TRUE plus FALSE equals 1, which Excel treats as TRUE. And therefore, if we sum those two arrays, we get a new array with values of 1 wherever the original array had a value of “A40” or “A41”.

(If we ever want to test for AND instead of OR, we’d multiply the two arrays. That works because TRUE times FALSE equals zero and TRUE times TRUE equals 1.)

The first formula in the NumSpills column, cell J4, has the long formula below. Each of its four IF functions returns zero if no pattern code has been entered for the the cell address that each IF function references. Otherwise, those IF functions return equal-size arrays of TRUE and FALSE values. And then those zero values and arrays are summed to create one array with the TRUE and FALSE values we need.

Here’s the formula that creates those arrays and then counts the number of accounts:

J4: =IF(ShowSpills, COUNTA(
FILTER(Meta[Acct],
IF( $E4= “”, 0, LEFT( Meta[Acct], LEN($E4) ) = $E4) +
IF( $F4= “”, 0, LEFT( Meta[Acct], LEN($F4) ) = $F4) +
IF( $G4= “”, 0, LEFT( Meta[Acct], LEN($G4) ) = $G4) +
IF( $H4= “”, 0, LEFT( Meta[Acct], LEN($H4) ) = $H4)   )), “Off” )

Here, in cell J4, the FILTER function returns a list of all accounts that match the patterns shown in the four cells in the range E4:H4. And and then the COUNTA function counts the items in that list.

Honestly, the formulas in column K are kind of a kludge, because of a limitation of Excel’s INDIRECT function.

In an ideal world, here’s how this table should work:

First, the formulas in column K list each row of accounts defined by the patterns in columns E-H. We should be able to enter that formula in column M in the table above, where account values currently are listed. And then, the accounts would spill to the right, adjusting dynamically as new accounts are added to the chart of accounts. We can do this today.

Second, we assign dynamic names to each list of accounts. We can do this today.

Third, in our report, as shown here, our formulas in the range F3:G7 each uses the INDIRECT function to return lists of the accounts that each formula must sum. This we can’t do today (in early 2021) because INDIRECT returns #REF! when it references dynamic range names.

And therefore, we use column K to generate horizontal lists that we must manually copy and paste to the accounts section of this table.

(I’ve also set up a request in Excel.UserVoice to allow INDIRECT to work with dynamic names. Please visit the link and add your approval to that request.)

So, until REDIRECT can work with dynamic range names, we use column K to generate a list of them in each cell in the column. I’ll explain how to use the formulas in column K in a second.

The formula in cell K4 uses this formula:

K4:  =IF( ShowSpills, TRANSPOSE(
FILTER( Meta[Acct],
IF( $E4=””, 0, LEFT( Meta[Acct], LEN($E4)) = $E4) +
IF( $F4=””, 0, LEFT( Meta[Acct], LEN($F4)) = $F4) +
IF( $G4=””, 0, LEFT( Meta[Acct], LEN($G4)) = $G4) +
IF( $H4=””, 0, LEFT( Meta[Acct], LEN($H4)) = $H4) )), “Off” )

This formula is virtually identical to the formula in cell J4, except that we replace the COUNTA function with TRANSPOSE. This is because the FILTER function returns a vertical list but we need a horizontal list.

Each of the formulas in column K wants to return a series of accounts, with each account in a different cell. But there’s no room for those accounts to the right of column K. Therefore, Excel returns the #SPILL! error message…which is exactly what we expect in this case.

Finally, here’s how we use those formulas in columns I through K:

1. Use the formulas in column I to count the number of accounts listed in each row of the table.
2. Use the formulas in column J to count the number of accounts that the account-generating formulas would generate if they could.
3. For any row where the numbers in columns I and J are different, first delete the accounts listed for that row, starting with column M.
4. To create a new calculated list, copy the formula in column K for that row to column M for that row.
5. Select the new list that begins in column M, copy it, and then use the Paste Special dialog (Ctrl + Alt + V) to paste the values for that range on top of the calculated list.
6. Use the Create Names dialog to rename the new list of accounts for that row of values.

I told you it was a kludge.

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.

Please remember to support my Excel.UserVoice requests about the INDIRECT function and the array of arrays.

And finally, if you haven’t done so already, you can download all three workbooks at this link.