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.

Screenshot-20130731-000073

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

Advertisements

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.

Usage:
=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
    Next
    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