Let’s today have a look on a new interesting feature of Excel 2010: Sparklines. These are small intense, simple, wordlike graphics for presenting trends and variations associated with some measurement. Sparklines normally don’t show axes or values. There are some products for older versions of Excel, you’ll find a link to an open source product at the end of this article. Following table figures out the different types of Sparklines in Excel 2010.
|–||Line||:||Used to display a line chart in a cell.|
|–||Column||:||Used to display a column chart in a cell.|
|–||Win/Loss||:||Used to display a win/loss chart in a cell, the columns have the same height.|
In order to explain the use of Sparklines in Excel 2010, we created a new workbook and filled some cells with sample data.
In the first two rows and columns corresponding to the quarters (column D to G), we entered some prices of a sample product. The last row calculates the difference between the prices of the current and the previous quarter, excepted the first quarter. Here we are assuming, that no data is present.
For inserting a new Sparkline to cell C5, we first select it, then we click on the Tab “Insert” and on “Line”; see the red bordered areas in the following figure.
The dialog „Create Sparklines“ shows the already selected cell in the location range. We need to select the data range, let’s take D5:G5. After clicking on “Ok”, we can see our Sparkline in cell C5. A new Tab offers some options for formatting and customizing the sparkline.
If we check the options “High Point” or “Low Point”, we can highlight the highest and/or lowest points of the Sparkline (see red bordered area in the figure below). Negative points can also be highlighted, we can change a line style by selecting a new one in “Style”. The colors of the Sparkline and highlighted points can be easily customized using the menues “Sparkline Color” and “Marker Color”.
Please note, that changing the marker colors only makes sense, if the points are already highlighted. In the figure above, I changed the color of the lowest point to red.
Let’s have a look what happens, if we autofill the cell below C5, a procedure we know from formulas.
As you can see, cell C6 contains then also a Sparkline (blue bordered area). The data range was extended from D5:G5 to D5:D6 and a Sparkline Group was created. If we change the type from “Line” to “Column”, we have to ungroup the Sparklines first (see next figure).
I placed a regular chart on the left side of my sheet, which simulates the Sparkline. The lowest price is 100,- Euro, this value represents our starting point. In the second quarter, the price is 200,- Euro, an increasement of 100%. In the third quarter, the price increases about 200%. In the fourth quarter the increase is only about 50%. If we swap the values of the first and second quarter, we can see that the columns in the Sparkline are also swapped. The zero point for the sparkline is the lowest value of our data range.
How does the win/loss sparkline look like? We insert a new one in cell C7 and select “Win/Loss” from our Insert Tab. In the sample below I changed the color for negative values to red.
Last but not least, we can customize the axes for the Sparklines by selection the options from the menue “Axis”; for example for the minimal and maximum values. By the way, we wrote this article in Word 2010 and didn’t encounter any problems. Finally, as promised, the link to an Open Source project for Sparklines in Excel 2003 and 2007:
- Sparklines for Excel (Excel 2003, 2007), Englisch
- Sparklines for Excel (Excel 2003, 2007), Download in Englisch
This article has also been published in German and in English on the following sites:
- Excel Ticker Blog, German
- Professional Excel Solutions by Maninweb.de, English
- Professional Excel Solutions by Maninweb.de, German