Pivotchart with two axes?

Chartman of the Bored

Board Regular
Joined
Apr 24, 2002
Messages
83
I'm trying to create a pivot chart showing two related sets of data. But they are different magnitudes/scales. So on the chart, one overwhelmes the other.

I have some data showing the price changes of a stock in half-hour increments and the number of shares traded during each half hour. I created a pivot table to see what the average price change is during each time period (I 20 days of data). For example, on average the price declines by 3 cents from 9:30am to 10:00am.

I also added a field to the pivot table showing average volume (# of shares traded) for each half hour. But when I try to create a pivot chart from the pivot table, the avg. price-change data is overwhelmed by the volume data and disappears.

I tried using a two-axes chart (bars and line), but that doesn't work. And I can't seem to add the volume data beneath the price bars (normally I have no trouble adding descriptive data to the bottom of pivot charts by adding the field).

I hope this isn't too confusing. Any ideas would be appreciated. Thanks!
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,951
Messages
5,834,554
Members
430,295
Latest member
amdis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top