How to make cumulative spend chart from pivot

I'm having trouble doing something that feels like it should be basic, but I'm not sure if the solution lies in with the way my data is arranged, the way the pivot table is set up, or the way I'm making a chart.

I have a table where I input amounts I've spent, and want a graph that tracks my total spend.

I will sometimes have more than one line item per day. Eg:

Monday - £10
Monday - £20
Tuesday - £15
Tuesday - £10
Wednesday - £5

I would then want the chart to show 3 data points. £30 spent on Monday, £55 spent by Tuesday and £60 spent by Wednesday.

My pivot chart will happily summarise the days, and output £30 for Mon, £25 for Tue and £5 for Wed, but I can't work out how to make it keep a running total, or otherwise display a running total on a graph.

Alternatively, I can add a running total to the original data, but the pivot table then sums this column for each day. Eg, for Monday the running total column displays £10, then £30, which the pivot table sums to £40 for Monday!

If someone could provide an explanation for how to achieve what I want, or an example with the data set above, I would be very grateful! It seems like it should be easy, but I've been stuck for an hour now!

Thank you kindly,

B

Hi Richard,

I have attached a sheet with the sample data. I have input 5 amounts over 3 days, with a running total and put all that data into a Pivot Table. However I'm unable to generate the chart I want from this table.

I am able to generate a chart that is more-or-less what I want directly from the data, and I have included this, though it has 2 issues. Firstly, it's not dynamic in the way I would want because it's not using the PivotTable. Secondly, it doesn't draw lines between the points in the way it would if there was only a single data entry per day. (eg, it draws a sloped line from £30 to £45, then a vertical line from £45 to £55, because they occur on the same day.)

Thanks in advance.

Amazing, thanks. Knew it would be something simple in the end!

Hmm, it's still not acting quite how I would like, but I'm not sure if this time it's going to be fixable.

When I extend my data-set, I have a couple of issues with the date axis.

Firstly, I'm not able to set the bounds of the date. Ideally I would want to set the x axis for a month, even when I only have a week's worth of data.

Secondly, I would want every day of the month to be on the axis, even if there were no entries in the data-set.

Example included with an additional entry mid-way through the month, and an example graph showing the correct spacing and x axis running to the end of the month.

Setting it up the way you advised, the chart skips days that have no entry, and I'm not able to set the date bounds. It's not really treating the date axis as a date, just a list of categories.

Are either of these possible?

Hi JeteMc,

That's certainly closer, though that's probably going to mean too much faffing on the back end, and still doesn't quite give the desired result (when I made the chart without using a Pivot Table, I could extend the axis even with no data present, and then project a trendline into the future, which this doesn't seem to do still).

eg:
Capture.PNG

Part of the reason I'm trying to do this with a Pivot Table is because the actual data has a lot of different columns and totals that I want to be able to easily switch between, so having to introduce even more columns to calculate the data is potentially going to be more hassle than simply manually making the graphs from the source data.

I really appreciate your last reply though, and I've certainly learned a couple of things from it. I'll carry on fiddling with what you've provided, as it might be that it ends up more useful than what I'm currently working with, so thank you kindly!

If anyone else has a way to get the desired result, please let me know.

Wow, those are some impressive formulas! I'm going to have to spend a while looking at this just to understand how they work!

It looks like the same effect I got just by adding a trend-line though, no?

I've attached an example similar to what I'm currently using now on my full data-set - Just charting from a running total and projecting a trendline (though mine has square corners, because of the multiple entries for each day.)

The reason I'm trying to get it with a Pivot Table is because I want to be able to change the chart to show, for example, how much I spent on Lunch, then how much on Dinner, then how much on Food all-together, then how much was on non-essentials, then how much was spent by me vs my partner, then how much was by cash how much by card, and a dozen other variables that I want to be able to combine on the fly.

At the moment I just picked a handful of permutations that I'm likely to use most often and made each their own chart, but I figured if I could do it with a Pivot Table, it would be much easier. I may have come across one of those things that seems simple, but Excel just doesn't like to quite do the way you want it.