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

AuntRuth

New Member
Joined
Apr 11, 2019
Messages
1
Using Excel 365, I am constructing vertical timelines by making scatter charts, following this method: https://www.vertex42.com/ExcelTemplates/vertical-timeline.html

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?
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Forum statistics

Threads
1,223,880
Messages
6,175,154
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top