Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a way to create stacked column charts or combination charts with columns overlaid by a. To adjust the horizontal axis settings for a sparkline group, use the SparklineGroup.HorizontalAxis property. This property provides access to the SparklineHorizontalAxis object, which allows you to specify the following axis options. Visibility; To display the horizontal axis on a sparkline, set the SparklineColor.IsVisible property of the SparklineHorizontalAxis object to true.
Excel 2013 lets you spruce up a worksheet with a whole bevy of graphics, including sparklines (new tiny charts that fit right inside worksheet cells), text boxes, clip art drawings supplied by Microsoft, as well as graphic images imported from other sources, such as digital photos, scanned images, and pictures downloaded from the Internet.
In addition to these graphics, Excel 2013 supports the creation of fancy graphic text called WordArt as well as a whole bevy of organizational and process diagrams known collectively as SmartArt graphics.
Spark up the data with sparklines in Excel 2013
Excel 2013 supports a special type of information graphic called a sparkline that represents trends or variations in collected data. Sparklines are tiny graphs generally about the size of the text that surrounds them. In Excel 2013, sparklines are the height of the worksheet cells whose data they represent and can be any of the following chart types:
- Line that represents the relative value of the selected worksheet data
- Column where the selected worksheet data is represented by tiny columns
- Win/Loss where the selected worksheet data appears as a win/loss chart; wins are represented by blue squares that appear above red squares (representing the losses)
Sparklines via the Excel 2013 Quick Analysis tool
In Excel 2013, you can use the new Quick Analysis tool to quickly add sparklines to your data. All you have to do is select the cells in the worksheet to be visually represented, click the Quick Analysis tool followed by Sparklines on its options palette. This displays buttons for the three types of sparklines: Line, Column, and Win/Loss.
To preview how your data looks with each type of sparklines, highlight the button in the palette with the mouse pointer or Touch Pointer. Then, to add the previewed sparklines to your worksheet, simply click the appropriate Sparklines button.
You can see the sample Mother Goose Enterprises worksheet with the first quarter sales after the cell range B3:D11 has been selected and then opened the Sparklines tab in the Quick Analysis tool’s palette. Excel immediately previews line-type trendlines in the cell range E3:E11 of the worksheet. To add these trendlines, all you have to do is click the Line option in the tool’s palette.
![Diagram Sparkline Axe Diagram Sparkline Axe](/uploads/1/2/5/8/125858094/151447276.png)
Sparklines from the Excel 2013 Ribbon
You can also add sparklines the good old-fashioned way using the Sparklines command buttons on the Insert tab of the Ribbon. To manually add sparklines to the cells of your worksheet:
- Select the cells in the worksheet with the data you want to represent with sparklines.
- Click the chart type you want for your sparklines (Line, Column, or Win/Loss) in the Sparklines group of the Insert tab or press Alt+NSL for Line, Alt+NSO for Column, or Alt+NSW for Win/Loss.Excel opens the Create Sparklines dialog box containing two text boxes:
- Data Range: Shows the cells you select with the data you want to graph.
- Location Range: Lets you designate the cell or cell range where you want the sparklines to appear.
- Select the cell or cell range where you want your sparklines to appear in the Location Range text box and then click OK.When creating sparklines that span more than a single cell, the number of rows and columns in the location range must match the number of rows and columns in the data range. (That is, the arrays need to be of equal size and shape.)
Because sparklines are so small, you can easily add them to the cells in the final column of a table. That way, the sparklines can depict the data visually and enhance meaning while being an integral part of the table.
How to format sparklines
After you add sparklines to your worksheet, Excel 2013 adds a Sparkline Tools contextual tab with its own Design tab to the Ribbon that appears when the cell or range with the sparklines is selected.
This Design tab contains buttons that edit the type, style, and format of the sparklines. The final group on this tab enables you to band a range of sparklines into a single group that can share the same axis and/or minimum or maximum values. This is useful when you want a collection of sparklines to share the same charting parameters so that they represent the trends in the data equally.
You can’t delete sparklines from a cell range by selecting the cells and then pressing the Delete button. Instead, to remove sparklines, right-click their cell range and select Sparklines→Clear Selected Sparklines from its context menu.
-->Coordinate-based chart types (column, bar, area, point, line, and range) have two axes that are used to categorize and display data relationships. Different types of formatting will be applied to each axis.
You can format axes by using the Axis Properties dialog box or by using the Properties pane. Right-click the axis you want to format and click Axis Properties to change values for the axis text, numeric and date formats, major and minor tick marks, auto-fitting for labels, and the thickness, color, and style of the axis line. To change values for the axis title, right-click the axis title, and click Axis Title Properties.
Axis labels identify major intervals on the chart. By default, the chart uses an algorithm to determine how the labels should be optimally placed on the axis to avoid overlapping text.
Note
You can create and modify paginated report definition (.rdl) files in Report Builder and in Report Designer in SQL Server Data Tools. Each authoring environment provides different ways to create, open, and save reports and related items.
Types of Axes
The chart has two primary axes: the value axis and the category axis.
When you drag a field from your dataset onto the chart surface, the chart will determine whether this field belongs on the category or value axis.
The value axis is usually the vertical axis, or y-axis, of the chart. It is used to display numeric data values that are being charted. A field that is dragged into the data fields region will be plotted on the value axis. The category axis is usually the horizontal axis, or x-axis, of the chart. For bar charts, these axes are reversed. In bar chart types, the category axis is the vertical axis and the value axis is the horizontal axis. For more information, see Bar Charts (Report Builder and SSRS).
How the Chart Calculates Axis Label Intervals
Before you format axis labels, you should understand how the chart calculates axis label intervals. This will enable you to set the properties necessary to achieve the axis labeling behavior that you want.
The axis scale is bound by a minimum and a maximum value that define the data range to be displayed along the axis. The chart calculates the minimum and maximum value along each axis based on the values in your result set. On the value axis, the scale will always be determined by the smallest and largest number in the value field. On the category axis, the minimum and maximum value types are determined depending on the type of your category field. Any field in a dataset can be categorized into one of three category field types. The following table illustrates these three types of category fields.
Category Field Type | Description | Example |
---|---|---|
Numeric | Categories are plotted in numeric order along the x-axis. | A sales report by employee identification number displays the employee identification numbers along the x-axis. |
Date/time | Categories are plotted in chronological order along the x-axis. | A sales report by month displays formatted dates along the x-axis. |
Strings | Categories are plotted in the order it first appears in the data source along the x-axis. | A sales report by region displays region names along the x-axis. |
All chart types with two axes are designed to suppress some axis labels when there are too many categories to fit in order to produce a cleaner image on the chart and avoid label collisions.
The application calculates where labels are placed on an axis according to the following steps:
- Minimum and maximum values are identified based on the values in the result set.
- An equidistant number of axis intervals, usually between four and six, are calculated based on these minimum and maximum values.
- Based on the axis label properties, labels are displayed at these intervals. Properties that affect label placement include font size, the angle at which the labels are displayed, and text wrapping properties. These axis label auto-fit options can be changed.
Example of How the Chart Calculates Axis Labels
The table shown here contains sample sales data to be plotted on a column chart. The Name field is added to the Category Groups area, and the Quantity field is added to the Values area.
Name | Quantity |
---|---|
Michael Blythe | 229 |
Jae Pak | 112 |
Ranjit Varkey Chudukatil | 494 |
Jillian Carson | 247 |
Linda Mitchell | 339 |
Rachel Valdez | 194 |
The Quantity field is plotted along the value -axis. The lowest value is 112 and the highest value is 494. In this case, the chart calculates the scale to start at 0 and end at 500. The chart also calculates five equidistant intervals of 100, and creates labels at 0, 100, 200, 300, 400, and 500.
The Name field is plotted along the category axis. The chart calculates between four and six labels and it calculates auto-fit settings to determine how the labels can fit on the category axis without causing label collisions. As a result, some category labels might be omitted. You can override auto-fitting options for each axis independently.
Displaying All Labels on the Category Axis
On the value axis, axis intervals provide a consistent measure of the data points on the chart. However, on the category axis, this functionality can cause categories to appear without axis labels. Typically, you want all categories to be labeled. You can set the number of intervals to 1 to show all categories. For more information, see Specify an Axis Interval (Report Builder and SSRS).
Note
By superseding the automatic labeling features with a manual interval on an axis, the chart must resize all other elements appropriately. As a result, you may encounter unpredictable results with the sizing and positioning of the labels, or the size of other elements on the chart.
Variable Axis Intervals
The chart calculates approximately five axis label intervals regardless of the size of the chart. On wider or taller charts, if you show only five labels on an axis, large gaps can appear between each label. This makes it more difficult to identify the value of each data point against the axis. To avoid this behavior on wider or taller charts, you can set a variable axis interval. The chart will calculate the optimal number of labels that can appear on the axis based on the width or height of the chart, depending on the corresponding axis. For more information, see Specify an Axis Interval (Report Builder and SSRS).
Sorting Axis Values
Categories appear along the x-axis in the order that they appear in the result set. You can change the group order by adding a SORT command to the query or by sorting the dataset using an expression. Chart data regions are sorted the same as all other data regions. For more information about how to sort data, see Sort Data in a Data Region (Report Builder and SSRS).
Specifying Scalar Values on the Category Axis
By default, the chart will only display axis labels for data points in the dataset that contain valid values. For example, if you have values of 1, 2, and 6 on the category axis, the chart will only show categories 1, 2, and 6. To maintain the scale of category values, you can specify the chart to use a scalar axis. In this scenario, the chart will show labels for 1-6 on the x-axis of the chart, even though your dataset does not contain values for 3-5.
There are two ways to set a scalar axis:
- Select the Scalar axis option in the Axis Properties dialog box. This will add numeric or date/time values to the axis where no data grouping values exist. For more information, see Axis Properties Dialog Box, Axis Options (Report Builder and SSRS).
- Select a field or type an expression for the Category field option in the Series Properties dialog box. The chart will add axis intervals for all values in the category field you specified.
Adding or Removing Side Margins from the Category Axis
In Bar, Column and Scatter chart types, the chart automatically adds side margins on the ends of the x-axis. You cannot change the size of the margin. In all other chart types, the chart does not add side margins. For more information, see Add or Remove Margins from a Chart (Report Builder and SSRS).
In This Section
See Also
Formatting a Chart (Report Builder and SSRS)
Charts (Report Builder and SSRS)
Formatting Data Points on a Chart (Report Builder and SSRS)
Charts (Report Builder and SSRS)
Formatting Data Points on a Chart (Report Builder and SSRS)