CODEWITHSUNDEEP
excelcharts
Jean
Jean

Reputation: 333

Draw an Excel Line Chart of my account balance

I made a table with my earnings and cash disbursements. And I list the account balance. Now I want to draw the account balance in a line chart. But the problem is that there isn't a value on every day. So the line between to entries is different because different time lies between two entries. Sometimes three days, sometimes 22 days, ...

How can I realize, that the line is constant after a value and the line only change on a day, with a new value?

Edit (from comment): I'm using Excel 2007

Upvotes: 4

Views: 6536

Answers (4)

Chuan Qin
Chuan Qin

Reputation: 675

I think the chart you need is a step line chart based on your description. The answer of @Excelll above has already pointed out a way to do this in Excel. But here I've another solution that could avoid manually adding some rows to your data. You could use the Funfun Excel add-in to create step line chart. Here is an example that I draw.

enter image description here

As you could see there is some code in the Excel. The Funfun Excel add-in allows you to use JavaScript code directly in Excel so that you could use powerful libraries like HighCharts.js or D3.js to draw charts that could be difficult in Excel alone. In this example, I used HighCharts.js. And it's quite easy to plot this chart since HighCharts.js iteself supports step line chart, so all you need to do is add the step option in your code like the code below. 

series: [{
    name: "Balance",
    data: balance,
    step: true,
    showInLegend: false
}]

Another advantage of the chart that I showed you comparing to other solution is that you get real time interval in your x-axis. As you mentioned, you may not take record of your balance everyday, the x-axis in the example above shows irregular time interval.

The Funfun also has an online editor in which you could explore your JavaScript code and result. You could check the detail of how I made the example chart in the link below.

https://www.funfun.io/1/#/edit/5a4e478f1010eb73fe125cb2

Once you are satisfied with the result you achieved in the online editor, you could easily load the result into your Excel using the URL above. But of course, first you need to add the Funfun add-in into your Excel by Insert - Office Add-ins. Here are some screenshots showing how you load the example into you Excel.

enter image description here

enter image description here

Disclosure: I'm a developer of Funfun

Upvotes: 1

Excellll
Excellll

Reputation: 5785

Like the others have said, to get the true time difference between your points to show up, you have to use a scatter plot instead of the Excel line graph.

To display the true state of your balance at in-between dates (i.e., no sloping lines), you'll need to do some transformation of your data. It amounts to creating two new columns of data with duplicate points for each balance: one at the date the account balance reached that level, and one at the date the balance left that level.

I suggest the following: To populate your Balance column, in the first cell (I'm starting my table at E2) enter

=B2

where B2 is the address of the first balance in your original table. In the next cell down enter 

=IF(E2<>E1,E2,OFFSET(B$2,COUNT(E$2:E2)/2,0))

Fill down this formula as far as you need to.

Then in the first cell of the Date column of your new table (I'm starting at D2) enter

=A2

where A2 is the first date in your original table. In the next cell down enter 

=IF(E3=E2,OFFSET(A$2,COUNT(E$2:E3)/2,0),D2)

Fill down this formula. Then use this new table as your source data for the scatter plot. Here's a screen capture of a sample:

enter image description here

Upvotes: 3

Jon49
Jon49

Reputation: 4606

Are you using an XY-Scatter Plot. A line chart adds in the missing days automatically. Make sure your x-axis is formatted as date.

If you insist on using XY-Scatter Plot then you can put in days for each day, if that day doesn't have data then for the y-axis put in =NA().

Upvotes: 1

Vicky
Vicky

Reputation: 13244

Excel is really rubbish at charting this kind of thing. In my experience, the best way to chart it is to start with an X-Y plot rather than a line graph, and select the option where it joins the points with a line.

You may also need to add a new column showing "days since start", and use that as the X axis instead of the date - I can't remember whether Excel 2007 can deal with dates properly or not. Try it and see.

Finally, if you include any cells in your chart that are not yet populated (eg because you want to make the chart from the whole column even though you only have a few entries so far), you need to have the empty cells populated with "#N/A" otherwise it will plot them as 0.

Upvotes: 0

Related Questions