Scatter plot in Excel 2016 displays extra data points

Question

When creating a scatter plot using Excel 2016 in some cases extra data points are displayed. As far as I can figure out this happens when the following conditions are met:

• The Y-axis of the scatter plot is displayed logarithmic AND
• Multiple data series are included in the plot AND
• The first cell of at least one of the data series contains the value 0, a negative value, text or an error (i.e. anything that cannot be plotted on a logarithmic scale)

The extra data point(s) are shown super-exposed on top of the last data point of a data series, using the marker style of the next series (when the first data point of that next series contains a value that cannot be plotted). When hovering over those extra data points, the x & y values associated with the first data point of the next series are displayed (see picture below).

This graph uses the following data table:

	Other	A	B	C	D
01/01/1950	98.70%	1.30%	0.00%	0.00%	0.00%
01/01/1951	99.21%	0.79%	0.00%	0.00%	0.00%
01/01/1952	99.51%	0.49%	0.00%	0.00%	0.00%
01/01/1953	97.67%	1.95%	0.39%	0.00%	0.00%
01/01/1954	95.22%	4.31%	0.48%	0.00%	0.00%
01/01/1955	85.48%	13.42%	0.00%	0.00%	1.10%
01/01/1956	78.88%	20.13%	0.99%	0.00%	0.00%
01/01/1957	75.19%	24.34%	0.47%	0.00%	0.00%
01/01/1958	71.33%	26.88%	1.67%	0.12%	0.00%
01/01/1959	64.28%	33.30%	2.09%	0.00%	0.00%
01/01/1960	54.74%	41.86%	3.30%	0.00%	0.00%
01/01/1961	33.40%	62.96%	3.07%	0.10%	0.00%
01/01/1962	21.04%	76.75%	1.83%	0.29%	0.00%
01/01/1963	13.21%	83.45%	1.64%	0.72%	0.26%
01/01/1964	8.57%	87.44%	2.82%	0.76%	0.00%
01/01/1965	5.05%	89.84%	3.51%	1.47%	0.00%
01/01/1966	2.50%	93.51%	2.43%	1.44%	0.00%
01/01/1967	1.62%	94.90%	2.32%	0.87%	0.00%
01/01/1968	0.84%	95.39%	1.82%	1.49%	0.06%
01/01/1969	0.30%	96.74%	1.15%	1.27%	0.06%
01/01/1970	-0.13%	96.33%	1.16%	1.74%	0.06%
01/01/1971	0.17%	97.19%	0.46%	1.61%	0.29%
01/01/1972	0.06%	96.85%	0.71%	2.08%	0.18%
01/01/1973	0.06%	96.48%	0.49%	1.88%	0.67%
01/01/1974	-0.15%	96.35%	0.22%	2.41%	0.73%
01/01/1975	0.53%	96.07%	0.23%	1.21%	1.66%
01/01/1976	0.89%	93.50%	0.00%	1.71%	3.57%
01/01/1977	0.08%	88.28%	0.08%	1.15%	9.02%
01/01/1978	1.25%	72.03%	0.10%	1.54%	23.53%
01/01/1979	0.73%	57.10%	0.15%	1.02%	40.56%
01/01/1980	1.42%	29.10%	0.11%	0.33%	68.60%
01/01/1981	0.30%	12.29%	0.00%	0.09%	87.02%
01/01/1982	0.09%	4.61%	0.00%	25.00%	95.20%

Can anyone tell me what is the cause of this problem?

How can this problem be prevented?

Answer 1

I can not reproduce the issue, when I recreate the chart based on your data I can see this data points too, but they are correct! Your data shows this values in the last row, e.g. for 2 values:

If you need further help please upload your file (maybe with anonymous data) on an online file hoster like https://www.dropbox.com and post the download link here.

A macro to anonymize data in selected cells can be downloaded here:
https://www.dropbox.com/s/rkfxuh85j5wyj9y/modAnonymize.bas?dl=1

If you want to upload it to OneDrive, it is important to follow these steps:
Zip your file!
https://support.microsoft.com/en-us/windows/zip-and-unzip-files-8d28fa72-f2f9-712f-67df-f80cf89fd4e5
Login to https://onedrive.live.com (Use the same Login ID and password as for this forum).
Click Upload in the top and choose your Zip file.
After uploading, right click the file and choose share.
Click Get a Link.
Copy the link and paste it here.

Andreas.

Answer 2

Dear Andreas,

thank you for you quick reply. I was a bit surprised that it wasn’t possible reproduce this issue. The graph you prepared is exactly as it is supposed to be.

Therefore, I opened the same file on a different computer. In that case it works fine, so it doesn’t appear to be an issue related to a damaged file. Therefore, I checked the Windows and Office versions on both computers that I used and they are not the same.

For the computer where the problem occurs these are:

Windows 10 version 21H1 Build 19043.1110

Office 2016 Version 16.0.14228.20204

For the computer where the problem does not occur these are:

Windows 10 Version 2004 Build 19041.1110

Office 2016 Version 16.0.4256.1001

Seeing that, I was thinking it might be an issue related to a specific build version or possibly to a damaged installation. I could run a repair action on my Office 2016 installation to see if that fixes the problem, though I will hold off on that for now until I hear back from you.

In the mean time I did find a workaround for this issue. When I replace the 0 values in my data table with #N/A, the problem does not occur.

In the uploaded file: https://1drv.ms/x/s!AiFSvS578xo4gkVkGDnTWg8Fxs4x?e=esblgR I created 2 graphs. Chart1 uses the original data with 0 values, whilst Chart2 uses the modified data where the 0 values are replaced with #N/A.

Kind regards,

Peter

Answer 3

Therefore, I opened the same file on a different computer. In that case it works fine, so it doesn’t appear to be an issue related to a damaged file. Therefore, I checked the Windows and Office versions on both computers that I used and they are not the same.

Hi Peter,

check the Printer Driver on the PC that shows the issue and try also to set a different one as default in Windows.

It might be also possible that the issue is due to a COM AddIn, therefore start Excel in Safe Mode and check the behaviour. Please follow these steps:

Close Excel
Press and hold the CTRL key
Open Excel
Wait for a message to appear and ask for "Safe Mode"
Release the CTRL key
Click Yes
After Excel opens
Click File\Open and select your file
Press and hold the SHIFT key
Click Open
Wait for your file to open
Release the SHIFT key
Test the behavior

BTW, when you want to share files with issues, do NOT upload them to OneDrive as XLS*. Zip your file, then upload!

Andreas.

Answer 4

Dear Andreas,

I checked the printer driver. By default this was set to "Microsoft print to PDF". I changed this to a different printer. This had no effect on the issue.

Regarding AddIns, there is one COM AddIn installed for a label printer and there are five active Excel AddIns:

1. AnalysisToolPak
2. AnalysisToolPak - VBA
3. Solver Add-in
4. custom ribbon
5. integration with a third party dll for performing a set of highly complicated & specialized calculations

No document related AddIns are present.

As per your recommendation I opened Excel in safe mode and then tried opening the file again as you suggested (holding down the Shift key while opening the file). This however gave an error "Cannot open Microsoft Excel Add-In for editing. Please edit the source document instead.". When I click on the OK button in this error message box, the file is opened in a new instance of Excel, but outside of safe mode. The problem then still persists.

When in safe mode I open the file without holding the Shift key, the file opens without any error message in safe mode. Also then the problem persists.

I have uploaded the zipped file at: https://1drv.ms/u/s!AiFSvS578xo4gknPbbA8vR0F9McR?e=rBctEd

Kind regards,

Peter

Answer 5

No document related AddIns are present.

As per your recommendation I opened Excel in safe mode and then tried opening the file again as you suggested (holding down the Shift key while opening the file). This however gave an error "Cannot open Microsoft Excel Add-In for editing. Please edit the source document instead.".

Hi Peter,

Document related doesn't matter in this case. Deactivate all AddIns and check the behavior. If the issue persists check your system as shown here

System file check (SFC) Scan and Repair System Files & - Microsoft Community

Then perform an Online repair

Repair an Office application - Office Support

I've checked your file with various versions from 2007 / Windows Vista till 2019 / Windows 10, I can not reproduce the issue.

Whatever it is, it's on that machine. Unfortunately I cannot offer any further help.

Andreas.