Keep Mailing: [ ::: ♥Keep_Mailing♥ ::: ]™ Creating Spark-Line in MS Excel

Tuesday, 19 May 2015

[ ::: ♥Keep_Mailing♥ ::: ]™ Creating Spark-Line in MS Excel

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.

Example Sparkline Implementation - Project Team Member - 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.

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

How to create sparklines in Excel 2010 - Tutorial

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

Sparkline Formatting Options in Excel 2010
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?

Sparklines & Missing Data - Examples

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 Tables

You 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.
Sparklines in Pivot Tables - An Example

Sparkline Tips & Tricks

Here 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”.

Source: Chandoo

Junaid Tahir
www.DailyTenMinutes.com