How to build an income statement in Excel from a trial balance

An income statement shows what a business earned and what it spent over a period, and what was left. In a spreadsheet you don't retype the totals. You write formulas that add up the account balances by category, so the statement rebuilds itself whenever a balance changes.

What an income statement in Excel needs to do

A trial balance lists every account with its balance. An income statement uses only the revenue and expense accounts, grouped into a few lines. The simplest version, called a multi-step income statement because it shows subtotals along the way, looks like this:

• Revenue: everything the business earned from customers.
• Cost of goods sold: what the goods it sold cost, including freight paid to bring them in.
• Gross profit: revenue less cost of goods sold.
• Operating expenses: the costs of running the business, such as rent, utilities and insurance.
• Net income: gross profit less operating expenses.

The work in a spreadsheet is the grouping. Each account row carries a category label, and a conditional sum adds up only the rows with the label you ask for. The function for one condition is SUMIF(range, criteria, sum_range). When you need two conditions, such as a category and a year, you use SUMIFS(sum_range, range1, criteria1, range2, criteria2). Notice that the sum range comes first in SUMIFS and last in SUMIF.

The subtotals are plain subtractions of the lines above. Because every line is a formula, a corrected balance in the trial balance flows straight through to net income. That is the reason the exercises insist on formulas rather than typed numbers.

Worked example: Pine Street Dental

The free exercise gives you Pine Street Dental's income statement accounts for the year ended December 31, 2026. The accounts are in no particular order, which is realistic: a trial balance is sorted by account number, not by where each line lands on the statement.

The statement goes in column H, rows 4 to 8, with the line labels already in column G.

Step 1, revenue. The two revenue accounts are Service Revenue ($247,360) and Sales Revenue ($525,640). SUMIF scans column B for the word "Revenue" and adds the matching balances in column C: $773,000.

Step 2, cost of goods sold. Cost of Goods Sold ($316,930) plus Freight-In ($15,460) gives $332,390. Freight-In belongs here because shipping costs to receive goods are part of what those goods cost.

Step 3, gross profit. =H4-H5 gives $440,610.

Step 4, operating expenses. Five accounts carry the "Operating expense" label. Together they total $224,170.

Step 5, net income. =H6-H7 gives $216,440.

Why the dollar signs? The ranges are absolute references, so they stay fixed if you copy the formula from H4 down to H5 and H7 and then change only the category text. If you want to know more about when to lock a reference, see absolute cell references in Excel. The category text must match column B exactly, apart from upper and lower case: "Operating expenses" with an s finds nothing and returns 0.

Debit and credit columns, and a contra revenue account

The second exercise works from Juniper Landscaping's adjusted trial balance, which has separate Debit and Credit columns and still includes the balance sheet accounts. Two ideas matter here.

First, the balance sheet rows look after themselves. Cash is labeled "Asset" and Notes Payable "Liability", so a sum that asks for "Revenue" or "Operating expense" never picks them up.

Second, revenue has a normal credit balance, but one revenue-category account, Sales Returns and Allowances, has a debit balance. It is a contra revenue account: it reduces revenue. So net revenue is the revenue credits minus the revenue debits:

=SUMIF($B$4:$B$23,"Revenue",$D$4:$D$23)-SUMIF($B$4:$B$23,"Revenue",$C$4:$C$23)

That is $257,640 + $194,360 − $4,520 = $447,480. Expenses work the other way: debits minus credits. Cost of goods sold is $189,840, gross profit $257,640, and operating expenses $108,480, which leaves operating income of $149,160.

This statement adds one more section below operating income: other income and expenses, for items outside day-to-day operations. Interest Revenue of $13,560 less Interest Expense of $4,520 gives other income, net, of $9,040. Net income is operating income plus that figure, =H8+H9, or $158,200.

Two years side by side

The third exercise puts Bluebird Veterinary's 2025 and 2026 balances in one list with a Year column, and asks for both years plus the percentage change. Each total now has two conditions, so SUMIFS is the right tool:

=SUMIFS($D$4:$D$17,$C$4:$C$17,"Revenue",$A$4:$A$17,H$3)

The year comes from the header cell H3 instead of being typed. The reference H$3 locks the row but not the column, so when you copy the formula one column to the right it becomes I$3 and picks up 2025 from the header there.

The change formula is =(H4-I4)/I4: the increase divided by the earlier year. Revenue grew by $75,235 on a 2025 base of $385,765, or 19.50%. Net income grew faster than revenue because operating expenses rose by only 11.94%.

Common mistakes

These are the slips the three exercises recognize, with what each one does to the numbers.

Counting cost of goods sold twice

If the operating expenses formula adds cost of goods sold to the operating expense rows, H7 becomes $556,560 for Pine Street Dental. Cost of goods sold has already come off in gross profit, so it is subtracted a second time, and net income turns into a loss of $115,950. Total only the "Operating expense" rows.

Skipping gross profit

Net income written as revenue less operating expenses, =H4-H7, gives $548,830. It leaves out cost of goods sold entirely. Start from gross profit in H6.

Ignoring the returns account

Summing only the revenue credits gives Juniper $452,000 of revenue instead of $447,480. The $4,520 of sales returns sits in the Debit column, and it has to come off.

Stopping at operating income

If net income simply repeats operating income, Juniper's statement shows $149,160 and drops the $9,040 of other income, net. Net income includes everything, so add H9.

Locking the year header completely

Writing the year as $H$3 locks the column too. When you copy the formula to column I it still asks for 2026, so both columns show the same amounts and every change is 0%. Use H$3, which lets the column move.

Dividing the change by the later year

Percentage change is measured against the starting point. =(H4-I4)/H4 divides by 2026 and shows revenue growth of 16.32% instead of 19.50%. Divide by the 2025 amount in column I.

How to check your statement

• Reconcile to the list. Revenue plus cost of goods sold plus operating expenses should account for every income statement account. For Pine Street Dental, $773,000 + $332,390 + $224,170 covers all nine rows.
• Try a change. Edit one balance and watch net income move by the same amount, in the right direction.
• Read the signs. Gross profit above revenue, or negative operating expenses, means a subtraction is the wrong way round.

MO-220 practice: preparing financial statements

This lesson covers the "Prepare financial statements" objective in the "Prepare and analyze financial statements" area of the MO-220 exam outline, which is about using formulas to aggregate data into statements. Accounting Exercises is not affiliated with Microsoft or Certiport. As MO-220 practice, keep in mind that MO-220 task instructions describe the purpose, such as "total the balances for each category", rather than naming a function. The exercises here are written the same way, so you practice choosing between SUMIF, SUMIFS and plain arithmetic yourself.

You work in a spreadsheet in your browser, using the same formulas you would write in Microsoft® Excel® workbooks, and each Check runs your formulas and compares the resulting values with the answer. For the conditional sums on their own, see SUMIFS totals by account in Excel. For the same statement worked by hand, see income statement practice. The next step is the other main statement, the balance sheet in Excel. The free exercise below gives you new numbers each time.