What are Excel Sparklines & How to use them? 5 Secret Tips

Sparklines (often called as micro-charts) add rich visualization capability to tabular data without taking too much space. This page provides a complete tutorial on Excel sparklines along with 5 secret tips.

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.

Excel Sparklines Tutorial – 3 steps

Creating sparklines in Excel is very easy. You follow 3 very simple steps to get beautiful sparklines in an instant.

1. Select the data from which you want to make a sparkline.
2. Go to Insert > Sparkline and select the type of sparkline (you have 3 options – line, column and win-loss chart)
3. Specify a target cell where you want the sparkline to be placed
4. Optional: Format the sparkline if you want.

Here is a short screen-cast showing you how a sparkline is created.

Types of Sparklines in Excel:

There are 3 basic types of sparklines in Excel 2010. They are,

1. Line chart
2. Column chart
3. Win-loss chart (useful for showing a bunch of wins & losses denoted by 1s and -1s)

Sparkline Formatting and Options – Explored

Whenever you select a cell with sparkline in it, 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 / customization options available are,

• Change the sparkline type – between line, column and win/loss
• 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?

• Non-numeric data: If the sparkline source data contains non-numeric data, they are neglected while plotting the sparklines.
• Errors & #NA values: If data has some #NA values, they are neglected
• Blanks: sparkline show blanks as gaps
• Zeros: If data has zeros, zero value is plotted
• Data in hidden rows / columns: If data has some hidden rows / columns, the values are neglected (unless you enable “Show data in hidden cells” option)

Sparklines in Tables & Pivot Tables

You can add sparklines to tables and pivot tables too. Adding them to pivot tables is a bit tricky but adding sparklines to tables is fairly straightforward and scales nicely.

5 Tips to use Sparklines better

Here is a bunch of quick tips & tricks for those of you starting on sparklines.

1. 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.
2. Change their size: When you adjust row-height or column-width of the cell containing sparkline, the size of sparkline changes too.
3. Juxtapose sparklines with conditional formatting icons to create stunning charts and dashboards.
4. If you want to copy a sparkline over to a ppt or document, you can use “copy as picture” option.
5. Enable high / low points to highlight important values

Sparklines & Compatibility

Sparklines are available since Excel 2010. They work in desktop and web versions of Excel.

What happens when someone opens a file with sparklines in Excel 2007?

Sparklines don’t show up in if you open the file in older version of Excel (say Excel 2007).

How does Sparklines compare with other alternatives?

Sparklines vs. In-cell Charts

In-cell charts are a powerful and lightweight way to create bite-sized visualizations. The main technique is to use REPT formula to repetitively show a bunch of symbols (usually | symbol) to create a small chart. The advantage of this approach is that they work in any version of Excel. But the dis-advantage is that we can make only few types of charts (bar charts, column charts by rotating cell text, dot plots). Also, incell charts require some knowledge of excel formulas and creativity.

This is where Excel Sparklines shine, as they are very easy to create and maintain.

Sparklines vs. Conditional Formatting

In Excel 2007, MS introduced a bunch of useful Conditional Formatting options like icons, heat maps that effectively create small visualizations of underlying data. These features are further improved in Excel 2010, 2013 and 2016. While conditional formatting based visualizations are easy to implement and scale very well, there are only few options (a bunch of traffic lights, data bars etc.). This could leave you high and dry if you are looking for rich visualization options. these new features require the actual data to be present in underlying cells (which is a head-ache).

Again, sparklines shine as a simpler and easier alternative.

Sparklines vs. Shrinking an actual chart

We can take an actual chart, strip it of all the clothing (remove gridlines, axis, legend, titles, labels etc.) and resize it so that it fits nicely in a cell [example]. This is the easiest and cleanest way to get sparklines in earlier versions of excel. However this approach has one problem. It doesn’t scale. (ie if you want to get 2 sparklines, you need to do twice the work). Of course, we can write some macros to take care of that, but if you are open to macros, you might as well use SfE and save a lot of trouble. But this approach of shrinking a real chart is better as it gives you full power to customize the underlying chart (add multiple series etc.) which is not available in excel sparklines.

Download Excel Sparklines Tutorial Workbook

Click here to download Excel sparklines tutorial workbook. It shows all three kinds of sparklines in a simple dashboard format. Use the data to create your own sparklines to learn more.

Conclusions on Sparklines

The sparklines in Excel is certainly a great step forward in the world of data visualization. It brings ease and consistency to most users who want better visualizations but do not know how to create them. That said, Microsoft hasn’t really introduced any new types of sparklines since 2010. This is disappointing. Ideally few more types of sparklines such as these can help with dataviz.

On a lighter note, Kudos to Office Team at MS for not adding any 3D capabilities to these sparklines. That would have unleashed a fresh dose of chart monsters.

#evba #etipfree #eama #kingexcel
📤How to Download ebooks: https://www.evba.info/2020/02/instructions-for-downloading-documents.html?m=1