I finally found an easy way to make charts automatically accommodate changes in the size of the data series.
Having many rows of data that I want to chart individually, and have the charts adjust themselves automatically when I add new data points to the ends of the row, I tried several complicated methods that didn’t work, I finally discovered this method that works well enough that I wanted to make it known to help others who I have seen struggling with the same problem during my on-line search for a solution.
This method should work for columns as well as rows of data.
- Extend the rows of the data series to a limit sufficient to meet your future needs.
- Fill the extended area with a non-numerical character, like an asterisk. Doing this gives the charting algorithm something chew on otherwise it will ignore empty cells and shrink its scope to the numerical data only.
- Create your chart so that data-range and data-series encompass all of your data and the additional cells containing asterisks.
- Your chart should have a curve bunched at the left edge followed by nothing in the chart area and asterisks in the X or Y axis after the numerical data.
- Select the columns containing the asterisks then hide the columns.
- Your chart should now exclude the asterisked placeholders and show your data expanded to fill the chart area.
- When you need to add a new column of data, unhide the columns of asterisked data then add your new data to the next column after the last numerical entry, replacing the asterisk.
- Again hide that columns containing asterisks and your chart will show the new data-point appended to the right side of the curve.
This is a simple workaround to an annoying problem that should suffice until charts can accept named-ranges. Good luck! I hope this helps.