What is a sparkline?A Sparkline is a small chart that is aligned with rows of some tabular data and usually shows trend information.
Here is an example of sparklines in a project team status report.
How to create sparklines in Excel 2010?Creating sparklines in excel 2010 is very easy. You follow 3 very simple steps to get beautiful sparklines in an instant.
- Select the data from which you want to make a sparkline.
- Go to Insert > Sparkline and select the type of sparkline (you have 3 options – line, column and win-loss chart)
- Specify a target cell where you want the sparkline to be placed
- Optional: Format the sparkline if you want.
Types of Sparklines in Excel 2010:
There are 3 basic types of sparklines in Excel 2010. They are,
- Line chart
- Column chart
- Win-loss chart (useful for showing a bunch of wins & losses denoted by 1s and -1s)
Sparkline Formatting and Options – Explored
In excel 2010, you will find a new ribbon called as “Sparklines – Design” ribbon. This is where all the formatting options for sparklines are included. Some of the key formatting / customizations you can do are,
- Change the sparkline type
- Change the source data / target cells of sparkline
- Set different colors for first point, last point, highest & lowest points (applicable for column and line chart types)
- Set axis options (show / hide axis, set min and max value for vertical axis, set axis type to date axis etc.)
- Group / un-group a bunch of sparklines (you can change formatting options, axis settings en-masse when you group sparklines)
- Remove sparklines
Sparklines & Missing Data – How does it work?
- If the sparkline source data contains non-numeric data, they are neglected while plotting the sparklines.
- If data has some #NA values, they are neglected
- If data has blanks, sparkline shows blanks too
- If data has zeros, zero value is plotted
- If data has some hidden rows / columns, the values are neglected (unless you enable “Show data in hidden cells” option)
Sparklines in Tables & Pivot TablesYou can add sparklines to tables and pivot tables too. Adding them to pivot tables is a bit tricky (I will write about that when I master the trick) but adding sparklines to tables is fairly straightforward and scales nicely.
Sparkline Tips & TricksHere is a bunch of quick tips & tricks for those of you starting on sparklines.
- You can auto-fill sparklines. Select the first set of values and add a sparkline. Now copy and past sparklines to auto-fill them based on data in adjacent cells.
- When you adjust row-height or column-width of the cell containing sparkline, the size of sparkline changes too.
- Juxtapose sparklines with conditional formatting icons to create stunning charts and dashboards.
- If you want to copy a sparkline over to a ppt or document, you can use “copy as picture” option.
- The sparklines feature is disabled whenever you open a workbook in “compatibility mode”.