How to create and use Sparklines in Excel 2010 to summarise and add context to your data and to also show trends
Using Sparklines in Excel 2010 to show data trends and interpret your data all in a single cell
Hi and welcome to my latest hub on Excel. Today, I am going to look at the Sparkline which is a new feature that Microsoft added to Excel 2010 and is a new and very special type of chart.
Charts allow you to illustrate trends and variations over time very clearly and succinctly and are a very powerful visual device.
A Sparkline is a chart that fits in a single cell which can be placed directly next to the data it is displaying.
- They allow you to quickly and simply show users of your spreadsheet any trends or variations that are present in the data.
- Sparklines add context to your data and make it far more powerful.
- Despite their small size, Sparklines can pack an awful lot of data into that single cell which makes them a very efficient and effective way of displaying data.
The best way to describe Sparklines is with a picture, so below is the example we will be working through in this hub:
The above figure shows the power of Sparklines in Excel. You can instantly see how the various stocks have performed over the last month. In addition, the numbers presented (today's and yesterday's close) are much more useful with the context provided by the Sparklines. I have created a traditional line chart so that we can compare them:
Sparklines have a number of advantages over traditional charts:
- A Sparkline takes up a lot less space on the page, fitting into a single cell
- They are much simpler and quicker to create
- Sparklines allow you to place a chart directly next to the data (imagine having line charts for each stock, the page would become very large)
- They give data context making the data more powerful and more useful
- Using a series of them allows an easy comparison of a large number of data sets
Of course, charts have many advantages over Sparklines:
- Charts allow you to show data in far greater detail
- You can compare other data series in one chart
- A wider range of chart types other that Line, Column and Win / Loss are available
- Charts are more flexible and configurable than Sparklines
- Trendlines can be added to charts
- A wider range of styles such as 3D can be used
Which you choose is down to your individual situation and how you want the data displayed. Both have their advantages and disadvantages over each other.
If you would like to know more about creating and using charts in Excel 2010 (or Excel 2007), I have an excellent hub that has step by step instructions on how to create beautiful charts. That hub can be found here:
OK, now that we know more about the advantages of using Sparklines, it is time to create one!
Creating a Sparkline to represent your data in Excel 2010
Before we create a Sparkline, ensure that you have the data you are going to represent in unhidden rows or columns.
Now we will create the Sparkline itself.
- Click on the cell in which you want to place the Sparkline
- Select the Insert tab
- Choose which of the three types (Line, Column and Win / Loss) of Sparkline you would like to use. We will select Line in this example
- This will open the Edit Sparklines dialogue box
- Select the data you want displayed and click OK
And that is it! Simply, repeat the process and create all the Sparklines you require
Using Grouping to smooth Sparklines in Excel 2010
Now that we have created all our Sparklines, you will notice that the line for Dell looks different to all the others. This is due to the unique nature of the data (Dell over the month has varied between three values that are all very close together, 13.83, 13.84 and 13.85). This will clearly misrepresent Dell’s data in the Sparkline which makes it look like it has been varying wildly from day to day as shown below.
To smooth the line for Dell, first we will group it.
- Select the cells N5 and N6
- Navigate to the Sparkline Tools / Design tab and click the Group button
- Now, select the Axis button to the left of the Group button
- Under Vertical Axis Maximum Value Options, select Same for all Sparklines
You can see that the Dell line is now straight which more closely represents what happened to the stock over the month of October.
Configuring Sparklines to work with Hidden Cells and adding High Points, Low Points and Markers in Excel 2010
If you were to hide the data that your Sparkline represents, you will notice very quickly that the Sparkline also disappears. To prevent this:
Select your Sparkline
- On the Sparkline Tools / Design tab, click the Edit Data button
- Choose Hidden & Empty Cells
- Select Show data in hidden rows and columns and press OK
So now I can hide the original data in columns B to G without causing the Sparkline to disappear.
Next, you may want to show the Highest and Lowest Points in your Sparkline. Also, you may want to show Markers, to further illustrate variation. To add these:
Select all of your Sparklines
- Choose the Sparkline Tools / Design tab and in the Show group, first click High Point and Low Point
You will see that the Sparkline for Dell has a large number of High and Low Points. This is because the stock price for Dell flipped between two values for the majority of the month.
Next, we will look at using Markers. These will obscure the High and Low Points, as you will see below. To add Markers:
- Once again, select all your Sparklines
- On the Sparkline Tools / Design tab, navigate to the Show group and select Markers
You can see that the Sparklines have depth due to the markers. I have expanded the cells in column N to show how what the markers look like:
Which you choose, depends on which you prefer and also which option best illustrates your data.
Using Conditional Formatting and nested IF statements to show whether a stock is higher or lower than yesterday in Excel 2010
The last part of our panel showing stock performance is the coloured arrow that illustrates if the stock is higher, lower or the same compared to yesterday. This consists of two parts:
- The first is an IF statement used to generate three numbers (2 for higher, 1 for the same and 0 for lower)
- The second is Conditional Formatting which takes those three numbers and shows a green up arrow for higher, an orange arrow for the same and a red down arrow for lower
The nested IF statement we use is:
What this formula does is it looks at K4 and L4 and compares them.
- If K4 is larger than L4 then M4 = 2
- If K4 and L4 are the same then M4 = 1
- If K4 is smaller than L4 then M4 = 0
IF statements are a very powerful and immensely useful tool allowing you to compare data and do one thing if the statement is true and another if it is false. You can use them with AND, OR and NOT to make even more powerful formulas. To learn more about how to use IF statements, particularly nested IF statements, I have a detailed hub on the subject:
To hide the number that is the result of our nested IF statement:
- Click the cell and select Format Cells
- Select the Custom category and in Type, enter three semi colons (;;;)
- Press OK and the cell is blank!
This is a really nifty little trick that I use over and over again to hide data in plain sight.
Next, we add Conditional Formatting to the same cell (M4).
- Select cell M4
- On the Home tab, click the Conditional Formatting button in the Styles group
- Choose New Rule
- For Select a Rule Type, choose Format all cells based on their values
- Under Format Style, pick Icon Sets from the drop down menu
- Change the Value and Type for each to be the same as the figure below
- Click OK
Finally, change Applies to so that all the cells you want formatted are selected (in my case M4 to M8).
To learn more about Conditional Formatting, particularly how to use them with Icon Sets, I have a hub that investigates them in far greater detail. My hub on the subject can be found here:
I also have a hub that introduces all the exciting changes to Conditional Formatting that Microsoft have introduced to Excel 2010. It covers improvements and enhancements to both Icon Sets and Data Bars. That hub can be found here:
Sparklines allow you to quickly and easily create a chart within a single cell that can be used to represent and illustrate trends or variations in your data. Sparklines have a number of advantages over normal charts:
- They use a lot less space on the page, fitting into one cell
- Sparklines add context to your data
- Compared to a normal chart, they are much simpler and take a lot less time to create
- You can put a Sparkline directly next to the data due to their small size which is especially useful for comparison when using a large number of data sets
Due to their simplicity, Sparklines:
- Summarise and simplify the data displayed
- Do not allow you to compare other data series in the same chart
- Restrict you to just Line, Column or Win / Loss charts
- Are less flexible and configurable than charts
- Cannot use Trend lines or styles such as 3D
Which you chose to use, depends on what you want to show and the nature of your data. Both have advantages and disadvantages and are better for certain situations.
In this hub, we have:
- Created and configured Sparklines and looked at how to use High Points, Low Points and Markers.
- Used groups to smooth Sparklines
- Configured Sparklines to work with hidden cells
- Finally, we used nested IF statements and Conditional Formatting with icon sets to complete our stock panel
I do hope that you enjoyed reading this hub as much as I enjoyed writing it and that you have found it useful and informative. Many thanks for reading. Please feel free to leave a comment below.
© 2013 Robbie C Wilson