Microsoft Excel 2016 Step by Step (2015)
Part 2: Analyze and present data
9. Create charts and graphics
In this chapter
Create charts
Create new types of charts
Customize chart appearance
Find trends in your data
Create dual-axis charts
Summarize your data by using sparklines
Create diagrams by using SmartArt
Create shapes and mathematical equations
Practice files
For this chapter, use the practice files from the Excel2016SBS\Ch09 folder. For practice file download instructions, see the introduction.
When you enter data into an Excel 2016 worksheet, you create a record of important events, whether they are individual sales, sales for an hour of a day, or the price of a product. What a list of values in cells can’t communicate easily, however, is the overall trends in the data. The best way to communicate trends in a large collection of data is by creating a chart, which summarizes data visually. In addition to the standard charts, with Excel 2016 you can create compact charts called sparklines, which summarize a data series by using a graph contained within a single cell.
You have a great deal of control over the appearance of your chart—you can change the color of any chart element, choose a different chart type to better summarize the underlying data, and change the display properties of text and numbers in a chart. If the data in the worksheet used to create a chart represents a progression through time, such as sales over several months, you can have Excel extrapolate future sales and add a trendline to the graph representing that prediction.
This chapter guides you through procedures related to creating a chart (including six chart types that are new in Excel 2016), customizing chart elements, finding trends in your data, summarizing data by using sparklines, and creating and formatting shapes, diagrams, and shapes containing mathematical equations.
Create charts
Excel 2016 lets you create charts quickly by using the Quick Analysis Lens, which displays recommended charts to summarize your data. When you select the entire data range you want to chart, clicking the Quick Analysis action button lets you display the types of charts Excel recommends.
Use the Quick Analysis Lens to add a chart to your worksheet
You can display a preview of each recommended chart by pointing to the icon representing that chart.
Tip
Press the F11 key to create a chart of the default type on a new chart sheet. Unless you or another user changed the default, Excel creates a column chart. For more information about keyboard shortcuts, see “Keyboard shortcuts” at the end of this book.
Display a live preview of a chart
Clicking the icon adds the chart to your worksheet.
If the chart you want to create doesn’t appear in the list of charts recommended by the Quick Analysis Lens, you can select the chart type you want from a gallery on the Insert tab of the ribbon. When you point to a subtype in the gallery, Excel displays a preview of the chart you will create by clicking that subtype.
Create charts by using tools on the Insert tab of the ribbon
When you click a chart subtype, Excel creates the chart by using the default layout and color scheme defined in your workbook’s theme.
If Excel doesn’t plot your data the way you want it to, you can change the axis on which Excel plots a data column. The most common reason for incorrect data plotting is that the column to be plotted on the horizontal axis contains numerical data instead of textual data. For example, if your data includes a Year column and a Volume column, instead of plotting volume data for each consecutive year along the horizontal axis, Excel plots both of those columns in the body of the chart and creates a sequential series to provide values for the horizontal axis.
A chart with horizontal axis labels plotted as data
You can change which data Excel applies to the vertical axis (also known as the y-axis) and the horizontal axis (also known as the x-axis). If Excel has swapped the values for the vertical and horizontal axes, you can switch the row and column data to update your chart. If the problem is a little more involved, you can edit how Excel interprets your source data.
Change how Excel plots your data by using the Select Data Source dialog box
The Year column should appear on the horizontal axis as a data category, which Excel refers to as the axis labels.
Identify horizontal (category) labels by using the Axis Labels dialog box
After you identify the cell range that provides the values for your axis labels, Excel will revise your chart.
A chart with horizontal and vertical axis values plotted correctly
After you create your chart, you can change its size to reflect whether the chart should dominate its worksheet or take on a role as another informative element on the worksheet.
Just as you can control a chart’s size, you can also control its location. You can drag a chart to a new location on its current worksheet, move the chart to another worksheet, or move the chart to its own chart sheet.
Pick a destination for a chart by using the Move Chart dialog box
To create a chart
1. Select the data you want to appear in your chart.
2. On the Insert tab of the ribbon, in the Charts group, click the type and subtype of the chart you want to create.
To create a chart of the default type by using a keyboard shortcut
1. Select the data you want to summarize in a chart.
2. Do either of the following:
• Press F11 to create the chart on a new chart sheet.
• Press the Alt+F1 key combination to create the chart on the active worksheet.
To create a chart by using the Quick Analysis Lens
1. Select the data you want to appear in your chart.
2. Click the Quick Analysis action button.
3. In the gallery that appears, click the Charts tab.
4. Click the chart type you want to create.
To create a recommended chart
1. Select the data you want to visualize.
2. In the Charts group, click the Recommended Charts button.
View Excel chart recommendations
3. Click the chart you want to create.
4. Click OK.
To change how Excel plots your data in a chart
1. Click the chart you want to change.
2. On the Design tool tab, in the Data group, click Select Data.
3. In the Select Data Source dialog box, do any of the following:
• Delete a Legend Entries (Series) data set by clicking the series and clicking the Remove button.
• Add a Legend Entries (Series) data set by clicking the Add button and, in the Edit Series dialog box that appears, selecting the cells that contain the data you want to add, and then clicking OK.
• Edit a Legend Entries (Series) data set by clicking the series you want to edit, clicking the Edit button, and, in the Edit Series dialog box, selecting the cells that provide values for the series.
• Change the order of Legend Entries (Series) data sets by clicking the series you want to move and clicking either the Move Up or Move Down button.
• Switch row and column data series by clicking the Switch Row/Column button.
• Change the values used to provide Horizontal (Category) Axis Labels by clicking that section’s Edit button and then, in the Axis Labels dialog box that appears, selecting the cells to provide the label values and then clicking OK.
To switch row and column values
1. Click the chart you want to edit.
2. In the Data group, click the Switch Row/Column button.
To resize a chart
1. Click the chart you want to edit.
2. On the Format tool tab, in the Size group, enter new values into the Height and Width boxes.
Or
Drag a handle to change the position of the chart’s edge or corner. You can do any of the following:
• Drag a handle in the middle of the top or bottom to change the chart’s height.
• Drag a handle in the middle of the left or right side to change the chart’s width.
• Drag a handle at a corner to change both the chart’s height and its width.
To reposition a chart within a worksheet
1. Click the chart.
2. Drag it to its new position.
To move a chart to another worksheet
1. Click the chart.
2. On the Design tool tab, in the Location group, click the Move Chart button.
3. In the Move Chart dialog box, click the Object in arrow.
4. In the Object in list, click the sheet to which you want to move the chart.
5. Click OK.
To move a chart to its own chart sheet
1. Click the chart.
2. In the Location group, click the Move Chart button.
3. In the Move Chart dialog box, click in the New sheet box.
4. Enter a name for the new sheet.
5. Click OK.
Create new types of charts
Excel 2016 introduces six new types of charts: waterfall, histogram, Pareto, box-and-whisker, treemap, and sunburst. Each of these new chart types enhances your ability to summarize your data and convey meaningful information about your business.
Waterfall charts summarize financial data by distinguishing increases from decreases and indicating whether a particular line item is an individual account, such as Direct Materials, or a broader measure, such as Starting Balance or Ending Balance.
Use waterfall charts to summarize financial data
Excel doesn’t automatically recognize which entries should be treated as totals, but you can double-click any columns that represent totals (or subtotals) and identify them so Excel knows how to handle them.
Histograms, which were previously available as part of the Data Analysis ToolPak, are now part of the standard Excel chart package. A histogram counts the number of occurrences of values within a set of ranges, where each range is called a bin. For example, a summary of daily package volumes for a delivery area could fall into several ranges.
Histograms summarize values by using groups called bins
A Pareto chart combines a histogram and a line chart to show both the contributions of categories of values, such as package delivery options (for example, overnight, priority overnight, and ground), and the cumulative contributions after each category is counted.
Pareto charts show category revenue and share of the total
A box-and-whisker chart combines several statistical measures, including the average (or mean), median, minimum, and maximum values for a data series, into a single chart. These charts provide a compact yet informative view of your data from a statistical standpoint.
Box-and-whisker charts provide graphic statistical summaries
The treemap chart divides data into categories, which are represented by colors, and shows the hierarchy of values within each category by using the size of the rectangles within the category. For example, you could represent regional frequencies for each package delivery option available to customers.
Treemap charts display contributions from elements of each data category
A sunburst chart breaks down a data set’s hierarchy to an even deeper level, showing the details of how much each subcategory of data contributes to the whole.
Sunburst charts show category contributions in detail
To create a waterfall chart
1. Select the data you want to visualize.
2. On the Insert tab, in the Charts group, click the Insert Waterfall or Stock Chart button.
3. Click the Waterfall chart type.
4. If necessary, identify a column as a total by clicking the column once to select the series, clicking the column again to select it individually, right-clicking the column, and then clicking Set as Total.
To create a histogram chart
1. Select the data you want to visualize.
2. In the Charts group, click the Insert Statistic Chart button.
3. In the Histogram group, click the Histogram subtype.
To create a Pareto chart
1. Select the data you want to visualize.
2. Click the Insert Statistic Chart button.
3. In the Histogram group, click the Pareto subtype.
To create a box-and-whisker chart
1. Select the data you want to visualize.
2. Click the Insert Statistic Chart button.
3. In the Histogram group, click the Box and Whisker subtype.
To create a treemap chart
1. Select the data you want to visualize.
2. In the Charts group, click the Insert Hierarchy Chart button.
3. In the Treemap group, click the Treemap subtype.
To create a sunburst chart
1. Select the data you want to visualize.
2. Click the Insert Hierarchy Chart button.
3. In the Sunburst group, click the Sunburst subtype.
Customize chart appearance
If you want to change a chart’s appearance, you can do so by using the Chart Styles button, which appears in a group of three buttons next to a selected chart. These buttons put chart formatting and data controls within easy reach of your chart.
Customize your chart by using the action buttons that appear beside the chart
The Chart Styles gallery has two tabs: Style and Color. The Style tab contains 14 styles from which to choose, and the Color tab displays a series of color schemes you can select to change your chart’s appearance.
Tip
If you prefer to work with the ribbon, these same styles appear in the Chart Styles gallery on the Design tab.
Select a color palette for your chart
Tip
The colors and styles in the Chart Styles gallery are tied to your workbook’s theme. If you change your workbook’s theme, Excel changes your chart’s appearance to reflect the new theme’s colors.
When you create a chart, Excel creates a visualization that focuses on the data. In most cases, the chart has a title, a legend (a list of the data series displayed in the chart), horizontal lines in the body of the chart to make it easier to discern individual values, and axis labels. If you want to create a chart that has more or different elements, such as additional data labels for each data point plotted on your chart, you can do so by selecting a new layout. If it’s still not quite right, you can show or hide individual elements by using the Chart Elements action button.
Click the Chart Elements action button to display or hide elements in the active chart
After you select a chart element, you can change its size and appearance by using controls specifically created to work with that element type.
Format a chart element by using a task pane designed for that element
You can use the third action button, Chart Filters, to focus on specific data in your chart. Clicking the Chart Filters action button displays a filter interface that is very similar to that used to limit the data displayed in an Excel table.
Focus on the data you want by using a chart filter
Selecting or clearing a check box displays or hides data related to a specific value within a series. You can also use the check boxes in the Series section of the panel to display or hide entire data series.
If you think you want to apply the same set of changes to charts you’ll create in the future, you can save your chart as a chart template. When you select the data you want to summarize visually and apply the chart template, you’ll create consistently formatted charts in a minimum of steps.
To apply a built-in chart style
1. Click the chart you want to format.
2. On the Design tool tab, in the Chart Styles gallery, click the style you want to apply.
Or
1. Click the chart you want to format.
2. Click the Chart Styles action button.
3. If necessary, click the Style tab.
4. Click the style you want to apply.
To apply a built-in chart layout
1. Click the chart you want to format.
2. In the Chart Layouts group, click the Quick Layout button.
Select a new layout from the Quick Layout gallery
3. Click the layout you want to apply.
To change a chart’s color scheme
1. Click the chart you want to format.
2. In the Chart Styles group, click the Change Colors button.
3. Click the color scheme you want to apply.
Or
1. Click the chart you want to format.
2. Click the Chart Styles action button.
3. If necessary, click the Color tab.
4. Click the color scheme you want to apply.
To select a chart element
1. Click the chart element.
Or
1. Click the chart.
2. On the Format tool tab, in the Current Selection group, click the Chart Elements arrow.
3. Click the chart element you want to select.
To format a chart element
1. Select the chart element.
2. Use the tools on the Format tool tab to change the element’s formatting.
Or
In the Current Selection group, click the Format Selection button to display the Format Chart Element task pane.
3. Change the element’s formatting.
To display or hide a chart element
1. Click the chart and do either of the following:
• On the Design tool tab, in the Chart Layouts group, click the Add Chart Element button, point to the element on the list, and click None to hide the element, or one of the other options to show the element.
• Click the Chart Elements action button and select or clear the check box next to the element you want to show or hide.
To create a chart filter
1. Click the chart you want to filter.
2. Click the Chart Filters action button.
3. Use the tools on the Values and Names tabs to create your filter.
To save a chart as a chart template
1. Right-click the chart.
2. Click Save as Template.
Save a chart as a template so that you can apply consistent formatting quickly
3. In the File name box, enter a name for the template.
4. Click Save.
To apply a chart template
1. Click the chart to which you want to apply a template.
2. On the Design tool tab, in the Type group, click Change Chart Type.
3. Click the All Charts tab.
4. Click the Templates category.
Apply a chart template to give your charts a consistent appearance
5. Click the template you want to apply.
6. Click OK.
Find trends in your data
You can use the data in Excel workbooks to discover how your business has performed in the past, but you can also have Excel 2016 make its best guess, for example, as to future shipping revenues if the current trend continues. As an example, consider a line chart that shows package volume data for the years 2009 through 2015.
Line chart that shows data over time
The total has increased from 2009 to 2015, but the growth hasn’t been uniform, so guessing how much package volume would increase if the overall trend continued would require detailed mathematical computations. Fortunately, Excel knows that math and can use it to add a trendline to your data.
Create a trendline to forecast future data values
You can choose the data distribution that Excel should expect when it makes its projection. The right choice for most business data is Linear—the other distributions (such as Exponential, Logarithmic, and Polynomial) are used for scientific and operations research applications. You can also tell how far ahead Excel should look—looking ahead by zero periods shows the best-fit line for the current data set, whereas looking ahead two periods would project two periods into the future, assuming current trends continued.
Change trendline characteristics by using the Format Trendline task pane
Tip
When you click the Trendline button in the Analysis group, one of the options Excel displays is Linear Forecast Trendline, which adds a trendline with a two-period forecast.
As with other chart elements, you can double-click the trendline to open a formatting dialog box and change the line’s appearance.
To add a trendline to a chart
1. Click the chart to which you want to add a trendline.
2. On the Design tool tab, in the Chart Layouts group, click the Add Chart Element button.
3. Point to Trendline and click the type of trendline you want to add.
To edit a trendline’s properties and appearance
1. Click the chart that contains the trendline.
2. On the Format tool tab, in the Current Selection group, click the Chart Elements arrow.
3. Click the element that ends with the word Trendline.
4. Click Format Selection.
5. Use the controls in the Format Trendline task pane to edit the trendline’s properties and appearance.
To delete a trendline
1. Click the trendline.
2. Press the Delete key.
Create dual-axis charts
The Excel 2016 charting engine is powerful, but it does have its quirks. Some data collections you might want to summarize in Excel will have more than one value related to each category. For example, each regional center for a package delivery company could have both overall package volume and revenue for the year. You can restructure the data in your Excel table to create a dual-axis chart, or combo chart, which uses two vertical axes to show both value sets in the same chart.
Tip
A Pareto chart, discussed earlier in this chapter, is a specific type of dual-axis chart.
To create a dual-axis chart
1. Select the data you want to visualize.
2. On the Insert tab, in the Charts group, click the Insert Combo Chart button.
3. Click the type of combo chart you want to create.
Or
Click Create Custom Combo Chart and use the settings in the Combo category of the All Charts tab to define your combo chart.
Summarize your data by using sparklines
You can create charts in Excel to summarize your data visually, by using legends, labels, and colors to highlight aspects of your data. It is possible to create very small charts to summarize your data in an overview worksheet, but you can also use a sparkline to create a compact, informative chart that provides valuable context for your data.
Edward Tufte introduced sparklines in his book Beautiful Evidence (Graphics Press, 2006), with the goal of creating charts that imparted their information in approximately the same space as a word of printed text. In Excel, a sparkline occupies a single cell, which makes it ideal for use in summary worksheets.
Data that Excel can summarize by using sparklines
You can create three types of sparklines: line, column, and win/loss. The line and column sparklines are compact versions of the standard line and column charts. The win/loss sparkline indicates whether a cell value is positive (a win), negative (a loss), or zero (a tie).
Examples of line, column, and win/loss sparklines
After you create a sparkline, you can change its appearance. Because a sparkline takes up the entire interior of a single cell, resizing that cell’s row or column resizes the sparkline. You can also change a sparkline’s formatting, modify its labels, or delete it entirely.
Format sparklines by using tools on the Sparkline Tools Design tool tab of the ribbon
Tip
Remember that sparklines work best when displayed in compact form. If you find yourself adding markers and labels to a sparkline, you might consider using a regular chart to take advantage of its wider range of formatting and customization options.
To create a sparkline
1. Select the data you want to visualize.
2. On the Insert tab, in the Sparklines group, do one of the following:
• Click the Line button.
• Click the Column button.
• Click the Win/Loss button.
Insert a sparkline by using the Create Sparklines dialog box
3. Verify that the data you selected appears in the Data Range box. If not, click the Collapse Dialog button next to the Data Range box, select the cells that contain your data, and then click the Expand Dialog button.
4. Click the Collapse Dialog button next to the Location Range box, click the cell where you want the sparkline to appear, and then click the Expand Dialog button.
5. Click OK.
To format a sparkline
1. Click the cell that contains the sparkline.
2. Use the tools on the Design tool tab to format the sparkline.
To delete a sparkline
1. Click the cell that contains the sparkline.
2. On the Design tool tab, in the Group group, click the Clear button.
Create diagrams by using SmartArt
Businesses define processes to manage product development, sales, and other essential functions. Excel 2016 comes with a selection of built-in diagram types, referred to as SmartArt, that you can use to illustrate processes, lists, and hierarchies within your organization.
Create SmartArt graphics by using the Choose A SmartArt Graphic dialog box
Clicking one of the buttons in the dialog box selects the type of diagram the button represents and causes a description of the diagram type to appear in the rightmost pane of the dialog box. The following table lists the nine categories of diagrams from which you can choose.
Tip
Some of the diagram types can be used to illustrate several types of relationships. Be sure to examine all your options before you decide on the type of diagram to use to illustrate your point.
After you click the button representing the type of diagram you want to create, clicking OK adds the diagram to your worksheet. As with other drawing objects and shapes, you can move, copy, and delete the SmartArt diagram as needed.
Show how your ideas relate by using SmartArt
While the diagram is selected, you can add and edit text; add, edit, or reposition shapes; and use the buttons on the ribbon to change the shapes’ formatting. To add text, you can either type directly into the shape or use the Text Pane, which appears beside the SmartArt diagram. When you’re done, click outside the shape to stop editing.
Tip
Pressing the Enter key after you edit the text in a SmartArt shape adds a new shape to the diagram.
To create a SmartArt graphic
1. Display the worksheet where you want the SmartArt graphic to appear.
2. On the Insert tab, in the Illustrations group, click the SmartArt button.
3. In the Choose a SmartArt Graphic dialog box, click the category from which you want to choose your graphic style.
4. Click the style of graphic you want to create.
5. Click OK.
To edit text in a SmartArt graphic shape
1. Click the shape, and then do either of the following:
• Edit the text directly in the shape.
• Click the corresponding line in the Text Pane and edit the text there.
To format shape text
1. Click the shape that contains the text you want to format.
2. Use the tools on the mini toolbar or the Home tab of the ribbon to format the text.
To add a shape
1. Click the shape next to where you want the new shape to appear.
2. On the Design tool tab, in the Create Graphic group, click the Add Shape arrow (not the button) and select where you want the new shape to appear.
Tip
If you click the Add Shape button (not the arrow), Excel adds a shape below or to the left of the current shape.
To delete a shape
1. Click the shape.
2. Press Delete.
To change a shape’s position
1. Click the shape you want to move.
2. In the Create Graphic group, do either of the following:
• Click Move Up.
• Click Move Down.
To change a shape’s level
1. Click the shape you want to move.
2. In the Create Graphic group, do either of the following:
• Click Promote.
• Click Demote.
To change a SmartArt graphic’s layout
1. Click the SmartArt graphic.
2. On the Design tool tab, in the Layouts group, click the More button in the lower-right corner of the Layouts gallery.
Select a new layout for your SmartArt diagram
3. Click the new layout.
Or
Click More Layouts to display the Choose a SmartArt Graphic dialog box, click a new layout in the dialog box, and click OK.
To change a SmartArt graphic’s color scheme
1. Click the SmartArt graphic.
2. On the Design tool tab, in the SmartArt Styles group, click the Change Colors button and click a new color scheme.
To apply a SmartArt Style
1. Click the SmartArt graphic.
2. In the SmartArt Styles group, click the More button in the lower-right corner of the SmartArt Styles gallery, and click the style you want to apply.
To format a shape
1. Click the shape you want to format.
2. Use the tools on the Format tool tab to change the shape’s formatting.
To delete a SmartArt diagram
1. Right-click the diagram, and then click Cut.
Create shapes and mathematical equations
With Excel, you can analyze your worksheet data in many ways, including summarizing your data and business processes visually by using charts and SmartArt. You can also augment your worksheets by adding objects such as geometric shapes, lines, flowchart symbols, and banners.
Tip
A SmartArt diagram is a collection of shapes that Excel treats as a collective unit. The shapes described in this topic are individual objects that Excel manages independently.
After you draw a shape on a worksheet, or select it after you’ve drawn it, you can use the controls on the Format tool tab of the ribbon to change its appearance.
Change shape formatting by using tools on the Drawing Tools Format tool tab
Tip
Holding down the Shift key while you draw a shape keeps the shape’s proportions constant. For example, clicking the Rectangle tool and then holding down the Shift key while you draw the shape causes you to draw a square.
You can resize a shape by clicking the shape and then dragging one of the resizing handles around the edge of the shape. You can drag a handle on a side of the shape to drag that side to a new position; if you drag a handle on the corner of the shape, you affect height and width simultaneously. If you hold down the Shift key while you drag a shape’s corner, Excel keeps the shape’s height and width in proportion as you drag the corner. You can also rotate a shape until it is in the orientation you want.
Tip
You can assign your shape a specific height and width by clicking the shape and then, on the Format tool tab, in the Size group, entering the values you want in the Height and Width boxes.
After you create a shape, you can use the controls on the Format tool tab to change its formatting. You can apply predefined styles or use the Shape Fill, Shape Outline, and Shape Effects buttons’ options to change those aspects of the shape’s appearance.
Tip
When you point to a formatting option, such as a style or option displayed in the Shape Fill, Shape Outline, or Shape Effects lists, Excel displays a live preview of how your shape would appear if you applied that formatting option. You can preview as many options as you want before committing to a change.
If you want to use a shape as a label or header in a worksheet, you can add text to the shape’s interior by clicking the shape and typing. If you want to edit a shape’s text, point to the text. When the mouse pointer is in position, it will change from a white pointer with a four-pointed arrow to a black I-bar. You can then click the text to start editing it or change its formatting.
Add text to shapes to make your labels stand out
You can move a shape within your worksheet by dragging it to a new position. If your worksheet contains multiple shapes, you can align and distribute them within the worksheet. Aligning shapes horizontally means arranging them so they are lined up by their top edge, bottom edge, or horizontal center. Aligning them vertically means lining them up so that they have the same right edge, left edge, or vertical center. Distributing shapes moves the shapes so they have a consistent horizontal or vertical distance between them.
If you have multiple shapes on a worksheet, you will find that Excel arranges them from front to back, placing newer shapes in front of older shapes.
Align shapes to make more attractive worksheets
You can change the order of the shapes to create exactly the arrangement you want, whether by moving a shape one step forward or backward, or moving it all the way to the front or back of the stack.
One other way to enhance your Excel files is to add mathematical equations to a worksheet. You can create a wide range of formulas by using built-in structures and symbols.
Build an equation by using the tools on the Equation Tools Design tool tab of the ribbon
Tip
Clicking the arrow next to the Equation button at the left end of the Equation Tools tab displays a list of common equations, such as the Pythagorean Theorem, that you can add with a single click.
Excel 2016 also provides the new capability of interpreting a handwritten equation that you draw directly into your worksheet.
Create an equation by writing it in the Ink Equation dialog box
To add a shape to a worksheet
1. On the Insert tab, in the Illustrations group, click the Shapes button to display the Shapes list.
2. Click the shape you want to add.
3. Click and drag in the body of the worksheet to define the shape.
To move a shape
1. Click the shape and drag it to its new location.
To resize a shape
1. Do either of the following:
• Grab a handle on a corner or edge of the shape to move one or more edges.
• On the Format tool tab, in the Size group, enter new values in the Height and Width boxes.
To rotate a shape
1. Click the shape and do one of the following:
• Drag the rotate handle (it looks like a clockwise-pointing circular arrow) above the shape to a new position.
• On the Format tool tab, in the Arrange group, click the Rotate button, and then select the rotate option you want.
• Click the Rotate button, and then click More Rotation Options to use the tools in the Format Shape task pane.
To change shape formatting
1. Click the shape you want to format.
2. Use the tools on the Format tool tab to change the shape’s appearance.
To add text to a shape
1. Click the shape.
2. Enter the text you want to appear in the shape.
3. Click outside the shape to stop editing its text.
To edit shape text
1. Point to the text in the shape. When the mouse pointer changes to a thin I-bar, click once.
2. Edit the shape’s text.
3. Click outside the shape to stop editing its text.
To format shape text
1. Point to the text in the shape. When the mouse pointer changes to a thin I-bar, click once.
2. Select the text you want to edit.
3. Use the tools on the mini toolbar and the Home tab of the ribbon to format the text.
4. Click outside the shape to stop editing its text.
To align shapes
1. Select the shapes you want to align.
2. On the Format tool tab, in the Arrange group, click the Align button.
3. Click the alignment option you want to apply to your shapes.
To distribute shapes
1. Select three or more shapes.
2. Click the Align button, and do either of the following:
• Click Distribute Horizontally to place the shapes on the worksheet with even horizontal gaps between them.
• Click Distribute Vertically to place the shapes on the worksheet with even vertical gaps between them.
To reorder shapes
1. Click the shape you want to move.
2. In the Arrange group, do either of the following:
• Click the Bring Forward arrow, and then click Bring Forward or Bring to Front.
• Click the Send Backward arrow, and then click Send Backward or Send to Back.
To delete a shape
1. Click the shape.
2. Press Delete.
To add a preset equation to a worksheet
1. On the Insert tab, in the Symbols group, click the Equation arrow (not the button).
2. Click the equation you want to add.
To add an equation to a worksheet
1. In the Symbols group, click the Equation button.
2. Use the tools on the Design tool tab of the ribbon to create the equation.
To add a handwritten equation to a worksheet
1. Click the Equation arrow.
2. Click Ink Equation.
3. In the Write Math Here area, write the equation you want to enter.
4. Click Insert.
To edit an equation
1. Click the part of the equation you want to edit.
2. Enter new values for the equation.
To delete an equation
1. Click the edge of the equation’s shape to select it.
2. Press Delete.
Skills review
In this chapter, you learned how to:
Create charts
Create new types of charts
Customize chart appearance
Find trends in your data
Create dual-axis charts
Summarize your data by using sparklines
Create diagrams by using SmartArt
Create shapes and mathematical equations
Practice tasks
The practice files for these tasks are located in the Excel2016SBS\Ch09 folder. You can save the results of the tasks in the same folder.
Create charts
Open the CreateCharts workbook in Excel, and then perform the following tasks:
1. Using the values on the Data worksheet, create a column chart.
2. Change the column chart so it uses the Year values in cells A3:A9 as the horizontal (category) axis values, and the Volume values in cells B3:B9 as the vertical axis values.
3. Using the same set of values, create a line chart.
4. Using the Quick Analysis Lens, create a pie chart from the same data.
Create new types of charts
Open the CreateNewCharts workbook in Excel, and then perform the following tasks:
1. Use the data on the Waterfall worksheet to create a waterfall chart. Identify the Opening Balance and Closing Balance values as totals.
2. Use the data on the Histogram worksheet to create a histogram.
3. Use the data on the Pareto worksheet to create a Pareto chart.
4. Use the data on the BoxAndWhisker worksheet to create a box-and-whisker chart.
5. Use the data on the Treemap worksheet to create a treemap chart.
6. Use the data on the Sunburst worksheet to create a sunburst chart.
Customize chart appearance
Open the CustomizeCharts workbook in Excel, and then perform the following tasks:
1. Using the chart on the Presentation worksheet, change the chart’s color scheme.
2. Change the same chart’s layout.
3. Using the chart on the Yearly Summary worksheet, change the chart’s type to a line chart.
4. Move the chart on the Yearly Summary worksheet to a new chart sheet.
Find trends in your data
Open the IdentifyTrends workbook in Excel, and then perform the following tasks:
1. Using the chart on the Data worksheet, add a linear trendline that draws the best-fit line through the existing data.
2. Edit the trendline so it shows a forecast two periods into the future.
3. Delete the trendline.
Create dual-axis charts
Open the MakeDualAxisCharts workbook in Excel, and then perform the following tasks:
1. Using the data on the Summary worksheet, create a dual-axis chart that displays the Volume series as a column chart and the Exceptions series as a line chart.
2. Ensure that the Exceptions values are plotted on the minor vertical axis at the right edge of the chart.
Summarize your data by using sparklines
Open the CreateSparklines workbook in Excel, and then perform the following tasks:
1. Using the data in cells C3:C14, create a line sparkline in cell G3.
2. Using the data in cells C3:C14, create a column sparkline in cell H3.
3. Using the data in cells E3:E14, create a win/loss sparkline in cell I3.
4. Change the color scheme of the win/loss sparkline.
5. Delete the sparkline in cell H3.
Create diagrams by using SmartArt
Open the MakeSmartArt workbook in Excel, and then perform the following tasks:
1. Create a process SmartArt diagram.
2. Fill in the shapes with the steps for a process with which you’re familiar.
3. Add a shape to the process.
4. Change the place where one of the shapes appears in the diagram.
5. Change the diagram’s color scheme.
6. Delete a shape from the diagram.
Create shapes and mathematical equations
Open the CreateShapes workbook in Excel, and then perform the following tasks:
1. Create three shapes and add text to each of them.
2. Edit and format the text in one of the shapes.
3. Move the shapes so you can determine which is in front, which is in the middle, and which is in back.
4. Change the shapes’ order and observe how it changes the appearance of the worksheet.
5. Align the shapes so their middles are on the same line.
6. Distribute the shapes evenly in the horizontal direction.
7. Delete one of the shapes.
8. Add a built-in equation such as the quadratic formula.
9. Enter an equation manually.