All of my Freebies are licensed under an MIT License (MIT)

Nifty Loader Shape Animation for Excel

Just one Code Module, 'Loader' is all that is required to create the Loader. The Loader consists of 2 Shapes that are overlaid and then 3 Animations are made. The first is a Rotation of the Foreground Rectangle using an Easing Function. The second is the Fill up of the Background Rectangle. The third is another Rotation of the Foreground Rectangle with a different Easing Function. Put together it's a beautiful thing!

Download Nifty Loader:

Nifty Loader Shape Animation for Excel by Mark Kubiszyn

UI Styled Animated Checkbox List for Excel

A compact, stylish list with labels as Headers, a modified Normal style for the background and animated Checkboxes. This little example was created in Excel at a Zoom of 400 and works well from a Zoom of 60 upwards. The Cell formatting has been lovingly crafted to give a shiny interface. I include the Code to toggle the Checkboxes and to check what is checked including the ability to report back on a list of Options. This is an awesome little freebie, I hope you like it. Instructions are in the File itself, enjoy!

Download UI Styled Animated Checkbox List:
UI Styled Options Animated Checkbox List.xlsm

UI Styled Animated Checkbox List for Excel by Mark Kubiszyn

Create Google-O-Meter Images for Excel

I have a couple of older versions of this Software available in my Archive, however I stumbled across a different version that works perfectly with Excel 64bit, so here is a way of generating Google-O-Meter Charts using VBA

Download GoogleOMeter.xlsm

Google-O-Meter for Excel by Mark Kubiszyn

Quickly Create Styles for Pivot Tables & Slicers

Why mess about for hours trying to create new Pivot Table and Slicer Styles? I have an AddIn and Testbed Creator Files for sale for a very tiny price that will do the job instantly. Repivot 2 contains Reslicer and a Testbed Creator that allows you to Test, Create and Save Pivot Table and Slicer Styles. You can download a FREE Stylesheet that contains some of the Styles built using this Software. Repivot allows you to turn off the Slicer Borders, shade Pivot Table Fields, add Pivot Table Row Banding and create light to dark Slicer Styles with Dark on Light and Light on Dark variations. The FREE File contains 18 Pivot Table Styles and 20 Slicer Styles for you to use with your Pivot Tables and Slicers

Download a FREE Example Stylesheet built using Repivot & Reslicer

Purchase Repivot & Reslicer together

Repivot and Reslicer Testbed Creator for Excel by Mark Kubiszyn

Create a T.O.C. Treelist for Excel - Free Example

This is a lovely example of creating a small T.O.C. (Table of Contents) using my Treelist Software. You can download this Example File for Free to see how the Code works. Then you can either tweak this one if you have the time and inclination or you can purchase Treelist for £2.99 at FastSpring

Download T.O.C. Treelist Example:
Treelist Demos.zip (Legacy Version Example Code)

T.O.C. Treelist Example for Excel by Mark Kubiszyn

New! Professional Light Theme for Treelist

Professional Light Theme for Treelist for Excel by Mark Kubiszyn

Filter & Sort a Multi-Series Combo Chart with Stacked Axis for Excel

This is a lovely example of setting up a Chart to track multi-series data whilst allowing it to be filtered and / or sorted

Download Filter & Sort a Multi-Series Combo Chart:
Filter & Sort a Multi-Series Combo Chart.xlsx

Multi-series Chart for Excel by Mark Kubiszyn

Filter, Link & Align & Sort Excel Tables on Seperate Worksheets using Slicers or Autofilters

Please Note: I offer a paid version that is far in advance of this Code and allows you to Filter and Sort for Cell Colour, Font Colour, Level Sorts, to specify specific Fields and to only turn on the AutoFilter Field Drop-down Buttons for these particular Fields. It is faster and smarter than the Code below and costs just £2.99 GBP
- find out more information and purchase here

We can all Filter multiple Pivot Tables using 1 Slicer or multiple Pivot Tables using multiple Slicers - it's easy, right? But what about Filtering, Linking and Sorting Excel Tables NOT Pivot Tables? Well here is an example of doing just that. With my Code you can Filter Excel Tables on different Sheets using Slicers or Table Filters. When you Filter any Table on a Worksheet all of the other Linked Tables will be Filtered exactly the same. You can apply Sorts to different Columns and they will also align in both Tables respectively

Features: use Table Filters or Slicers to align your Tables on different Sheets
Caveats: a Formula should be added to all Sheets to force Calculations. only 1 Table per Worksheet. Only shared Table Fields can be aligned (Filtering different Fields will break the alignment). The Sort can be Ascending/Descending and on multiple Columns, cool eh?

Download Filter Multiple Tables:
Filter Multiple Tables.xlsm
Filter Multiple Tables w Sort.xlsm

Link Multiple Tables (Sheet1) in Excel by Mark Kubiszyn

Link Multiple Tables (Sheet2) in Excel by Mark Kubiszyn

Maintain a Rolling Window of different Time Series Data in a Master Table as a single line in a Table for Combinations Weekly or Monthly

A further rationale is that we want to maintain an Excel Table that has the ability to Slice data, include adjustments but will also keep all Records in the order that they have been updated or added to the Master Table

- consider 3 lists of varying combinations that are updated at any time with any brand new combinations added to the bottom - this means that we can leave adjustments correctly in our Excel Table for many Columns and they will always snyc nicely with all of our Tables
The Window should also be able to roll itself forward ie. At the end of the first Year we would then Roll to the next Year
Data can be repaced or inserted into the Master Table for specific Time Periods
Use the Update Tab to update data to the Master Table on the Data Tab
This Table is then linked to a proper Excel Table on the Table Tab so that we can Slice and compare the data accordingly. Adjustment Cells can be maintained in this dynamic Table as any new combinations will automatically populate at the END of the Table

TO USE - decide on your Time Series data and add/adjust the File accordingly. Then copy a mass load for your Sales History into the Data Tab. After that data can be Copied and Paste into the Update Tab, a Time Series and Dates can be selected and then pressing the 'Update' Button will either INSERT or UPDATE your records using ODBC SQL Batch processes

When creating your Excel Table change the Custom Format for Number Values to #,##0;-#,##0;"—", for Text or Unique Combinations use General;General;"—"

Download Maintain a Rolling Window of different Time Series Data in a Master Table as a single line or combination Weekly:
Rolling Time Series (Week Template).xlsb

Download Maintain a Rolling Window of different Time Series Data in a Master Table as a single line or combination Monthly:
Rolling Time Series (Month Template).xlsb

Converting Monthly Data into Weekly Data

Here are some examples of converting Monthly Data into Weekly Data with a couple of Charts demonstrating the shape after the split from Months into Weeks

Download Month into Weeks:
Month into Weeks Formula.xlsx

Download Month into Week & Visa Versa:
Month to Week & Visa Versa.xlsx

Download Month into Week with a Seasonality Phasing Vector
Month into Weeks Formula (with weekly phasing).xlsx

Converting Weekly Data into Monthly Data

Here are two examples of converting multiple Rows of Week totals into Month totals with a couple of Charts demonstrating the shape after the conversion/aggregation from Weeks into Months. The simplest method is to split each ISO week into a Daily rate for Monday to Friday (Workdays) and then SUM these up for Jan through to Dec. A more complex method that can allow the Week 52 / Week 53 parts to be built in uses Technical Periods to create the Workday splits. Another way is to use a Pivot Table however the data needs to be transposed to do this and can only be ran for a single items so I have not included this method

Download Daily Split Method:
Week into Month using Daily Split.xlsx

Download Technical Periods Method:
Technical Periods (Week into Month).xlsx

Simple Alerts for Excel

Simple Alert Messages on a Lightbox for Excel. Works in a variety of Excel Versions. You can choose from a Success, Info, Warning or Danger Alert. You can choose to not display a Close Button or use HTML Formatting on the Message. You can even allow Text to be Copied from a Message. Download the Freebie Workbook below

Download Alerts:

Alert Messages for Excel by Mark Kubiszyn

MessageBox for Excel

A Lightbox and alternative Message Box (or MsgBox) for Excel. Capture Button Clicks and a user Prompt. Dismiss the Message Box using a Mouse Click. Download the Freebie Workbook below

Download MessageBox:

MessageBox for Excel by Mark Kubiszyn

Badass! Email Template for Excel

Simple Excel Template in a variety of Colours with Custom Cell Formatting that you can populate and Copy & Paste into an Email that looks great and preserves formatting and Cell Contents. Let your users Copy Excel data as it was designed from Outlook - check it out below

Download Badass:

Badass Email Template for Excel by Mark Kubiszyn

Snatch Extreme Data Pulling for Excel

Simple but powerful Data pulls from Closed and / or Open Workbooks regardless of whether or not the current data has been Saved. You can choose whether to return zeros for Blank Cells or not. You can cancel out any Formula Errors. It's ultra-fast and easy-peasy. Update: 12/12/2021 - use together with 2 new Methods to create awesome dynamic pulls including a SharePoint Folder with a Code example of my latest method of Closed Workbook Data retrieval demonstrating how to get this technique working correctly on SharePoint as well as a Local Folder. Download my Examples and start pulling data now! Tip: for CSV Files use my ODBC Examples detailed below

Fast pull, Excel File can be Closed, Open and does not need to be Saved for the data pulls to work
Use in VBA Code with a variable by wrapping in quotes ie. " & Variable & "
Depending on what you are pulling, you can just use .Formula or even omit this member and it will use the default Value
It doesn't matter what Sheet has focus in the Closed Workbook
Apostrophe's are not brought back from a Closed Workbook
The zero (0) return for Blank Cells issue can be corrected with a small Formula modification - see Example6

Cannot be used directly with =Cell or with a dynamic Defined Name
Will return zero (0) for Blank Cells in the Range being pulled (you can use Replace to correct this issue as the data is pulled)
ArrayFormula has a 255 Character limit
Not as flexible or versatile as ODBC where you can pull and join data from multiple Worksheets

Download Snatch including a SharePoint Folder with a Code example of my latest method of Closed Workbook Data retrieval demonstrating how to get this technique working correctly on SharePoint as well as a Local Folder:

Snatch ODBC Versatile Data Pulling for Excel

Simple or complex, powerful Data pulls using ODBC from Closed and / or Open Workbooks. You can choose whether to preserve Formatting, Auto-fit Columns and Overwrite the data. You can bring back Column Headers as new Named Headers (or Fields). You can perform INNER JOINS on multiple Worksheets and then Sort the results Ascending or Descending. My Examples let you pick from various different SQL Statements to see the effects of the data pulls immediately. You can also simply add the connection and SQL and then just refresh the data going forward - it's up to you! Download my Examples and check out the powerful, flexibility using ODBC on Excel Workbook Files

Download SnatchODBC:

Snatch DAO for Access (Including an Access Table & Query Example)

Simple or complex, powerful Data pulls using DAO from a Closed Access Database. A simple Table data pull and a Query pull are included - download the Examples below

Download SnatchDAO for Access:

Aggregate Data at lightening speed by Product, by Product by Location and by Product by Customer using ODBC & SQL

Fast SQL to Aggregate data from a Product, Location and Customer level up to Product, Product by Location and Product by Customer level - download the Example below

Download SnatchDAO for Access:

Preloader Transition in VBA for Excel by Mark Kubiszyn using spin.js library by Felix Gnass

Preloader is a beautiful CSS animating fade in/out spinner written in VBA for Excel that runs in a WebBrowser control on a non-modal transparent Userform with rounded corners, framed Window or a thick bordered Window with Caption. It can be ran as a simple Transition with a fade-in duration, display duration and a fade-out duration or it can be used as a wait animation while running some task in your Code. Please read the Preloader Documentation on the link above and use the MyDoEvents alternative DoEvents when using Preloader - check out one of my download links below for demonstrations of using Preloader in the real world

Download Preloader:
VBA Projects - Preloader.xls - Blank Preloader Workbook
Preloader UserForm.zip

Preloader Transition in VBA for Excel by Mark Kubiszyn using spin.js library by Felix Gnass

Filterlist, Fast, Flexible, Searchable List Control with ''Fuzzy'' and Case Sensitive Search Capability

Filterlist is a UserForm written in VBA for Excel. It retrieves Key/Item data from a 2 Column contiguous Worksheet Range providing the end user with a fast, flexible, searchable list control with ''Fuzzy'' and case sensitive search capability. Filterlist displays the values in a list as either Keys, Items, Keys & Items or Items & Keys. It also allows you to sort (and persist the sort) in the list either ascending or descending. You can do this with a full list or any part of a filtered search list

Filterlist can be linked to a specific Cell in your Worksheet so that when an end user presses enter or makes a Selection in the list the Cell reflects either the Key or Item value. You can also link the Cell to the search term entered in the search Edit box - choosing the search term option allows you to capture the Key or Item if an end user makes a Selection in the list or the value in the search Edit box, whichever comes first. This is particularly useful when you want to use the search term to filter an Autofiltered Range

Download Filterlist:

Filterlist, Fast, Flexible, Searchable List Control with ''Fuzzy'' and Case Sensitive Search Capability by Mark Kubiszyn

Frostee Theme for Filterlist

Delicious, a very Delicious Drop-down Menu with Theme Changer for Excel 2010 by Mark Kubiszyn using a modified version of the Rollover technique by Jordan Goldmeier

Delicious is a Drop-down Menu that highlights a list of Menu Items when you hover over a Menu Header or an individual Menu Item. It does this by Conditional Formatting using my modified version of the Rollover technique by Jordan Goldmeier. Delicious has been designed for Excel 2010 but works in newer versions of Excel too

Delicious has an expandable, movable and configurable Menu system featuring Menu Headers and Menu Items that exhibit a Rollover as the Mouse is moved across them. When they are in a Rollover state, they become Hyperlinks for jumping to other locations or for using in VBA to run Code. As the Mouse is moved away the Menu Headers and Menu Items all lose focus and the Drop-down Menu is reset

Delicious also features a theme changer that can apply an entire theme to the Drop-down Menu quickly. You can change the Fill, Font Colour, Font Size, Font Style (Bold), Font Face and add Thin Borders to create some pretty delicious effects. For Juiicy fans there is also a Juiicy style theme

Please Note: Delicious has been designed and tested in Excel 2010 for Windows 7. Conditional Formatting that link to other Worksheets is not allowed in earlier versions of Excel. Delicious uses a link in Conditional Formatting that links to the setup Worksheet. Unfortunately using a Defined Name is not an option that works either. As a workaround you will find a file called Delicious-2007-version.xlsm included in the Delicious Project Files

Download Delicious:

Delicious, a very Delicious Drop-down Menu with Theme Changer for Excel 2010 by Mark Kubiszyn

Trial Dialog for Excel 2003 (old versions)

Here is a little Trial Dialog I made in 2012. The Dialog cannot be closed until the countdown has expired. Only then will the 'OK' Button be enabled. The small Red Cross is hidden but a workaround is given should you want to dislpay it. CTRL+Break is disabled by the Code. You can pass in a Message when you call the UserForm like this:

Public Sub TrialDialogDemo()
 TrialDialog.Tag = "(ANNOYING NAG MESSAGE):"
End Sub

Download Trial Dialog for Excel 2003 (old versions):
Trial Dialog.xls

Trial Dialog by Mark Kubiszyn

Trial Dialog for Excel 2010/2013/2016 32bit & 64bit (new versions)

Here is my simple Trial Dialog for Excel 2010/2013/2016 32bit & 64bit (new versions). I ported it over from the old .XLS file above and tweaked the UserForm Code Module slightly to take a couple of Members that allow you to specify the timing Interval and whether or not to display the small Close Cross

Public Sub TrialDialogDemo3()
   TrialDialog.Caption = "This is a Non-modal Trial Dialog..."
   TrialDialog.CloseCross = False
   TrialDialog.Interval = 6
   TrialDialog.Tag = "(ANNOYING NAG MESSAGE 3):"
   TrialDialog.Show False
End Sub

Download Trial Dialog for Excel 2010/2013/2016 32bit & 64bit (new versions):
Trial Dialog.xlsm

Trial Dialog running, you cannot close the Dialog whilst timing-out

Trial Dialog time-out allowing the Dialog to be closed

5 PixelBuddha Spinners

Spinnera re a beautiful set of 5 PixelBuddha Preloaders for Excel 2007 to 2016 in 2 sizes that display the status of an indeterminate process. The Spinners are embedded Base64 encoded data displayed in a WebBrowser Control upon a transparent UserForm so that they appear to be floating on the main Excel Application Window. They are faded in and out when displayed using a VBA call to Javascript Functions written into the HTML Markup. You can use them when you are completing some task in VBA

Petros Chatzipantazis, Elvis Zem, the guys at Spreadsheet1 have taken the Spinner Project and improved the data storage, added additional Spinners and created a visual demo of Flat Animated Loaders for Excel.
You can view it at:
http://www.spreadsheet1.com/flat-animated-preloaders-spinners-for-excel.html or download it from the links below

Download Spinners:
Single Spinner.xlsm
Spinner UserForm.zip
Spinner fade in and out.html
Spinner World Cup 2018.xlsm

Spinner by Mark Kubiszyn

Creating a Spinner is easy. You can choose from 5 different Spinners in two sizes 64x64 pixels and 128x128 pixels. The Code below demonstrates how to select a Spinner and fade it in and out while running a simple task. You can use DoEvents or the alternative Spinner.MyDoEvents to pass messaging back to the main Excel Application whilst running your task

' // fade the Spinner in
Spinner.FadeIn PixelBuddhaSpinner:=NewtonsCradle128x128, _
                            Duration:=1500, _
                            Disable:=CTRLBreak + MouseKeyboard, _
                            Position:=ApplicationCenter, _

' // complete some task
'    make sure that your task is long enough to allow the Spinner to Fade in before you start fading it out
'    you can adjust the Fade in & Fade out timings accordingly or increase the WaitForDuration to match
'    the Fade in time
'    use the alternative DoEvents Function which allows better processing control

' // or the regular DoEvents

' // fade the Spinner out
If Spinner.Running Then Spinner.FadeOut Duration:=1500

Nanoscopic Progressbar

VBA Progressbar is a nanoscopic Progressbar (on a transparent UserForm) with Spinner (Base64 embedded animated GIF) and Pause, Resume & Abort Controls

The Code in this VBA Project demonstrates the following:
1. How to remove the 3D Border from the WebBrowser Control
2. How to make a UserForm Transparent
3. How to embed a Base64 encoded animated GIF image as HTML Markup and write the data out to a WebBrowser Control
4. How to toggle images ON/Off on a UserForm

Download Nanoscopic Progressbars:
Progressbar UserForm.zip

Nanoscopic Progressbar by Mark Kubiszyn

Facebook Loader

This is an old Legacy Loader brought up-to-date a little to run on Excel 2016/2019. It has embedded HTML Markup and demonstrates how to style Transparent UserForm and a WebBrowser Control to hide the Scrollbars

Download Facebook Loader:
Facebook Loader.xls

Facebook Loader by Mark Kubiszyn

Kiosk Full Screen Excel

I can't believe I am now on Version 9 of this Software - I have written so many different variants as standalone Files and AddIns for many Versions of Excel. Here are a couple of my favourites fully working with Excel 2013/2016 64 bit. Kiosk_v2.0.0.3.xlsm or Kiosk_v2.0.0.3.xlam (AddIn) and Version, Kiosk v8.2.xlsm, which is a standalone File. Both allow you to go Full Screen with Excel, hiding everything if required. The older version uses different methodolgy to Hide the Taskbar and Start Button. The newer Version allows you to create Manual Excel Windows of any size - have fun and if you are looking for something even better, check out my new Kiosk v9.xlam AddIn

Download Kiosk Full Screen Excel:
Kiosk_v2.0.0.3.xlsm (run a_main.kiosk, {ESC} to exit Full Screen mode)
Kiosk_v8.2.xlsm (F4 to exit Full Screen modes)

Older Versions of Kiosk (legacy versions have not been upgraded to 64bit):
Kiosk v6.xlam
Kiosk v6.xlsm
Automation Example for Kiosk.xlsm
Kiosk v6 Dev.xls
Kiosk 4.1 AddIn.zip

Kiosk v9 by Mark Kubiszyn

Create a Ribbon Radiobuttons Group with PNG Images. Method 1: GDI Method

There isn't a Radiobutton Group available for the Ribbon, nor is there an easy way of getting transparent images on the Ribbon (you can use BMP images which can be stored and retrived using UserForm Images, however you cannot use the Alpha value to make them transparent when loaded into an Image and .CUR or .ICO can be loaded but not rendered onto the Ribbon). In fact there are a couple of methods that do work. The image retrival method using the GDI is well known and I implement the GDI method here. With my XML and Callback Code you can have unlimited numbers of Radiobuttons that update their respective settings in a Worksheet - ideal if you are planning an AddIn which is what this should be used for. I store my images internally, compressing them within the Worksheet itself using Custom Properties, extract them to the users Temp Folder and then read them back in using GDI Functions with API's. I use purchased transparent, PNG images, however you can substitute these for your own if preferred. All of the Code is given free to load in your own two images

Download the GDI Method:

Create a Ribbon Radiobuttons Group with PNG Images. Method 2: Activesheet Shapes & Clipboard Method

The second method is to use the Clipboard to Copy the Shape images from a Worksheet and then create the IPictures required for the Ribbon and toggle them in real-time accordingly. The beauty about this method is that you can mess about with the PNG or SVG Icons for Colours etc. and just click on the Ribbon Radiobuttons to update your new Colours - cool eh? The downside is a little loss of quality I think

Download the Activesheet Shapes & Clipboard Method:

Rollover Technique, lots of Rollover examples

The Rollover technique was first demonstrated by Jordan Goldmier, where he found that you could add the =HYPERLINK() function to a Cell and run a VBA Function that would be triggered whenever a Mouse is 'Rolled over' the Cell. My Rollover technique differes only in the fact that I use various methods to prevent the Rollover constantly firing in the background and triggering the Function. I also use the Excel COLUMN() and ROW() Functions for some examples

I demonstrate how you can use Rollovers in Merged Cells, capture Mouse Clicks, use a Custom Cell Format to allow different Rollover Text to be displayed in the Cell, use a Rollover in a Table to highlight each Row, how to reset a Rollover when the Mouse has moved away from the Cells, how to Clear the Rollover effect when the Mouse is moved away from the Cells, a 2-Color Option technique, using the Rollover as both a Rollover and a Hyperlink to a Cell or Webpage and using a Rollover in a Grid. There is an example of a Rollover Toolbar using an embedded 'Icon-Works' Font and an example of Sheet Navigation using an embedded 'Font-Awesome' Font. I include 2 Full Menu System Rollover examples using the 'Glyphyx One NF' Font built for and with the help of Emanuelle Lacroix. I include my Juiicy Template and themes with Typography and numerous Menu Rollover examples. You can find my original Metro Menu Boilerplate Rollover example demonstrating linking to other Cells and using different Text for the Rollovers in a colourful Grid format. I have also included a very quick and dirty Rollover which uses inverse Range intersects to perform the Rollovers - see Very Simple Rollover Tests w Click.xlsm

Check them all out below

Download Rollover Examples:
Rollover Example.xlsm (2 Sheets of Rollover examples)
Rollover.xlsm (alternative technique)
Very Simple Rollover Tests w Click.xlsm (very simple Rollover Range examples that can just copied down)
Rollover Toolbar.xlsbm (embedded Icon-Works Font)
Rollover Toolbar Sheet Navigator using Font Awesome.xlsb (embedded Font-Awesome Font)
Menu.xlsm (embedded Glyphyx One NF Font)
Menu (fullscreen).xlsm (embedded Glyphyx One NF Font, Full Screen version)
Mint & Chocolate Rollover Buttons
Widget Rollover Buttons
UI Element Rollover Buttons
Metro Menu boilerplate.xlsm
Standard Report Schema (Rollover-enabled).xlsm
Business Report Schema (Rollover-enabled).xlsm
Colour Banding with Hyperlink Rollover.xlsm
Disable Cursor Movement in Menu.xlsm

Paid Software that demonstrate or use Rollovers to great effect:
Dark Excel

Metro Menu Boilerplate by Mark Kubiszyn

A Rollover Menu using an embedded Glyphyx One NF Font

Create a Rollover Button

Why use the Rollover technique? Well because it's lighteningly fast, and I have found it as quick, if not quicker than using a Label MouseOver event - remember Shapes do not have such an event, so this combination of Hyperlink Rollover and Shape produces a lovely Button action. Here are all of my Examples (previously sold, but now totally free to download and use):

Download Rollover Button Examples:
Create a Rollover Button Part 1.xlsm
Create a Rollover Button Part 2.xlsm
Create a Rollover Button Part 3.xlsm
Create a Rollover Button Part 4.xlsm
Create a Rollover Button Part 5.xlsm


Here are some Charts that I have designed or created following articles over the Years, that are different from most Charts that you see. The first Chart is a Chart that I created. It is a Sales Combination Chart that uses repeated data series to allow lines and fill to overlap each other in order of precedent (Green takes precedent over Red and Blue takes precedent over Green) and the lighter Colours only show if the lines have precedent. The second Chart was designed by Jon Peltier and I followed his article in order to replicate it (link included in download to the article). I built both 2 x 2 and 3 x 3 Grid Scatter Charts for a Project at work and I include demonstrations of use and how to normalise data. The third Chart is a dynamic Category Chart. It has series that you can toggle on/off using Checkboxes and a Spin Button to reduce the number of visble Weeks

Download Chart Examples:
XY Scatter Chart with 2x2 and 3x3 Shaded Grid.xlsx
Category Charts.xlsb

Sales Combination Chart by Mark Kubiszyn

2x2 Grid Scatter Chart Chart by Mark Kubiszyn built from an article by Jon Peltier

3x3 Grid Scatter Chart Chart by Mark Kubiszyn built from an article by Jon Peltier

Fix Chart Label Overlap

Here is my VBA Code to help fix the Label Overlap on Charts for 2 Series. In each Subroutine you can specify your Chart Name, the 2 different Chart Series that you want to fix for any overlapping Labels and the distance that you want to reposition any Labels, in pixels. There are 2 Files below. One is for when you make both Series have their Labels centered on the Series and the other is for when you have the first Series place its Labels above the Series and where you have the second Series place its Labels below the Series. Update 04.09.2021 added code to ignore missing Labels in one or more of the 2 Series

Download Fix Chart Overlap Examples:
Fix Chart Label Overlap (Center Series).xlsm
Fix Chart Label Overlap (Above & Below Series).xlsm

Fix Chart Labels Overlap for 2 Series by Mark Kubiszyn

Demand Planning - Zero Forecast Report

Here is a really simple, but effective Zero Forecast Report with Sortable Formula to show you the 'Demand w Zero F/cast this wk' in first place, then 'Demand w Zero F/cast last 4 wks' next, then 'Demand w Zero F/cast last 3 wks' and finally 'Demand w Zero F/cast last 2 wks'. When the data is refreshed with your own data you can then simply sort the 'Demand v Zero Fcast' Header Largest to Smallest. I made it like this so as to look at the Products without Forecast for Demand in the current Week first when sorted, then to start looking at different Products according to how long a Forecast may have been missing by the longest length. The Formula can exclude any Products with the Text 'pump' in their Product Description (you can change this to anything you may want to exclude from your Product Range). I use this with BEx/BW/BI data every week to see what is selling without a Forecast and for how long this has been happening

Download Zero Forecast Report Example:
Zero Forecast Report.xlsx

Zero Forecast Report for Demand Planning by Mark Kubiszyn

This website is using cookies More details