Dynamic Chart

Dynamic Horizon Chart with Series Toggle for Excel

Version 1
17Nov 2016

Dynamic Chart Documentation

By: Mark Kubiszyn


Dynamic Chart for Excel Screenshot



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

FEATURES

- 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


REQUIRED

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:

Dynamic Chart Short Horizon

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:

Dynamic Chart New Data

Don't worry that you receive the message shown below - we will fix that in a minute:

Dynamic Chart Message Following Paste

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:

Dynamic Chart Update Horizons

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:

Dynamic Chart New Chart with Updated data

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

CHANGELOG

17.11.2016 - (Version 1) released