Excel – Chart options missing in excel 2016

A workaround but I would install AutoHotkey and set it to immediately dismiss that dialog when it appears: http://www.autohotkey.com/docs/commands/SetTimer.htm

; Example #1: Close unwanted windows whenever they appear:
#Persistent
SetTimer, CloseMailWarnings, 250
return

CloseMailWarnings:
WinClose, Microsoft Excel, Negative or zero values cannot be plotted
return

You'll soon think of 50 windows and dialogs you'd like to close as soon as they appear so the initial effort to install it and set it up will soon pay off.

You can't specify specific dates in any Excel axis, at best you can define the start/end/interval of the axis markers. But, you can create a custom axis to represent the values you want shown.

1. Ideally, convert your data into an Excel Data Table (not required, but will generally make life easier for you).
2. Add a "helper" column (or row, if your data stays in your original format) named horizontal_labels with all 0's.
3. Add another data series horizontal_labels to your chart with the following values:
   X values = Year
   Y values = horizontal_labels
4. Format the default horizontal axis to remove labels and ticks.
   • Click on the axis to select it.
   • Right-click on the axis and select Format Axis from the context menu.
   • In the Format Axis menu, choose Axis Options (icon looks like a 3 series column chart).
   • Expand Tick Marks and set Major type and Minor Type both to None.
   • Expand Labels and set Label Position to None.
   • Each change should take effect immediately, so you can see the changes step-by-step.
5. Add Data Labels for your horizontal_labels data series, and format them to show the X value, below the marker.
6. Format to taste.
7. Bonus, if you want to add custom vertical gridlines to match your dates, just use Y Error bars for your horizontal_labels data series, formatted to taste.