Create a Balance Sheet with Excel
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.
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.
Now, let’s take a look at our desired balance sheet layout.
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.
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:
- 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.
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.
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
- 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.
Feel free to download the file that was used to create the screenshots above, which includes the formulas, and remember, Excel rules!
Excel is not what it used to be.
You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.
Want to learn Excel?
Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.