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
    • Inflation & Deflation
    • Public Company Data
    • 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
    • Functions Explained
    • Logical Functions
    • Lookup Formulas and Functions
    • Math Functions
    • Range Names
    • Rounding Functions
    • Statistical Functions
    • Text Functions
  • Excel Strategies
    • Analytical Methods
    • Excel Productivity
    • Excel-Friendly OLAP
    • Visualization
  • Training & Solutions
  • Downloads
Sign in
Welcome!Log into your account
Forgot your password?
Password recovery
Recover your password
Search
Friday, March 24, 2023
  • 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 & SimulationInflation & DeflationPublic Company DataReportingTests & ValidationsTime Value of Money
      Growing too fast can be dangerous to your company's health. Use the Sustainable Growth Rate ratio to track your company's financial ability to grow.
      Accounting & Finance

      How Fast Is Too Fast?

      Aggregation Functions

      How to Smooth Data by Using the TREND Function

      Two economists have introduced a new leading indicator, which predicts a recession soon. These Excel charts illustrate their insight. If they're correct, Excel users will be very busy in the months ahead.
      Charting Methods

      Consumer Sentiment Suggests a 2022 Recession

      Your data can contain many important, hidden patterns. But simple transformations in Excel can help to reveal them—as this example illustrates.
      Analytical Methods

      Simple Transformations Can Reveal Hidden Patterns in Your Data

  • Excel Methods
    • AllCharting MethodsConditional FormattingData ManagementError ReductionExcel DashboardsExcel TablesExcel’s Graphic ToolsPrintingReport TablesShortcutsVBAWorksheet Interaction
      Aggregation Functions

      How to Smooth Data by Using the TREND Function

      Two economists have introduced a new leading indicator, which predicts a recession soon. These Excel charts illustrate their insight. If they're correct, Excel users will be very busy in the months ahead.
      Charting Methods

      Consumer Sentiment Suggests a 2022 Recession

      Your data can contain many important, hidden patterns. But simple transformations in Excel can help to reveal them—as this example illustrates.
      Analytical Methods

      Simple Transformations Can Reveal Hidden Patterns in Your Data

      A shortage of truckers is one cause of the supply chain crisis in the US. This Excel chart suggests that the problem won't end soon.
      Blog

      An Excel Chart of a Major U.S. Supply Chain Problem

  • Formulas & Functions
    • AllAggregation FunctionsArray FormulasCounting FunctionsDate-Time FunctionsDates and TimesDynamic Array FormulasFinancial FunctionsFunctions ExplainedLogical FunctionsLookup Formulas and FunctionsMath FunctionsRange NamesRounding FunctionsStatistical FunctionsText Functions
      Aggregation Functions

      How to Smooth Data by Using the TREND Function

      As inflation rises, so do mortgage interest rates—causing house prices to fall. Here's how Excel's PV function can help you estimate what you new house price will be.
      Blog

      Here’s How Inflation Could Affect the Value of Your Home

      Aggregation Functions

      How to Smooth Monthly Trends with Centered Moving Averages

      To reduce errors in reports, you should foot and cross-foot them. But Excel's floating-point arithmetic gets in the way. Here's how to fix the problem with a standard deviation calculation.
      Error Reduction

      How to Foot and Cross-Foot Excel Reports in a Floating-Point World

  • Excel Strategies
    • AllAnalytical MethodsExcel ProductivityExcel-Friendly OLAPVisualization
      Aggregation Functions

      How to Smooth Data by Using the TREND Function

      Two economists have introduced a new leading indicator, which predicts a recession soon. These Excel charts illustrate their insight. If they're correct, Excel users will be very busy in the months ahead.
      Charting Methods

      Consumer Sentiment Suggests a 2022 Recession

      Your data can contain many important, hidden patterns. But simple transformations in Excel can help to reveal them—as this example illustrates.
      Analytical Methods

      Simple Transformations Can Reveal Hidden Patterns in Your Data

      Aggregation Functions

      How to Smooth Monthly Trends with Centered Moving Averages

  • Training & Solutions
    • Use this unique Excel dashboard to track the general rate of inflation, your personal rate, and the rate of the goods and services you buy.
      Business & Economics

      Inflation Tracker Excel Dashboard with Consumer Prices

      Update this Excel dashboard with 15 key US mortgage interest rates every business day, with one command. Each chart shows 71 days of rates.
      Training & Solutions

      Mortgage Rates Excel Dashboard

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

      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 & Solutions

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

      Growing too fast can be dangerous to your company's health. Use the Sustainable Growth Rate ratio to track your company's financial ability to grow.
      Accounting & Finance

      How Fast Is Too Fast?

      Aggregation Functions

      How to Smooth Data by Using the TREND Function

      Your data can contain many important, hidden patterns. But simple transformations in Excel can help to reveal them—as this example illustrates.
      Analytical Methods

      Simple Transformations Can Reveal Hidden Patterns in Your Data

      Aggregation Functions

      How to Smooth Monthly Trends with Centered Moving Averages

      To reduce errors in reports, you should foot and cross-foot them. But Excel's floating-point arithmetic gets in the way. Here's how to fix the problem with a standard deviation calculation.
      Error Reduction

      How to Foot and Cross-Foot Excel Reports in a Floating-Point World

      Use this unique Excel dashboard to track the general rate of inflation, your personal rate, and the rate of the goods and services you buy.
      Business & Economics

      Inflation Tracker Excel Dashboard with Consumer Prices

      All Categories

      Latest Articles

      Growing too fast can be dangerous to your company's health. Use the Sustainable Growth Rate ratio to track your company's financial ability to grow.

      How Fast Is Too Fast?

      Accounting & Finance Charley Kyd - July 26, 2022
      0
      (Originally published in Inc Magazine.) What typically tops the list of worries of the chief executive officers of fast growing companies? Financing that growth, according...

      How to Smooth Data by Using the TREND Function

      Aggregation Functions Charley Kyd - January 17, 2022
      0
      Years ago, I read that Prof. William S. Cleveland had suggested that data could be smoothed by calculating a centered trendline through adjacent data—a...
      Two economists have introduced a new leading indicator, which predicts a recession soon. These Excel charts illustrate their insight. If they're correct, Excel users will be very busy in the months ahead.

      Consumer Sentiment Suggests a 2022 Recession

      Charting Methods Charley Kyd - December 17, 2021
      0
      In recent months, business websites have speculated about recessions and stagflation in 2022. These predictions could affect your Excel work significantly in the next few...
      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
      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.

      How to Use Array Formulas to Report Groups of Accounts

      December 29, 2020
      In this final article of the SUMIFS, SUMPRODUCT series, you'll learn more reasons to use advanced multi-criteria lists in marketing and finance.

      Advanced SUMIFS Calculations with Criteria Lists

      July 11, 2018
      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.