Sign in
  • Blog
  • All Posts
  • Business & Economics
    • Accounting & Finance
    • Analytical Charting
    • Analytical Methods
    • Business Statistics
    • Cash Flow
    • Data Management
    • Data Visualization
    • Economic Indicators
    • Financial Analysis
    • Forecasting & Simulation
    • Reporting
    • Tests & Validations
    • Time Value of Money
  • Excel Methods
    • Charting Methods
    • Conditional Formatting
    • Data Management
    • Error Reduction
    • Excel Dashboards
    • Excel Tables
    • Excel’s Graphic Tools
    • Printing
    • Report Tables
    • Shortcuts
    • VBA
    • Worksheet Interaction
  • Formulas & Functions
    • Aggregation Functions
    • Array Formulas
    • Counting Functions
    • Date-Time Functions
    • Dates and Times
    • Dynamic Array Formulas
    • Financial Functions
    • Lookup Formulas and Functions
    • Range Names
    • Rounding Functions
    • Statistical Functions
    • Text Functions
  • Excel Strategies
    • Excel Productivity
    • Excel-Friendly OLAP
  • Training & Services
  • Downloads
Sign in
Welcome!Log into your account
Forgot your password?
Password recovery
Recover your password
Search
Saturday, February 27, 2021
  • Sign in / Join
  • Contact
Sign in
Welcome! Log into your account
Forgot your password? Get help
Password recovery
Recover your password
A password will be e-mailed to you.
Microsoft Excel User, Microsoft Excel Learning, Microsoft Excel Training, Microsoft Excel Lessons, Microsoft Excel Tips Microsoft Excel User, Microsoft Excel Learning, Microsoft Excel Training, Microsoft Excel Lessons, Microsoft Excel Tips
  • Blog
  • All Posts
  • Business & Economics
    • AllAccounting & FinanceAnalytical ChartingAnalytical MethodsBusiness StatisticsCash FlowData ManagementData VisualizationEconomic IndicatorsFinancial AnalysisForecasting & SimulationReportingTests & ValidationsTime Value of Money
      You can ratchet down errors in your Excel reports by using an Error Summary Table that uses conditional formatting to alert you to errors.
      Accounting & Finance

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

      To use Account Groups in Excel formulas, you first must define the groups. Here’s how to do it using either simple lists or Dynamic Arrays.
      Array Formulas

      How to Define General Ledger Account Groups in Excel

      How to aggregate named groups of GL accounts.
      Accounting & Finance

      How to Report GL Account Groups in Excel

      In Excel, you can report named groups of GL account numbers, product codes, and so on—just as you report one such number. Here’s how.
      Accounting & Finance

      How to Use Array Formulas to Report Groups of Accounts

  • Excel Methods
    • AllCharting MethodsConditional FormattingData ManagementError ReductionExcel DashboardsExcel TablesExcel’s Graphic ToolsPrintingReport TablesShortcutsVBAWorksheet Interaction
      You can ratchet down errors in your Excel reports by using an Error Summary Table that uses conditional formatting to alert you to errors.
      Accounting & Finance

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

      To use Account Groups in Excel formulas, you first must define the groups. Here’s how to do it using either simple lists or Dynamic Arrays.
      Array Formulas

      How to Define General Ledger Account Groups in Excel

      All Excel reports perform at least four tasks. Good reports assign these tasks to four sections, with specific worksheets defining each section.
      Excel Methods

      How to Structure Your Report Workbook

      Excel's Linked Picture feature (Camera tool) allows us to position a table in reports while ignoring the row and column settings in the report worksheet.
      Excel’s Graphic Tools

      Ideas about Using Excel’s Linked-Picture (Camera) Tool

  • Formulas & Functions
    • AllAggregation FunctionsArray FormulasCounting FunctionsDate-Time FunctionsDates and TimesDynamic Array FormulasFinancial FunctionsLookup Formulas and FunctionsRange NamesRounding FunctionsStatistical FunctionsText Functions
      The LET function is the most powerful function that Microsoft has released for Excel in years. Here's an introduction to its features.
      Array Formulas

      How to Use Excel’s LET Function

      Here's how to create an Excel array from two others, with the arrays stacked either one on top of the other, or side-by-side, like books on a shelf.
      Downloads

      How to Stack and Shelve Dynamic Arrays

      To use Account Groups in Excel formulas, you first must define the groups. Here’s how to do it using either simple lists or Dynamic Arrays.
      Array Formulas

      How to Define General Ledger Account Groups in Excel

      We compare Excel's five columnar lookup functions.
      Downloads

      XLOOKUP vs VLOOKUP vs INDEX-MATCH vs SUMIFS

  • Excel Strategies
    • AllExcel ProductivityExcel-Friendly OLAP
      How to aggregate named groups of GL accounts.
      Accounting & Finance

      How to Report GL Account Groups in Excel

      In Excel, you can report named groups of GL account numbers, product codes, and so on—just as you report one such number. Here’s how.
      Accounting & Finance

      How to Use Array Formulas to Report Groups of Accounts

      If you want all your Excel reports, analyses, forecasts, and other Excel work to be highly productive, this is the only strategy that will work for you.
      Business & Economics

      An Introduction to Excel Data Plumbing

      All Excel reports perform at least four tasks. Good reports assign these tasks to four sections, with specific worksheets defining each section.
      Excel Methods

      How to Structure Your Report Workbook

  • Training & Services
    • Use this Excel dashboard to track 27 economic indicators of the United States' recovery from the Covid-19 recession.
      Training & Services

      Learn How to Use Excel to Track the US Recovery from…

      This Excel dashboard report lets you follow key economic trends while showing you how to gain a HUGE increase in your Excel productivity at work.
      Training & Services

      Track Key Economic Indicators Every Weekday with this Excel Dashboard

  • Downloads
Microsoft Excel User, Microsoft Excel Learning, Microsoft Excel Training, Microsoft Excel Lessons, Microsoft Excel Tips ExcelUser.com
Home Business & Economics Accounting & Finance How to Report GL Account Groups in Excel
  • Business & Economics
  • Accounting & Finance
  • Formulas & Functions
  • Aggregation Functions
  • Array Formulas
  • Excel Strategies
  • Excel Productivity
  • Range Names
  • Reporting

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.

By
Charley Kyd
2688
Facebook
Twitter
Pinterest
Linkedin
    The formulas in this Income Statement use named Account Groups to sum any number of accounts in one formula.
    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.
    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.
    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.
    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.

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

    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.
    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.

    • TAGS
    • Account Groups
    • Chart of Accounts
    • Dynamic Arrays
    • Error-Checking
    • Income Statements
    • INDIRECT Function
    Facebook
    Twitter
    Pinterest
    Linkedin
      Previous articleHow to Use Array Formulas to Report Groups of Accounts
      Next articleXLOOKUP vs VLOOKUP vs INDEX-MATCH vs SUMIFS
      Charley Kyd
      Early in my career, I worked nearly 20 years as the CFO of turnarounds and startups. But I eventually got burned out fighting continual struggles with cash flow. That's when I started to write about Lotus 1-2-3, the spreadsheet software that I'd been using most of the time during the CFO days. When Excel was about to be introduced for the PC, one of my magazine editors set up a meeting for me to see the product, talk with the developers, and write a cover story about Excel. So I used the first version of Excel before it was launched. And I had also used the first version of VisiCalc before it was launched. And then,

      RELATED ARTICLESMORE FROM AUTHOR

      The LET function is the most powerful function that Microsoft has released for Excel in years. Here's an introduction to its features.
      Array Formulas

      How to Use Excel’s LET Function

      You can ratchet down errors in your Excel reports by using an Error Summary Table that uses conditional formatting to alert you to errors.
      Accounting & Finance

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

      To use Account Groups in Excel formulas, you first must define the groups. Here’s how to do it using either simple lists or Dynamic Arrays.
      Array Formulas

      How to Define General Ledger Account Groups in Excel

      In Excel, you can report named groups of GL account numbers, product codes, and so on—just as you report one such number. Here’s how.
      Accounting & Finance

      How to Use Array Formulas to Report Groups of Accounts

      If you want all your Excel reports, analyses, forecasts, and other Excel work to be highly productive, this is the only strategy that will work for you.
      Business & Economics

      An Introduction to Excel Data Plumbing

      Use this Excel dashboard to track 27 economic indicators of the United States' recovery from the Covid-19 recession.
      Training & Services

      Learn How to Use Excel to Track the US Recovery from the Covid Recession

      All Categories

      Latest Articles

      The LET function is the most powerful function that Microsoft has released for Excel in years. Here's an introduction to its features.

      How to Use Excel’s LET Function

      Array Formulas Charley Kyd - February 14, 2021
      0
      In the summer of 2020, Microsoft introduced the LET function for Excel 365—one of the most-significant new worksheet functions that Microsoft has introduced in...
      You can ratchet down errors in your Excel reports by using an Error Summary Table that uses conditional formatting to alert you to errors.

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

      Accounting & Finance Charley Kyd - February 9, 2021
      0
      Decades ago, I worked as a cost accountant for a large company. But because our department received terrible reports, I wrote my own reports...using...
      Here's how to create an Excel array from two others, with the arrays stacked either one on top of the other, or side-by-side, like books on a shelf.

      How to Stack and Shelve Dynamic Arrays

      Downloads Charley Kyd - February 1, 2021
      0
      While using Excel 365 recently, I needed to create one dynamic array that would consist of two arrays, with one stacked on top of...
      Advertisement
      Advertisement
      ExcelUser.com, for business users of Microsoft Excel
      ABOUT US
      ExcelUser, Inc. Copyright © 2004 - 2020 by Charles W. Kyd, all rights reserved. Content, graphics, and HTML code are protected by US and International Copyright Laws, and may not be copied, reprinted, published, translated, hosted, or otherwise distributed by any means without explicit permission.
      FOLLOW US
      • Contact
      • Terms of Use
      • Privacy Policy
      • Disclaimer
      • Earnings Policy
      • Reprints and Reposts
      © ExcelUser, Inc. Copyright © 2004 - 2020 by Charles W. Kyd, all rights reserved.
      MORE STORIES
      Does your company need to make scheduled payments to companies that don't send invoices? This worksheet will help to plan those cash requirements.

      Manage Periodic Payments with an Excel Cash Calendar

      February 17, 2020
      You can ratchet down errors in your Excel reports by using an Error Summary Table that uses conditional formatting to alert you to errors.

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

      February 9, 2021
      Here's how to use Excel to figure out whether you're making or losing money on the cash you borrow, why it matters, and what you can do about it.

      Weighing Your Debt Load

      December 17, 2019
      Edit with Live CSS
      Save
      Write CSS OR LESS and hit save. CTRL + SPACE for auto-complete.