How To Make An Expense Spreadsheet: A Practical Business Guide

Create fields for date, ID, company, purpose, category, type, amount, currency and source. Keep amounts nonnegative and let Expense or Refund choose the sign. Add monthly totals with matching SUMIFS ranges. Check the result using fictional purchases, a refund and a row outside the month. Protect formulas against accidental edits and preserve a reviewed copy with private source records.

How to make an expense spreadsheet: create the columns

Begin with a blank sheet rather than a dashboard. A dashboard needs records to summarise. Put the header in the first row and use one row for each event below it. Avoid merged cells in the input area; every row should hold the fields needed to understand that event after sorting.

The supplied pack uses this layout so its formulas can be checked precisely:

Keep company, project and category separate. One combined text label makes it hard to select just a company or compare a category across projects. A distinct payment account also helps when matching the register to statements.

Add date and amount validation

Require a true date and a nonnegative numeric amount. Offer Expense and Refund as the permitted types. Use a clear review-status list so the same state does not appear under several spellings. Treat validation as an input aid, then check pasted data as well.

The signed calculation follows a simple rule: Expense returns the amount, Refund returns its negative, and an unknown type needs an error. In a small explanation formula, that is =IF(H2="Expense",I2,IF(H2="Refund",-I2,NA())). The supplied workbook adds guards for dates, required identifiers and reporting currency.

Do not use zero for an invalid type. That can make the final total look complete while dropping an unresolved row. Likewise, do not hide all errors with a blanket zero-returning formula. Correct the input or keep the unresolved state visible.

Enter a worked dataset

Use fictional records before entering real costs. Give company A January purchases of 120 and 80, then a supplier refund of 20. Give company B a separate January cost of 70. Add a company A cost of 999 dated 1 February.

The January company A result should be 180. Company B should be 70. The February cost must stay outside January. This tiny dataset checks more than a single grand total because it exercises the sign, entity and date criteria separately.

Use Office for the 120 purchase and 20 refund, and Travel for the 80 purchase. A's Office total should be 100 and Travel 80. Those add to 180. Keep the expected results beside the example so you can repeat the check after an import or range edit.

Build monthly and category totals

Use SUMIFS to add the signed amount range with paired date and company criteria. If the start date is in B2, the exclusive end in C2 and selected company in D1, the pack's pattern is:

=SUMIFS(Transactions!$N$2:$N$1001,
 Transactions!$A$2:$A$1001,">="&B2,
 Transactions!$A$2:$A$1001,"<"&C2,
 Transactions!$C$2:$C$1001,$D$1)

Every range starts and ends on the same rows. The end date is the first day of the next period, excluded. That makes the month boundary clear. Add category column G as another paired criterion when building a category total.

Check the actual cells referenced in your version. A copied formula can point to a nearby date or company cell while looking almost identical. Use the worked dataset to confirm the meaning, not just the formula's spelling.

Check missing receipts and duplicate IDs

Add an exception column rather than relying on cell colour. Check whether the source reference is blank, whether an ID occurs more than once and whether the status still needs review. A text result lets someone filter or export the open issues.

A duplicate warning is a prompt to inspect the source. Two separate purchases can have the same amount. One purchase can also be entered twice with different IDs. Use the date, source and account together before deciding what to remove or correct.

Keep a private receipt index with source ID, private reference, entry ID and review status. The source should be findable by intended reviewers without making receipts public. A filled link cell does not prove the document exists or that access is appropriate.

Protect, print and back up the sheet

Mark input cells clearly and protect formula columns against accidental edits. This is not encryption or a confidentiality boundary. Use file permissions and private storage for that separate need. Recheck protection after importing into another platform.

Set repeated print headers and inspect the page width. A long sheet can become unreadable when shrunk to one page. Use the separate writing forms for a paper-first process, and label their totals as manual.

The supplied capacity is rows 2 through 1001. Extend every related range and validation rule before adding records beyond it. Preserve the original worked file and a reviewed period copy. CSV is useful for input-row transfer, but it does not keep formulas or the workbook's structure.

Keep the input rules beside the file

Write a short Start Here note that states the sign rule, currency and row limit. List the known example results. A new person should be able to check the file without asking what a negative amount means. Keep the note in the workbook rather than only in a message that can be lost.

Review that note after a formula or range change. The instructions and the file should describe the same calculation.

FAQ: building the sheet

Why use a separate refund type?

It keeps the entered amount nonnegative while making its direction explicit. The signed formula subtracts supplier refunds from recorded expense. This avoids relying on a reviewer to infer meaning from a minus sign alone. Keep the original purchase and refund as traceable events instead of changing the earlier amount to conceal the return.

Can I use a simple SUM instead?

A simple sum can add a whole amount range, but it does not select company, category or reporting dates. Use it only when that scope is truly the question. SUMIFS makes the intended criteria explicit. Check the result with records from another company and another month so the boundary is demonstrated.

What should invalid input return?

Use a visible error or unresolved state that calls for review. Do not silently turn an unknown type, missing budget or unsupported currency into zero. A zero looks like a known amount. The supplied workbook uses guarded calculations and an exception column so incomplete inputs cannot masquerade as complete records with no cost.

Do I need separate files for every month?

The register can hold several months and select a period through date criteria. Keep the date boundaries clear and preserve reviewed period copies when needed. Separate live input copies can create conflicting records if the same event is entered twice. Decide which file owns the data and how reports refer back to it.

Sources and checks

Checked 15 October 2026. The examples are fictional and the arithmetic is original.

• IRS records: Business recordkeeping and supporting documents.
• Excel SUMIFS: Official multiple-criteria sum syntax.
• Sheets SUMIFS: Official Sheets multiple-criteria sum syntax.
• IRS books: Publication 583, December 2024 edition, Recordkeeping System section.

Related records and guides

• how to keep track of business expenses
• real estate agent expenses spreadsheet

Verdict on how to make an expense spreadsheet

How to make an expense spreadsheet is best learned by checking a small, varied dataset. Build the records first, verify the criteria and keep errors visible. A total is useful when its scope and sources are clear.