We help IT Professionals succeed at work.

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

libertyforall2
on
30,509 Views
Last Modified: 2012-05-10
How could I force a 45 degree angle line in an excel scatter plot instead of a trend line??
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010

Commented:
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

Author

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

Author

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.
CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010

Commented:
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

Author

Commented:
Excel for Mac 2008 version 12.2.7 (100910)

Author

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
CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010

Commented:
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

Author

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?

Author

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.
CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Got it. Perfect!

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.