Cash Flow Chart in Excel: Build and Read One

To make a cash flow chart in Excel, put periods and cash figures in a table, calculate net cash flow and closing cash, then chart the monthly figures with a line or column chart. For a useful starting point, chart closing cash to see how the balance changes over time; chart cash inflows and outflows separately when you want to compare what is coming in with what is going out.

What a cash flow chart shows

A cash flow chart is a visual summary of cash entering and leaving a business, household, project, or account over time. It can show the net movement in each period, the cash balance at the end of each period, or the separate totals for money received and money paid. Those views answer different questions, so decide what you want to understand before choosing a chart.

Cash flow is about timing: when cash is actually received or paid. That is not always the same as revenue and expenses recorded under accrual accounting. For example, an invoice may count as revenue before a customer pays it. A cash flow chart based on bank receipts and payments will show the cash only when it arrives or leaves. Label the data clearly as actual cash, forecast cash, or a mixture, rather than implying the chart shows profit.

In Excel, the chart is linked to worksheet data. When you correct or update a figure in the chart’s source range, the chart can update too. This makes a spreadsheet practical for recurring monthly reporting, but it also means that a mistaken input or poorly chosen range can produce a misleading picture.

Set up a monthly cash flow table

For Microsoft Excel for Microsoft 365 on Windows, create a row for each month and use consistent columns. For a simple example, enter the following headers in row 1:

A: Month
B: Cash In
C: Cash Out
D: Net Cash Flow
E: Opening Cash
F: Closing Cash

Enter month names in column A and the period’s total receipts and payments in columns B and C. Use positive amounts for both cash in and cash out; the calculation will subtract the outflow. In E2, enter the opening cash balance for the first month. For illustration, suppose January begins with $5,000, receives $12,000, and pays out $9,500.

With cash-in values in B2:B13 and cash-out values in C2:C13, put this formula in D2 to calculate the first month’s net cash flow: =B2-C2. For the illustrative January figures, the result is $2,500 because $12,000 minus $9,500 equals $2,500. Copy the formula down column D for the remaining months.

With the January opening balance in E2 and net cash flow in D2, enter this formula in F2 to calculate January’s closing balance: =E2+D2. The result is $7,500: the opening $5,000 plus $2,500 of net inflow. The closing balance is a running total, not an additional cash receipt.

With January’s closing balance in F2 and February’s opening balance in E3, enter this formula in E3: =F2. It returns $7,500, carrying January’s close into February. Copy it down column E, and copy the closing-balance formula from F2 down column F. If your first period is not January, use the same sequence: enter the actual opening balance once, then carry each period’s close into the next period.

Choose the chart that answers your question

A line chart of closing cash is usually the clearest choice for answering, “How much cash will be available over time?” The line makes a rising or falling balance easy to spot, and a low point can flag a period that deserves closer review. It does not explain why the balance changed, so keep the underlying receipts and payments available.

A column chart of net cash flow is better for showing the direction and size of each period’s movement. Positive columns represent more cash in than out; negative columns represent more cash out than in. If the chart’s vertical axis crosses at zero, readers can distinguish inflow periods from outflow periods without relying on color alone.

To compare the components, chart cash in and cash out as two series in a clustered column chart. This makes their monthly sizes visible side by side, but does not show the running balance. A line chart and a component chart can be placed next to each other when a report needs to communicate both the cash position and its drivers. Avoid putting too many series on one chart: crowded labels and competing scales make it harder to interpret.

A waterfall chart can show how a starting amount changes through a sequence of inflows and outflows to a final amount. It is useful for explaining a single bridge, such as opening cash, several categories of movement, and closing cash. It is less suitable for showing a long monthly trend. In Excel, waterfall charts are available in supported desktop versions, including Microsoft 365; if the chart option is missing in Excel for the web or another version, use a line or column chart or open the workbook in desktop Excel.

Create a line chart in Excel

Arrange the periods in column A and closing cash in column F, with headers in row 1 and one period per row.
Select the period and closing-cash ranges, including their headers. If they are separated by other columns, select A1:A13, then hold Ctrl while selecting F1:F13 on Windows. On a Mac, use Command instead of Ctrl.
On the ribbon, choose Insert > Insert Line or Area Chart > Line. Excel inserts a chart using the selected data.
Select the chart title and replace it with a specific label, such as Monthly Closing Cash. Add a vertical-axis label or a note identifying the currency if readers might not know what the amounts represent.
Check that the horizontal axis lists the periods in chronological order and that the plotted values are closing cash, not net cash flow.

For a chart of net cash flow instead, select the period range in column A and the net-cash-flow range in column D, then use the same Insert path. A chart made from adjacent columns may include unwanted series, so check the plotted data and use Chart Design > Select Data if you need to adjust the range or series. The Chart Design tab appears when the chart is selected.

On Mac, the desktop ribbon offers comparable chart choices, though shortcuts differ: use Command rather than Ctrl for nonadjacent selections. Excel for the web supports common chart creation, but available chart types and formatting controls can vary. If a particular option or chart element is unavailable, use the desktop app for that edit.

Make the chart readable and honest

Use a title that states both the measure and the period. “Cash” is vague; “Forecast closing cash by month” tells readers what is plotted and signals that the figures are estimates. Include a currency label or a clear note such as USD, and make the date range visible. If the chart combines actual and forecast periods, distinguish them with a clear label or visual treatment and explain where the forecast begins.

Formatting should clarify rather than decorate. Use consistent number formats in the table and chart, show negative values clearly, and avoid excessive decimal places when the underlying information is rounded to whole dollars. If the chart includes both inflows and outflows, use distinct series colors and retain a legend. Do not rely on red and green alone to communicate meaning, since some readers have difficulty distinguishing those colors.

Be careful with the vertical axis. A line chart of balances that starts well above zero can make small changes look dramatic. A zero baseline is often a useful reference, but an axis range should still be chosen so the data can be read. Do not truncate or alter the scale simply to make a forecast look reassuring or alarming. If you use a second axis in a combination chart, label it prominently; otherwise, readers may compare values that are on different scales as if they were equivalent.

Actual cash flow versus a forecast

An actual cash flow chart summarizes recorded receipts and payments. A forecast chart estimates future timing and amounts, so its usefulness depends on assumptions such as expected customer payment dates, payroll dates, supplier terms, rent, taxes, and planned purchases. Keep assumptions visible in the workbook instead of burying them in chart labels. If possible, compare actual results with the forecast for each period and revise future estimates when new information arrives.

A monthly chart can conceal short-term cash pressure. A business might end a month with a positive balance but have a large payment due before a customer invoice is collected. Weekly or daily forecasting may be more informative when timing within the month matters. The right interval depends on the question and the quality of the available data; displaying more frequent periods is not automatically better if the source figures are uncertain.

A chart also cannot verify that every bank transaction has been captured. Reconcile the source figures to reliable records and investigate missing, duplicated, or misclassified transactions. In particular, transfers between accounts should not be counted as new cash for the organization as a whole if doing so would double-count the same money.

Common chart problems and how to fix them

The chart shows the wrong trend: Check whether you selected closing cash or net cash flow. A series of monthly changes is not the same as a running balance.
Months appear out of order: Use real Excel dates formatted to display as month names, or verify that text month labels are arranged chronologically. Alphabetical sorting puts April before February.
The chart includes blanks or totals: Check the selected range and remove summary rows or unused future periods if they distort the display. Keep a total row outside the chart range unless you deliberately want it plotted.
Negative amounts look like positive cash: Confirm that outflows are subtracted in the net-flow calculation and that the vertical axis includes values below zero where needed.
The chart stops updating: Check whether new rows fall inside the chart’s source range. Converting the source data to an Excel table with Insert > Table can make a growing dataset easier to manage, though you should still verify the chart range after adding periods.
The line has unexpected gaps: Look for blank cells, text stored in place of numbers, or formulas returning empty text. Correct the source data and review the chart’s handling of empty cells.

Keep the workbook maintainable

Separate inputs from calculations and presentation. One worksheet can hold imported or entered transactions, another can summarize monthly totals, and a chart sheet or dashboard can display the result. For a small workbook, separate sheets are not essential, but consistent labels and a clear source range make errors easier to find.

Document whether amounts include tax, which accounts are included, and how periods are defined. If several people update the file, protect formula cells from accidental edits while leaving input cells accessible. Keep a dated copy before making major structural changes. For recurring reports, verify that new data covers the intended date range and that the chart has not silently retained an old endpoint.

Frequently Asked Questions

How do I make a simple cash flow chart in Excel?
Set up columns for period, cash in, cash out, net cash flow, opening cash, and closing cash. Calculate net flow as cash in minus cash out, carry each closing balance into the next period, then insert a line chart for closing cash or a column chart for net flow.

Should I chart net cash flow or closing cash?
Use net cash flow to show whether each period added or used cash. Use closing cash to show the balance available at each period’s end. They are related but answer different questions, so a report may need both.

Can Excel make a cash flow forecast chart?
Yes. Enter forecast receipts and payments by period, calculate the projected net flow and closing balance, and chart the projected balance. Label the figures as a forecast, record the assumptions, and update them as actual information becomes available.

Why does my cash flow chart show a negative balance?
First check the source amounts, signs, formulas, and opening balance. If the figures are correct, the chart may be indicating that planned payments exceed available cash in that period. A spreadsheet highlights a timing issue; it does not determine what action is appropriate.

Can I use a waterfall chart for monthly cash flow?
You can use a waterfall chart to explain a sequence of changes between a starting and ending balance. For many consecutive months, a line chart of closing cash or a column chart of monthly net flow is generally easier to compare.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.