[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


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

Posted on 2010-11-15
Medium Priority
Last Modified: 2012-05-10
How could I force a 45 degree angle line in an excel scatter plot instead of a trend line??
Question by:libertyforall2
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
LVL 50
ID: 34143711

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 Comment

ID: 34149942
"send it to the secondary axis" How do I do this?
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!


Author Comment

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.
LVL 50
ID: 34151301

>> "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 Comment

ID: 34157753
Excel for Mac 2008 version 12.2.7 (100910)

Author Comment

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.
LVL 50
ID: 34159295

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

Author Comment

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?

Author Comment

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.
LVL 50

Accepted Solution

Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 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

Author Closing Comment

ID: 34160346
Got it. Perfect!

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

649 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