Excel Balance Sheet

This post illustrates how to create a financial statement such as a balance sheet with built-in Excel features and functions. Our primary objective is to build a workbook that efficiently pulls values from the trial balance into the balance sheet.

Overview

For the purpose of this post, let’s assume we have a data sheet that contains the source data for the report, we’ll call this data the trial balance. Next, we’ll assume that we need to get the data from the data sheet into the report sheet in an automated way. In order to efficiently retrieve the amounts, or account balances, into the report (balance sheet) we’ll use Excel’s built-in conditional summing function SUMIFS. If you’ve not explored this amazing function, I wrote a blog post that discusses the SUMIFS Function so feel free to check it out if you would like to review the function’s syntax.

Let’s take a look at the data sheet containing the data for the report, our simplified trial balance.

Excel Trial Balance with Account Balances

Now, let’s take a look at our desired balance sheet layout.

Excel Balance Sheet structure

One challenge that Excel users face when building a report such as a balance sheet is the fact that the report labels don’t match the data labels. For example, the account name in the trial balance is Checking, but the value needs to be reflected in the balance sheet as Cash. We need to provide Excel with these label translations so that we can use the SUMIFS function to retrieve the correct account balances for each report line.

In the screenshot below, I’ve updated the trial balance and mapped the account names to the financial statement labels.

Trial balance with account balances mapped to the financial statement lines

Also note that in some cases, many accounts are mapped to one report line, for example, AR and AR Allowance are both mapped to the Accounts Receivable balance sheet line.

Once we have defined the mapping, we can easily compute the financial statement values with the SUMIFS function. For example, we could use the following formula in cell C7 in our balance sheet:

=SUMIFS(TB!D:D,TB!C:C,B7)

Where:

• TB!D:D is the sum range, the account balance column on the TB trial balance worksheet
• TB!C:C is the criteria range, the report label column on the TB trial balance worksheet
• B7 is our report label

We can fill the formula throughout the report to complete the balance sheet. The completed report is shown below.

Excel Balance Sheet with amount formulas

And there you have it!

Note: for recurring-use workbooks, you can set up the mapping in a dedicated worksheet and then use lookup functions to retrieve the report labels into the trial balance, or conversely, use SUMIFS to retrieve the trial balance values into the map itself.

Additional Considerations

In addition to achieving the key objective, which was to automatically group and pull account balances into the balance sheet, here are a few finer points to consider when building recurring-use reporting workbooks.

• Build dynamic formula-based report headers
• Indent with the indent command, not with leading spaces or into a new column
• Use an error check sheet to ensure all data from the trial balance made it to the balance sheet
• Use a start here sheet to store global settings
• Use the SUBTOTAL function rather than SUM to compute the balance sheet totals
• Use skinny rows between the last data row and the formula row

Notes

• If you have not played with the SUBTOTAL function before, please enroll into the FREE Better Summing online course so you can learn the details of this marvelous function!
• The SUBTOTAL function and skinny rows were covered in Excel University Volume 1.
• Additional post concepts including mapping tables, dynamic headers, errorck, start here, and indentation, are derived from Excel University Volume 2. For more information or details, please feel free to pick up a copy of the book or enroll into the online course.

Conclusion

Feel free to download the file that was used to create the screenshots above, which includes the formulas, and remember, Excel rules!

ExcelBalanceSheet