Forum Discussion

SunilChitre's avatar
SunilChitre
Copper Contributor
Feb 03, 2023

Dynamic series in chart

I am a novice to EXCEL and trying to prepare a monthly progress chart as per the attached WB. It is noticed that as per actuals in the part of the year if I have blanks for subsequent months my series takes zero and extends till the end of the year. I am looking for stopping the line chart at the end of the month.  Table as below.

 

MonthPlanActuals PlanActuals
Jan1915 1915
Feb1918 3833
Mar1919 5752
Apr1920 7672
May1925 9597
Jun1921 114118
Jul1920 133138
Aug1916 152154
Sep19  171 
Oct19  190 
Nov19  209 
Dec19  228 

 

This is how the chart looks. I do not want actuals scattered with lines extending beyond Aug.

 

Secondly, I am not able to get the months in X-axis. 

Any suggestions?

 

 

excel

1 Reply

Sort By
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Feb 03, 2023

    SunilChitre 

    Click anywhere on the chart.

    Activate the 'Chart Design' tab of the ribbon and click 'Select Data'.

    Click the 'Hidden and Empty Cells button'.

    Select Gaps as option for 'Show empty cells as', then OK your way out.

Resources