TIL Excel Sparklines

TIL that Excel has this thing called “Sparklines” where you can have a mini-chart or visualization of your data in as small a space as a single cell.  I made samples of the 3 types of Sparklines (Line, Column, and Win/Loss) captured in the image below.  In each of the table, the last column displays the sparkline created for the data in the same row.


The Sparklines thingy is available from the Insert menu of the Excel Ribbon.


Concatenating cells with a delimiter

Earlier today, I was looking for an excel function somewhat similar to ruby’s join function for arrays. Given a range of cells and a character to use as a delimiter, I wanted the function’s output to be a concatenation of the cell values alternating with the delimiter. I googled and was easily able to find a solution that suited my needs well enough.

=MConcate(B6:AX6,"|") – Concatenates B6 to AX6 with “|” as delimiter

VBA code:

Public Function MConcate(Data As Variant, delimiter As Variant) As Variant

    Dim vntBuild As Variant
    Dim vntItem As Variant

    For Each vntItem In Data
        vntBuild = vntBuild & vntItem & delimiter
    If Len(vntBuild) > 0 Then
        MConcate = Left(vntBuild, Len(vntBuild) - Len(delimiter))
    End If

End Function

Reference: http://www.ozgrid.com/forum/showthread.php?t=86223