0

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 plottingcurrent and this is what I want desire

Improve this question
2
  • 1
    So make a separate x axis column with just the years and use that.
    – Solar Mike
    Commented Jun 5 at 9:10
  • @Excellor when I choose only years in pivot table and plot a line graph it does not show the changes within that particular year , it just connect the sum of all years and make a plot but I want to show the fluctuations within the years as well as shown in desired plot
    – aryanknp
    Commented Jun 5 at 9:16

1 Answer 1

Reset to default
2

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:

enter image description here

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

Improve this answer
1
  • yeah this was the exact issue
    – aryanknp
    Commented Jun 10 at 5:12

Not the answer you're looking for? Browse other questions tagged or ask your own question.