Excel graph - two lines, one axis with date

Question

I want to make Excel chart that shows my accounts' balances in time.

I have 2 bank accounts and I have all transactions saved in Excel. The data's format is:

Column A   Column B   Column C   Column D   Column E
Date_1     Balance_1  [blank]    Date_2     Balance_2

Dates are random (there could from 1 to 40 transactions per month).

My problem is that I don't know how to make the X-axis show date (linear, not from the data) and then show appropriate balance on the Y-axis. I mean that X-axis should show each day even though there is no transaction, so each month should have same length. (This problem isn't shown in the example.)

The main problem is that both accounts have transactions dated differently and that shouldn't be a problem in the chart - I need to make these points independent of the other account. There are thousands of transactions, I can't resolve this problem by adding duplicate dates and balances so their count matches.

Sorry for my English, I really try to make it clear though, hope you can help me.

Thanks for the advice!

Answer 1

The easiest fix I can think of is to change from a Line Chart to a Scatter Chart (with Lines). For each series (account 1 and account 2) use:

1. The date column for your X-values
2. The balance column for your Y-values.
3. Format your X-axis with:
   • minimum as your start date
   • maximum as your end date
   • 1 as your major unit (this will ensure every date between your start and end are shown, regardless of whether they have data).

Both series will then have points on the appropriate dates.

Answer 2

Alternatively, you could put the data into a single range, with columns Date, Account A, Account B. Create a line chart, which by default has no lines between points wherever there is a gap in the column of values for either account. Go to Select Data, click on the Hidden and Empty Cells button at the bottom left of the dialog, and choose Connect Points With Line.

If you convert the data range into a table, then the chart will automatically include new data as you add it to the table.