Scatter Chart plotting row numbers from data sheet / or / not plotting negative values


New Member
Apr 11, 2019
Using Excel 365, I am constructing vertical timelines by making scatter charts, following this method:

In that url, the first illustration shows the desired output; second one shows the simple data table. I am not using a downloaded template, rather, following the steps described. (My data files are small - around 40 lines of the three columns shown in that urls data table.)

Because the desired timeline is long, I divided it into two files (1963-2007 and 2008-present). The first file produced a chart just as shown in that url -- Years on Y axis, a position marker on X, and data labels directly input from a text column.

Here's the problem -

Last night I had the second timeline's file set up. The chart-in-progress was displaying all data points in proper X-Y position, with data labels of selected text as directed. However, at that stage of production, several text-box data labels were positioned on top of each other. So the next steps were to adjust the display by (1) changing some position numbers in the data table and (2) manually resizing some text boxes.

Today I returned to the 2d file, did a tiny amount of text editing, and the chart blew up - data points disappearing, labels knocked back to overlapping formats, etc. Since then I can't reproduce the initial good result from the first file.

Now, in a fresh file, attempting to repeat that process with a second data set, in same format.
Three different types of behavior are thwarting me:

(a) chart tries to plot points using data sheet row numbers on X axis, instead of the column of position numbers (eg row numbers 2 thru 42 - those numbers are not typed anywhere, instead of using the values in Position column which range between 9 and -9).

(b) If I get the chart redirected to plot from the column of Position numbers, then the chart plots data points only from the rows that contain a number of 0 or greater, and not from any row that contains a negative number.

(c) When I edited the Position column to contain only positive numbers, every data row was plotted. However, when I used Axis format commands to reposition the Y-axis (Year) labels to the center of the page, every data point that should have plotted to left of that axis label disappeared. (The full set of X-Axis Position labels remained, but the left half of the plot went blank.)

Since the first file had worked perfectly, I suspect corruption. So tried new clean files, with data put in using Copy | Paste Special |Values -- followed instructions again as before -- kept hitting these problems.

Can't find any description of scatter chart problems that sound like these. Suggestions?

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Watch MrExcel Video

Forum statistics

Latest member