Contents
Method 1: Sparklines in Excel 2010
One of the revolutionary innovations in the latest version of Microsoft Excel 2010 is sparklines — mini-graphics that fit inside cells and visually display the dynamics of numerical data:
To create such mini-graphics, we need to select the cells where we want to place them and use the group buttons Sparklines (Sparklines) from the tab Insert (Insert):
In the dialog box that opens, you need to specify the input data range and the output range:
Created minigraphics can be formatted and customized in every possible way using the dynamic tab Constructor (Design):
In particular, you can easily change the color of the lines and columns of the sparkline and highlight the minimum and maximum values with special colors:
Since the sparkline is not a separate graphical object, but acts, in fact, as the background of the cell, it does not interfere with entering text, numbers or other information into the cell. This opens up scope for imagination – if you play a little trick with the alignment of the entered text, the width and height of the cell, you can achieve interesting results:
What if you still have an old version of Excel? Or do you need a graph type that is not in the sparkline set? Let’s move on to the next one!
Method 2. Additional add-ons for microplots
In fact, the idea of such charts has been in the air for quite some time. Even for the 2003 version of Excel, there were several add-ons with similar functionality, the most famous of which were the wonderful free Sparklines by Edward Taft and the paid BonaVista microcharts ($169) and Bissantz SparkMaker ($200). Yes, and I once also tried to do something similar in the PLEX add-on.
If we take the free Sparklines add-on as an example, then after connecting it we will get a new tab (or toolbar) that looks something like this:
As you can see, this add-on can build significantly more than three types of charts 🙂
The only negative is that the add-in will need to be installed on all computers where you plan to work with a file containing such diagrams.
Method 3. Repeat characters N times
The “budget option” of one-dimensional micrographs are repeating symbols of the same type that mimic a bar chart. To do this, you can use the text function REPEAT (REPT), which can display any given character in the cell the required number of times. To display non-standard characters (knowing their code), you can use the function SYMBOL (CHAR). In general, it looks like this:
The character with code 103 is the black rectangle of the Webdings font, so be sure to set this font for cells C2:C12. You can also play around with characters in other fonts, for example column E uses character code 110 from the Wingdings font.
Method 4. Macros
The method is an improved previous version, where a set of repeating characters (the “|” sign is used) is created not by a formula, but by a simple user-defined function in VBA. Moreover, a separate column is created for each cell, because the function uses a line break character after each number – it looks something like this:
To use this trick in your file, open the VBA editor (Alt + F11), add a new module to the book (menu Insert – Module) and copy the function code there NanoChart:
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 insert the NanoChart function into the required cells, specifying numeric data as arguments, as in the figure above. For the resulting cells with micrographs, you must enable word wrap and rotate 90 degrees through the menu Format – Cells – Alignment (Format — Cells — Alignment). The MaxSymbols constant specifies the length of the highest bar in the mini-histogram.
Another similar method was honestly peeped at http://www.dailydoseofexcel.com/. It consists in adding a user-defined function in VBA to the file for automatic construction sparklines – miniature graphs inside the cells. Open the VBA editor (Alt + F11), add a new module to the book (menu Insert – Module) and copy this Visual Basic code there:
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) 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 in the function wizard in the category User Defined there is a new function LineChart with two arguments — range and chart color code. If you insert it into an empty cell, for example, to the right of a numeric line and then copy it, as usual, to the entire column, then we get a very nice display of numeric data in the form of mini-graphs:
- Microplots in the PLEX add-on
- What are macros, where to insert macro code in VBA, how to use them.
- Edward Taft’s free Sparklines add-on