Microcharts. Simple way to create a nice report.
By Ilya Parshin, Nov. 24, 2014, 9:49 p.m.

Method #1. Sparklines in Excel

One of the groundbreaking innovations in the last version of Microsoft Excel 2010/2013 is the possibility of using sparklines – minicharts placed within cells and demonstrating the dynamics of numeric data:

In order to create such minicharts, it is necessary to select those cells, where we want to put it, and then use buttons of Sparklines group from Insert tab:

In the appearing dialogue box, you need to specify the range of source data and the output range:

Created minicharts may be formatted and adjusted in every way by means of Design dynamic tab:

In particular, you can easily change the color of lines and columns of the sparkline and mark minimum and maximum values with special colors:

Since the sparkline basically plays the role of cell background (it is not a separate graphical object), it would not make any problems if you need to enter text, numbers or other information into the cell.

What can you do, if you have an old Excel version at the moment? Or you need such type of chart, which is not offered in the sparklines set? Let’s move forward to the next method!

Method 2. Extra add-ons for microcharts

Actually, the idea of such charts was in the air for quite a while. Even for Excel 2003, there were several add-ons with similar functionality, the most known of them were the remarkable add-on Sparklines (free) by Edward Tufte, and BonaVista microcharts (paid) and Bissantz SparkMaker (paid). The only drawback is that add-ons must be installed on all computers, where you are planning to work with a file containing such charts.

Method 3. N-times iteration of symbols

The “budget choice” for one-dimensional microcharts is repeating similar symbols for imitation of a bar chart. For this purpose, you may use text function REPT, which can iteratively output any selected symbol into the box. In order to output irregular symbols (knowing their code), you may use CHAR function. Essentially, it looks like this:

A symbol with code 103 is a black rectangular from Webdings font, so don’t forget to set this font for boxes C2:C12. Also, you can play with symbols from other fonts, for example, a symbol with code 110 from Wingbinds font was used in E column.

Method 4. Macros

This method is an improved version of previous method, where a set of repeating symbols (“I” symbol is used) is created not by a formula, but a simple user-defined function on VBA. At that, a separate column is created for each box, since the function uses line break symbol after each number. It looks like this:

In order to use this trick in your file, you’ll need to open VBA editor (Alt+F11), add a new module to the book (Insert -> Module) and copy Nanochart function code into the module:

                    Function NanoChart(rng As Range) As String
                        Const MaxSymbols = 10

                        For Each cell In rng
                            outstr = outstr & WorksheetFunction.Rept("|", cell / WorksheetFunction.Max(rng) * MaxSymbols) & Chr(10)
                        Next cell
                        NanoChart = outstr
                    End Function
                

Then, we paste NanoChart function into the needed cells indicating numeric data as arguments (as shown on the picture above). It is necessary to enable word wrap and 90 degrees turn (Format -> Cells -> Alignment) for the resulting cells with microcharts. MaxSymbols constant sets a length of the highest column in the minihistogram.

One more method was fairly seen at http://www.dailydoseofexcel.com/. It involves addition of user-defined VBA function for automatic construction of sparklines (tiny charts within cells) to the file. Open VBA editor (Alt+F11), add a new module to the book (Insert -> Module) and copy this Visual Basic code into the module:

                    Function LineChart(Points As Range, Color As Long) As String
                        Const cMargin = 2
                        Dim rng As Range, arr() As Variant, i As Long, j As Long, k As Long
                        Dim dblMin As Double, dblMax As Double, shp As Shape

                        Set rng = Application.Caller

                        ShapeDelete rng

                        For i = 1 To Points.Count
                            If j = 0 Then
                                j = i
                            ElseIf Points(, j) > Points(, i) Then
                                j = i
                            End If
                            If k = 0 Then
                                k = i
                            ElseIf Points(, k) < Points(, i) Then
                                k = i
                            End If
                        Next
                        dblMin = Points(, j)
                        dblMax = Points(, k)

                        With rng.Worksheet.Shapes
                            For i = 0 To Points.Count - 2
                                Set shp = .AddLine( _
                                    cMargin + rng.Left + (i * (rng.Width - (cMargin * 2)) / (Points.Count - 1)), _
                                    cMargin + rng.Top + (dblMax - Points(, i + 1)) * (rng.Height - (cMargin * 2)) / (dblMax - dblMin), _
                                    cMargin + rng.Left + ((i + 1) * (rng.Width - (cMargin * 2)) / (Points.Count - 1)), _
                                    cMargin + rng.Top + (dblMax - Points(, i + 2)) * (rng.Height - (cMargin * 2)) / (dblMax - dblMin))

                                On Error Resume Next
                                j = 0: j = UBound(arr) + 1
                                On Error GoTo 0
                                ReDim Preserve arr(j)
                                arr(j) = shp.Name
                            Next

                            With rng.Worksheet.Shapes.Range(arr)
                                .Group

                                If Color > 0 Then .Line.ForeColor.RGB = Color Else .Line.ForeColor.SchemeColor = -Color
                            End With

                        End With

                        LineChart = ""
                    End Function

                    Sub ShapeDelete(rngSelect As Range)
                        Dim rng As Range, shp As Shape, blnDelete As Boolean

                        For Each shp In rngSelect.Worksheet.Shapes
                            blnDelete = False
                            Set rng = Intersect(Range(shp.TopLeftCell, shp.BottomRightCell), rngSelect)
                            If Not rng Is Nothing Then
                                If rng.Address = Range(shp.TopLeftCell, shp.BottomRightCell).Address Then blnDelete = True
                            End If

                            If blnDelete Then shp.Delete
                        Next
                    End Sub
                

Now, a new function LineChart with two arguments (range and chart color code) has appeared in the User-Defined category of the function master. If you paste it into a blank cell, for example, on the right from the numeric line, and then copy it on the whole column (as usual), you will get quite a nice presentation of numeric data in the form of minicharts:

Original article here

If you have any remarks about article feel free mail to mail@datamonkey.pro

Achievement unlocked
A lot of words about why we are so awesome. Or something else. Dont know. Really.