Yes. In Excel, record cash receipts and payments in a transaction log, use SUMIFS to total them by date and category, then calculate net cash flow and add it to opening cash. This produces a practical cash flow statement that updates as you add transactions.
What a cash flow statement shows
A cash flow statement summarizes cash that came into and went out of a business during a defined period. It separates activity into operating, investing, and financing sections, then reconciles the period’s net cash movement to the cash balance at the end. For a useful report, the statement must use actual cash receipts and payments, not simply sales and expenses recorded on an accrual basis.
That distinction matters because profit and cash flow are not the same. A sale made on credit may increase revenue and profit before the customer pays. Depreciation reduces accounting profit but does not itself use cash in the period. A cash flow report helps explain when money moved, while an income statement measures revenue and expenses under the accounting method being used.
Choose a direct or indirect format
The direct method lists major types of cash received and paid, such as customer receipts, supplier payments, wages, and rent. It is easy to understand and works naturally with a transaction-based Excel workbook: categorize each cash movement, then total the categories.
The indirect method starts with net income and adjusts for noncash items and changes in working capital, among other items. It is useful when reconciling accounting profit to operating cash flow, but it requires reliable accounting balances and more judgment about classification. The worked example below uses the direct approach for clarity. Formal reporting requirements depend on the applicable accounting framework and an organization’s circumstances, so a spreadsheet layout alone does not establish compliance.
Set up the transaction log
Use one row per cash movement. In a new worksheet, put these headings in row 1: Date, Description, Category, Cash In, and Cash Out. Enter dates in column A, descriptions in B, categories in C, incoming amounts in D, and outgoing amounts in E. Enter each amount as a positive number in the appropriate cash column; leave the other cash column blank or enter zero. Do not enter the same transaction as both an inflow and an outflow.
For example, a customer payment belongs in Cash In and might be categorized as Operating. A payment for equipment belongs in Cash Out and might be categorized as Investing. Loan proceeds are generally a financing inflow, while repayment of loan principal is generally a financing outflow. Interest, taxes, and some other items can be classified differently under different reporting rules, so use the treatment required for the report you are preparing.
- Use consistent category names: a spelling difference can cause a formula to omit a row.
- Record cash transactions rather than invoices that have not yet been paid or collected.
- Keep the transaction log as a single continuous list, with no subtotal rows inside the data.
- Check that dates are real Excel dates, not text that only looks like a date.
In Microsoft Excel for Microsoft 365 on Windows, select the headings and data and press Ctrl+T to create a table; confirm that the table has headers. On Mac, use Command+T. A table expands as transactions are added, which is convenient for data entry and filtering. The formulas below use fixed ranges for readability; if the log grows beyond row 100, extend those ranges or adapt the formulas to your table.
Build the statement layout
On a summary area or a separate worksheet, enter the report start date in H2 and end date in H3. Put Operating, Investing, and Financing in G6, G7, and G8. Use H for cash received, I for cash paid, and J for net cash flow. Add labels for Net change in cash, Opening cash, and Ending cash below the category totals. The same start and end dates must be used for every section so the statement covers one consistent period.
The formula setup assumes dates in A2:A100, categories in C2:C100, cash received in D2:D100, cash paid in E2:E100, the category label in G6, and report dates in H2:H3. With those cells in place, enter the following formula in H6 to total receipts for the category named in G6 and the selected date range. For the example below, the Operating total is 8,000.
With the transaction columns and summary cells just described, enter this formula in H6:
=SUMIFS($D$2:$D$100,$C$2:$C$100,$G6,$A$2:$A$100,">="&$H$2,$A$2:$A$100,"<="&$H$3)
With the same date, category, and summary layout, enter this formula in I6 to total payments. In the example, Operating payments total 4,200.
=SUMIFS($E$2:$E$100,$C$2:$C$100,$G6,$A$2:$A$100,">="&$H$2,$A$2:$A$100,"<="&$H$3)
With receipts in H6 and payments in I6, enter this formula in J6 to calculate category net cash flow. In the example, it returns 3,800.
=H6-I6
Copy H6:J6 down through row 8. The category reference changes to G7 and G8, while the dollar signs keep the transaction ranges and report dates fixed. For the example, the three net amounts are 3,800 for Operating, negative 2,500 for Investing, and 2,800 for Financing.
With the three section totals in J6:J8, enter this formula in J9 for the net change in cash. The example returns 4,100.
=SUM(J6:J8)
With opening cash entered in H10 and net change in J9, enter this formula in H11 for ending cash. If H10 is 10,000, the result is 14,100.
=H10+J9
Worked example and arithmetic check
Suppose the report covers January and the transaction log contains these seven movements: customer payment of 8,000; wages paid of 3,000; equipment purchased for 2,500; loan proceeds of 4,000; rent paid of 1,200; loan principal repaid of 500; and an owner draw of 700. Assume the first, second, and fifth items are Operating; the equipment purchase is Investing; and the loan proceeds, principal repayment, and owner draw are Financing.
- Operating: 8,000 received less 3,000 in wages and 1,200 in rent equals 3,800 net cash provided.
- Investing: no cash received less 2,500 for equipment equals negative 2,500.
- Financing: 4,000 received less 500 of principal and 700 of owner draws equals 2,800.
- Net change: 3,800 minus 2,500 plus 2,800 equals 4,100.
- Ending cash: opening cash of 10,000 plus the 4,100 increase equals 14,100.
This arithmetic provides a quick check on the formulas. It is illustrative, not a claim that every business should classify each type of payment identically. Confirm categories and reporting treatment against the organization’s accounting policy and applicable requirements; ask a qualified accounting professional when uncertain.
Reconcile the result to real cash balances
A calculated ending balance should agree with the relevant cash records after considering the scope of the report. Compare it with bank statements and cash-account records for the same date, and investigate differences such as unrecorded transactions, duplicate entries, bank fees, timing differences, or transactions entered in the wrong period. If the report covers several bank accounts, make sure the opening balance and transaction log cover the same accounts.
Reconciliation is more than checking that a formula produces a number. A spreadsheet can calculate totals correctly while the source data is incomplete or misclassified. Keep supporting records, note the period covered, and make clear whether the report includes only bank cash or also other cash accounts. For formal financial statements, use the required accounting process and review rather than relying solely on a worksheet.
Common formula and classification problems
- Dates stored as text: Date-based criteria may not include text entries as expected. Convert the entries to actual Excel dates and check the start and end dates.
- Inconsistent category labels: “Operating” and “Operating ” with a trailing space may not match. Use a consistent list of categories and inspect unexpected zero totals.
- Wrong sign convention: The example stores payments as positive amounts in Cash Out and subtracts them in the net formula. If payments are already entered as negative numbers, subtracting them again would reverse the intended result.
- Range too short: Formulas limited to row 100 will ignore transactions below row 100. Expand the ranges or design the workbook so new entries are included.
- Mixing accrual activity with cash activity: An unpaid invoice is not a cash receipt or payment in this transaction-log approach. Include it only when cash actually moves, unless you are preparing an indirect-method reconciliation from accounting records.
- Wrong period boundaries: Check that H2 is on or before H3 and that both dates match the intended reporting period. The formulas include transactions dated on both boundary dates.
Make the workbook easier to maintain
Give the transaction and summary areas clear labels, use a consistent date format, and keep raw entries separate from calculated totals. If you use Excel’s table feature, apply filters to review a period or category without deleting rows. Protecting formula cells can reduce accidental edits, but it does not replace keeping a backup or checking the data.
For a monthly report, you can change H2 and H3 to the desired month’s first and last dates; the summary formulas then recalculate for that period. To compare months, create one summary column or worksheet per period, or build a more detailed report using a date-based layout. Avoid copying a prior month’s totals as fixed values if you want the report to update from the transaction log.
Frequently Asked Questions
Can Excel create a cash flow statement automatically?
Excel can total and organize the transactions once they are entered and categorized, but it cannot know the correct classification or detect every missing transaction on its own. Review the source data and reconcile the result.
What is the basic cash flow statement formula?
For a period, net cash flow is cash received minus cash paid. Ending cash is opening cash plus the net change in cash, provided the balances and transactions cover the same accounts and period.
Should expenses be entered as negative numbers?
Not in the example layout: payments are positive values in a separate Cash Out column, and the statement subtracts them. A different design can use signed amounts, but do not mix conventions or subtract negative payments a second time.
Can I use this for personal cash flow?
Yes. You can adapt the categories to income, bills, savings transfers, and other personal cash movements. Keep transfers between your own accounts from being counted as income or spending if the goal is to understand your overall cash position.
Why does my SUMIFS total show zero?
Check that the category text matches exactly, the dates are real Excel dates within H2 and H3, and the amounts are in the column referenced by the formula. Also confirm the transaction lies within the formula’s row range.
Related
- Can You Capitalize Words in Excel? Methods That Work
- Can You Align Shapes in Excel? A Practical Guide to Layout
- Can Power Automate Refresh Excel Power Query?
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Highlight Upcoming Dates in Excel with a Formula RuleTo highlight dates coming up in the next 30 days, select the date cells and create a formula-based…
- IFERROR With IF in Excel: Syntax, Examples, and TipsTo use IFERROR with IF in Excel, put the IF function inside IFERROR: =IFERROR(IF(B2=0,"No quantity",A2/B2),"Check values"). IF makes…
- Excel Conditional Formatting: Create a Formula RuleTo create a formula-based conditional formatting rule in Excel for Microsoft 365 on Windows, select the cells to…
- Extract Text From a Cell in Excel With Simple FormulasTo extract part of a cell’s text in Excel, use a formula that matches what you need: for…
- Excel Formula for Finding Matches: A Practical GuideTo find the position of a value, use MATCH with exact-match mode. With the value to find in…
- Excel XLOOKUP and Power Query: How They Work TogetherExcel XLOOKUP and Power Query work together, but they do different jobs: Power Query imports, cleans, and combines…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.