SCATTER AND LINE COMBO - Quarterly revenue graph HELP

Welcome to the forum!

I'm not particularly skilled with pivot tables or charts, so hopefully someone more useful comes along, but if you're just looking to get the job done, then you can enter a row after each month in your original table with "Total" listed as the opportunity. List the cumulative sum through that point in the "Revenue" column. These entries will basically act as cumulative subtotals. I feel like there's an easier way, but to my knowledge, Excel's native subtotal feature does not track cumulative values and I haven't figured out how to get the pivot table to do it for you while still treating the cumulative total as a single series. In my version, the 12 new entries appear as a single series that can be tracked with a line. Your column with the cumulative totals can be deleted - it was problematic anyway because each total was attached to different opportunity in column A.

On a separate note, "Jan" was out of order on your initial attachment. It's fixed in mine.

Take a look at the attachment, hopefully it will help. Again, I don't think it's the most efficient solution, but it isn't hard to implement with your current data.

I'm glad it's working for you! I didn't do anything fancy, I just manually inserted 12 new entries into your original data table on 'sheet1' with the opportunity name 'total' and the proper month, quarter, and calculated cumulative total. Excel did the rest.

As to the question in your PM about a two scale axis, I don't see why it wouldn't be possible, but I'm not going to be near Excel for the weekend, so I'm not in a position to test it out and I'm not good enough with pivot charts to provide much instruction without having it front of me. Try fiddling with the formatting and I might be able to help next week if you're still stuck and/or nobody else jumps in to help.

Cantosh said this:

I just manually inserted 12 new entries into your original data table on 'sheet1' with the opportunity name 'total' and the proper month, quarter, and calculated cumulative total.

I don't think anything was added to the pivot table, rather the ORIGINAL data. Hope this helps.

No - that bit I don't know. How about attaching your latest file here? Not promising anything, as I never use pivot tables myself!

OK, so select the table and insert your pivot chart, making sure that the settings match Cantosh's. Next change the chart type to stacked line with markers, then go through all but the TOTAL data series and switch the line off.

Attach the workbook AFTER you have tried adding the chart, please.

Attach the workbook with your attempt at the chart included, please. Otherwise i can't help! I can produce the chart - it's no good giving me a workbook without your attempt at it as we will be going round in circles.

I don't know - sorry. It's moved too far away from the original example for me to help now, but I don't see the grand total column in the ORIGINAL data, which is where I understand it needs to be, somehow. Hopefully someone can help - I'll put out a call for help.

Hi,

Change the chart type to Line with markers and not Stacked line with markers.

Click on one of the month name cells in column A, then select the dropdown in A2 and choose Sort A-Z.

If you haven't gotten there yet, then to add a second scale to the y-axis for the 'Total' series, just right-click on the line on the chart representing the 'total' series, select 'Format Data Series', and under 'Series Options' select 'Secondary Axis'. You can then right-click on the two axes individually and use 'Format Axis' to adjust the scale you want on each.