Ledgers are crucial for keeping track of expenses, and managing the budget of any entity, be it a small house to an international company. Preparing a general ledger (GL) table can be a daunting task, especially when dealing with lots of category codes. If your table contains sequential codes, you will find this method useful that can help generate all numbers at once.
In September 2018, Microsoft has introduced the new concept of dynamic arrays and “spill” behavior to create dynamic table easily. The SEQUENCE function that came with this update can generate an array from a number sequence. Using the SEQUENCE function, you can generate numbers from 1 to 10 with an increment of 1. Let's see how the SEQUENCE function can help generate date values dynamically.
SEQUENCE Function Basics
The SEQUENCE function can generate an array of sequential numbers. You can define how many rows or columns of numbers are to be generated, as well as the start value and the increment (step).
|
rows |
The number of rows to be returned. |
|
[columns] |
Optional. The number of columns to be returned. The default value is 1. |
|
[start] |
Optional. The starting value. The default value is 1. |
|
[step] |
Optional. The increment of each step. The default value is 1. |
Generating the General Ledger (GL) Codes
The GL Codes usually are multi-digit numbers to provide a basis for easy identification. In our example, while a top-level category, such as Advertising, can be assigned number 1000, a sub-level of this category, Social Media Ads, gets 1001. Based on this structure, the top-level categories can get 2000, 3000, and so on.
The SEQUENTIAL function can generate the numbers for top-level categories, since they increase by an identical incremental value. In our example, we generated numbers from 1000 to 12000 using the following formula:
The formula creates a a 12-row, 1-column range with numbers starting from 1000 that increase by 1000 at each step.
Alternative Approach
If you do not want to use a formula or you do not have access to the SEQUENCE function, you can mimic same effect usind the AutoFill feature. Of course, this will be a one-time action, and not update with other changes in your workbook, as in the formula-based approach.
