Unable to format date axes on excel

Question

I have various dates from 1995 to now in my excel database, I am trying to count no of entries for each date and plot the date(x-axis) vs count(y-axis) plot

So first I create a pivot on date column and then create a table of date vs count and then plot it,

I want to show x axis at 2 year interval and not individual date type, but when I click on format axes it doesn't allow to change intervals or view bounds

Here is what I am currently plotting and this is what I want

Answer 1

In my version of Excel 365, it seems that a line chart derived from a Pivot Table does not have the options to set the bounds or units on the horizontal axis, and also that setting the Number format on this dialog has no effect.

A possible workaround is to select those two columns from the Pivot Table output, then copy => paste special => numbers and value formats to some other area.

Ensure that you do not include Totals or Subtotals.

If you then create a line chart from that copied value, you should see the appropriate options from the Format Axis dialog:

The latter allows you to set the bounds and intervals using dates.