Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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).

enter image description here

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!

share|improve this question

2 Answers 2

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.

share|improve this answer

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.

new data, chart, and empty cell dialog

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.