LinkBack URL
About LinkBacks
Hi, is there a way to automatically put color zones on graphs? Pls see attached. I would like the area below 1.5 to be red zone, 1.5 to 2.0 to be blue zone and above 2.0 to be green zone. Thanks.
Hi, is there a way to automatically put color zones on graphs? Pls see attached. I would like the area below 1.5 to be red zone, 1.5 to 2.0 to be blue zone and above 2.0 to be green zone. Thanks.
Here is a link to a site that has instructions on how to create the zones that you want.
http://peltiertech.com/excel-charts-...izontal-bands/
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Thank you.
Hi, I went through the exercise starting from scratch but could not get the secondary vertical axis to max at 8.0 (the same as the primary vertical axis). Do you have the same problem?
Select the data for the bands (the shaded range F1:H8) and create a stacked area chart (not stacked 100% area), with series data in rows. The charts all look the same for a few steps, until we actually have a series that has either XY, Column, or Line type.
This is as good a time as any to apply your fill colors to the bands. It is best to use light shades, so the bands do not overwhelm the original data.
Copy the original data, select your chart, and use paste special to add the data as a new series. It is added as another stacked area series.
Change the added series to the desired chart type (XY Scatter, Column, or Line). Now the charts assume their unique appearances.
Format the new series and assign it to the secondary axis. This was already done in the first chart when the added series was converted to XY type.
Select the data for the bands (the shaded range F1:H8) and create a stacked area chart (not stacked 100% area), with series data in rows. The charts all look the same for a few steps, until we actually have a series that has either XY, Column, or Line type.
This is as good a time as any to apply your fill colors to the bands. It is best to use light shades, so the bands do not overwhelm the original data.
Copy the original data, select your chart, and use paste special to add the data as a new series. It is added as another stacked area series.
Change the added series to the desired chart type (XY Scatter, Column, or Line). Now the charts assume their unique appearances.
Format the new series and assign it to the secondary axis. This was already done in the first chart when the added series was converted to XY type.
Select the Secondary Vertical Axis the right click and choose Format Axis. In Axis Options choose Maximum and enter 8. Click close.Originally Posted by L.LEE
I eliminated all but 3 of the colours to match your example and added values to approximate your distribution of the horizontal bars. I deleted the X and Y axis for the original Area Chart and then moved the X and Y axis to the normal positions of bottom and left by changing the values in the Format Axis to where the Axis crosses to Automatic. If the Axis doesn't move choose Axis value of 0.
Thanks. I could manually do that but was wondering if there is a way for left and right vertical axis to synchronise automatically.
Not built into Excel, but, if we go back to Jon Peltier's site, he has a couple of tutorials that should be useful for such a thing:
http://peltiertech.com/secondary-axe...tional-scales/ shows how to "fake" the secondary axis using a dummy series. You can manually set the axis values for the primary axis, enter those values into the spreadsheet, then have formulas for the dummy series that becomes the secondary axis.
http://peltiertech.com/link-excel-ch...lues-in-cells/ has a VBA routine that can automatically link axis limits to spreadsheet cells, where you can have whatever formula you like in those cells. You can manually enter primary and secondary axis values into cells, or enter the primary axis values, and have formulas that calculate the secondary axis values from the primary axis values.
I think that VBA might be able to accomplish that. I say "might" because I don't know for sure.
Perhaps someone with VBA charting experience could chime in with an opinion on accomplishing the automation.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks