You’ll face two main problems when creating dynamic line charts in Excel:

Problem 1: Line series crashes to zero once you attempt to cut-off the series from a certain point. For example you’d like to control the length of the line based on a drop-down. So when the user selects June, the line series shows data only until June.

Problem 2: Positioning the series legend at the end of the line series poses a problem. The line can get longer or shorter, but you’d like the series label to dynamically adjust its position.

In this tutorial you learn 4 tricks:

1. How to prevent your line series from dropping to zero.
2. How to create dynamic charts based on a Point of View (in this case month selection which is controlled by a data validation list).
3. How to integrate your legend inside the chart – i.e. at the end of the line series for improved readability. This generally is not a problem if your series is fixed to a certain number of categories (for example for Budget data we generally have 12 months available). But for a dynamic series such as Actual data, the series expands as the data for the next months come in. In this case we need to use a trick to get the legend, move with the series as the line gets longer.
4. How to hide or make error values in cells invisible with conditional formatting.

Watch the steps in this video:

Feel free to Download the Workbook HERE.

Use these techniques in your own reports

Unbeatable value!