• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 17273
  • Last Modified:

How could I force a 45 degree angle line in an excel scatter plot

How could I force a 45 degree angle line in an excel scatter plot instead of a trend line??
0
libertyforall2
Asked:
libertyforall2
  • 7
  • 4
1 Solution
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

create a second data series for the 45 degree line, for example

X   Y
0   0
1   1

Add this series to your scatter plot, then send it to the secondary axis and set the min to 0 and max to 1 on both the secondary X and the secondary Y axis. Format with line and no data markers.

cheers, teylyn
0
 
libertyforall2Author Commented:
"send it to the secondary axis" How do I do this?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
libertyforall2Author Commented:
I took a look at the link at is says In A1: =ROW()
In B1: =ROW()
copy A1 and B1 down, let's say 20 rows.
Select A1:B20, Insert>Graph and choose a line graph

This is not helpful. When I click on A1 and enter =ROW() and then I try to go to B1 it stays in A1 and turns into =ROW()+B1. If my data is in column C & D. How would I do this? It also says choose a line graph, but I need a scatter plot not a line graph. I just need a line within the scatter plot.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

>> "send it to the secondary axis" How do I do this?

I take it that adding the new series to the chart is not the problem? After doing that, select the newly added series, then open the Format dialog, e.g by right-clicking and selecting "Format Series".

In Excel 2003 the command is on the Axis tab. Select the Secondary Axis radio button.
In newer versions, it is on the Series Options tab.

With the new series on the secondary axis, format the secondary Y axis scale to be minimum 0 and maximum 1. Then you need to switch on the secondary X axis temporarily, to be able to format it:

XL 2003 and earlier: Chart > Chart Options > Axes tab > tick the Secondary > Value (X) axis box
XL 2007 and later: Chart Tools > Layout > Axes button > Secondary Horizontal axis > Show default axis.

Since the commands are so different in various Excel versions, it is always good to know what version of Excel you're working with.

If this comment does not help you solve your issue, please let us know what your Excel version is.

cheers, teylyn

0
 
libertyforall2Author Commented:
Excel for Mac 2008 version 12.2.7 (100910)
0
 
libertyforall2Author Commented:
I have attached my excel file with the data. I am can you should me what needs to be done? I am unclear on exactly how to spread the 0 & 1 data points in the column to create the line. Also, I do not want my domain & range to necessarily be one.
so4konascatter12.xls
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

I have no clue what you mean with "domain & range".

See attached for your updated chart. I applied exactly the steps I described above. Excel 2008 for Mac is similar to Excel 2007.

I left the secondary axes visible, but you probably want to hide them. Click each of the secondary axes, then open the formatting dialog and set

Major tick mark type = none
Minor tick mark type = none
Axis labels = none

cheers, teylyn
Copy-of-so4konascatter12-1.xls
0
 
libertyforall2Author Commented:
Ok. The domain and range is the minimum to maximum values of X & Y. The line looks good but how would I get rid the the vertical & horizontal secondary axis text boxes? on the top and right of the chart graph?
0
 
libertyforall2Author Commented:
When I try to delete the secondary horizontal & vertical value axis the line moves. IT is possible to remove this without moving the line? Otherwise the numbers will be confusing.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
You don't want to delete the actual axes, just make them invisible. I described above how to format the axes to hide the tick marks and labels. You can also set the axes lines to "none". I don't know which text boxes you are referring to, but if it's the axes titles, you can just hide them, too. Use the Layout ribbon, click Axis Titles, select the axis in question and select "none".

cheers, telyn
0
 
libertyforall2Author Commented:
Got it. Perfect!
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now