Chapter 1
In This Chapter
Understanding how to chart worksheet data
Creating an embedded chart or one on its own chart sheet
Editing an existing chart
Formatting the elements in a chart
Saving customized charts as templates and using these templates to create new charts
Adding sparklines to worksheet data
Printing a chart alone or with its supporting data
Charts present the data from your worksheet visually by representing the data in rows and columns as bars on a chart, for example, or as pieces of a pie in a pie chart. For a long time, charts and graphs have gone hand-in-hand with spreadsheets because they allow you to see trends and patterns that you often can’t readily visualize from the numbers alone. Which has more consistent sales, the Southeast region or the Northwest region? Monthly sales reports may contain the answer, but a bar chart based on the data shows it more clearly.
In this chapter, you first become familiar with the terminology that Excel uses as it refers to the parts of a chart — terms that may be new, such as data marker and chart data series, as well as terms that are probably familiar already, such as axis. After you get acquainted with the terms, you begin to put them to use going through the simple steps required to create the kind of chart that you want, either as part of the worksheet or a separate chart sheet.
The art of preparing a chart (and much of the fun) is matching a chart type to your purposes. To help you with this, I guide you through a tour of all the chart types available in Excel 2016, from old standbys, such as bar and column charts, to ones that may be new to you, such as radar charts and surface charts. Finally, you discover how to print charts, either alone or as part of the worksheet.
The typical Excel chart is comprised of several distinct parts. Figure 1-1 shows an Excel clustered column chart added to a worksheet with labels identifying the parts of this chart. Table 1-1 summarizes the parts of the typical chart.
Table 1-1 Parts of a Typical Chart
Part |
Description |
Chart area |
Everything inside the chart window, including all parts of the chart (labels, axes, data markers, tick marks, and other elements in this table). |
Data marker |
A symbol on the chart that represents a single value in the spreadsheet. A symbol may be a bar in a bar chart, a pie in a pie chart, or a line on a line chart. Data markers with the same shape or pattern represent a single data series in the chart. |
Chart data series |
A group of related values, such as all the values in a single row in the chart — all the quarterly sales for Rock CDs in the sample chart, for example. A chart can have just one data series (shown in a single bar or line), but it usually has several. |
Series formula |
A formula describing a given data series. The formula includes a reference to the cell that contains the data series name, references to worksheet cells containing the categories and values plotted in the chart, and the plot order of the series. The series formula can also have the actual data used to plot the chart. You can edit a series formula and control the plot order. |
Axis |
A line that serves as a major reference for plotting data in a chart. In two-dimensional charts, there are two axes: the x (horizontal/category) axis and the y (vertical/value) axis. In most two-dimensional charts (except, notably, column charts), Excel plots categories (labels) along the x-axis and values (numbers) along the y-axis. Bar charts reverse the scheme, plotting values along the y-axis. Pie charts have no axes. Three-dimensional charts have an x-axis, a y-axis, and a z-axis. The x- and y-axes delineate the horizontal surface of the chart. The z-axis is the vertical axis, showing the depth of the third dimension in the chart. |
Tick mark |
A small line intersecting an axis. A tick mark indicates a category, scale, or chart data series. A tick mark can have a label attached. |
Plot area |
The area where Excel plots your data, including the axes and all markers that represent data points. |
Gridlines |
Optional lines extending from the tick marks across the plot area, thus making it easier to view the data values represented by the tick marks. |
Chart text |
A label or title that you add to the chart. Attached text is a title or label linked to an axis such as the Chart Title, Vertical Axis Title, and Horizontal Axis Title that you can’t move independently of the chart. Unattached text is text that you add such as a text box with the Text Box command button on the Insert tab of the Ribbon. |
Legend |
A key that identifies patterns, colors, or symbols associated with the markers of a chart data series. The legend shows the data series name corresponding to each data marker (such as the name of the red columns in a column chart). |
An embedded chart is a chart that appears right within the worksheet (like the one shown in Figure 1-1) so that when you save or print the worksheet, you save or print the chart along with it. Note that your charts don’t have to be embedded. You can also choose to create a chart in its own chart sheet in the workbook at the time you create it. Embed a chart on the worksheet when you want to be able to print the chart along with its supporting worksheet data. Place a chart on its own sheet when you intend to print the charts of the worksheet data separately.
My personal favorite way to create a new embedded chart from selected data in a worksheet in Excel 2016 is with the Recommended Charts command button on the Insert tab of the Ribbon (Alt+NR).
When you use this method, Excel opens the Insert Chart dialog box with the Recommended Charts tab selected similar to the one shown in Figure 1-2. Here, you can preview how the selected worksheet data will appear in different types of charts simply by clicking its thumbnail in the list box on the left. When you find the type of chart you want to create, you then simply click the OK button to have it embedded into the current worksheet.
To the right of the Recommended Charts button in the Charts group of the Ribbon’s Insert tab, you find particular command buttons with galleries for creating the following particular types and styles of charts:
When using the galleries attached to these chart command buttons on the Insert tab to preview your data as a particular style of chart, you can embed the chart in your worksheet simply by clicking its chart icon.
If you’re not sure what type of chart best represents your data, rather than go through the different chart type buttons on the Ribbon’s Insert tab, you can use the All Charts tab of the Insert Chart dialog box shown in Figure 1-3 to “try out” your data in different chart types and styles. You can open the Insert Chart dialog box by clicking the Dialog Box launcher in the lower-right corner of the Charts group on the Insert tab and then display the complete list of chart types by clicking the All Charts tab in this dialog box.
For those times when you need to select a subset of a data table as the range to be charted (as opposed to selecting a single cell within a data table), you can use the Quick Analysis tool to create your chart. Just follow these steps:
Click the Quick Analysis tool that appears at the lower-right corner of the current cell selection.
Doing this opens the palette of Quick Analysis options with the initial Formatting tab selected and its various conditional formatting options displayed.
Click the Charts tab at the top of the Quick Analysis options palette.
Excel selects the Charts tab and displays buttons for different types of charts that suit the selected data, such as Column, Stacked Bar and Clustered Bar, followed by a More Charts option buttons. The different types of chart buttons preview the selected data in that kind of chart. The final More Charts button opens the Insert Chart dialog box with the Recommended Charts tab selected. Here you can preview and select a chart from an even wider range of chart types.
In order to preview each type of chart that Excel 2016 can create using the selected data, highlight its chart type button in the Quick Analysis palette.
As you highlight each chart type button in the options palette, Excel’s Live Preview feature displays a large thumbnail of the chart that will be created from your table data. (See Figure 1-4.) This thumbnail appears above the Quick Analysis options palette for as long as the mouse or Touch pointer is over its corresponding button.
When a preview of the chart you actually want to create appears, click its button in the Quick Analysis options palette to create it.
Excel 2016 then creates and inserts an embedded chart of the selected type in the current worksheet. This embedded chart is active so that you can immediately move it and edit it as you wish.
Sometimes you know you want your new chart to appear on its own separate sheet in the workbook and you don’t have time to fool around with moving an embedded chart created with the Quick Analysis tool or the various chart command buttons on the Insert tab of the Ribbon to its own sheet. In such a situation, simply position the cell pointer somewhere in the table of data to be graphed (or select the specific cell range in a larger table) and then just press F11.
Excel then creates a clustered column chart using the table’s data or cell selection on its own chart sheet (Chart1) that precedes all the other sheets in the workbook as shown in Figure 1-5. You can then customize the chart on the new chart sheet as you would an embedded chart that’s described later in the chapter.
You can use the command buttons on the Design tab of the Chart Tools contextual tab to make all kinds of changes to your new chart. This tab contains the following command buttons:
As soon as Excel draws a new chart in your worksheet, the program selects your chart and adds the Chart Tools contextual tab to the end of the Ribbon and selects its Design tab. You can then use the Quick Layout and Chart Styles galleries to further refine the new chart.
Figure 1-6 shows the original clustered column chart (created in Figure 1-5) after selecting Layout 9 on the Quick Layout button’s drop-down gallery and then selecting the Style 8 thumbnail on the Chart Styles drop-down gallery. Selecting Layout 9 adds Axis Titles to both the vertical and horizontal axes as well as creating the Legend on the right side of the graph. Selecting Style 8 gives the clustered column chart its dark background and contoured edges on the clustered columns themselves.
Normally when Excel creates a new chart, it automatically graphs the data by rows in the cell selection so that the column headings appear along the horizontal (category) axis at the bottom of the chart and the row headings appear in the legend (assuming that you’re dealing with a chart type that utilizes an x- and y-axis).
You can click the Switch Row/Column command button on the Design tab of the Chart Tools contextual tab to switch the chart so that row headings appear on the horizontal (category) axis and the column headings appear in the legend (or you can press Alt+JCW).
Figure 1-7 demonstrates how this works. This figure shows the same clustered column chart after selecting the Switch Row/Column command button on the Design tab. Now, column headings (Qtr 1, Qtr 2, Qtr 3, and Qtr 4) are used in the legend on the right and the row headings (Genre, Rock, Jazz, Classical, and Other) appear along the horizontal (category) axis.
When you click the Select Data command button on the Design tab of the Chart Tools contextual tab (or press Alt+JCE), Excel opens a Select Data Source dialog box similar to the one shown in Figure 1-8. The controls in this dialog box enable you to make the following changes to the source data:
The example clustered column chart in Figures 1-6 and 1-7 illustrates a common situation where you need to use the options in the Source Data Source dialog box. The worksheet data range for this chart, A2:Q7, includes the Genre row heading in cell A3 that is essentially a heading for an empty row (E3:Q3). As a result, Excel includes this empty row as the first data series in the clustered column chart. However, because this row has no values in it (the heading is intended only to identify the type of music download recorded in that column of the sales data table), its cluster has no data bars (columns) in it — a fact that becomes quite apparent when you switch the column and row headings, as shown earlier in Figure 1-7.
To remove this empty data series from the clustered column chart, you follow these steps:
Click the Chart1 sheet tab and then click somewhere in the chart area to select the clustered column chart; click the Design tab under Chart Tools on the Ribbon and then click the Select Data command button on the Design tab of the Chart Tools contextual tab.
Excel opens the Select Data Source dialog box in the 2016 Sales worksheet similar to the one shown in Figure 1-8.
Click Genre at the top of the Legend Entries (Series) list box and then click the Remove button.
Excel removes the empty Genre data series from the clustered column chart as well as removing the Genre label from the Legend Entries (Series) list box in the Select Data Source dialog box.
After you close the Select Data Source dialog box, you will notice that the various colored outlines in the chart data range no longer include row 3 with the Genre row heading (A3) and its empty cells (E3:Q3).
The command buttons on the Format tab on the Chart Tools contextual tab make it easy to customize particular parts of your chart. Table 1-2 shows you the options that appear on the Format tab. Note that depending on the type of chart that’s selected at the time, some of these options may be unavailable.
Table 1-2 Format Tab Options
Tab Group |
Option Name |
Purpose |
Current Selection |
Chart Elements |
Click this command button to select a new chart element by choosing its name from the button’s drop-down menu. |
Format Selection |
Click this command button to open a Format dialog box for the currently selected chart element as displayed on the Chart Elements drop-down list button. | |
Reset to Match Style |
Click this command button to remove all custom formatting from the selected chart and to return it to the original formatting bestowed by the style selected for the chart. | |
Insert Shapes |
Click the thumbnail of the shape you want to add to your chart on the drop-down gallery with a whole bunch of preset graphic shapes. (See Book V, Chapter 2 for details.) | |
Shape Styles |
Shape Styles |
Click the Shape Styles’ More button to display a drop-down gallery in which you can preview and select new colors and shapes for the currently selected chart element as displayed on the Chart Elements drop-down list button. |
Shape Fill |
Click this command button to display a drop-down color palette in which you can preview and select a new fill color for the currently selected chart element as displayed on the Chart Elements drop-down list button. | |
Shape Outline |
Click this command button to display a drop-down color palette in which you can preview and select an outline color for the currently selected chart element as displayed on the Chart Elements drop-down list button. | |
Shape Effects |
Click this command button to display a drop-down menu containing a variety of graphics effect options (including Shadow, Glow, Soft Edges, Bevel, and 3-D Rotation), many of which have their own pop-up palettes that allow you to preview their special effects, where you can select a new graphics effect for the currently selected chart element as displayed on the Chart Elements drop-down list button. | |
WordArt Styles |
WordArt Styles |
Click the WordArt Styles More button to display a drop-down WordArt gallery in which you can preview and select a new WordArt text style for the titles selected in the chart. If the Chart Area is the currently selected chart element as displayed on the Chart Elements drop-down list button, the program applies the WordArt style you preview or select to all titles in the chart. |
Text Fill |
Click this command button to display a drop-down color palette in which you can preview and select a new text fill color for the titles selected in the chart. If the Chart Area is the currently selected chart element as displayed on the Chart Elements drop-down list button, the program applies the WordArt style you preview or select to all titles in the chart. You can also select an image to be used as the text fill rather than a color by selecting the Picture option below the color palette. | |
Text Outline |
Click this command button to display a drop-down color palette in which you can preview and select a new text outline color for the titles selected in the chart. If the Chart Area is the currently selected chart element as displayed on the Chart Elements drop-down list button, the program applies the WordArt style you preview or select to all titles in the chart. | |
Text Effects |
Click this command button to display a drop-down menu with the Shadow, Reflection, Glow, Bevel, 3-D Rotation, and Transform graphics effect options active, each of which have their own pop-up palettes that you can use to preview and select special effects for the titles selected in the chart. If the Chart Area is the currently selected chart element as displayed on the Chart Elements drop-down list button, the program applies the WordArt style you preview or select to all titles in the chart. | |
Arrange |
Bring Forward |
Click this button to move the object to a higher layer in the stack or choose the Bring to Front option from the button’s drop-down menu to bring the selected embedded chart or other graphic object to the top of its stack. (See Book V, Chapter 2 for details.) |
Send Backward |
Click this button to move the object to a lower level in the stack or choose the Send to Back option from the button’s drop-down menu to send the selected embedded chart or other graphic object to the bottom of its stack. (See Book V, Chapter 2 for details.) Note that this command button and its options are available only when more than one embedded chart or other graphic object is selected in the worksheet. | |
Selection Pane |
Click this command button to display and hide the Selection and Visibility task pane that shows all the graphic objects in the worksheet and enables you to hide and redisplay them as well as promote or demote them to different layers. (See Book V, Chapter 2 for details.) Note that this command button and its options are available only when more than one embedded chart or other graphic object is selected in the worksheet. | |
Align |
Click this button to display a drop-down menu that enables you to snap the selected chart to an invisible grid on another graphic object as well as to choose between a number of different alignment options when multiple graphic objects are selected. (See Book V, Chapter 2 for details.) | |
Group |
Click this button to display a drop-down menu that enables you to group the selected embedded chart with other graphic objects (such as text boxes or predefined shapes) for purposes of positioning and formatting. (See Book V, Chapter 2 for details.) Note that this command button and its options are available only when more than one embedded chart or other graphic object is selected in the worksheet. | |
Rotate |
Click this button to display a drop-down menu with options that enable you to rotate or flip a selected graphic object. Note that this command button and its options are available only when graphic objects other than embedded charts are selected in the worksheet. | |
Size |
Shape Height |
Use this text box to modify the height of the selected embedded chart by typing a new value in it or selecting one with the spinner buttons. |
Shape Width |
Use this text box to modify the width of the selected embedded chart by typing a new value in it or selecting one with the spinner buttons. |
The Chart Elements button (with the plus sign icon) that appears in the upper-right corner of your chart when it’s selected contains a list of the major chart elements that you can add to your chart. To add a particular element missing from the chart, select the element’s check box in the list to put a check mark in it. To remove a particular element currently displayed in the chart, deselect the element’s check box to remove its check mark.
To add or remove just part of a particular chart element or, in some cases as with the Chart Title, Data Labels, Data Table, Error Bars, Legend, and Trendline, to also specify its layout, you select the desired option on the element’s continuation menu. (See Figure 1-9.)
So, for example, to reposition a chart’s title, you click the continuation button attached to Chart Title on the Chart Elements menu to display and select from among the following options on its continuation menu:
Data labels identify the data points in your chart (that is, the columns, lines, and so forth used to graph your data) by displaying values from the cells of the worksheet represented next to them. To add data labels to your selected chart and position them, click the Chart Elements button next to the chart and then select the Data Labels check box before you select one of the following options on its continuation menu:
Sometimes, instead of data labels that can easily obscure the data points in the chart, you’ll want Excel to draw a data table beneath the chart showing the worksheet data it represents in graphic form.
To add a data table to your selected chart and position and format it, click the Chart Elements button next to the chart and then select the Data Table check box before you select one of the following options on its continuation menu:
Figure 1-10 illustrates how the sample clustered column chart (introduced in Figure 1-6) looks with a data table added to it. This data table includes the legend keys as its first column.
When Excel first adds any title to a new chart, the program gives it a generic name, such as Chart Title or AXIS TITLE (for both the x- and y-axis title). To replace such generic titles with the actual chart titles, click the title in the chart or click the name of the title on the Chart Elements drop-down list. (Chart Elements is the first drop-down button in the Current Selection group on the Format tab under Chart Tools. Its text box displays the name of the element currently selected in the chart.) Excel lets you know that a particular chart title is selected by placing selection handles around its perimeter.
After you select a title, you can click the insertion point in the text and then edit as you would any worksheet text, or you can click to select the title, type the new title, and press Enter to completely replace it with the text you type. To force part of the title onto a new line, click the insertion point at the place in the text where the line break is to occur. After the insertion point is positioned in the title, press Enter to start a new line.
After selecting a title, you can then click the insertion point in the text and then edit as you would any worksheet text, or you can triple-click to select the entire title and completely replace it with the text you type. To force part of the title onto a new line, click the insertion point at the place in the text where the line break is to occur. After the insertion point is positioned in the title, press Enter to start a new line. After you finish editing the title, click somewhere else on the chart area to deselect it (or a worksheet cell, if you’ve finished formatting and editing the chart).
Double-click anywhere in a word in the chart title to completely select that word. Triple-click anywhere in the title text to completely select that chart title. When you double- or triple-click a chart title, a mini-bar appears above the title with buttons for modifying the selected text’s font, font size, and alignment, as well as for adding text enhancements such as bold, italic, and underlining.
Excel 2016 offers you several methods for formatting particular elements of any chart that you create. The most direct way is to right-click the chart element (title, plot area, legend, data series, and so forth) in the chart itself. Doing so displays a mini-bar with options such as Fill, Outline, and (in the case of chart titles), Style. You can then use the drop-down galleries and menus attached to these buttons to connect the selected chart element.
If the mini-bar formatting options aren’t sufficient for the kind of changes you want to make to a particular chart element, you can open a task bar for the element. The easiest way to do this is by right-clicking the element in the chart and then selecting the Format option at the bottom of the shortcut menu that appears. This Format option, like the task pane that opens on the right side of the worksheet window, is followed by the name of the element selected so that when the Chart Title is selected, this menu option is called Format Chart Title, and the task pane that opens when you select this option is labeled Format Chart Title. (See Figure 1-11.)
The element’s task pane contains groups of options, often divided into two categories: Options for the selected element on the left — such as Title Options in the Format Chart Title task pane or Legend Options in the Format Legend task pane — and Text Options on the right. Each group, when selected, then displays its own cluster of buttons and each button, when selected has its own collection of formatting options, often displayed only when expanded by clicking the option name.
When you choose the Format Chart Title option from a chart title’s shortcut menu, Excel displays a Format Chart Title task pane similar to the one shown in Figure 1-11. The Title Options group is automatically selected as is the Fill & Line button (with the paint can icon).
As you can see in Figure 1-11, there are two groups of Fill & Line options: Fill and Border (neither of whose particular options are initially displayed when you first open the Format Chart Title task pane — in this figure, I clicked both Fill and Border so that you could see all of the Fill options and the first part of the Border options). Next to the Line & Fill button is the Effects button (with the pentagon icon). This button has four groups of options associated with it: Shadow, Glow, Soft Edges, and 3-D Format.
You would use the formatting options associated with the Fill & Line and Effects buttons in the Title Options group when you want to change the look of the text box that contains the select chart title. More likely when formatting most chart titles, you will want to use the commands found in the Text Options group to actually change the look of the title text.
When you click Text Options in the Format Chart Title task pane, you find three buttons with associated options:
The axis is the scale used to plot the data for your chart. Most chart types will have axes. All 2-D and 3-D charts have an x-axis known as the horizontal axis and a y-axis known as the vertical axis with the exception of pie charts and radar charts. The horizontal x-axis is also referred to as the category axis and the vertical y-axis as the value axis except in the case of XY (Scatter) charts, where the horizontal x-axis is also a value axis just like the vertical y-axis because this type of chart plots two sets of values against each other.
When you create a chart, Excel sets up the category and values axes for you automatically, based on the data you are plotting, which you can then adjust in various ways. The most common ways you will want to modify the category axis of a chart is to modify the interval between its tick marks and where it crosses the value axis in the chart. The most common ways you will want to modify a value axis of a chart is to change the scale that it uses and assign a new number formatting to its units.
To make such changes to a chart axis in the Format Axis task pane, right-click the axis in the chart and then select the Format Axis option at the very bottom of its shortcut menu. Excel opens the Format Axis task pane with the Axis Options group selected, displaying its four command buttons: Fill & Line, Effects, Size & Properties, and Axis Options. You then select the Axis Options button (with the clustered column data series icon) to display its four groups of options: Axis Options, Tick Marks, Labels, and Number.
Then, click Axis Options to expand and display its formatting options for the particular type of axis selected in the chart. Figure 1-12 shows the formatting options available when you expand this and the vertical (value) or y-axis is selected in the sample chart.
The Axis Options for formatting the Vertical (Value) Axis include:
The Axis Options for formatting the Horizontal (Category) Axis include
The Tick Marks options in the Format Axis task pane include the following two options whether the Horizontal (Category) Axis or the Vertical (Value) Axis is selected:
Note that when modifying the Horizontal (Category) Axis, Excel offers an Interval Between Marks Tick Marks option that enables you to change the span between the tick marks that appear on this x-axis.
In addition to changing y- and x-axis formatting settings with the options found in the Axis Options and Tick Marks sections in the Format Axis task pane, you can modify the position of the axis labels with the Label Position option under Labels and number formatting assigned to the values displayed in the axis with Category option under Number.
To reposition the axis labels, click Labels in the Format Axis task pane to expand and display its options. When the Vertical (Value) Axis is the selected chart element, you can use the Label Position option to change the position to beneath the horizontal axis by selecting the Low option, to above the chart’s frame by selecting the High option, or to completely remove their display in the chart by selecting the None option on its drop-down list.
When the Horizontal (Category) Axis is selected, you can also specify the Interval between the Labels on this axis, specify their Distance from the Axis (in pixels), and even modify the Label Position with the same High, Low, and None options.
To assign a new number format to a value scale (General being the default), click Number in the Format Axis task pane to display its formatting options. Then, select the number format from the Category drop-down list and specify the number of decimal places and symbols (where applicable) as well as negative number formatting that you want applied to the selected axis in the chart.
After going through extensive editing and formatting of one of Excel’s basic chart types, you may want to save your work of art as a custom chart type that you can then use again with different data without having to go through all the painstaking steps to get the chart looking just the way you want it. Excel makes it easy to save any modified chart that you want to use again as a custom chart type.
To convert a chart on which you’ve done extensive editing and formatting into a custom chart type, you take these steps:
Right-click the customized chart in the worksheet or on its chart sheet to select its chart area and display its shortcut menu.
You can tell that the chart area (as opposed to any specific element in the chart) is selected because the Format Chart Area option appears near the bottom of the displayed shortcut menu.
Choose the Save As Template option from the shortcut menu.
Excel opens the Save Chart Template dialog box. The program automatically suggests Chart1.crtx as the filename, Chart Template Files (*.crtx) as the file type, and the Charts folder in the Microsoft Templates folder as the location.
After creating a custom chart template in this manner, you can then use the template anytime you need to create a new chart that requires similar formatting by following these steps:
Click the Dialog Box launcher in the lower-right corner of the Charts group on the Insert tab of the Ribbon.
The Insert Chart dialog box appears with the Recommended Charts tab selected.
Click the All Charts tab and then select the Templates option in the Navigation pane of the Insert Chart dialog box.
Excel then displays thumbnails for all the chart templates you’ve saved in the main section of the Create Chart dialog box. To identify these thumbnails by filename, position the mouse pointer over the thumbnail image.
As soon as you click OK, Excel applies the layout and all the formatting saved as part of the template file to the new embedded chart created with the data in the current cell selection.
Excel 2016 supports a type of information graphic called sparklines that represents trends or variations in collected data. Sparklines — invented by Edward Tufte — are tiny graphs (generally about the size of text that surrounds them). In Excel 2016, sparklines are the height of the worksheet cells whose data they represent and can be any one of following three chart types:
To add sparklines to the cells of your worksheet, you follow these general steps:
Click the type of chart you want for your sparkline (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, which contains two text boxes: Data Range, which shows the cells you selected with the data you want graphed, and Location Range, where you designate the cell or cell range where you want the sparkline graphic to appear.
Select the cell or range of cells where you want your sparkline to appear in the Location Range text box and then click OK.
When creating a sparkline that spans more than a single cell, the Location Range must match the Data Range in terms of the same amount of rows and columns. (In other words, they need to be arrays of equal size and shape.)
Figure 1-13 shows you a worksheet data table after adding sparklines to the table’s final column. These sparklines depict the variation in the sales over four quarters as tiny line graphs. As you can see in this figure, when you add sparklines to your worksheet, Excel 2016 adds a Design tab to the Ribbon under Sparkline Tools.
This Design tab contains buttons that you can use to edit the type, style, and format of the sparklines. The final group (called Group) on this Design tab enables you to band together a range of sparklines into a single group that can share the same axis and/or minimum or maximum values (selected using the options on its Axis drop-down button). This is very useful when you want a collection of different sparklines to all share the same charting parameters so that they equally represent the trends in the data.
To print an embedded chart as part of the data on the worksheet, you simply print the worksheet from the Print Settings screen in the Backstage view by pressing Ctrl+P. To print an embedded chart by itself without the supporting worksheet data, click the chart to select it before you press Ctrl+P to open the Print screen in the Backstage view. In the Print screen, the Print Selected Chart appears as the default selection in the very first drop-down list box under the Settings heading and a preview of the embedded chart appears in the Preview pane on the right.
To print a chart that’s on a separate chart sheet in the workbook file, activate the chart sheet by clicking its sheet tab and then press Ctrl+P to open the Print panel, where Print Active Sheet(s) appears in as the default selection for the Settings drop-down list box and the chart itself appears in the Preview pane on the right.
3.145.111.183