Count of Years in column

Making the table dynamic is easy.

but first, why do you want to get rid of the helper? It is needed to summarise the Table in a simple way so that Excel can produce the chart. If it distresses you, call it a summary table, not a helper.

The easiest workaround is (of course) to place the chart on top of the summary Table. Then you can't see it.

I used 3 dynamic Named Ranges, similar to this:

=Sheet1!$H$4:INDEX(Sheet1!$H:$H,MATCH(1E+100,Sheet1!$H:$H))

to adjust ranges automatically (Called dates, Year and Count). CTRL-F3 to view.

I extended the years in the Summary Table down to 2025. I used this formula to count the dates per year:

=IFERROR(1/(1/SUMPRODUCT(--(YEAR(Dates)=G4))),NA())

This returns the #N/A error, which Excel will ignore, when there are no dates in the year. I then referenced Year and Count for the chart, making sure to include the sheet name as well, =Sheet1!Count and =Sheet1!Year. Excel will replace those with the filename.

Now it's all dynamic. If desired, the ugly #N/A errors can be hidden by conditional formatting.