Cross-tab charts with SFE

In-cell charts are not meant to be accurate, but to provide a quick overview and by densifying information, to allow the eye to englobe multiple charts. Then, the brain will correlate those small parallel multiples.
Nevertheless, it is key to provide some context or reference, even for small charts.

In SFE, I have always taken care to include reference in the different types of charts :

• Horizontal lines in BarChart and LineChart.
• Normality band in LineChart.
• Horizontal and Vertical lines in (unpublished yet) ScatterChart.

Those references are here to answer the following questions when analysing data  : What is my target ? What is my limit ? Where is the budget level ?... etc

An additionnal reference can provide great help when it comes to read and analyse periodical data : background shading. To identify weekends in a week or every other month in a year for example.

The intensity of the shade must be carefully choosen, in order to be identified by the eye, but remaining neutral, without disturbing the reading of the the lines or columns.

Anyway, SFE now includes the possibility to create periodical vertical grey areas.

For this purpose, I added 3 additional parameters to Barchart and LineChart :

• ShadedStart : At which position the first grey band should start
• ShadedWidth : How many positions on the x axis should be shaded
• TransparentWhidth : How many positions should follow without shading.

Example : show a grey background for saturdays and sundays, starting sunday.

• ShadedStart = -1. With ShadedStart = 0, the chart would have started with a grey zone covering Saturday + Sunday. -1 will have the chart to start with 1 position (sunday) on a grey background.
• ShadedWidth : 2. For Saturday + Sunday
• TransparentWhidth : 5. For the 5 working days of the week

As shown in the example above, the grey zone covers 100% of the cell's height, in order to display a continuous band when LineChart and BarChart are vertically aligned.

Robert Mundigl and Matt Grams provide an excellent example (again!) of Cross-tab chart with standard excel charts here.

PS : You might have noted that LineChart, BarChart and ScaleLine also provide a vertical reference line... that will be described in the next post.

Versions log

Sparklines v 3.2

New Features

• Possibility to install as an add-in for Excel. Take care if you want to distribute files created with the add-in, the Sparklines function will not work on other users' PC.

• Menu and Toolbar - All functions can be accessed via those 2 means
  

• Vertical ScaleLine - Display a a vertical measure scale next to Linecharts, BarCharts or Heatmaps. Choose left or right size.
  

Minor Changes

• Heatmap - now includes the possibility to create arrays of piecharts, with variable size and colors
  
• LineChart - Corrected error handling of min and max values
  
• StackedChart - Reviewed the display of legend, in order to fit in segments
  
• Pareto- Reviewed the display of legend

-----------------------------------------------------------------------------------------

Sparklines v 3.0

• All charts - added possibility to embed intermediary formulas inside the UDFs)
• All charts - Each Sparklines is properly renamed (Type, active sheet and position)
  

• PieChart - New type of chart Heatmap - New type of chart, suitable for 2D and 3D heatmaps (Size + Color)
  
• Boxplot - New type of chart
• BarChart Added one new parameter (color) to choose the color of the bars
• LineChart - Added parameters for colors of lines
• BulletChart - Added possibility to choose a color scheme
• ScaleLine - Added 3 text zone (Left ; center ; right) and choice of font size
• VariChart - Added possibility to choose a color scheme

• Color Scales pdf file- 2 separate sheets with well balanced color scales.
• Macro to delete Sparklines on the active sheet, but Sparklines only.