This is my Dynamic Horizon Chart with Series Toggle for Excel. You can view any Horizon in your data Range by Selecting the Horizon Start and End Buckets. You can Toggle Series on/off using the Checkboxes. Adjustments to this Worksheet are fairly straightforward as there is no VBA Code. The Dynamic Chart works by referencing a copy of the data using Formula which also prevents Blank values from being plotted on the Chart. Other settings are used by 6 Defined Names, 1 for the Horizon Buckets and then 5 more, 1 for each Series of data. These Defined Names use a Cell Start Reference, the number of Columns wide (X-Offsets) and an Offset for the Series position (the Y-Offset). You can add more or remove Series with minor modifications to the Worksheet
- select any Horizon to display the buckets dynamically on the Chart ie. M 01.2015 - M 12.2018
- toggle any of the Chart Series on/off using Checkboxes
- Paste new data in Quarters, Weeks or Days with only minor modifications to the Worksheet
- add/remove Series with only minor modifications to the Worksheet
Excel 2010 or Excel 2013 (32bit or 64bit), working with Windows 7 & 10
Knowledge of Excel Formula and Named Ranges (Defined Names) if you want to develop this for other Projects. You can Copy & Paste your own data with minor adjustments to the Worksheet and you are good to go
Using the Dynamic Chart
Open the Workbook. The License can be removed as long as an attribution is left near the Chart Settings (these may of course be hidden if you wish). Test the Workbook by selecting a different Horizon. Try toggling the Series on and off. The settings that control the Chart, the Checkboxes and the dynamic Chart Series are shown further down in the "Sheet1" Worksheet. Here is the same Chart with a shorter Horizon and the 'Baseline Forecast with C Seasonality' Series removed (I have also removed the yellow information labels). Unfortunately there is no way without VBA to toggle the Legend Entry, so that will remain even though the Series is not displayed:
The Chart Series Types
The Chart is a Combination Chart. It uses these Chart Types: 'Line', 'Line with Markers' and 'Area'. THe 'Line' Chart Type is used for the 'Final Forecast' Series. The 'Line with Markers' Chart Type is used for both the 'Baseline Forecast with C Seasonality' Series and the 'APO Baseline Forecast' Series. The 'Area' Chart Type is used for the 'Total Sales Last Year' Series.
Changing the Data for Your Data
Obviously you will not want to use my data for your Chart, so here I will detail how to change the data to be your own data of different Series and of different Buckets ie. Weeks, Days etc. For this example I will chage the Buckets to be Weeks. I will change the Series data for the first 3 Series to be 'Total Sales', 'Total Sales Last Year' and 'Final Baseline Forecast' - we will ignore the last 2 Series completely. The first Series will become 'Calendar year/week'
Step 1. Paste your new data over the existing data
Ensure that the data is aligned in the sense that you have an extra Column (see Column B below which is Blank) where I have 'Unit' in the Dynamic Chart Workbook and then Copy the new data and Paste Special Values over the existing data like this:
Don't worry that you receive the message shown below - we will fix that in a minute:
Click okay to the warning message. Type in the first and last Bucket of your first Series (this should always be the Time ie. Years, Months, Quarters, Weeks or Days etc.) into the Horizon Start Week and Horizon End Week Drop-downs. Click through any additional warning messages. Oh' and depending on your data, remember to use an aposthrophe if the leading digit is a zero (0) like this '01.2015. Also ensure that you do indeed enter a valid Bucket for both Horizons otherwise Excel will tell you that the entry has been restricted. The Chart will now spring into life like this:
Clear the 2 Series that we do not want to show and remove the Checkboxes from the view. Click on the Chart Legend and highlighting the unwanted Legend Series one at a time, press Delete. Select a different Horizon. My NEW Chart looks like this:
Hiding the Settings
You can Hide the settings data by selecting Row 46-80 and then using right-click->Hide. Unfortunately hiding any of the copy data for the Series will prevent the Series from being displayed on the Chart - you can of course simply change the Font Colour for this data to white and change the Calculation Style to 'Normal' as a workaround
Moving the Settings or Rescoping the Defined Names
You can Move the settings within the current Worksheet. Unfortunately, you cannot Move these settings to another Worksheet. The Defined Names are scoped to the 'Workbook' and cannot be rescoped to the 'Worksheet'
Copying the Chart into another Workbook
You can of course Copy this Chart into another Workbook by right-clicking on the Tab and using 'Move or Copy...'
A Word on Horizon Start & End Weeks
The Worksheet uses Defined Names to dynamically build the Horizon Start and End Weeks. This is to ensure that a user cannot select a Start Week that is greater than an End Week otherwise the Chart would lose its Series and corrupt. If you would like to just use the full Horizon for both Start and End Weeks, simply use the Data Validation for List and Select the entire Horizon of your data (removing the Denfined Names). Now you will be able to pick any Buckets for the full Horizon
Changing the Worksheet Name
You can change the Worksheet Name and the dynamic Named Ranges will reflect the change with the Chart Series picking up on the new Sheet Name. The Formula that does this is in the Chart Settings Section
17.11.2016 - (Version 1) released