Rolling 6 Months Dashboard template with clear and flexible design can be a perfect solution for you if you want to display huge amount of data from various sources.
Check out the following guide from excel tricks:
- List box to select the Region (on the left side of dashboard)
- Scroll Bar (Form Control) to manage the displayed data (under the chart)
- Conditional Formatting to highlight the selected 6 months of 24
- OFFSET function to manage the selected data
Apply dynamic filters
Go to the Developer tab on the Ribbon, in the Controls group then click Properties.
To define the control properties, follow these instructions: In the Input range box, enter a cell reference to a range that contains the values to display in the list box.
In the Cell link box, enter a cell reference that contains the list box selection. The linked cell returns the number of the selected item in the list box. The first item in the range returns a value of 1, the second item in the range returns a value of 2, and so on.
Check the ’data’ Sheet!
Typical examples of items in these types of lists are sales rep names, region names, and product items. We’ll use comparison function for regions, We’ll use it on the left side of dashboard.
How to visualize the selected months data on a dynamic chart?
Just put a slider (scroll bar) so it will only show the selected 6 months. Excel provide the time series slider to you , where you can select any month range and explore all the months data as you needs.
By moving the slider to the right and to the left simultaneously the contents of the list continually changes and contains only the actually chosen 6 months.
This is how we make our dashboard dynamic.
Download the rolling 6 months dashboard and create your template in 5 minutes