Rolling 6 Months Dashboard template with a clear and flexible design can be a perfect solution for you if you want to display a huge amount of data from various sources.
In this tutorial, we will show you something different from either: how to create a highly interactive dashboard template that shows the rolling 6 months.
Check out the following guide from excel tricks:
- List box to select the Region (on the left side of dashboard)
- Form Control (scroll bar) 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 the comparison function for regions, We’ll use it on the left side of the 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 provides the time series slider to you, where you can select any month range and explore all the month’s data as your need.
By moving the slider to the right and to the left simultaneously the contents of the list continually changes and contain 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