Tuesday, 19 May 2015

[HM:257972] 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.

  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.

How to create sparklines in Excel 2010 - Tutorial

Types of Sparklines in Excel 2010:

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

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 

 Follow me on Twitter  View my profile on LinkedIn  Like my page on Facebook  Read my articles on my personal blog  Subscribe to my Feed  Visit my Google+ Page

We are also on Face Book, Click on Like to jois us
FB Page: https://www.facebook.com/pages/Hyderabad-Masti/335077553211328
FB Group: https://www.facebook.com/groups/hydmasti/

You received this message because you are subscribed to the Google Groups "Hyderabad Masti" group.
To unsubscribe from this group and stop receiving emails from it, send an email to hyd-masti+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

No comments:

Post a Comment