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 firstname.lastname@example.org