Excel 2010 Working with Sparklines

Author: mety Nagm Labels::

Excel 2010

Working with Sparklines


Lesson 18
Sparklines are miniature charts that fit into a single cell. Since they're so compact, you can place a large number of them in your worksheets. For example, you could place one sparkline on each row to show trends within that row. In this lesson, you will learn how to insert sparklines and change their typeand appearance.

Creating Sparklines

Launch video!Watch the video (4:50). Need help?
Sparklines were introduced in Excel 2010 to be a convenient alternative to charts. Unlike a traditional chart, a sparkline is placedinside a cell, allowing you to easily create a large number of sparklines (for example, one on each row).
Watch the video to learn about creating and modifying sparklines.

Types of Sparklines

There are three different types of sparklines: LineColumn, and Win/LossLine and Column work the same as line and column charts. Win/Loss is similar to Column, except it only shows whether each value is positive ornegative, instead of how high or low the values are. All three types can display markers at important points, such as the highest and lowest points, to make them easier to read.

Why Use Sparklines?

Sparklines are basically charts, so why would you want to use sparklines instead of charts? Sparklines have certain advantages that make them more convenient in many cases. Imagine you have 1000 rows of data. If you place a sparkline on each row, it will be right next to its source data, making it easy to see the relationshipsbetween the numbers and the sparkline. If you used a traditional chart, it would need to have 1000 data series in order to represent all of the rows, and you would probably need to do a lot of scrolling to find the relevant data in the worksheet.
Sparklines are ideal for situations where you just want to make the data clearer and more eye-catching, and where you don't need all of the features of a full chart. On the other hand, charts are ideal for situations where you want to represent the data in greater detail, and they are often better for comparing different data series.

To Create Sparklines:

Generally, you will have one sparkline for each row, but you can create as many as you want in any location you want. Just like with formulas it's usually easiest to create a single sparkline and then use the fill handle to automatically create the sparklines for the remaining rows.
  1. Select the cells that you will need for the first sparkline. In this example, we are creating a sparkline for Kathy Albertson, so we will select her sales data.
    Selecting cellsSelecting cells
  2. Click the Insert tab.
  3. In the Sparklines group, select Line. A dialog box will appear.
    The Line commandThe Line command
  4. Make sure the insertion point is next to Location Range.
  5. Click the cell where you want the sparkline to be. In this example, we'll select the cell to the right of the selected cells.
    Choosing a location for the sparklineChoosing a location for the sparkline
  6. Click OK. The sparkline will appear in the document.
  7. Click and drag the fill handle downward.
    Dragging the fill handleDragging the fill handle
  8. Sparklines will be created for the remaining rows.
    The finished sparklinesThe finished sparklines

    Changing the Appearance of Your Sparklines

     To Show Points on the Sparkline:

    Certain points on the sparkline can be emphasized with markers, or dots, making the sparkline more readable. For example, in a line with a lot of ups and downs, it may be difficult to tell which ones are the highest and lowest points, but if you show the High Point and Low Point, it will be easy to identify them.
    1. Select the sparklines that you want to change. If they are grouped, you only need to select one of them.
    2. Locate the Show group in the Design tab.
    3. Hover over the different checkboxes to see a description of each one.
      Hovering over the High Point checkboxHovering over the High Point checkbox
    4. Check each option that you want to show. The sparklines will update to show the selected options.
      The updated sparklinesThe updated sparklines

    To Change the Style:

    1. Select the sparklines that you want to change.
    2. Locate the Style group in the Design tab.
    3. Click the More drop-down arrow to show all of the available styles.
      Viewing all of the available stylesViewing all of the available styles
    4. Select the desired style.
      Selecting a sparkline styleSelecting a sparkline style
    5. The sparklines will update to show the selected style.
      The updated sparklinesThe new sparkline style

    To Change the Sparkline Type:

    1. Select the sparklines that you want to change.
    2. Locate the Type group in the Design tab.
    3. Select the desired type (Column, for example).
      Converting the sparkline type to ColumnConverting the sparkline type to Column
    4. The sparkline will update to reflect the new type.
      The converted sparklinesThe converted sparklines
    Some sparkline types will be better or worse for certain types of data. For example, Win/Loss is best suited for data where there may be positive and negative values (such as net earnings).

    Changing the Display Range

    By default, each sparkline is scaled to fit the maximum and minimum values of its own data. This allows it to fill the entire cell no matter how high or low the values are. However, it has a downside: if you are trying to compare several sparklines, you won't be able to tell at a glance which ones have higher or lower values. The solution is to make the display range the same for all of the sparklines.

    To Change the Display Range:

    1. Select the sparklines that you want to change.
    2. In the Design tab, click the Axis command. A drop-down menu will appear.
    3. Under Vertical Axis Minimum Value Options and Vertical Axis Maximum Value Options, select Same for All Sparklines.
      Changing the range of the sparklinesChanging the range of the sparklines
    4. The sparklines will update to reflect the new range.
      The updated sparklinesThe updated     sparklines



Blog Archive

Powered by Blogger.

I made these pages for me and my friends to help solving the problem we face regarding Computer & internet, if anyone wants me to answer a question or find out about some information please send me email and I will try to reply.*P.S. some of the article I wrote and the other I found on the internet I posted them in sprit of learning and shearing, please forgive me if you found something you don’t want to be in my blog, email me and I will delete them. Thank you for your interest in my pages.امل نجم Amal Nagm

banner 1 banner 2