Solved

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

Posted on 2010-11-15
12
10,515 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??
0
Comment
Question by:libertyforall2
  • 7
  • 4
12 Comments
 
LVL 4

Expert Comment

by:tzwimfam
ID: 34142662
0
 
LVL 50

Expert Comment

by:teylyn
ID: 34143711
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
 

Author Comment

by:libertyforall2
ID: 34149942
"send it to the secondary axis" How do I do this?
0
 

Author Comment

by:libertyforall2
ID: 34149991
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
 
LVL 50

Expert Comment

by:teylyn
ID: 34151301
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
 

Author Comment

by:libertyforall2
ID: 34157753
Excel for Mac 2008 version 12.2.7 (100910)
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:libertyforall2
ID: 34158226
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
 
LVL 50

Expert Comment

by:teylyn
ID: 34159295
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
 

Author Comment

by:libertyforall2
ID: 34159793
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
 

Author Comment

by:libertyforall2
ID: 34159820
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
 
LVL 50

Accepted Solution

by:
teylyn earned 500 total points
ID: 34159844
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
 

Author Closing Comment

by:libertyforall2
ID: 34160346
Got it. Perfect!
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now