Balance Chart in Excel: Track an Account Over Time

To create a balance chart in Excel, list transaction dates and signed amounts, calculate a running balance beside them, then select the date and balance columns and insert a line chart. This shows how an account balance changes over time, making increases, withdrawals, and trends easier to spot than they are in a transaction list. The steps below are for Microsoft Excel for Microsoft 365 on Windows.

What You’ll Need

Excel for Microsoft 365 and a worksheet containing transaction dates and amounts.
An opening balance, if the chart should include money already in the account before the first listed transaction.
Consistent transaction signs: enter money added as a positive amount and money taken out as a negative amount.

This guide treats a balance chart as a chart of an account balance over time. You can use the same setup for a cash balance, project hours remaining, inventory on hand, or another value that changes through dated additions and deductions. For inventory, for example, receipts are positive and items used or sold are negative.

Step-by-Step

Set up the transaction table. In a blank worksheet, enter Date in A4, Description in B4, Change in C4, and Balance in D4. Put your opening balance in D2, and label C2 Opening balance if you want to make the value clear. Enter one transaction per row, starting at row 5. For example, put dates in column A, short explanations in column B, and the signed transaction amounts in column C. Keep the column headings in one row and avoid blank rows within the transaction list. This arrangement gives Excel a clear sequence to calculate and chart.
Enter a small sample or check your existing data. For a worked example, assume D2 contains 1,000. Enter four transactions in C5:C8: 250, -80, -120, and 300. The first amount adds to the account; the next two reduce it; the last adds to it. Put a valid date in A5:A8 for each transaction. The dates should represent the order in which the balance changed. If you already have a ledger, use its real dates and amounts instead of the sample values, and keep the sample calculation in mind as a check.
Calculate the first running balance. With the opening balance in D2 and signed transaction amounts in C5 downward, enter =$D$2+SUM($C$5:C5) in D5. The dollar signs keep the opening balance and first transaction row fixed where needed, while the final row reference expands as you copy the formula. With the example values, D5 returns 1,250 because 1,000 plus 250 equals 1,250. Press Enter and confirm that the result is a number, not an error or text.
Fill the formula down the balance column. Select D5 and drag the fill handle, the small square at the cell’s lower-right corner, down to the last transaction row. You can also copy D5, select the remaining cells in column D, and paste. In the example, the subsequent balances are 1,170, 1,050, and 1,350. Check the arithmetic: each balance equals the balance above it plus the current transaction. Do not fill the formula far below your actual data unless you deliberately want extra rows; empty transaction rows can otherwise produce repeated balance values.
Check the dates and order. Make sure every transaction has a date and that dates are stored as Excel dates rather than text. Sort the entire table by Date from oldest to newest if the transactions are out of order. Select the table before sorting so that descriptions and amounts remain attached to their dates. If two transactions occur on the same day, put them in the order they happened, or add a time or sequence number to distinguish them. The balance is cumulative, so a different order can change the path shown even when the final balance is the same.
Select the chart data. Select the date cells in A5:A8, then hold Ctrl and select the matching balance cells in D5:D8. Use the actual last row of your data in place of row 8. On a Mac, hold Command while selecting the second range. Select only the transaction rows, not the opening-balance label or blank rows. The chart needs dates for its horizontal axis and running balances for its vertical values; descriptions and individual transaction changes are useful in the ledger but are not needed for this basic balance trend.
Insert a line chart. On the ribbon, choose Insert > Insert Line or Area Chart > 2-D Line. A line chart is a practical choice because it emphasizes changes across dates. Excel inserts the chart on the worksheet. If it appears blank or its axes look wrong, see Troubleshooting below; the most common causes are selecting the wrong ranges or having dates stored as text. On Excel for Mac, use the Insert tab and choose a line chart from the chart options. Excel for the web also offers basic line charts, although some formatting controls may be arranged differently.
Add a clear title and labels. Select the chart, then choose Chart Design > Add Chart Element > Chart Title > Above Chart. Click the title in the chart and type a useful name, such as Account balance by date. To label the axes, choose Chart Design > Add Chart Element > Axis Titles and add a horizontal title such as Date and a vertical title such as Balance. These labels help readers understand what the line measures, especially if the chart is copied into a report or presentation.
Format the balance axis as money when appropriate. Right-click a value on the vertical axis and choose Format Axis. In the Format Axis pane, open Number and select Currency or Accounting, then choose an appropriate number of decimal places and symbol. For a balance that is not money, choose a suitable number format instead. Avoid displaying more decimals than your data supports. If labels are crowded, widen the chart or reduce the number of displayed decimal places rather than shrinking the text until it is hard to read.
Review the chart against the ledger. Confirm that the earliest date is at the left, the latest date is at the right, and the final plotted point matches the last running balance. In the sample, the chart ends at 1,350. Check that a withdrawal makes the line fall and a deposit makes it rise. If the worksheet will keep growing, you can convert the transaction range into an Excel table by selecting it and choosing Insert > Table. A table can make it easier to add and maintain records; still check that the chart includes new rows as you add them.

The example is illustrative and only demonstrates spreadsheet arithmetic. For an actual account, reconcile the amounts and dates against the relevant records. A chart displays the values in your worksheet; it does not confirm that the source data is complete or correct.

Common Mistakes

Entering every transaction as positive. A running balance will rise even when money leaves the account. Record deposits as positive and withdrawals as negative, and check the sign before copying data from another system.
Charting transaction amounts instead of balances. The Change column shows the size of each deposit or withdrawal, while the Balance column shows the accumulated amount after each transaction. For a balance-over-time chart, use the Balance values.
Leaving transactions out of chronological order. A line chart connects points in the selected row order. If the dates are unsorted, the line can jump backward and forward in time, creating a misleading picture. Sort the complete table, not just the date column.
Including headings or blank cells unintentionally. Excel may interpret a heading as a data point or add extra categories if the selection is inconsistent. Select matching date and balance rows only, and make sure each plotted date has a corresponding balance.
Forgetting the opening balance. If transactions begin after an account already has money in it, starting the running balance at zero understates every point. Put the correct starting value in D2. If the account truly starts at zero, enter 0 there.

Tips

Make the line easier to scan. Resize the chart so the date labels and currency values are readable. Use a descriptive title and keep gridlines light enough that they do not compete with the data.
Choose a useful date range. If there are many daily records, a chart covering several years may look crowded. Filter the table to a period of interest or create a separate summary at weekly or monthly intervals. Make it clear when points represent period-end balances rather than every transaction.
Keep the raw transaction record intact. Use one row per transaction and avoid replacing the signed amount with a manually calculated balance. A separate Balance column lets you audit the arithmetic and makes it easier to identify the row where an error begins.
Use a consistent number format. Format both the balance cells and chart axis consistently, especially if the chart is being shared. Negative balances should remain visible; do not use formatting that hides their minus signs or makes them appear positive.
Plan for additions. If you append transactions regularly, use an Excel table for the data and check that the chart expands to include new rows. If it does not, select the chart, choose Chart Design > Select Data, and review the chart data range. Excel for Mac has similar chart data controls, though pane placement can differ.

Troubleshooting

The balance is wrong after one row. Compare that row’s formula with the previous row and check the amount’s sign. The first formula should add the first transaction to the opening balance; each later formula should include all changes from the first transaction through its own row. Also confirm that the opening balance cell is D2 and the first transaction is on row 5, as assumed in the example.

The chart is blank or shows dates as numbers. Check that the selected ranges contain actual values and that the date cells are formatted as dates. If a date was imported as text, changing its number format alone may not convert it. Re-enter the date or use Excel’s data-conversion tools to turn the text into a real date, then refresh the chart selection if needed.

The chart plots the wrong series or direction. Select the chart and choose Chart Design > Select Data. Check that the series values point to the running-balance cells and the horizontal axis labels point to the date cells. If dates appear in the legend instead of along the bottom, correct the series and axis-label ranges in that dialog.

New rows are missing. The chart may be using a fixed range that stops at the original final row. Open Chart Design > Select Data and extend the chart range to include the new dates and balances, or use a table-based source and verify that the new row is inside the table. Confirm that the new balance formula was filled down before expecting another plotted point.

The line has unexpected flat sections or spikes. Look for blank dates, duplicate records, an unusually large transaction, or a formula copied into rows with no transactions. Check the ledger values first rather than changing the chart scale to conceal a problem. If a single genuine transaction is much larger than the others, the vertical axis may make smaller changes look less dramatic; explain that scale if the chart is shared.

Frequently Asked Questions

What chart type is best for an account balance?

A 2-D line chart is a good default for a balance tracked over dates because it shows the direction and continuity of change. A column chart can work when you want to compare a small number of specific dates, such as month-end balances. Choose based on whether the emphasis is trend or comparison.

Can I make a balance chart without transaction descriptions?

Yes. Descriptions help you audit the ledger but are not required for the chart. You need dated records, signed changes, and a running balance. Select the date and balance columns for the chart.

How do I show the opening balance on the chart?

Add a row for the starting date with the opening balance as its plotted value, then make the first transaction balance the next point. Keep that starting point out of the transaction sum, or the opening balance could be counted twice. The simpler setup in this guide uses the opening balance in D2 and charts only post-transaction balances.

Why does the chart change when I sort the table?

The line connects values in the order Excel reads the rows. Sorting changes that order, which changes the line’s route. Sort the whole transaction table by date from oldest to newest so dates, changes, and balances stay together.

Can I use this method for a zero-based balance?

Yes. Enter 0 as the opening balance in D2, then calculate the running total from the signed changes. The same approach works for other cumulative measures, such as remaining inventory or project hours, provided additions and deductions use consistent signs.

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.