Excel 2013: Challenges in Charting (using Combo Charts) 1

[beadedbytes]

Interesting oddities are occurring while creating approx. 40 charts using combo chart along with the Primary and Secondary feature.


Details about my process:
- Data table example is listed below. The fields and format are the same for all 40 charts - just the values change for each table.

- Used combo chart and Clustered column option for both Cost and Return% fields. Return% is the secondary axis.
- Cost and Return% fields are overlapping bars inside the graphs.
- Cost field Format Data Series >> Series Overlap = 0% and Gap Width = 50%
- Cost field Fill = light yellow
- Cost values are used for Vertical (Value) Axis (right side of chart)

- Return% field Format Data Series >> Series Overlap = -27% and Gap Width = 150%
- Return% field Fill = Dark Gray
- Return% values are used for Secondary Vertical (Value) Axis (left side of chart)

The two bars should be 100% overlapped with the Cost bars being wider than Return% bars.

Data Table Details
Item#|Cost|Return%
Item 1|1149|0.00301
Item 2|1576|0.00348
Item 3|0|0
Item 4|0|0

Issue 1:
For whatever reason, I created the first 5 charts using the info above. The vertical axes on the left is the Return% field. However, when I create the 6th chart using the same methodology, the left vertical axis uses the Cost values instead of Return%. In subsequent charts thereafter, sometimes it's the Return% on the left and other times it's the Cost.

I even tried to switch fields 2 and 3 in the table. Again, sometimes it works; other times it does not. Do you know what might be occurring? I there a way to flip the Vertical axes (i.e. left values become the right axes values and vice versa).

Issue 2:
The Cost and Return% fields has Fill color/Series Overlay/Gap Width specified above. Again, after creating several charts as expected, I noticed that the next chart has changed after creation. Return% and Cost bars have flipped specs for Fill color/Series Overlay/Gap Width. I noticed that, for some reason, the 2nd and 3rd columns the corresponding data table had also been flipped. Is this the cause of the change in the graph? If so, is there way to control the Fill color/Series Overlay/Gap Width so that specs for each field remains the same regardless of column order in data table?

Issue 3:
As mentioned previously, I need to create approx. 40 charts. Is there a way to create the 1st chart, make a copy of that chart and then copy and paste a new data table into this 2nd chart?

Thank you in advance for feedback.

 

[beadedbytes]

I believe I have figured out Issue #2. Just need help with Item #1 and #3. Thank you.

 

[SkipVought]

The fields and format are the same for all 40 charts - just the values change for each table.

In cases like this, I would (and have) use one chart with a drop down control to select the data to display. In addition you'll need to create one table of source data from which to create the chart data, either by filtering the data per the drop down selection or creating a query table subset of the drop down results. But I would NOT mess with multiple tables UGH!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[beadedbytes]

Skip,
If I post a file (of 4 sample charts), would you be willing to show me how to combine? (No is okay. )

 

[SkipVought]

Yes, but it might be later today or tomorrow as my wife and I are getting a dinner ready for our family.

I will be more than happy to look at your sample workbook and show you what might be possible.

Happy New Year!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[beadedbytes]

That's fine, Skip. It's the holidays! Family and fun first.

I'll post something in the next couple hours. Thank you eternally!!

 

[beadedbytes]

Skip --

Attached is a file containing 4 separate data tables with corresponding combo charts off to the right. For every state reported, there are 4 views (i.e. charts) that need to be created. Also, once the charts are created, I usually take snapshots (i.e. copy/paste picture) to use in other document (Excel, PowerPoint, Word). Right now, I report on ten states (40 charts) and more states will be added as the new year progresses.

For all charts in this instance, I'd like the Return% to be the left vertical axis. However, I cannot seem to get Chart1 to display it this way even after starting over from scratch. Charts 2-4 are fine. I'm stumped as to why I can't control what data to use for the left (or right) vertical axis.

Again, thank you for your time.

Cheers and enjoy this holiday!

 

[SkipVought]

I created 2 new sheets, that should be all the sheets necessary for your 40+ charts.

The Example sheet has one chart, composite data for TX, that should be appended to for all your other states. Each state's data has Chart1 thru Chart4, marked by the Chart column. BTW, this is a Structured Table. As you add or delete rows from this ST, it will automatically appear in the chart.

Example also has 2 Data Validation DropDown Lists: SelectedState and Selected Chart. Making a selection immediately reflects in the chart since there is VBA event code that changes the Filter criteria to match the selection. Also the Chart Title is concatenated in ChartTitle.

The Support sheet has a list of states and the chart names. At present the list includes TX & PA, with the chart data (tChtDta) having 2 test rows for PA. So when you add data for your other states, 1) mke sure that all columns in tChtDta are properly filled and 2) the tStates table has the appropriate state abbreviations added.



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[beadedbytes]

Dear Skip,

You are A-M-A-Z-I-N-G.

Conceptually, I get what you've done and believe you've made my process much more efficient, thereby saving me time in the future.

I'm currently in the throes of finishing a report for principals who are going out of the country for a month in early Jan. I'm going to take a pause and enjoy what's left of this holiday weekend. Thereafter, I will digest in detail what you've provided and circle back with you next week.

While these words are insufficient - Thank You!

Will be in touch.

 

[SkipVought]

Thanks. I think we can help you get what you need and keep it simple.

We can also help you streamline the process of loading a PowerPoint with new pix of the 40+ charts.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[julianuy]

Thanks for contributing with this content.
I found it very helpful.
Julian

 

[SkipVought]

@Julian, that's what Tek-Tips is all about

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]