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.
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:
We can fill the formula throughout the report to complete the balance sheet. The completed report is shown below.
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.
Feel free to download the file that was used to create the screenshots above, which includes the formulas, and remember, Excel rules!
A balance sheet is one of the most important financial statements that provides a snapshot of a company’s financial health on a given day. It lists a company’s assets, liabilities, and shareholders’ equity to give readers an idea of what the company owns and owes as well as the amount invested by shareholders.
Learning how to create a balance sheet in Excel is an invaluable skill for accounting and finance professionals or even small business owners who want to better understand their company’s finances. In this comprehensive guide, I will walk you through the step-by-step process of building a balance sheet in Excel using some easy-to-follow examples.
Overview of a Balance Sheet
Before diving into creating a balance sheet in Excel let’s first go over the key elements of a balance sheet
-
Assets – These are economic resources owned or controlled by a company which hold future economic value Assets are listed in order of liquidity on a balance sheet Current assets like cash, accounts receivable, and inventory are listed first followed by long-term assets like property, plant and equipment.
-
Liabilities – These are financial obligations or debts that a company owes to outside parties. Current liabilities like accounts payable and short-term borrowings are listed first followed by long-term liabilities like bonds payable and long-term debt.
-
Shareholders’ equity – Also known as net assets or net worth, shareholders’ equity represents the residual value left for shareholders if all assets were liquidated and liabilities paid off. It includes share capital and retained earnings.
-
Total assets = Total liabilities + Total shareholders’ equity – This accounting equation must balance for the balance sheet to be correct. The total assets must equal the total liabilities plus shareholders’ equity.
Now let’s see how to create this balance sheet structure in Excel.
Step 1 – Format the Balance Sheet Headings in Excel
We will first create the column headings for the balance sheet accounts.
-
Open a new Excel worksheet.
-
Merge and center cells A1 through C1. Type in “Balance Sheet” and make it bold.
-
In cell A2, enter the date for the balance sheet. Format it as bold.
-
In cell A3, type “Assets”. Merge and center it. Make it bold.
-
In cell A4, enter “Current Assets”. Make it bold.
-
In cell B4, enter “Liabilities & Equity”. Merge and center it. Make it bold.
-
In cell C5, type “Current Liabilities”. Make it bold.
Your Excel sheet should now look like this:
![Balance Sheet Headings][]
We have created columns for Assets, Liabilities & Equity. The next step is to enter the accounts.
Step 2 – Enter Asset, Liability and Equity Accounts
Under the Current Assets heading, enter the following accounts from cell A5 downwards:
- Cash
- Accounts Receivable
- Inventory
For each account, enter a row with the account name in column A and the amount in column B.
Under Current Liabilities in column C, enter these accounts:
- Accounts Payable
- Short-term Debt
For each account, enter the name in column C and amount in column D.
It should now look like: ![Balance Sheet Accounts][]
Next, we need to add headings and accounts for long-term assets and liabilities.
Step 3 – Include Long-term Assets and Liabilities
-
Enter “Long-term Assets” in cell A9 and “Long-term Liabilities” in cell C9. Make them bold.
-
Below Long-term Assets, enter these accounts: – Property, Plant & Equipment – Investments
-
Below Long-term Liabilities, enter these accounts: – Bonds Payable – Long-term Debt
Your balance sheet will now have both current and long-term accounts listed:![Balance Sheet with Long-term Items][]
Now we need to add Equity accounts to complete the balance sheet structure.
Step 4 – Include Equity Section
-
Enter “Equity” in cell C13 and make it bold.
-
Below it, enter these accounts: – Share Capital – Retained Earnings
Your Equity section with accounts is now complete.
Now we’re ready to add formulas to calculate sub-totals and totals.
Step 5 – Add Sub-totals and Totals with Excel Formulas
To complete the balance sheet, we need to calculate sub-totals and totals using Excel formulas:
-
In cell B8, enter =SUM(B5:B7) to sum current assets.
-
In cell D8, enter =SUM(D5:D7) to sum current liabilities.
-
In cell B12, enter =SUM(B10:B11) to sum long-term assets.
-
In cell D12, enter =SUM(D10:D11) to sum long-term liabilities.
-
In cell D15, enter =SUM(D13:D14) to sum equity.
-
In cell B16, enter =B8+B12 to total assets.
-
In cell D16, enter =D8+D12+D15 to total liabilities and equity.
The final balance sheet with totals calculated is shown below:![Balance Sheet with Totals][]
The total assets equals total liabilities and equity, so our balance sheet balances!
Some formatting was applied to make the accounts left aligned and amounts right aligned. The Accounting number format was also applied to the amounts.
And that’s it! In just a few simple steps, you have created a complete balance sheet structure in Excel.
Now let’s look at another example of creating a balance sheet in Excel using a vertical layout.
Bonus: Vertical Balance Sheet in Excel
For this example, we will create a vertical balance sheet with assets on top and liabilities & equity below.
Follow these steps:
-
Enter headings for Assets, Liabilities & Equity each in Column A.
-
Enter current asset accounts in Column A and amounts in Column B.
-
Use SUM formula to total current assets.
-
Enter long-term assets and total them.
-
Add Total Assets formula.
-
Repeat steps 2-5 for Liabilities and Equity sections.
-
The final balance sheet should resemble:![Vertical Balance Sheet][]
This creates a compact vertical balance sheet view in Excel.
Things to Remember
Here are some tips when creating a balance sheet in Excel:
- Use Accounting number format for amounts.
- Organize accounts from most liquid to least liquid.
- Assets = Liabilities + Equity is a key balancing equation.
- Cross check totals using the SUM function.
- Use conditional formatting to highlight positive and negative amounts.
- Freeze panes so headers remain visible when scrolling.
Next Steps
You can expand your Excel balance sheet skills with:
- Adding charts and graphs to visualize the data
- Including more line items and breakdowns
- Linking to other sheets for trial balances
- Automating balances with VLOOKUPS
- Updating with actual figures each period
So that sums up how to make a balance sheet template in Excel! With some practice, you’ll be able to easily create, update and analyze balance sheets using Excel.
Jeff LenningI 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.
You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.
Excel Tutorial: How to Make a Balance Sheet
How do I write a balance sheet in Excel?
Open up a new file on Microsoft Excel. Put in [Company Name] Balance Sheet at cell A1 for easy identification. Leave some space for formatting, then on the first column of the third row, write Assets. This is the section where you’ll put in the values for everything your company has.
What is a balance sheet in Excel?
As promised: Excel Tutorial: How to Create an Income Statement • Excel Tutorial: H The balance sheet describes the assets, liabilities, and equity of a firm at a specific point in time. Assets are the (tangible or intangible) things that a firm owns. Liabilities are the firm’s debts.
How do you balance a balance sheet?
A key aspect of a balance sheet is that it must balance—meaning the total assets must equal the total liabilities plus equity. Create a formula at the end of your balance sheet to verify this equality. Subtract the sum of liabilities and equity from the sum of assets.
How do I create an effective balance sheet?
Here are a few tips to help you create an effective balance sheet: Use a template. The Microsoft Word Download Center offers many balance sheet templates that can help you save time and effort when entering information into a balance sheet. Use the “Borders” button to create guiding lines in your balance sheet.
