Accounting Excel Formulas: Balance Sheets, Income Statements and Ratios

Key Takeaways 

• Excel formulas like SUM, IFERROR and basic arithmetic are the foundation of accurate balance sheets and income statements. 
• Named ranges allow you to build readable financial ratio calculations directly from your reports. 
• Using Spark Lines and percentage-change columns adds visual impact to multi-year financial reporting. 
• The IFERROR function prevents misleading #DIV/0 errors when building report templates before data is entered. 

Why Excel Formulas Matter for Accounting 

Accounting is more than just debits and credits. It is the calculation and analysis of those debits and credits that generate accounting information an organization can act upon. Excel remains the most widely used tool for building financial statements, calculating ratios and benchmarking performance, making accounting excel formulas essential knowledge for anyone in a finance role. 

Whether you are a bookkeeper preparing monthly reports or a controller analyzing year-over-year trends, the right Excel formulas help you work faster, reduce errors and produce reports your organization can act on. Excel is also invaluable for comparing your company's performance against competitors. If you compete with organizations that are publicly held, you can find their financial filings on http://www.sec.gov/edgar.shtml. 

In this article we will walk through two core financial reports, the balance sheet and the income statement, and then show how to use that data to produce financial ratios for industry comparison. 

Essential Accounting Excel Formulas at a Glance 

Before diving into the detailed walkthroughs, here is a quick reference of the key excel formulas for accounting covered in this article: 

These are the basic accounting formulas in excel that every finance professional should have in their toolkit, and they pair well with the built-in Excel finance formulas used for loan amortization, depreciation schedules and present value analysis. The sections below show exactly how to apply them. 

Balance Sheet

The specific line items on a balance sheet vary by industry and organization. However, the top part of the balance sheet typically contains assets, followed by liabilities. The difference between these is known as Owner's or Stockholder's Equity. There can be several subsections, for example, Fixed Assets, Current Assets, Goodwill and Intangibles. We would add up all the items classified as current assets, then all the items classified as fixed assets to arrive at total assets.

We do the same for our current liabilities and long-term liabilities. Current liabilities would be things like payroll and income taxes. Long-term liabilities would include long-term debt. We would add up all the items under current liabilities, then long term liabilities, then the total of those. 

If you report several years together, you can utilize Spark Lines to show growth or decline over the reporting periods. You can also create additional columns to calculate the percentage growth or decline for more impact, and conditional formatting can automatically highlight figures that fall outside expected ranges. If you are generating a report template, you may not have entered any figures yet. In that case, your percentage formulas may show #DIV/0 errors. Of course, you know they're not really errors—there's just no data yet. You can use the IFERROR function in front of the percentage calculation to show a blank or zero until accurate numbers are entered. In the sample workbook for this article on the Balance Sheet worksheet, we've applied all these techniques to Assets. 

Income and Expense Statement 

Another useful report is the Income Statement, also called the Income and Expense Statement, and Revenue Statement. Unlike the Balance Sheet, which is a snapshot in time, an Income Statement covers a specific period to show how revenue was earned or lost. Simply stated: How much did it cost us to earn the revenue we made? Like the balance sheet, the composition of the income statement can vary depending upon industry or complexity of the operation. In our example, we're using easy line items, such as: 

• Revenue 
• Cost of Goods Sold (COGS) 
• Gross Profit 
• Selling and General Administrative Expense 
• Depreciation and Amortization 
• Operating Income 
• Interest Expense 
• Other Expenses 
• Income Before Taxes 
• Income Taxes 
• Net Income 

Income statements may also include additional shareholder impact information. You perform these calculations: 

• Revenue – COGS = Gross Profit 
• Gross Profit - Selling and General Administrative Expense = Operating Income 
• Operating Income - Other Expenses = Income Before Taxes 
• Income Before Taxes – Income Taxes = Net Income 

As you can see in a worksheet with these calculations, at first glance, it may not be easy to tell where the formulas are. Use the Show Formulas tool (Ctrl+`) to see where all the formulas are before beginning to edit to make sure you don't delete any formulas. 

Ratios 

All of the items above when used in various combination and calculations yield ratios that allow us to compare ourselves to others in our industry and determine how we're doing in comparison. A solid grasp of financial statements is essential for researching and determining what is "normal" or "good" for your particular industry. Comparing ratios across vastly different industries, such as a small local retailer and a global technology corporation, will not give you meaningful insights. 

By using named ranges to name each relevant line on the Balance Sheet and Income statement, we can just type in these terms and the relevant math operators to get the ratios. If you highlight both the label and the value in a cell range, use the Create from Selection button on the Formula tab in the Defined Names group to create the named ranges.

Tips for Using Accounting Excel Formulas Effectively 

The walkthroughs above cover the core formulas, but a few best practices will help you get even more value out of your accounting excel formulas in day-to-day work: 

• Use named ranges for every financial line item. When your ratio formula reads =Current_Assets/Current_Liabilities instead of =B12/B25, anyone reviewing the workbook can understand it instantly. 
• Wrap division formulas in IFERROR. Building report templates before data is available? The IFERROR function keeps your worksheets clean by replacing #DIV/0 errors with a blank or zero until real numbers are entered. 
• Audit with Ctrl+` before sharing. Toggling Show Formulas lets you verify that no formula was accidentally overwritten with a hard-coded number, a common and costly mistake in shared workbooks. 
• Add Spark Lines to multi-year reports. A small in-cell chart next to each line item gives stakeholders an immediate visual of trends without requiring a separate chart sheet. 
• Lock formula cells to prevent accidental edits. Use Excel's Protect Sheet feature to allow data entry in input cells while keeping your SUM, subtraction and ratio formulas safe from unintended changes. 
• Standardize with templates. If your team produces the same balance sheet or income statement each period, save a master template with all formulas, named ranges and formatting already in place. This reduces setup time and ensures consistency across reporting cycles. 

Build Your Accounting Excel Skills with Pryor Learning 

Mastering Excel for accountants takes practice, and the right training can accelerate your progress. Pryor Learning offers live and on-demand courses designed to help accounting professionals build the Excel skills that matter most, from foundational formulas to advanced reporting techniques. 

• The sample workbook included with this article is an example of the practical, hands-on approach Pryor takes to every course. If you are ready to sharpen your skills and bring more confidence to your financial reporting, explore Pryor's finance and accounting training and find the right fit for your role and experience level.