Manually adjust axis numbering on Excel chart












3















I want to adjust the X axis of my scatter diagram according to my diagram points in MS-Excel 2010. I have seen in some guides that I can change it by editing the "Axis Labels" in "Select Data". As you can see in the picture below, this button is not activated for me. In the diagram I want to change the X axis labels to (0.8 1.0 1.3 1.5 1.7).



Sample Data










share|improve this question

























  • In your question you reference Word 2010-do you mean Excel 2010?

    – dav
    Nov 30 '15 at 18:08
















3















I want to adjust the X axis of my scatter diagram according to my diagram points in MS-Excel 2010. I have seen in some guides that I can change it by editing the "Axis Labels" in "Select Data". As you can see in the picture below, this button is not activated for me. In the diagram I want to change the X axis labels to (0.8 1.0 1.3 1.5 1.7).



Sample Data










share|improve this question

























  • In your question you reference Word 2010-do you mean Excel 2010?

    – dav
    Nov 30 '15 at 18:08














3












3








3


1






I want to adjust the X axis of my scatter diagram according to my diagram points in MS-Excel 2010. I have seen in some guides that I can change it by editing the "Axis Labels" in "Select Data". As you can see in the picture below, this button is not activated for me. In the diagram I want to change the X axis labels to (0.8 1.0 1.3 1.5 1.7).



Sample Data










share|improve this question
















I want to adjust the X axis of my scatter diagram according to my diagram points in MS-Excel 2010. I have seen in some guides that I can change it by editing the "Axis Labels" in "Select Data". As you can see in the picture below, this button is not activated for me. In the diagram I want to change the X axis labels to (0.8 1.0 1.3 1.5 1.7).



Sample Data







microsoft-excel charts label axis






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Dec 2 '15 at 21:19









fixer1234

19k144982




19k144982










asked Nov 30 '15 at 16:53









HamedHamed

23116




23116













  • In your question you reference Word 2010-do you mean Excel 2010?

    – dav
    Nov 30 '15 at 18:08



















  • In your question you reference Word 2010-do you mean Excel 2010?

    – dav
    Nov 30 '15 at 18:08

















In your question you reference Word 2010-do you mean Excel 2010?

– dav
Nov 30 '15 at 18:08





In your question you reference Word 2010-do you mean Excel 2010?

– dav
Nov 30 '15 at 18:08










4 Answers
4






active

oldest

votes


















5














It's irregular not to show normal axis labels, because it may cause a lack of comprehension in whoever is reading the chart. I know I would be distracted thinking, "Why did he choose to do that?" But people ask all kinds of questions.



So here is how you'd do what you want.



Add a column of data with all zeros. This will produce a series of data points along the horizontal axis.



Make your chart with all the data (below left).



Adjust your axis as desired (below right).



data and beginning of chart formatting



Hide the horizontal axis labels. Best way is to use custom number format of " " (single space surrounded by double quotes), so there will be room for the data labels without having to manually adjust the plot area size. (top left chart below).



Select the series along the axis, and add data labels. Excel adds Y value labels (all zero) above or left of the points. (top right chart below).



Format the data labels: Select the option to show Y values and deselect other options; select the "below" position; use a number format with one decimal digit. (bottom left chart below).



Delete the legend, and reformat the series along the X axis so it mimics an axis (medium gray line, cross markers using medium gray border and no fill). (bottom right chart below).



enter image description here






share|improve this answer



















  • 1





    Elegant solution, would never have thought of doing it like that myself :)

    – eirikdaude
    Dec 27 '15 at 21:33











  • Clever. I was focusing on the gridlines because that's how the OP displayed the problem, but the actual question wording was about the axis labels.

    – fixer1234
    Dec 27 '15 at 23:06



















2














This answer deals with Excel-2013, but I suspect Excel-2010 (if that indeed is what you are using) is similar enough that it shouldn't matter. To edit the scale of your axis, right-click on it and select format axis:



enter image description here



This should bring up a dialog containing several choices, to set the interval between the gridlines go to:



Axis Options -> Axis Options -> Units


enter image description here



The major unit sets the interval between major gridlines, and the minor unit sets the interval between minor gridlines. I hope this helped :)






share|improve this answer
























  • Dear Eirikdaude, thank you very much for your answer. The problem with the method you explained is that it produces more numbers in axis including the numbers I need. However, I want only the numbers I have specified would be shown in the axis labels which do not have constant increment steps. Do you have any comments to this? Thanks in advance

    – Hamed
    Dec 1 '15 at 11:06











  • @Hamed, EirikDaude's solution is proper. Why do you only want to show some labels?

    – Jon Peltier
    Dec 27 '15 at 20:55











  • Dear Jon. Both solutions are perfect but since I need to only a few numbers regarding to the specific available tab widths, I needed only to have the available ones in my chart. I could easily do it in Minitab but there was a lot of other graphical restrictions with that software. With the line diagram in execl, I could not show the trend easily.

    – Hamed
    Jan 7 '16 at 8:03





















2














If I understand your problem correctly, it looks like you have data at an irregular interval, and you want to plot it as XY data but have the X axis grid lines match the X values of the data. That can't be done natively in Excel, and actually defeats the purpose of using a scatter chart.



Line Chart



If you aren't concerned with displaying the data proportionally on the X axis, you can use a line chart:



line chart



This treats the X values as categories and simply stacks the Y values next to each other at a uniform interval on the chart. Notice that you get the actual X values displayed but every point is at an equal interval even though the X values are not. If the X value of the last point was 100, it still would be plotted at the same location as the 1.7.



Scatter Chart



A scatter, or XY, chart plots the X values proportionally. The grids are at fixed intervals to provide a way to visualize the proportionality of the data. That's why the grid lines don't run through all of your data points. You can actually force Excel to plot "apparent" grid lines where you want:



XY chart



This was done by starting at 0.78 and using an interval of 0.24. When you round the X axis values to one decimal place, they display the values you want. However, the grids are still in proportional locations and you can see that the grid lines don't actually run through the data points (except for 1.5, which happened to work out to be an exact X value).



Other Solutions



LCD Grid Lines



If the data lends itself to this solution, which it does in this example, you can use a grid line interval that is a "lowest common denominator" interval to pass through every point:



LCD



This keeps everything proportional plus labels every point. It even adds visual clues to the proportionality of the X values because you can see the number of intervening lines.



Manual Grid Lines



As mentioned earlier, uniform grid lines allow the reader to visualize the proportional nature of the data's X locations. If you need grid lines running through every point, you could manually add them (draw them in), for all points that don't fall on a standard grid line. If you need only those lines, set the X axis to have no grid lines and manually add each one you want. Keep in mind that readers are used to seeing XY data having uniform grid lines, so your chart will be a bit of an optical illusion and people might consider it "misleading".



Data Labels



If the goal is just to display the actual X values so they can be read directly from the chart, the normal way to do that would be to use standard grid lines and add data labels where needed.






share|improve this answer
























  • Thank you very much for complete and useful explanation. I cannot vote for you because of low reputation so please accept my greets here.

    – Hamed
    Dec 3 '15 at 8:49



















-1















  1. Cancel Out the Select Data Source dialog box

  2. Right-click on the axis labels

  3. Select Format Labels






share|improve this answer
























  • And then what??

    – G-Man
    Nov 30 '15 at 22:19











Your Answer








StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














draft saved

draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1007268%2fmanually-adjust-axis-numbering-on-excel-chart%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























4 Answers
4






active

oldest

votes








4 Answers
4






active

oldest

votes









active

oldest

votes






active

oldest

votes









5














It's irregular not to show normal axis labels, because it may cause a lack of comprehension in whoever is reading the chart. I know I would be distracted thinking, "Why did he choose to do that?" But people ask all kinds of questions.



So here is how you'd do what you want.



Add a column of data with all zeros. This will produce a series of data points along the horizontal axis.



Make your chart with all the data (below left).



Adjust your axis as desired (below right).



data and beginning of chart formatting



Hide the horizontal axis labels. Best way is to use custom number format of " " (single space surrounded by double quotes), so there will be room for the data labels without having to manually adjust the plot area size. (top left chart below).



Select the series along the axis, and add data labels. Excel adds Y value labels (all zero) above or left of the points. (top right chart below).



Format the data labels: Select the option to show Y values and deselect other options; select the "below" position; use a number format with one decimal digit. (bottom left chart below).



Delete the legend, and reformat the series along the X axis so it mimics an axis (medium gray line, cross markers using medium gray border and no fill). (bottom right chart below).



enter image description here






share|improve this answer



















  • 1





    Elegant solution, would never have thought of doing it like that myself :)

    – eirikdaude
    Dec 27 '15 at 21:33











  • Clever. I was focusing on the gridlines because that's how the OP displayed the problem, but the actual question wording was about the axis labels.

    – fixer1234
    Dec 27 '15 at 23:06
















5














It's irregular not to show normal axis labels, because it may cause a lack of comprehension in whoever is reading the chart. I know I would be distracted thinking, "Why did he choose to do that?" But people ask all kinds of questions.



So here is how you'd do what you want.



Add a column of data with all zeros. This will produce a series of data points along the horizontal axis.



Make your chart with all the data (below left).



Adjust your axis as desired (below right).



data and beginning of chart formatting



Hide the horizontal axis labels. Best way is to use custom number format of " " (single space surrounded by double quotes), so there will be room for the data labels without having to manually adjust the plot area size. (top left chart below).



Select the series along the axis, and add data labels. Excel adds Y value labels (all zero) above or left of the points. (top right chart below).



Format the data labels: Select the option to show Y values and deselect other options; select the "below" position; use a number format with one decimal digit. (bottom left chart below).



Delete the legend, and reformat the series along the X axis so it mimics an axis (medium gray line, cross markers using medium gray border and no fill). (bottom right chart below).



enter image description here






share|improve this answer



















  • 1





    Elegant solution, would never have thought of doing it like that myself :)

    – eirikdaude
    Dec 27 '15 at 21:33











  • Clever. I was focusing on the gridlines because that's how the OP displayed the problem, but the actual question wording was about the axis labels.

    – fixer1234
    Dec 27 '15 at 23:06














5












5








5







It's irregular not to show normal axis labels, because it may cause a lack of comprehension in whoever is reading the chart. I know I would be distracted thinking, "Why did he choose to do that?" But people ask all kinds of questions.



So here is how you'd do what you want.



Add a column of data with all zeros. This will produce a series of data points along the horizontal axis.



Make your chart with all the data (below left).



Adjust your axis as desired (below right).



data and beginning of chart formatting



Hide the horizontal axis labels. Best way is to use custom number format of " " (single space surrounded by double quotes), so there will be room for the data labels without having to manually adjust the plot area size. (top left chart below).



Select the series along the axis, and add data labels. Excel adds Y value labels (all zero) above or left of the points. (top right chart below).



Format the data labels: Select the option to show Y values and deselect other options; select the "below" position; use a number format with one decimal digit. (bottom left chart below).



Delete the legend, and reformat the series along the X axis so it mimics an axis (medium gray line, cross markers using medium gray border and no fill). (bottom right chart below).



enter image description here






share|improve this answer













It's irregular not to show normal axis labels, because it may cause a lack of comprehension in whoever is reading the chart. I know I would be distracted thinking, "Why did he choose to do that?" But people ask all kinds of questions.



So here is how you'd do what you want.



Add a column of data with all zeros. This will produce a series of data points along the horizontal axis.



Make your chart with all the data (below left).



Adjust your axis as desired (below right).



data and beginning of chart formatting



Hide the horizontal axis labels. Best way is to use custom number format of " " (single space surrounded by double quotes), so there will be room for the data labels without having to manually adjust the plot area size. (top left chart below).



Select the series along the axis, and add data labels. Excel adds Y value labels (all zero) above or left of the points. (top right chart below).



Format the data labels: Select the option to show Y values and deselect other options; select the "below" position; use a number format with one decimal digit. (bottom left chart below).



Delete the legend, and reformat the series along the X axis so it mimics an axis (medium gray line, cross markers using medium gray border and no fill). (bottom right chart below).



enter image description here







share|improve this answer












share|improve this answer



share|improve this answer










answered Dec 27 '15 at 21:16









Jon PeltierJon Peltier

2,9321420




2,9321420








  • 1





    Elegant solution, would never have thought of doing it like that myself :)

    – eirikdaude
    Dec 27 '15 at 21:33











  • Clever. I was focusing on the gridlines because that's how the OP displayed the problem, but the actual question wording was about the axis labels.

    – fixer1234
    Dec 27 '15 at 23:06














  • 1





    Elegant solution, would never have thought of doing it like that myself :)

    – eirikdaude
    Dec 27 '15 at 21:33











  • Clever. I was focusing on the gridlines because that's how the OP displayed the problem, but the actual question wording was about the axis labels.

    – fixer1234
    Dec 27 '15 at 23:06








1




1





Elegant solution, would never have thought of doing it like that myself :)

– eirikdaude
Dec 27 '15 at 21:33





Elegant solution, would never have thought of doing it like that myself :)

– eirikdaude
Dec 27 '15 at 21:33













Clever. I was focusing on the gridlines because that's how the OP displayed the problem, but the actual question wording was about the axis labels.

– fixer1234
Dec 27 '15 at 23:06





Clever. I was focusing on the gridlines because that's how the OP displayed the problem, but the actual question wording was about the axis labels.

– fixer1234
Dec 27 '15 at 23:06













2














This answer deals with Excel-2013, but I suspect Excel-2010 (if that indeed is what you are using) is similar enough that it shouldn't matter. To edit the scale of your axis, right-click on it and select format axis:



enter image description here



This should bring up a dialog containing several choices, to set the interval between the gridlines go to:



Axis Options -> Axis Options -> Units


enter image description here



The major unit sets the interval between major gridlines, and the minor unit sets the interval between minor gridlines. I hope this helped :)






share|improve this answer
























  • Dear Eirikdaude, thank you very much for your answer. The problem with the method you explained is that it produces more numbers in axis including the numbers I need. However, I want only the numbers I have specified would be shown in the axis labels which do not have constant increment steps. Do you have any comments to this? Thanks in advance

    – Hamed
    Dec 1 '15 at 11:06











  • @Hamed, EirikDaude's solution is proper. Why do you only want to show some labels?

    – Jon Peltier
    Dec 27 '15 at 20:55











  • Dear Jon. Both solutions are perfect but since I need to only a few numbers regarding to the specific available tab widths, I needed only to have the available ones in my chart. I could easily do it in Minitab but there was a lot of other graphical restrictions with that software. With the line diagram in execl, I could not show the trend easily.

    – Hamed
    Jan 7 '16 at 8:03


















2














This answer deals with Excel-2013, but I suspect Excel-2010 (if that indeed is what you are using) is similar enough that it shouldn't matter. To edit the scale of your axis, right-click on it and select format axis:



enter image description here



This should bring up a dialog containing several choices, to set the interval between the gridlines go to:



Axis Options -> Axis Options -> Units


enter image description here



The major unit sets the interval between major gridlines, and the minor unit sets the interval between minor gridlines. I hope this helped :)






share|improve this answer
























  • Dear Eirikdaude, thank you very much for your answer. The problem with the method you explained is that it produces more numbers in axis including the numbers I need. However, I want only the numbers I have specified would be shown in the axis labels which do not have constant increment steps. Do you have any comments to this? Thanks in advance

    – Hamed
    Dec 1 '15 at 11:06











  • @Hamed, EirikDaude's solution is proper. Why do you only want to show some labels?

    – Jon Peltier
    Dec 27 '15 at 20:55











  • Dear Jon. Both solutions are perfect but since I need to only a few numbers regarding to the specific available tab widths, I needed only to have the available ones in my chart. I could easily do it in Minitab but there was a lot of other graphical restrictions with that software. With the line diagram in execl, I could not show the trend easily.

    – Hamed
    Jan 7 '16 at 8:03
















2












2








2







This answer deals with Excel-2013, but I suspect Excel-2010 (if that indeed is what you are using) is similar enough that it shouldn't matter. To edit the scale of your axis, right-click on it and select format axis:



enter image description here



This should bring up a dialog containing several choices, to set the interval between the gridlines go to:



Axis Options -> Axis Options -> Units


enter image description here



The major unit sets the interval between major gridlines, and the minor unit sets the interval between minor gridlines. I hope this helped :)






share|improve this answer













This answer deals with Excel-2013, but I suspect Excel-2010 (if that indeed is what you are using) is similar enough that it shouldn't matter. To edit the scale of your axis, right-click on it and select format axis:



enter image description here



This should bring up a dialog containing several choices, to set the interval between the gridlines go to:



Axis Options -> Axis Options -> Units


enter image description here



The major unit sets the interval between major gridlines, and the minor unit sets the interval between minor gridlines. I hope this helped :)







share|improve this answer












share|improve this answer



share|improve this answer










answered Nov 30 '15 at 22:49









eirikdaudeeirikdaude

6172620




6172620













  • Dear Eirikdaude, thank you very much for your answer. The problem with the method you explained is that it produces more numbers in axis including the numbers I need. However, I want only the numbers I have specified would be shown in the axis labels which do not have constant increment steps. Do you have any comments to this? Thanks in advance

    – Hamed
    Dec 1 '15 at 11:06











  • @Hamed, EirikDaude's solution is proper. Why do you only want to show some labels?

    – Jon Peltier
    Dec 27 '15 at 20:55











  • Dear Jon. Both solutions are perfect but since I need to only a few numbers regarding to the specific available tab widths, I needed only to have the available ones in my chart. I could easily do it in Minitab but there was a lot of other graphical restrictions with that software. With the line diagram in execl, I could not show the trend easily.

    – Hamed
    Jan 7 '16 at 8:03





















  • Dear Eirikdaude, thank you very much for your answer. The problem with the method you explained is that it produces more numbers in axis including the numbers I need. However, I want only the numbers I have specified would be shown in the axis labels which do not have constant increment steps. Do you have any comments to this? Thanks in advance

    – Hamed
    Dec 1 '15 at 11:06











  • @Hamed, EirikDaude's solution is proper. Why do you only want to show some labels?

    – Jon Peltier
    Dec 27 '15 at 20:55











  • Dear Jon. Both solutions are perfect but since I need to only a few numbers regarding to the specific available tab widths, I needed only to have the available ones in my chart. I could easily do it in Minitab but there was a lot of other graphical restrictions with that software. With the line diagram in execl, I could not show the trend easily.

    – Hamed
    Jan 7 '16 at 8:03



















Dear Eirikdaude, thank you very much for your answer. The problem with the method you explained is that it produces more numbers in axis including the numbers I need. However, I want only the numbers I have specified would be shown in the axis labels which do not have constant increment steps. Do you have any comments to this? Thanks in advance

– Hamed
Dec 1 '15 at 11:06





Dear Eirikdaude, thank you very much for your answer. The problem with the method you explained is that it produces more numbers in axis including the numbers I need. However, I want only the numbers I have specified would be shown in the axis labels which do not have constant increment steps. Do you have any comments to this? Thanks in advance

– Hamed
Dec 1 '15 at 11:06













@Hamed, EirikDaude's solution is proper. Why do you only want to show some labels?

– Jon Peltier
Dec 27 '15 at 20:55





@Hamed, EirikDaude's solution is proper. Why do you only want to show some labels?

– Jon Peltier
Dec 27 '15 at 20:55













Dear Jon. Both solutions are perfect but since I need to only a few numbers regarding to the specific available tab widths, I needed only to have the available ones in my chart. I could easily do it in Minitab but there was a lot of other graphical restrictions with that software. With the line diagram in execl, I could not show the trend easily.

– Hamed
Jan 7 '16 at 8:03







Dear Jon. Both solutions are perfect but since I need to only a few numbers regarding to the specific available tab widths, I needed only to have the available ones in my chart. I could easily do it in Minitab but there was a lot of other graphical restrictions with that software. With the line diagram in execl, I could not show the trend easily.

– Hamed
Jan 7 '16 at 8:03













2














If I understand your problem correctly, it looks like you have data at an irregular interval, and you want to plot it as XY data but have the X axis grid lines match the X values of the data. That can't be done natively in Excel, and actually defeats the purpose of using a scatter chart.



Line Chart



If you aren't concerned with displaying the data proportionally on the X axis, you can use a line chart:



line chart



This treats the X values as categories and simply stacks the Y values next to each other at a uniform interval on the chart. Notice that you get the actual X values displayed but every point is at an equal interval even though the X values are not. If the X value of the last point was 100, it still would be plotted at the same location as the 1.7.



Scatter Chart



A scatter, or XY, chart plots the X values proportionally. The grids are at fixed intervals to provide a way to visualize the proportionality of the data. That's why the grid lines don't run through all of your data points. You can actually force Excel to plot "apparent" grid lines where you want:



XY chart



This was done by starting at 0.78 and using an interval of 0.24. When you round the X axis values to one decimal place, they display the values you want. However, the grids are still in proportional locations and you can see that the grid lines don't actually run through the data points (except for 1.5, which happened to work out to be an exact X value).



Other Solutions



LCD Grid Lines



If the data lends itself to this solution, which it does in this example, you can use a grid line interval that is a "lowest common denominator" interval to pass through every point:



LCD



This keeps everything proportional plus labels every point. It even adds visual clues to the proportionality of the X values because you can see the number of intervening lines.



Manual Grid Lines



As mentioned earlier, uniform grid lines allow the reader to visualize the proportional nature of the data's X locations. If you need grid lines running through every point, you could manually add them (draw them in), for all points that don't fall on a standard grid line. If you need only those lines, set the X axis to have no grid lines and manually add each one you want. Keep in mind that readers are used to seeing XY data having uniform grid lines, so your chart will be a bit of an optical illusion and people might consider it "misleading".



Data Labels



If the goal is just to display the actual X values so they can be read directly from the chart, the normal way to do that would be to use standard grid lines and add data labels where needed.






share|improve this answer
























  • Thank you very much for complete and useful explanation. I cannot vote for you because of low reputation so please accept my greets here.

    – Hamed
    Dec 3 '15 at 8:49
















2














If I understand your problem correctly, it looks like you have data at an irregular interval, and you want to plot it as XY data but have the X axis grid lines match the X values of the data. That can't be done natively in Excel, and actually defeats the purpose of using a scatter chart.



Line Chart



If you aren't concerned with displaying the data proportionally on the X axis, you can use a line chart:



line chart



This treats the X values as categories and simply stacks the Y values next to each other at a uniform interval on the chart. Notice that you get the actual X values displayed but every point is at an equal interval even though the X values are not. If the X value of the last point was 100, it still would be plotted at the same location as the 1.7.



Scatter Chart



A scatter, or XY, chart plots the X values proportionally. The grids are at fixed intervals to provide a way to visualize the proportionality of the data. That's why the grid lines don't run through all of your data points. You can actually force Excel to plot "apparent" grid lines where you want:



XY chart



This was done by starting at 0.78 and using an interval of 0.24. When you round the X axis values to one decimal place, they display the values you want. However, the grids are still in proportional locations and you can see that the grid lines don't actually run through the data points (except for 1.5, which happened to work out to be an exact X value).



Other Solutions



LCD Grid Lines



If the data lends itself to this solution, which it does in this example, you can use a grid line interval that is a "lowest common denominator" interval to pass through every point:



LCD



This keeps everything proportional plus labels every point. It even adds visual clues to the proportionality of the X values because you can see the number of intervening lines.



Manual Grid Lines



As mentioned earlier, uniform grid lines allow the reader to visualize the proportional nature of the data's X locations. If you need grid lines running through every point, you could manually add them (draw them in), for all points that don't fall on a standard grid line. If you need only those lines, set the X axis to have no grid lines and manually add each one you want. Keep in mind that readers are used to seeing XY data having uniform grid lines, so your chart will be a bit of an optical illusion and people might consider it "misleading".



Data Labels



If the goal is just to display the actual X values so they can be read directly from the chart, the normal way to do that would be to use standard grid lines and add data labels where needed.






share|improve this answer
























  • Thank you very much for complete and useful explanation. I cannot vote for you because of low reputation so please accept my greets here.

    – Hamed
    Dec 3 '15 at 8:49














2












2








2







If I understand your problem correctly, it looks like you have data at an irregular interval, and you want to plot it as XY data but have the X axis grid lines match the X values of the data. That can't be done natively in Excel, and actually defeats the purpose of using a scatter chart.



Line Chart



If you aren't concerned with displaying the data proportionally on the X axis, you can use a line chart:



line chart



This treats the X values as categories and simply stacks the Y values next to each other at a uniform interval on the chart. Notice that you get the actual X values displayed but every point is at an equal interval even though the X values are not. If the X value of the last point was 100, it still would be plotted at the same location as the 1.7.



Scatter Chart



A scatter, or XY, chart plots the X values proportionally. The grids are at fixed intervals to provide a way to visualize the proportionality of the data. That's why the grid lines don't run through all of your data points. You can actually force Excel to plot "apparent" grid lines where you want:



XY chart



This was done by starting at 0.78 and using an interval of 0.24. When you round the X axis values to one decimal place, they display the values you want. However, the grids are still in proportional locations and you can see that the grid lines don't actually run through the data points (except for 1.5, which happened to work out to be an exact X value).



Other Solutions



LCD Grid Lines



If the data lends itself to this solution, which it does in this example, you can use a grid line interval that is a "lowest common denominator" interval to pass through every point:



LCD



This keeps everything proportional plus labels every point. It even adds visual clues to the proportionality of the X values because you can see the number of intervening lines.



Manual Grid Lines



As mentioned earlier, uniform grid lines allow the reader to visualize the proportional nature of the data's X locations. If you need grid lines running through every point, you could manually add them (draw them in), for all points that don't fall on a standard grid line. If you need only those lines, set the X axis to have no grid lines and manually add each one you want. Keep in mind that readers are used to seeing XY data having uniform grid lines, so your chart will be a bit of an optical illusion and people might consider it "misleading".



Data Labels



If the goal is just to display the actual X values so they can be read directly from the chart, the normal way to do that would be to use standard grid lines and add data labels where needed.






share|improve this answer













If I understand your problem correctly, it looks like you have data at an irregular interval, and you want to plot it as XY data but have the X axis grid lines match the X values of the data. That can't be done natively in Excel, and actually defeats the purpose of using a scatter chart.



Line Chart



If you aren't concerned with displaying the data proportionally on the X axis, you can use a line chart:



line chart



This treats the X values as categories and simply stacks the Y values next to each other at a uniform interval on the chart. Notice that you get the actual X values displayed but every point is at an equal interval even though the X values are not. If the X value of the last point was 100, it still would be plotted at the same location as the 1.7.



Scatter Chart



A scatter, or XY, chart plots the X values proportionally. The grids are at fixed intervals to provide a way to visualize the proportionality of the data. That's why the grid lines don't run through all of your data points. You can actually force Excel to plot "apparent" grid lines where you want:



XY chart



This was done by starting at 0.78 and using an interval of 0.24. When you round the X axis values to one decimal place, they display the values you want. However, the grids are still in proportional locations and you can see that the grid lines don't actually run through the data points (except for 1.5, which happened to work out to be an exact X value).



Other Solutions



LCD Grid Lines



If the data lends itself to this solution, which it does in this example, you can use a grid line interval that is a "lowest common denominator" interval to pass through every point:



LCD



This keeps everything proportional plus labels every point. It even adds visual clues to the proportionality of the X values because you can see the number of intervening lines.



Manual Grid Lines



As mentioned earlier, uniform grid lines allow the reader to visualize the proportional nature of the data's X locations. If you need grid lines running through every point, you could manually add them (draw them in), for all points that don't fall on a standard grid line. If you need only those lines, set the X axis to have no grid lines and manually add each one you want. Keep in mind that readers are used to seeing XY data having uniform grid lines, so your chart will be a bit of an optical illusion and people might consider it "misleading".



Data Labels



If the goal is just to display the actual X values so they can be read directly from the chart, the normal way to do that would be to use standard grid lines and add data labels where needed.







share|improve this answer












share|improve this answer



share|improve this answer










answered Dec 2 '15 at 20:38









fixer1234fixer1234

19k144982




19k144982













  • Thank you very much for complete and useful explanation. I cannot vote for you because of low reputation so please accept my greets here.

    – Hamed
    Dec 3 '15 at 8:49



















  • Thank you very much for complete and useful explanation. I cannot vote for you because of low reputation so please accept my greets here.

    – Hamed
    Dec 3 '15 at 8:49

















Thank you very much for complete and useful explanation. I cannot vote for you because of low reputation so please accept my greets here.

– Hamed
Dec 3 '15 at 8:49





Thank you very much for complete and useful explanation. I cannot vote for you because of low reputation so please accept my greets here.

– Hamed
Dec 3 '15 at 8:49











-1















  1. Cancel Out the Select Data Source dialog box

  2. Right-click on the axis labels

  3. Select Format Labels






share|improve this answer
























  • And then what??

    – G-Man
    Nov 30 '15 at 22:19
















-1















  1. Cancel Out the Select Data Source dialog box

  2. Right-click on the axis labels

  3. Select Format Labels






share|improve this answer
























  • And then what??

    – G-Man
    Nov 30 '15 at 22:19














-1












-1








-1








  1. Cancel Out the Select Data Source dialog box

  2. Right-click on the axis labels

  3. Select Format Labels






share|improve this answer














  1. Cancel Out the Select Data Source dialog box

  2. Right-click on the axis labels

  3. Select Format Labels







share|improve this answer












share|improve this answer



share|improve this answer










answered Nov 30 '15 at 17:36









Scott HoltzmanScott Holtzman

3092513




3092513













  • And then what??

    – G-Man
    Nov 30 '15 at 22:19



















  • And then what??

    – G-Man
    Nov 30 '15 at 22:19

















And then what??

– G-Man
Nov 30 '15 at 22:19





And then what??

– G-Man
Nov 30 '15 at 22:19


















draft saved

draft discarded




















































Thanks for contributing an answer to Super User!


  • Please be sure to answer the question. Provide details and share your research!

But avoid



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1007268%2fmanually-adjust-axis-numbering-on-excel-chart%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







Popular posts from this blog

Сан-Квентин

Алькесар

Josef Freinademetz