# 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.

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

### Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

### 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?

Our training programs start at \$29 and will help you learn Excel quickly.

1. rijpe vrouwen on July 2, 2016 at 7:16 pm

I found your website was much handy for us! if u keep going the good job I’ll visit again to ur weblog.

• Kurt LeBlanc on July 5, 2016 at 5:21 am

Thanks for the kind words Rijpe!

Kurt LeBlanc

2. Charles Asher on July 20, 2016 at 6:27 pm

Thank you so much for this. The underwrites of my mortgage made me do one of these and I had no clue how to do it. Your site probably saved me many hours of work.

• Kurt LeBlanc on July 21, 2016 at 5:41 am

Kurt LeBlanc

3. Shaks on April 13, 2017 at 4:51 am

Very explanative, thank you!

4. Jordan Director on September 23, 2018 at 10:05 pm

Hi,
I am trying to create an Excel document that allows me to simply enter in my expenses and then see my account balances in a separate tab.
I have created one sheet that includes all the raw data of my expenses. In the other sheet I have a balance sheet that shows my credit card balances, bank accounts and investment accounts.
Is there any way to link these two tabs? For example, if I enter in an expense into my expense tab for \$6.99 paid from my Checking Account, then how can I make my Checking Account Balance on my Balance Sheet tab reflect the \$6.99 charge?