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 Formulas & Functions Array Formulas How to Define General Ledger Account Groups in Excel
  • Formulas & Functions
  • Array Formulas
  • Excel Methods
  • Conditional Formatting
  • Downloads
  • Range Names
  • Business & Economics
  • Reporting

How to Define General Ledger Account Groups in Excel

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.

By
Charley Kyd
10346
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.

    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 with monthly changes by account serves as the data Table for our Excel formulas.
    This Table with monthly changes by account serves as the data Table for our Excel formulas.

    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.

    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.

    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.

    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.

    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:

    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.

    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.

    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.

    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.

    Select non-contiguous ranges of accounts in preparation for using the Create-Names command.
    Select non-contiguous ranges of accounts in preparation for using the Create-Names command.

    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.

    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.

    This is the initial view of the Border control for the Conditional Formatting's Format Cells dialog.
    This is the initial view of the Border control for the Conditional Formatting’s Format Cells dialog.

    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.

    Being careful not to disturb the left border, set the top and bottom borders as shown.
    Being careful not to disturb the left border, set the top, bottom, and right borders as shown.

    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.

    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.

    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:

    The table, calculations, and settings that define the groups of accounts.
    The table, calculations, and settings that define the groups of accounts.

    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…

    The table, calculations, and settings that define the groups of accounts.

    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 table, calculations, and settings that define the groups of accounts.

    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.

    The table, calculations, and settings that define the groups of accounts.

    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.

    Formulas in each row of the Excel Income Statement reference the name of its group account in column A.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.)

    The table, calculations, and settings that define the groups of accounts.

    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.

    • TAGS
    • Account Groups
    • Chart of Accounts
    • COUNTA Function
    • Download
    • Dynamic Arrays
    • Dynamic Names
    • Dynamic Range Names
    • FILTER Function
    • Income Statements
    • INDIRECT Function
    • Number Formats
    • OFFSET Function
    • ROWS Function
    • TRANSPOSE Function
    Facebook
    Twitter
    Pinterest
    Linkedin
      Previous articleXLOOKUP vs VLOOKUP vs INDEX-MATCH vs SUMIFS
      Next articleHow to Stack and Shelve Dynamic Arrays
      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

      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

      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

      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

      Conditional Formatting

      How to Set Up Multiple Conditional Formats with Formulas

      When conditional formatting rules conflict, Excel can deliver unexpected formatting results. These two guidelines make Excel's logic clear.
      Conditional Formatting

      How Excel Manages Conflicting Conditional Formats with Formulas

      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
      How to Use SUMPRODUCT to Find the Last Item in an Excel List

      How to Use SUMPRODUCT to Find the Last Item in an...

      February 17, 2020
      Excel's SUMPRODUCT function offers much of the power of Excel array formulas, but without special treatment.

      Use Excel’s SUMPRODUCT to Summarize Worksheet Data

      December 9, 2019
      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

      February 14, 2021
      Edit with Live CSS
      Save
      Write CSS OR LESS and hit save. CTRL + SPACE for auto-complete.