FW: Creating Spark-Line in MS Excel

[prasannam]

 

 

Subject: 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.

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.

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 2010:

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

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

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

---

w