As the Series X value, select the independent variable for your data point. In the Edit Series window, do the following: Enter a meaningful name in the Series name box, e.g. In the Select Data Source dialogue box, click the Add button. so the second series will have x coordinates of 1- n. Right-click any axis in your chart and click Select Data. The second series will plot the y coordinate correctly, but then substitutes my X coordinate with a Point If I create a new blank scatter chart and set up the series one by one, the first series will use my designated coordinates from the columns specified. I have a large spreadsheet of data and need to plot relationships between data using data for the X axis from one column and data for the y axis from a different column.Įxcel substitutes my designated X axis value and instead plots the y coordinate with a point number (sequential lists from 1 to N) as the X coordinate. WTF? What kind of geniuses decided that huh? Excel is broken. I now have the latest version of Office 2016.Įxcel cannot accept my selection of x Axis data coordinate, and substitutes it with a point number. I can then apply a custom date format to show only the year in the horizontal axis.I've used every version of Excel since 1991.
Then enter Januas the minimum and as the maximum. To show only years, and make sure the line is displayed correctly across the range, I can set units to 1 year. If I plot the data as a line chart, Excel correctly interprets the dates and builds an automatic horizontal axis to fit the date range, with the unusual setting of 8 months for units. This is monthly stock price data over a period of more than 15 years, from July 2001 through May 2017, in more than 100 rows. Notice that even though the axis type is now text, Excel still understands the dates.įor example, I can apply a different date format, and the chart immediately updates. Now the data points are evenly spaced, and line up with the dates shown on the horizontal axis. You can get to it by hovering over the data series, right clicking, and selecting the Format Data Labels. Excel allows you to put a label with each data point in a data series, and we will use that feature to do it.
#Excel x axis data points how to#
How to fix date format for X-axis in Excel. In that case, you can switch the type to text. Next, we need to put numbers beside the tick marks on the third axis we created. Change the Category to Percentage and on doing so the axis data points will now be shown in the form of. However, there may be times where you want a simple, even distribution. The axis shows an accurate distribution of the data. I would like the X axis data points to also line up with the vertical lines on the graph rather than between the lines.
Now it's clear that the stock price data is plotted across the date range, and the data points don't necessarily line up with the dates in the axis. When I create a chart, the Y axis data points line up with the horizontal lines on the graph while the X axis data points line up between the vertical lines. It'll be easier to see what's happening if I add data labels and drop lines to the chart. This is a little strange and maybe even confusing. Notice since we have a large date range, the interval is set to 1 year, and all dates are January 1. You can see category in the name, and if I open the format task pane to axis options, you can see Excel is using the Automatic setting, which, since we have valid dates, means we have options for minimum and maximum dates, as well as date intervals. If plot this stock price data as a line chart, the horizontal axis is automatically set up as a category axis with a type of "date". When you create a chart using valid dates on a horizontal axis, Excel automatically sets the axis type to date.įor example, this stock price data is spaced out over a period of more than 10 years, in random intervals. In this video, we'll look at an example of how Excel plots dates on a horizontal axis.
0 kommentar(er)
