An Excel accounting formulas cheat sheet starts with =SUM(range) for totals, a simple addition formula for running balances, and criteria-based formulas such as =SUMIFS(sum_range,criteria_range,criteria) for reports. The examples below are for Excel for Microsoft 365 on Windows and show how to total transactions, track invoices, calculate margins, and check a reconciliation, with the cell layouts and expected results stated for each formula.
Set up an accounting worksheet before adding formulas
Formulas are only as dependable as the data they reference. Keep one transaction per row, use a clear header for each column, and avoid inserting subtotals or blank separator rows in the middle of a data table. A practical transaction sheet might have Date in column A, Account in B, Amount in C, Description in D, and Status in E. Store dates as Excel dates and amounts as numbers, not as text containing currency symbols.
Use a consistent sign convention. For example, record money received as a positive amount and payments as a negative amount. That makes a net total a simple sum and reduces the chance of reversing a subtraction. If your bookkeeping process uses separate debit and credit columns, keep those columns distinct and calculate their difference deliberately rather than mixing conventions.
Format amounts as Currency or Accounting using Home > Number. Formatting changes how a value looks; it does not change the underlying number. A value displayed as $125.00 can still be used in addition, comparisons, and other calculations. A number stored as text may look similar but will not behave reliably in every formula, so check the cell alignment, use Excel’s error indicators, or convert imported values to numbers before relying on a report.
Quick totals and running balances
For a straightforward total, SUM is the standard starting point. With transaction amounts in C2:C100, where receipts are positive and payments negative, enter =SUM(C2:C100) in C101. If the entries add to 1,250.00, the result is 1,250.00. The range can include empty cells, which SUM ignores. Keep the total outside the transaction rows so it does not get included accidentally when the source range expands.
A running balance shows the balance after each transaction. Suppose the opening balance is in D1 as 500.00, and the first transaction is in C2 as -75.00. In D2, enter =D1+C2; the result is 425.00. Copy the formula down column D, so each row adds its transaction amount to the balance above. If the next transaction in C3 is 200.00, D3 returns 625.00. Confirm that the opening balance is entered once and that copied formulas continue to refer to the previous row rather than to a fixed balance.
When you filter a transaction list and want a subtotal for only the displayed rows, SUBTOTAL can help. With amounts in C2:C100, enter =SUBTOTAL(9,C2:C100) in a cell outside the list. The 9 specifies SUM; rows removed by an AutoFilter are excluded. The 9 version can still include rows hidden manually. Use =SUBTOTAL(109,C2:C100) instead if you also want manually hidden rows excluded. If the visible transactions total 860.00, the filtered subtotal is 860.00. Avoid placing this result inside the range it totals.
Find totals by account, date, or category
SUMIFS adds values only when one or more conditions match. This is useful for totals by account, customer, department, or reporting period. Assume dates are in A2:A100, account names in B2:B100, and amounts in C2:C100. To total Rent entries dated in calendar year 2026, enter =SUMIFS(C2:C100,B2:B100,"Rent",A2:A100,">="&DATE(2026,1,1),A2:A100,"<"&DATE(2027,1,1)). If two qualifying monthly rent payments are 1,200.00 each, the result is 2,400.00.
The less-than date for the following year is intentional: it includes every date in 2026, even if some entries contain times as well as dates. In a flexible report, put the account name and start and end dates in cells, then use those cells as the criteria instead of typing fixed values into the formula. This makes it easier to change the reporting period without editing the formula itself.
For one condition, SUMIF can be shorter. With account names in B2:B100 and amounts in C2:C100, enter =SUMIF(B2:B100,"Rent",C2:C100) to total every Rent amount. SUMIFS is often more convenient even for a single condition because you can add more criteria later. Check spelling and extra spaces in category names: Rent and Rent followed by a space may be treated as different text.
Count invoices and flag overdue items
Counting records can answer questions that a monetary total does not, such as how many invoices remain unpaid. If invoice statuses are in D2:D100, enter =COUNTIF(D2:D100,"Unpaid") in a summary cell. If three cells contain exactly Unpaid, the result is 3. COUNTIF is not case-sensitive, but the criterion still needs to match the intended wording. A dropdown list for status entries can reduce spelling variations.
You can flag an unpaid invoice based on its due date. Assume the due date is in E2 and the status is in F2. In G2, enter =IF(AND(E2<TODAY(),F2<>"Paid"),"Overdue","Current"). With a due date of October 15, 2026, and a status other than Paid, the result on November 2, 2026 is Overdue. TODAY updates with the current date, so this formula is a live flag, not a permanent record of what was overdue at a past date. For a saved period-end report, use a fixed reporting date in a cell and refer to it instead of TODAY.
Make sure due dates are real Excel dates and that the formula is filled down for every invoice row. A blank due date can produce an unintended overdue flag because Excel treats blank values like zero in some comparisons. If blank dates are possible, add an explicit blank check to the logic or require a due date before an invoice can be marked open.
Calculate margins and round currency deliberately
Gross margin is gross profit divided by sales. Assume sales are in B2 and cost of goods sold is in C2. Enter =IFERROR((B2-C2)/B2,0) in D2. If sales are 1,000.00 and costs are 650.00, the result is 0.35, or 35% when D2 is formatted as Percentage. IFERROR returns zero if sales are zero and the division would otherwise produce an error. In a report, you may prefer a blank or a note for zero sales rather than displaying zero margin; choose the presentation that makes the situation clear.
ROUND is useful when a calculation should be expressed to a set number of decimal places. Assume B2 contains a taxable amount of 137.45 and C2 contains an illustrative rate of 8.25%. Enter =ROUND(B2*C2,2) in D2. The unrounded product is 11.333625, so the formula returns 11.33. This is a spreadsheet arithmetic example, not tax guidance: actual tax calculations and rounding rules depend on the applicable requirements and transaction details. Do not round every intermediate calculation automatically; rounding at different stages can cause small differences in a final total.
Excel stores many decimal values in binary floating-point form, so a displayed 0.00 difference does not always mean two underlying values are mathematically identical. For comparisons that need a currency tolerance, consider comparing rounded values or checking whether the absolute difference is smaller than a chosen threshold. Document the threshold and use a consistent rule in the workbook rather than hiding unexplained discrepancies.
Reconcile two totals and investigate differences
A reconciliation formula can show whether two independently calculated totals agree. Suppose the bank statement total is in B2 and the worksheet’s cash total is in C2. In D2, enter =B2-C2. If both values are 4,825.60, the result is 0.00; if the worksheet is 25.00 higher, the result is 25.00. Label the result Difference and format it as currency so that a nonzero amount is visible.
A zero difference is a useful check, but it does not prove every entry is correct. Two errors can offset each other, or the same transaction can be missing from both sides. If the difference is not zero, check the statement period, opening and closing balances, duplicate entries, omitted transactions, sign convention, and whether pending transactions are treated consistently. Keep a note of any timing differences rather than changing a number simply to make the check equal zero.
For bigger reconciliations, use a consistent transaction identifier where possible and compare the two source lists systematically. Sorting and filtering can help locate unmatched entries, while conditional formatting can draw attention to blanks or unusual values. A formula is a control aid, not a substitute for confirming the source documents and deciding why a difference exists.
Choose formulas, tables, and reports that fit the task
Use the simplest formula that answers the question. SUM is suitable for a total; SUMIF or SUMIFS is better for totals that depend on categories or dates; COUNTIF is for counting matching entries; and IF is for returning a label based on a condition. A complicated formula is not automatically more accurate. Clear headings, consistent source data, and visible checks often make a workbook easier to review than a single dense formula.
Convert a transaction range to an Excel Table with Insert > Table, or use Ctrl+T on Windows, after confirming that the range has headers. Tables expand as new rows are added and can make formulas and filters easier to manage. Microsoft 365 supports structured references in table formulas, though the examples here use ordinary cell ranges because they are easy to follow. If you copy a formula down, verify the references: relative references shift, while references with dollar signs such as $B$2 stay fixed when copied.
For larger summaries, a PivotTable can group and total records without writing a separate formula for every category. Select a source cell and choose Insert > PivotTable, then assign fields to Rows, Columns, Values, and Filters as needed. PivotTables are useful for exploration, while formulas can be preferable when you need a fixed, directly visible calculation in a report. Refresh a PivotTable after source data changes. Do not assume that a PivotTable or formula validates the original bookkeeping entries.
Common formula problems and practical safeguards
- Numbers stored as text: Imported amounts may look numeric but fail to total as expected. Check the source and convert the values to numbers before relying on the result.
- Ranges that stop too early: A formula ending at row 100 will not include a new entry in row 101. Excel Tables or carefully maintained ranges can reduce this risk.
- Mixed signs: If payments are sometimes entered as positive and sometimes negative, net totals can be misleading. Choose and document one convention.
- Errors hidden with IFERROR: IFERROR can make a report look clean while concealing a broken reference or unexpected text. Use it only when the fallback result is meaningful, and investigate errors during setup.
- Dates interpreted incorrectly: Regional settings affect how typed dates are read and displayed. Use actual date values and check the displayed date, rather than assuming every imported date was parsed as intended.
In Excel for Microsoft 365 on Windows, formulas normally use commas between arguments as shown here. Some regional configurations use a different list separator, which can affect how a formula must be entered locally. On Mac, the same core worksheet functions are available, although keyboard shortcuts and some ribbon details can differ. Excel for the web also supports common functions such as SUM and SUMIFS, but certain desktop workflow controls may be arranged differently. If a workbook is shared across versions, avoid relying on newer functions unless every user’s Excel version supports them.
Accounting worksheets may contain confidential financial information. Limit access to the workbook, store it in an approved location, and keep source records and backups according to your organization’s process. Worksheet protection can help prevent accidental edits to formula cells, but it is not a substitute for access controls or a secure storage policy.
Frequently Asked Questions
What is the most useful Excel formula for accounting?
SUM is a common starting point because it totals transaction amounts. SUMIFS is especially useful when a report needs totals by account, date range, customer, or another condition.
How do I calculate a running balance in Excel?
Put the opening balance in a cell, then add each row’s signed transaction amount to the previous balance. For example, with the opening balance in D1 and the first transaction in C2, use =D1+C2 in D2 and fill it down.
Can Excel automatically calculate an accounting balance?
Yes. A formula can update a running balance when transaction amounts change, provided the opening balance, sign convention, and transaction range are correct. It cannot independently confirm that all transactions were recorded accurately.
How do I sum expenses for one account only?
Use SUMIF for one condition or SUMIFS for several. Keep account names consistent, and use a date criterion as well if the total should cover only a particular reporting period.
Why does my Excel total not match my calculator?
Check for numbers stored as text, omitted rows, duplicate entries, hidden filters, inconsistent signs, and rounding at different stages. Also confirm that the workbook and calculator are using the same values and reporting period.
Are Excel formulas a replacement for accounting software?
Not necessarily. Excel can support calculations and analysis, but it does not by itself ensure complete records, audit trails, access controls, or compliance with a particular accounting process. Choose tools and review procedures appropriate to the organization and its requirements.
Related
- Power Query Split Cell Into Rows: Steps and Examples
- Power Query Editor: How to Split Text into Multiple Columns
- Power Query Union Tables: How to Append Queries
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- XLOOKUP Excel Version: Compatibility and AvailabilityXLOOKUP is available in Microsoft 365, Excel 2021, and Excel 2024, as well as Excel for the web…
- Copy Formulas in Excel Across Rows: A Simple GuideTo copy a formula horizontally, select its cell and drag the small fill handle at the lower-right corner…
- Excel Vertical Lookup: VLOOKUP, XLOOKUP, and TipsAn Excel vertical lookup searches down the first column of a table for a value, then returns related…
- Excel Let Text Overflow? What Happens to Cell TextYes. In Excel for Microsoft 365 on Windows, text can extend visually past a cell’s right edge when…
- Excel MAX Function: Find the Highest Value in a RangeTo find the largest number in Excel, enter a formula such as =MAX(B2:B10) in an empty cell, replacing…
- Excel IFS List: Syntax, Examples, and Common FixesExcel’s IFS function checks conditions in order and returns the result paired with the first condition that is…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.