Multiple Plot areas in one chart area

I have a multiple line chart that uses both vertical axises where the range changes regularly for each series.  I set both axises to the desired setting so that one line (the one that used the left axis) is always on top of the other (right axis).  But as soon as I change the source data, the lines go back to overlapping.  Is there a way to have one line chart always be on top of the other so that they aren't touching regardless of ranges?  I thought of having multiple plot areas within one chart but i didn't see any options for that.  thanks in advance.
0
anonymous (74722)
11/12/2003 7:21:10 PM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
1148 Views

Similar Articles

[PageSpeed] 38

Zac -

You could use a macro to change the axis scales for you.  This simple 
example squashes the working range of the primary axis into the bottom 
half of the chart, and the working range of the secondary axis into the 
top half of the chart:

Sub ReSetDualAxes()
     Dim i As Integer
     With ActiveChart
         For i = 1 To 2
             With .Axes(2, i)
                 .MajorUnitIsAuto = True
                 .MaximumScaleIsAuto = True
                 .MinimumScaleIsAuto = True
                 .MajorUnit = .MajorUnit
                 .MaximumScale = .MaximumScale
                 .MinimumScale = .MinimumScale
                 If i = 1 Then
                     .MaximumScale = 2 * .MaximumScale _
                         - .MinimumScale
                 Else
                     .MinimumScale = 2 * .MinimumScale _
                         - .MaximumScale
                 End If
             End With
         Next
     End With
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Zac wrote:
> I have a multiple line chart that uses both vertical axises where the range changes regularly for each series.  I set both axises to the desired setting so that one line (the one that used the left axis) is always on top of the other (right axis).  But as soon as I change the source data, the lines go back to overlapping.  Is there a way to have one line chart always be on top of the other so that they aren't touching regardless of ranges?  I thought of having multiple plot areas within one chart but i didn't see any options for that.  thanks in advance.

0
11/13/2003 2:35:53 AM
Zac -

After running the code a couple times, I realize a minor change to the 
..MajorUnit makes the chart look less cluttered.  Whenever you set a new 
source data range for the chart, select the chart and run the macro.

Sub ReSetDualAxes()
     Dim i As Integer
     With ActiveChart
         For i = 1 To 2
             With .Axes(2, i)
                 .MajorUnitIsAuto = True
                 .MaximumScaleIsAuto = True
                 .MinimumScaleIsAuto = True
                 .MajorUnit = 2 * .MajorUnit
                 .MaximumScale = .MaximumScale
                 .MinimumScale = .MinimumScale
                 If i = 1 Then
                     .MaximumScale = 2 * .MaximumScale _
                         - .MinimumScale
                 Else
                     .MinimumScale = 2 * .MinimumScale _
                         - .MaximumScale
                 End If
             End With
         Next
     End With
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Jon Peltier wrote:
> Zac -
> 
> You could use a macro to change the axis scales for you.  This simple 
> example squashes the working range of the primary axis into the bottom 
> half of the chart, and the working range of the secondary axis into the 
> top half of the chart:
> 
> Sub ReSetDualAxes()
>     Dim i As Integer
>     With ActiveChart
>         For i = 1 To 2
>             With .Axes(2, i)
>                 .MajorUnitIsAuto = True
>                 .MaximumScaleIsAuto = True
>                 .MinimumScaleIsAuto = True
>                 .MajorUnit = .MajorUnit
>                 .MaximumScale = .MaximumScale
>                 .MinimumScale = .MinimumScale
>                 If i = 1 Then
>                     .MaximumScale = 2 * .MaximumScale _
>                         - .MinimumScale
>                 Else
>                     .MinimumScale = 2 * .MinimumScale _
>                         - .MaximumScale
>                 End If
>             End With
>         Next
>     End With
> End Sub
> 
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> http://www.geocities.com/jonpeltier/Excel/index.html
> _______
> 
> Zac wrote:
> 
>> I have a multiple line chart that uses both vertical axises where the 
>> range changes regularly for each series.  I set both axises to the 
>> desired setting so that one line (the one that used the left axis) is 
>> always on top of the other (right axis).  But as soon as I change the 
>> source data, the lines go back to overlapping.  Is there a way to have 
>> one line chart always be on top of the other so that they aren't 
>> touching regardless of ranges?  I thought of having multiple plot 
>> areas within one chart but i didn't see any options for that.  thanks 
>> in advance.
> 
> 

0
11/13/2003 2:41:41 AM
Reply:

Similar Artilces:

Deleting "hidden" charts on a worksheet
Hi everyone, I've been playing around w/ a worksheet that contains data and charts, inserting and removing multiple rows at a time. Unfortunately, I did not change the properties of the charts and they were all set to "Move and Size w/ Cells". When I deleted a series of rows, these charts disappeared and left a line across the spreadsheet where they used to be. I've tried everything to get rid of them (because they are now calculating incorrectly and giving me error messages), including deleting the rows where they appear, trying to select them on the sheet, but nothing w...

can you make one worksheet update another
I have two similar worksheets within the same workbook. One sheet is sorted by name and the other sheet is sorted by a date for a speicfic name. Is there a way to change the date on the sheet, sorted by name, and that will automatically find the name on the other sheet(sorted by date) and change the date to the second sheet? Jaime, The normal way is to have one table, and sort it as needed when needed. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Jaime S." <Jaime S.@discussions.microsoft.com> wrote in mess...

Pivot chart and Total row
Hi All I have a Pivot table and attached Pivot chart (all good so far). However, there are three columns (drop column details here) which are the two columns of data and a grand total column (showing the average of my two data columns) When I turn it into a pivot chart it does not display the grand total column as well (is there a way to do this). I have tried to go into the chart options but these are locked. I can copy and paste the results but this is a bit clumsy. Any suggestions - any help greatly appreciated Kind regards Rexmann PS using excel 2003 ...

Can i let 1(one) person see the adress lists
Hi all (I really hope you can help me on this) I have one user who needs to control who is on the lists on the exchange server. I have hide the users who is on the lists from the domain users becausethey change the users and cause trouble. Is there any way that i can give this one user access to still view who is on the lists and control/add/delete if she needs to.Please, please tell me this is possible as it would make my life MUCH easier Thanks eveyone On Tue, 27 Mar 2007 14:43:00 +0200, "MSExchange2003Student" <exchangestudent@newsgroups.com> wrote: >Hi all &g...

Multiple users one pop account
I'm trying to troubleshoot a problem. The client has a 2 pop mail accounts on the system. One account is personal. The other is shared and multiple users access the mail from that account. In order to ensure that all users are able to retrieve that mail, the shared account is configured to leave mail on the server and then after 10 days it is pulled from the server. The problem is that out of the 4 people who share that pop mail account (from different computers), one of them is getting duplicates for mail that has already been retrieved by that system? (not all the time, just once a...

All in one Windows 2003 Server as PDC with SQL and CRM
hello, can i install an Microsoft CRM Production Server as an All-In-one Solution with Microsoft CRM and SQL 2000 Server on the PDC (Windows 2003 Server)? Is this supported? What are the possible disadvantages besides not having an secondary Domain Controller for extra security? Andy in a testing environment this works but in production is not a good idea. The only supported single server deployment is sbs 2003 etc. why not supported? apart from SBS you should never start treating your domain controllers as application servers as if anything goes wrong you risk losing your entire AD....

Multiple CheckBox
Hi, I created an option group that have 8 check boxes and I need to choice more than one checkbox, but when I check one the other is unchecked and so on. Any help would be appreciated. Thanks Paulo Pasmatos, Don't use an option group to contain your checkboxes. Just place 8 individual checkboxes on the form. Then. you can check or uncheck 1 or more boxes at one time. But, beware... you'll have to do a "geometrically" large amount of coding, if you need to "interrogate" 8 checkboxes. Ex. is 1 True and 2 True and 3 False, or 1 False, 2 True... and on an...

How do I delete chart superimposed on Excel document?
Created Excel document. Print preview did not show gridlines. In attempting to get gridlines, somehow got into charts. A chart was created and is superimposed on original document. How do I get rid of the chart? How do I get gridlines on to original document? Oz Right-click on the chart and Edit>Clear>All To print gridlines.................File>Page Setup>Sheet Check "gridlines" and uncheck "draft quality". Gord Dibben MS Excel MVP On Fri, 4 Aug 2006 14:27:01 -0700, Oz Osborn <Oz Osborn@discussions.microsoft.com> wrote: >Created Excel docume...

Make columns different sizes within one particular column. Excel
I'm trying to make a spreadsheet that will allow me to make columns different sizes at different places. I want split Cell B2 into 5 columns while having Column B3 as one larger column. how can i do this? Hi short version: you can't :-) you may use merged cells (though I really would not recommend this). >-----Original Message----- >I'm trying to make a spreadsheet that will allow me to make columns different >sizes at different places. I want split Cell B2 into 5 columns while having >Column B3 as one larger column. how can i do this? >. > ...

multiple sales persons per customer
Some companies are set up so that a certain brand or class of items are sold by certain salespersons, which means that a single customer can be serviced by a number of sales persons. The system should have a relation of Item/Customer to sales person or Item Class/Customer to sales person. This will then allow a single customer to have several sales persons and because there is a relationship to the inventory item, when the item appears on a sales document the correct salesperson will be attributed to the sale. At the moment the system allows several sales persons per sales document but...

Chart: range info from seriescollection?
I would like to fill a combobox (Rowsource) with data that are used in for instance the XValues or Values of a Seriescollection of a graph. "ActiveChart.SeriesCollection(1).Values" does not give the right type (I was hoping for a string type of info like "Sheet1.Range(a1:a10)"). How to get a Range to be used as Rowsource for my combobox??? Thanks for your suggestions, JB The Values and XValues will return an array of values. To get the range address like this, you will need to parse the series formula. Or you could use John Walkenbach's class module for extractin...

Is it possible to save an excel chart as .emf?
I would like to save an excel chart as .emf instead of .jpeg or .gif using the script below. Is this possible? Sub SaveChartAsGIF () Fname = ThisWorkbook.Path & "\" & ActiveChart.Name & ".gif" ActiveChart.Export FileName:=Fname, FilterName:="GIF" End Sub ...

Step change charts
Help, I am trying to create a bar charts by year but only want to show increases and decreases through the middle years as step changes i.e. 1999 100 2000 +10 2001 -30 2002 +40 2003 120 How can I do this? What I should see is the first vertical bar being 100 then from the 100 level an increase of 10 to get to 110 then a decrease bar of -30 (from the 110 level) to get to 80 etc etc. For a tutorial, see the Excel | Charts | Waterfall Chart page of my web site. -- Regards, Tushar Mehta, MS MVP -- Excel www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Cus...

Excel Bar Chart with many elements Example
I am working with a survey database where one field represents one question in the Survey. Some questions allow the user to enter multiple answers. This data isn't normalized but I have t work with what I've got. Anyway I tried to create a chart in Acces with the wizard but got the error: "Cannot have more than elements in the chart". Anyway I attempted to recreate the char in Excel but my chart looks crammed and canno print entirely within one page. Does anyone know how or have a example of how to create a bar chart with many elements in a clea and consice manner T...

Can no longer cut/paste ranges in chart "edit data" fields?
In Excel 2007 I can no longer cut/paste cell ranges into the x and y datasets using the edit data tool. Is there a way to turn this back on? Also the chart range resets itself to the current length of the data, however, in many instances I want to set an exact future range. Is there a way to have excel not reset the range to what it "thinks" I want. > In Excel 2007 I can no longer cut/paste cell ranges into the x and y > datasets > using the edit data tool. Is there a way to turn this back on? I have no problem doing what you can't. Could you provide more det...

Exchange 2003 send multiple copies of same email to clients!
I have just finished the the basic setup of Echange 2003. It only manages about 75 email acounts. I have noticed 2 things and think they are related. Firts if there is no subject for email being recieved it will hang on that email and never download to client using OutLook or Outlook Express. I can go in and through the Web based mail and remove the problem email all goes back to normal. Secondly sometimes my clients will get multiple copies of the same email. I have one now that has four copies and everytime she opens her Outlook it gives her another copy. Any help as always appreci...

How does one print the columns from a Visio ERD diagram?
I see them but can't print them. ...

company split
Hi all, hoping to get some advice on this, the company i work for consists of two divisions who are effectively looking to split, and do there own thing. But initially will continue to share resources such as IT. Is it possible in exchange to allow them both to have there own email addresses such as name@company1 and name@company2 and to hide these so that relevant individuals only see the names of users relevent to there division/company in their address book. Effectively what i need to be able to do is run 2 seperate companies from one exchange box. bearing in mind that both compani...

Use Data from one sheet to populate Purchase Order on another sheet -Excel 2002
Hello, Wonder if this is possible.. Sheet1 - has a 'list' of parts that need to be ordered - their description, qty, type of material, part number etc Sheet2 - I want to create a requisition from those items listed on Sheet1 based on type of material... Basically need to take the data from Sheet 1 (can be 50 - 200 items/parts) keyed in by designers and create a purchase order requisition for our vendors/suppliers based on the type of material. Is there a way to extract.. say. material is H20.. and of them 50 items keyed in.. 10 of them are H20.. so that I can fill in the qty, descr...

Can I have one register from two accounts in M03 and in import question
I have just begun trying-out Money 2003 and am having trouble doing what I want to do. I have two accounts, one is a Merrill Lynch money market account which contains my portfolio and my VISA account and I also write checks in that account. The second account is a Credit Union account in which deposits are made and from which I pay bills. I would like to have one register that will contain all deposits and expenditures, including the VISA charges, from both accounts. Can I do this and how? Also, ML only keeps 90 days worth of transactions. I would like to load the entire year into Mone...

can not see chart
I created a simple chart. For some odd reason the chart is all gray. I can only see the chart if i select the chart object. Please help! Thank you! Hi, Via the menus Tools > Options. On the View tab select Obejcts Show All. Cheers Andy Nick wrote: > I created a simple chart. For some odd reason the chart is all gray. I can > only see the chart if i select the chart object. Please help! Thank you! -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

derivative plot
How do i create a derivative plot? I'm supposed to take reaction rates (slopes) from my graphs and enter in new columns in the spreadsheet...i'm confused thanks You need to calculate the slopes somehow, or were you just going to eyeball the? I've sometimes used a moving regression on the data. Say my X (or time) and Y data were in A1:B100 and I wanted a 5-point moving average. I would enter this formula in C3, and fill it down to C98: =SLOPE(B3:B7,A3:A7) The derivative plot would use columns A and C. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Soluti...

on server reboot multiple emails get returned as undeliverable
I have an SBS 2003 server running exchange, all latest service packs etc. We have hit a problem a couple of times when on reboot of the server end users get emails returned as undeliverable as they have exceeded the delivery date. It does not affect all outgoing messages, and the destinations are valid. It is as though the SMTP gets stuck on some messages, and only notices on re-start. Any ideas? F Frank <noreply@127.0.0.1> wrote: > I have an SBS 2003 server running exchange, all latest service packs > etc. > We have hit a problem a couple of times ...

Surpressing plot of blank cells
I'm creating a line chart plot using Excel 2007. The data range for plot lines contains some blank cells. These blank cells get plotted as zero points. How do I change this behavior to plot only points that have values and to leave the blank cells unplotted? I appreciate your help, -John Hi, If they are truly blank then check the plot empty cells setting via. Select Data > Hidden and Empty cells. If the Cells in fact contain formula then use NA() instead of "". This will suppress the data marker but will not break the line. Alternatively use Autofilter to hide un...

How to avoid when linked source data is changed, chart turns blan.
When I created new charts in Excel 2003, each previous chart became blank. Therefor I saved the file unter a new name and copied the previous chart to the new file. This created ofcourse links to the other files. Now I want to change the source file, but when I do that the chart becomes blank as well. Anybody who had this problem before and found out how to solve it ? Rgds, Chris ...