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:
- How to prevent your line series from dropping to zero.
- How to create dynamic charts based on a Point of View (in this case month selection which is controlled by a data validation list).
- 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.
- 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.