The following describes the steps to create a Dynamic Chart that adjusts, expands, and shortens based on user input date ranges and user defined input from a drop-down list for plotting. These dynamic charts can be used on Dashboards and Summary pages of a financial model for easy presentation, and it updates automatically without any extra work.
The four easy steps for creating a Dynamic Chart from a source (data) are:
1. Prepare the data first to which the data in the graph links to
2. Create a Drop-Down List for the Series Names that are to be plotted
3. Define Name Ranges for X-axis data series and Y-axis data series which will automatically update as user inputs the start and end point of the ranges for X or Y axis data inputs
4. Create a chart using the Series Names from the Drop-Down List, the Name Ranges created for X and Y axis data
Step 1: Prepare the data
The first step is to prepare the data. In this case, a new sheet (a Summary tab or a Dashboard), is created and the specific data series or points that are to be plotted, are presented on this tab. These data points are links to the sources of the data on other tabs.
As described in the Video, the main data is residing on Sheet 1 of the excel workbook. We have then created a more presentable and dynamic data dashboard on Sheet 2. In such case, if something goes wrong in the graph or data, we know the source of the data is intact and the mistake is somewhere in the dashboard data.
On Sheet 2, we have made this data dynamic such that the user can input a Start_Date and End_Date and only the data for that specified range will appear on the sheet. We have done this through the use of three True and False flags and IF functions. Please refer to the video for an overview of the methodology as well as the formulas below.
Step 2: Create a Drop-Down List for Series Names for plotting
The second step is to create a drop-down list (Alt + D + L which is the shortcut to bring up the data validation window) and choose List under the Allow section and select the series of your choice under the Source section. We sourced the data from Sheet 2 Data Labels which we dynamically prepared. If you have checked the ‘ignore blank’ in the Data Validation window, the blank rows are ignored, and the list looks coherent. This is just in case we have added blank lines in between the rows of the series.
On Sheet 2 we have create three drop-down lists, two for Start and End Dates and one for Series Names to be plotted.
Step 3: Define Name Ranges for X-axis data series and Y-axis data series for automatic updating
The third step is to create name ranges for the data inputs. This would allow for the input data of the graph to become dynamic and once the user changes the start and end date inputs, the data expands and contracts accordingly for purposes of plotting on the graph. This would also make the X and Y axis range on the graph dynamic.
We have broken down this step into three parts.
Part 1: We create a Current Selection using Index() and Match() functions to only display the Y-axis data points to be plotted in the graph based on user defined inputs (i.e. Start_Date, End_Date, Series Name from drop-down lists). In other words, we match the x and y (rows and columns) position of the data point for Current Selection series in the prepared data section. The formulas and inputs for Current Selection row are displayed below.
Part 2: We create Dynamic Names Ranges for the Current Selection data.
We do this part using Index functions. The index function in this case returns the reference to the corresponding values in the range of cells. For instance, when the user inputs 2019 as the Start_Date and 2022 as the End_Date, the combination of the Index function shown below will return E34:H34 for the Current Selection and E5:H5 for the Dates. The combination is in the form of Index():Index() and we would then use Match() functions to find the start and end point in the range. The formulas and inputs for creating the dynamic references are displayed below.
Part 3: We copy and paste these created formulas for Name Ranges in the Name Manager window
Press Ctrl + F3 (the shortcut for Name Manager Window) and input the formulas we created in Part 2 in the Name Manager window for the two created Names: Current_Selection and Dates.
Step 4: Create a chart using the Series Names from the Drop-Down List, the Name Ranges created for X and Y axis data
The fourth step is to insert a chart and select the proper data for Y and X axis. Click anywhere blank outside our data ranges on Sheet 2. From the top bar select Insert and insert an empty chart. Right click on the chart and click Select Data to bring the Select Data Source window. Click the Add button under the Legend Entries to bring up the Edit Series window. For Series Name, link it to the Series Name for which we created a user defined drop-down menu. For Series Values, ensure to type =Sheet2!Current_Selection. Under Horizontal Category Axis Labels, click Edit, and for the Axis label range: type =Sheet2!Dates.
Now as we change the Start_Date and End_Dates, the series updates and the range on the Chart also updates!
We can then edit the chart as explained toward the end of the Video to make it more presentable by adding the currency and data labels and editing the positioning of the X-axis on the Chart.
Sources: