sodcalculator

10 Tech How-To

Income Statement in Excel: Step-by-Step Guide

Guide 10-52934 · Updated · Step by step

This guide walks you through building a professional income statement (profit and loss statement) entirely in Excel. You’ll set up a clear, formula-driven template that automatically calculates gross profit, operating income, and net income from your revenue and expense inputs. All you need is a basic understanding of Excel and your financial data.

What You’ll Need

  • A computer with Microsoft Excel (2016 or later, or Excel for the web)
  • Your company’s revenue and expense figures for the period (monthly, quarterly, or annual)
  • Basic familiarity with Excel: entering data, copying formulas, and formatting cells

Step-by-Step

  1. Open a new Excel workbook. Create two worksheets: name the first one “Inputs” and the second one “Income Statement”. The Inputs sheet will hold your raw numbers, and the Income Statement sheet will display the formatted report.
  2. Set up the Inputs sheet. In column A, list each line item you need: Revenue (Sales), Cost of Goods Sold (COGS), Operating Expenses (rent, salaries, utilities, marketing, etc.), Interest Expense, and Taxes. In column B, enter the dollar amounts for each. Use separate rows for each expense category so you can sum them easily.
  3. Create the Income Statement layout. On the Income Statement sheet, in column A, enter the following rows in order:
    • Revenue
    • Cost of Goods Sold
    • Gross Profit
    • Operating Expenses
    • Operating Income
    • Interest Expense
    • Income Before Taxes
    • Taxes
    • Net Income

    Leave column B empty for now—this is where formulas will go.

  4. Link the revenue and COGS. Click on cell B2 (next to Revenue) and type =Inputs!B2 (assuming your revenue is on the Inputs sheet in B2). Do the same for COGS: =Inputs!B3. This pulls the values from your Inputs sheet.
  5. Calculate gross profit. In cell B4 (Gross Profit), enter the formula =B2-B3. This subtracts COGS from revenue.
  6. Sum operating expenses. On the Inputs sheet, add a cell that sums all your operating expenses. For example, if expenses are in B4:B9, use =SUM(B4:B9). Then, on the Income Statement, link to that total (e.g., =Inputs!B10) in the Operating Expenses row.
  7. Calculate operating income. In cell B6 (Operating Income), enter =B4-B5. This is gross profit minus operating expenses.
  8. Subtract interest and taxes. Link Interest Expense from Inputs (e.g., =Inputs!B11). Then in cell B8 (Income Before Taxes), enter =B6-B7. Link Taxes (e.g., =Inputs!B12) and in cell B10 (Net Income), enter =B8-B9.
  9. Format the numbers. Select all the cells with dollar amounts (B2:B10). Go to the Home tab, click the Number Format dropdown, and choose “Currency” or “Accounting”. Accounting aligns the dollar signs and is standard for financial statements.
  10. Add labels and emphasis. Bold the main section headers (Revenue, Gross Profit, Operating Income, Net Income). Indent subcategories like Operating Expenses and Interest Expense by using the Increase Indent button in the Alignment group. This makes the statement easier to read.
  11. Add a period label. In cell D1 (or C1), type the period, e.g., “For the Year Ended December 31, 2025”. This is crucial for context.
  12. Test your formulas. Change a number on the Inputs sheet and verify the totals update automatically. If they do, your statement is working correctly.

Common Mistakes

  • Mixing up COGS and operating expenses. COGS are costs directly tied to producing goods or services (materials, direct labor). Operating expenses are indirect (rent, office supplies, marketing). Misclassifying them will make your gross profit and operating income inaccurate.
  • Hardcoding numbers instead of using formulas. Typing a value like 50000 instead of a formula means the statement won’t update when your inputs change. Always use cell references.
  • Forgetting to include all expenses. A common error is leaving out items like depreciation, interest, or taxes. Double-check your expense list against your accounting records.
  • Using the wrong sign convention. If you enter expenses as negative numbers, your formulas need to add them, not subtract. Keep all inputs positive and use subtraction in the formulas.
  • Not separating one-time items. For a clear picture, separate unusual gains or losses (e.g., selling equipment) from regular operating items. You can add a line for “Other Income/Expense” before taxes.

Tips

  • Use Excel Tables. Convert your Inputs data into a table (Insert > Table). This makes it easier to add new expense categories, and your SUM formulas will automatically expand to include them.
  • Add a comparative column. Add a second column for the previous period and calculate the dollar and percentage change. This turns your income statement into a powerful analysis tool.
  • Create a chart. Select the revenue and expense rows and insert a stacked column chart to visualize the relationship between costs and profit.
  • Protect the formulas. Once everything is set, you can lock the cells with formulas (Format Cells > Protection > Locked) and protect the worksheet. This prevents accidental overwrites.
  • Use consistent formatting. Apply the same number format and decimal places throughout. Use a single currency symbol and align all amounts to the right.

Troubleshooting

  • My formulas show #REF! errors. This usually means a referenced cell was deleted. Go to the Inputs sheet and check that all the cells you linked to still exist. Adjust the formulas to point to the correct cells.
  • The totals don’t update when I change inputs. Check that your workbook is set to automatic calculation. Go to Formulas > Calculation Options and select “Automatic”. If it’s already automatic, try pressing F9 to recalculate manually.
  • Numbers appear as #####. This means the column is too narrow. Double-click the column header border to auto-fit the width, or manually widen it.
  • My SUM formula omits new rows. If you added rows after the SUM range, the formula won’t include them. Use a SUM that covers the entire column (e.g., =SUM(Inputs!B:B)) or convert to an Excel Table.
  • The statement looks cluttered. Use the “Format Painter” to copy a clean format across rows. Also, consider using a separate section for “Other Income/Expenses” to keep operating items distinct.

Frequently Asked Questions

Q: Can I use this template for a personal income statement?
A: Yes, just replace revenue with your salary and other income, and list personal expenses instead of business ones. The structure is identical.

Q: How do I add a percentage column?
A: In a new column, divide each line item by total revenue and format as a percentage. For example, if revenue is in B2 and COGS in B3, use =B3/$B$2 and format as %. This shows each item as a percentage of sales.

Q: What’s the difference between gross profit and operating income?
A: Gross profit is revenue minus the direct cost of goods sold. Operating income is gross profit minus all operating expenses (like rent, salaries, marketing). It shows profit from core business operations.

Q: Should I include taxes in operating expenses?
A: No. Income taxes are typically shown separately after operating income, because they are not considered an operating cost. This is standard practice in financial reporting.

Q: Can I make this statement automatically pull data from other sheets?
A: Absolutely. Instead of linking to a single Inputs cell, you can use formulas like =SUM(Sheet2!B2:B10) to aggregate data from multiple sheets, or use Power Query to consolidate data from external sources.

Q: How often should I update the income statement?
A: It depends on your needs. Monthly statements are common for management review, while quarterly and annual statements are typical for external reporting. Update it as often as you need to make informed decisions.

Related

General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.

More in this bed

The whole Tech How-To bed →