10

Creating Charts and Graphics

In this chapter, you will learn how to ✔ Create charts. ✔ Customize the appearance of charts. ✔ Find trends in your data. ✔ Summarize your data by using sparklines. ✔ Create dynamic charts by using PivotCharts. ✔ Create diagrams by using SmartArt. ✔ Create shapes and mathematical equations.

When you enter data into a Microsoft Excel 2010 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 2010 you can create compact charts called sparklines, which summarize a data series using a graph contained within a single cell. You have a great deal of control over your charts’ appearance—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. Just as you can create a PivotTable dynamic view to reorganize your data dynamically, you can create a PivotChart dynamic view that reflects the contents and organization of the associated PivotTable. You can also add shapes, display mathematical equations, and create diagrams, such as organizational charts, that are useful in many organizations.

259

260

Chapter 10

Creating Charts and Graphics

In this chapter, you’ll learn how to create a chart and customize its elements, find trends in your overall data, summarize data using sparklines, create dynamic charts, and create and format shapes, diagrams, and shapes containing mathematical equations.
Practice Files Before you can complete the exercises in this chapter, you need to copy the book’s practice files to your computer. The practice files you’ll use to complete the exercises in this chapter are in the Chapter10 practice file folder. A complete list of practice files is provided in “Using the Practice Files” at the beginning of this book.

Creating Charts
To create a chart, select the data you want to summarize visually and then, on the Insert tab, in the Charts group, click the type of chart you want to create to have Excel display the available chart subtypes.

Troubleshooting The appearance of buttons and groups on the ribbon changes depending on the width of the program window. For information about changing the appearance of the ribbon to match our screen images, see “Modifying the Display of the Ribbon” at the beginning of this book.

Creating Charts

261

When you click a chart subtype, Excel creates the chart by using the default layout and color scheme defined in your workbook’s theme.
Keyboard Shortcut Press F11 to create a chart of the default type. Unless you or another user changed the default, Excel creates a column chart. See Also To see a complete list of keyboard shortcuts, see “Keyboard Shortcuts” at the end of this book.

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.

262

Chapter 10

Creating Charts and Graphics

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). To make that change, select the chart and then, on the Design tab, in the Data group, click Select Data to display the Select Data Source dialog box.

The Year column doesn’t belong in the Legend Entries (Series) pane, which corresponds to a column chart’s vertical axis. To remove a column from an axis, select the column’s name, and then click Remove. To add the column to the Horizontal (Category) Axis Labels pane, click that pane’s Edit button to display the Axis Labels dialog box.

In the Axis Labels dialog box, click the Collapse Dialog button at the right edge of the Axis Label Range field, select the cells to provide the values for the horizontal axis (not including the column header, if any), click the Expand Dialog button, and then click OK. Click OK again to close the Select Data Source dialog box and revise your chart.

Creating Charts

263

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. For example, Gary Schare, the chief executive officer of Consolidated Messenger, could create a workbook that summarizes the performance of each of his company’s business units. In that case, he would display the chart and data for each business unit on the same worksheet, so he would want to make his charts small. To resize a chart, select the chart, and then drag one of the handles on the chart’s edges. Handles in the middle of the edges enable you to resize the chart in one direction. Dragging a handle on the left or right edge enables you to make the chart narrower or wider, whereas dragging the handles on the chart’s top and bottom edges enable you to make the chart shorter or taller. Dragging a corner handle enables you to change the chart’s height and width at the same time; holding down the Shift key as you drag the corner handle changes the chart’s size without changing its proportions. Just as you can control a chart’s size, you can also control its location. To move a chart within a worksheet, drag the chart to the desired location. If you want to move the chart to a new worksheet, click the chart and then, on the Design contextual tab, in the Location group, click Move Chart to display the Move Chart dialog box.

264

Chapter 10

Creating Charts and Graphics

To move the chart to a new chart sheet, select the New Sheet option and type the new sheet’s name in the accompanying field. Selecting the New Sheet option creates a chart sheet that contains only your chart. You can still resize the chart on that sheet, but when Excel creates the new chart sheet, the chart takes up the full sheet. To move the chart to an existing worksheet, select the Object In option and then, in the Object In list, click the worksheet to which you want to move the chart. In this exercise, you’ll create a chart, change how the chart plots your data, move your chart within a worksheet, and move your chart to its own chart sheet. SET UP You need the YearlyPackageVolume_start workbook located in your Chapter10 practice file folder to complete this exercise. Start Excel, open the YearlyPackageVolume_start workbook, and save it as YearlyPackageVolume. Then follow the steps.

1. On the Data worksheet, click any cell in the Excel table. 2. On the Insert tab, in the Charts group, click Bar and then, in the 2D Bar group, click the first chart subtype. (The chart subtype is named Clustered Bar). Excel creates the chart, with both the Year and Volume data series plotted in the body of the chart.

Creating Charts

265

3. On the Design tab, in the Data group, click Select Data.
The Select Data Source dialog box opens.

4. In the Legend Entries (Series) area, click Year. 5. Click Remove.
The Year series disappears.

6. In the Horizontal (Category) Axis Labels area, click Edit.
The Axis Labels dialog box opens.

7. Select cells B3:B9, and then click OK.
The Axis Labels dialog box closes, and the Select Data Source dialog box reappears with the years in the Horizontal (Category) Axis Labels area.

266

Chapter 10

Creating Charts and Graphics

8. Click OK.
Excel redraws your chart, using the years as the values for the horizontal axis.

9. Move the mouse pointer over the body of the chart.
The mouse pointer changes to a four-headed arrow.

10. Drag the chart up and to the left so that it covers the Excel table. 11. On the Design tab, in the Location group, click Move Chart.
The Move Chart dialog box opens.

12. Click New sheet, type Volume Chart in the sheet name box, and then click OK.
Your chart appears on a chart sheet named Volume Chart.

CLEAN UP Save the YearlyPackageVolume workbook, and then close it.

Customizing the Appearance of Charts

267

Customizing the Appearance of Charts
If you want to change a chart’s appearance, select the chart and then, on the Design tab, click a style in the Chart Styles gallery. The gallery contains far more chart styles than are shown on the ribbon—to select a new look for your chart, click the More button at the gallery’s lower-right corner, and then click the design you want.

Tip The 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 by using the tools in the Insert tab’s Charts group, Excel creates an attractive chart that focuses on the data. In most cases, the chart has a title, legend (list of 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 the chart and then, on the Design tab, in the Chart Layouts group, clicking the layout you want. If you don’t find the exact chart layout you like, you can use the tools on the Layout contextual tab to control each element’s appearance and placement.

268

Chapter 10

Creating Charts and Graphics

In the Axes group, clicking Gridlines enables you to determine whether the chart displays horizontal and vertical gridlines and, if it does, at what value intervals they should appear. In addition to changing your chart’s layout, you can control the appearance of each element within the chart. To select a chart element to format, click that element. For example, if you want to change the formatting of the data series named Volume in the column chart you created in the previous exercise, clicking any column in the series selects the entire series. Any formatting changes you make then apply to every point in the entire series. If you want to change a single data point, select the entire series, and then click the chart element (for example, a column) that represents the data point you want to change. For example, you can highlight the column representing the year 2008 in the chart you created in the previous exercise. You can display a list of the selectable chart elements by selecting the chart and then, on the Format tab, in the Current Selection group, clicking the Chart Elements arrow. Then just click the desired chart element to select it.

After you select the chart element, you can drag one of the element’s handles to resize the element or drag the element to another location within the chart. To change the chart element’s format, use the tools and dialog box launchers in the Format tab’s Shape Styles, Word Art Styles, Arrange, and Size groups to change the element’s appearance. You can also select the chart element and then, on the Format tab, in the Current Selection group,

Customizing the Appearance of Charts

269

click Format Selection to display a Format dialog box that enables you to change the chart element’s appearance.

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 save a chart as a chart template, select the chart you want to use as a template and then, on the Design tab, in the Type group, click Save Template. Use the controls in the dialog box that appears to name and save your template. Then, to create a chart based on that template, select the data you want to summarize and then, on the Insert tab, in the Charts group, click any chart type, and then click All Chart Types to display the Create Charts dialog box. Under Choose A Chart Type, click Templates, and then click the template you want to use.
Tip You can apply a template to an existing chart by selecting the chart and then, on the Design tab, in the Type group, clicking Change Chart Type to display the Change Chart Type dialog box. Click Templates, and then click the template you want to use.

270

Chapter 10

Creating Charts and Graphics

In this exercise, you’ll change a chart’s layout, apply a new Chart Style, change the number format of the values on the vertical axis, save the chart as a chart template, and apply the template to another chart. SET UP You need the VolumeByCenter_start workbook located in your Chapter10 practice file folder to complete this exercise. Open the VolumeByCenter_start workbook, and save it as VolumeByCenter. Then follow the steps.

1. On the Presentation worksheet, select the chart. 2. On the Design tab, in the Chart Layouts group, click the first chart layout (its screen tip says the layout name is Layout 1). Excel changes the chart’s layout.

3. On the Design contextual tab, in the Chart Styles group, click the More button.
The Chart Styles gallery expands.

Customizing the Appearance of Charts

271

4. Click Style 7 (it’s the second-from-the-right style on the top row of the gallery).
Excel changes the chart’s style.

5. Right-click the values on the vertical axis, and then click Format Axis.
The Format Axis dialog box opens.

6. In the left pane, click Number.
The Format Axis dialog box displays the Number page.

7. In the Category list, click Number.
The Format Axis dialog box displays the Number style’s options.

8. In the Decimal places field, type 0. 9. If necessary, select the Use 1000 Separator (,) check box. 10. Click Close.

272

Chapter 10

Creating Charts and Graphics

Excel changes the format of the values on the vertical axis.

11. On the Design contextual tab, in the Type group, click Save As Template.
The Save Chart Template dialog box opens.

12. In the File name field, type Cool Blue. 13. Click Save.
Excel saves your template.

14. On the tab bar, click the Yearly Summary sheet tab.
The Yearly Summary worksheet appears.

Customizing the Appearance of Charts Finding Trends in Your Data

273

15. Select the chart and then, on the Design tab, in the Type group, click Change
Chart Type. The Change Chart Type dialog box opens.

16. Click Templates.
The My Templates list appears.

17. Click the Cool Blue custom template, and then click OK.
Excel applies the template to your chart.

CLEAN UP Save the VolumeByCenter workbook, and then close it.

274

Chapter 10

Creating Charts and Graphics

Finding 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 2010 make its best guess, for example, as to future shipping revenues if the current trend continues. As an example, consider a graph that shows the fleet maintenance costs for the years 2004 through 2009 for Consolidated Messenger.

The total has increased from 2004 to 2009, but the growth hasn’t been uniform, so guessing how much maintenance costs would increase if the overall trend continued would require difficult mathematical computations. Fortunately, Excel knows that math. To have Excel project future values in the maintenance costs data series, click the chart and then, on the Layout tab, in the Analysis group, click Trendline. Click More Trendline Options to display the Format Trendline dialog box. With the Trendline Options page of the Format Trendline dialog box, 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.

Finding Trends in Your Data

275

Tip If you don’t know which distribution to choose, use Linear. The other distributions are used for scientific and engineering applications and you will know, or be told by a colleague, when to use them.

After you pick the distribution type, you need to tell Excel how far ahead to project the data trend. The horizontal axis of the chart used in this example shows revenues by year from 2004 to 2009. To tell Excel how far in the future to look, type a number in the Forecast area’s Forward box. In this case, to look ahead one year, type 1 in the Forward box, and then click OK to add the trendline to the chart.
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.

276

Chapter 10

Creating Charts and Graphics

In this exercise, you’ll add a trendline to a chart. SET UP You need the FutureVolumes_start workbook located in your Chapter10 practice file folder to complete this exercise. Open the FutureVolumes_start workbook, and save it as FutureVolumes. Then follow the steps.

1. Select the chart. 2. On the Layout contextual tab, in the Analysis group, click Trendline, and then click More Trendline Options. The Format Trendline dialog box opens.

3. If necessary, in the Trend/Regression Type area, click Linear. 4. In the Forecast area, in the Forward field, type 3. 5. Click Close.
Excel adds the trendline to the chart.

CLEAN UP Save the FutureVolumes workbook, and then close it.

Summarizing Your Data by Using Sparklines
You can create charts in Excel workbooks 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 sparklines, which are new in Excel 2010, to create compact, informative charts that provide 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 2010, a sparkline occupies a single cell, which makes

Summarizing Your Data by Using Sparklines

277

it ideal for use in summary worksheets. As an example, suppose Lori Penor wanted to summarize the monthly revenue data for one of Consolidated Messenger’s local branches.

Lori 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). To create a line sparkline, you select the data you want to summarize and then, on the Insert tab, in the Sparklines group, click the Line button. When you do, Excel 2010 displays the Create Sparklines dialog box.

The data range you selected appears in the Data Range box. If the data range is not correct, you can click the Collapse Dialog button to the right of the Data Range box, select the correct cells, and then click the Expand Dialog button. Then, in the Location Range box, type the address of the cell into which you want to place your sparkline. When you click OK, Excel creates a line sparkline in the cell you specified.

278

Chapter 10

Creating Charts and Graphics

You follow the same basic procedure to create a column sparkline, except that instead of clicking the Line button in the Insert tab’s Sparklines group, you click the Column button. To create a win/loss sparkline, you need to ensure that your data contains, or could contain, both positive and negative values. If you measured monthly revenue for Consolidated Messenger, every value would be positive and the win/loss sparkline would impart no meaningful information. Comparing revenue to revenue targets, however, could result in positive, negative, or tie values, which can be meaningfully summarized by using a win/loss sparkline. To create a win/loss sparkline, follow the same data selection process and click the Win/ Loss button.

Summarizing Your Data by Using Sparklines

279

Months in which Consolidated Messenger’s branch exceeded its revenue target appear in the top half of the cell in blue, months in which the branch fell short of its target appear in the bottom half of the cell in red, and the month in which the revenue was exactly the same as the target is blank. 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. When you click a sparkline, Excel displays the Sparkline Tools Design contextual tab.

You can use the tools on the Design contextual tab to select a new style; show or hide value markers; change the color of your sparkline or the markers; edit the data used to create the sparkline; modify the labels on the sparkline’s axes; or group, ungroup, or clear sparklines. You can’t delete a sparkline by clicking its cell and then pressing the Delete or Backspace key—you must click the cell and then, on the Design contextual tab of the ribbon, click the Clear button.
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.

In this exercise, you’ll create a line, column, and win/loss sparkline, change the sparkline’s formatting, and clear a sparkline from a cell. SET UP You need the RevenueSummary_start workbook located in your Chapter10 practice file folder to complete this exercise. Open the RevenueSummary_start workbook, and save it as RevenueSummary. Then follow the steps.

1. Select the cell range C3:C14. 2. On the Insert tab, in the Sparklines group, click Line.
The Create Sparklines dialog box opens.

3. Verify that C3:C14 appears in the Data Range box. Then, in the Location Range box, type G3 and click OK. Excel 2010 creates a line sparkline in cell G3.

4. Select the cell range C3:C14. 5. On the Insert tab, in the Sparklines group, click Column.
The Create Sparklines dialog box opens.

280

Chapter 10

Creating Charts and Graphics

6. Verify that C3:C14 appears in the Data Range box. Then, in the Location Range box, type H3 and click OK. Excel creates a column sparkline in cell H3.

7. Drag the right edge of the column H header to the right until the cell’s width is approximately doubled. Excel displays more details in the sparkline.

8. Select the cell range E3:E14. 9. On the Insert tab, in the Sparklines group, click Win/Loss.
The Create Sparklines dialog box opens.

10. Verify that E3:E14 appears in the Data Range box. Then, in the Location Range box, type I3 and click OK. Excel creates a win/loss sparkline in cell I3.

11. With cell I3 still selected, on the Design contextual tab, in the Style gallery, click the right-most sparkline style. Excel changes the win/loss sparkline’s appearance.

12. Click cell G3 and then, on the Design contextual tab, in the Group group, click the Clear button. The sparkline disappears. CLEAN UP Save the RevenueSummary workbook, and then close it.

Creating Dynamic Charts by Using PivotCharts

281

Creating Dynamic Charts by Using PivotCharts
Just as you can create PivotTables that you can reorganize on the fly to emphasize different aspects of the data in a list, you can also create dynamic charts, or PivotCharts, to reflect the contents and organization of a PivotTable. Creating a PivotChart is fairly straightforward. Just click any cell in a list or Excel table you would use to create a PivotTable, and then click the Insert tab. In the Tables group, in the PivotTable list, click PivotChart to create the chart. To create a PivotChart from an existing PivotTable, click a cell in the PivotTable, display the Insert tab and then, in the Charts group, click the type of chart you want to create. After you complete either of these procedures, Excel displays a new PivotChart in your workbook.

Any changes to the PivotTable on which the PivotChart is based are reflected in the PivotChart. For example, if the data in an underlying data set changes, clicking the Refresh button in the Data group on the Analyze contextual tab will change the PivotChart to reflect the new data. Also, if you filter the contents of a PivotTable, the filter will be reflected in the PivotChart. For instance, if you click 2006 in the Year list of a revenue analysis PivotTable and then click OK, both the PivotTable and the PivotChart will show only revenues from 2006.

282

Chapter 10

Creating Charts and Graphics

See Also For more information on manipulating PivotTables, see “Filtering, Showing, and Hiding PivotTable Data” in Chapter 9, “Creating Dynamic Worksheets by Using PivotTables.”

A PivotChart has tools with which you can filter the data in the PivotChart and PivotTable. Clicking the Year arrow, clicking (All) in the list that appears, and then clicking OK will restore the PivotChart to its original configuration. If you ever want to change the chart type of an existing chart, you can do so by selecting the chart and then, on the Design tab, in the Type group, clicking Change Chart Type to display the Change Chart Type dialog box. When you select the type you want and click OK, Excel re-creates your chart.
Important If your data is the wrong type to be represented by the chart type you select, Excel displays an error message.

In this exercise, you’ll create a PivotTable and associated PivotChart, change the underlying data and update the PivotChart to reflect that change, change the PivotChart’s type, and then filter a PivotTable and PivotChart.

Creating Dynamic Charts by Using PivotCharts

283

SET UP You need the RevenueAnalysis_start workbook located in your Chapter10 practice file folder to complete this exercise. Open the RevenueAnalysis_start workbook, and save it as RevenueAnalysis. Then follow the steps.

1. On the Through 2009 worksheet, click any cell in the Excel table. 2. On the Insert tab, in the Tables group, click the PivotTable arrow and then, in the list that appears, click PivotChart. The Create PivotTable With PivotChart dialog box opens.

3. Verify that the QuarterlyRevenue table appears in the Table/Range field and that the New Worksheet option is selected.

4. Click OK.
Excel creates the PivotTable and associated PivotChart.

5. In the PivotTable Field List task pane, drag the Center field header from the
Choose fields to add to report area to the Legend Fields area. Axis Fields area.

6. Drag the Year field header from the Choose fields to add to report area to the 7. Drag the Quarter field header from the Choose fields to add to report area to the Axis Fields area, positioning it below the Year field header. the Values area.

8. Drag the Revenue field header from the Choose fields to add to report area to
Excel updates the PivotChart to reflect the field placements.

284

Chapter 10

Creating Charts and Graphics

9. Click the 2010 sheet tab.
The 2010 worksheet appears.

10. Select the data in cells B2:E10, and then press Ctrl+C.
Excel copies the data to the Microsoft Office Clipboard.

11. On the tab bar, click the Through 2009 sheet tab.
The Through 2009 worksheet appears.

12. Select cell B147, and then press Ctrl+V.
Excel pastes the data into the worksheet and includes it in the Excel table.

13. Click the tab of the worksheet that contains the PivotTable and the PivotChart.
The PivotChart appears.

Creating Dynamic Charts by Using PivotCharts

285

14. Select the PivotChart and then, on the Analyze contextual tab, in the Data group, click Refresh. Excel adds the new table data to your PivotChart.

15. On the Design contextual tab, in the Type group, click Change Chart Type.
The Change Chart Type dialog box opens.

16. Click Line, and then click the first Line chart subtype. 17. Click OK.
Excel changes your PivotChart to a line chart.

18. In the PivotTable Field List task pane, in the Choose fields to add to report area, point to the Center field header.

286

Chapter 10

Creating Charts and Graphics

19. Click the filter arrow that appears and then, in the filter menu, clear the Select All check box. Excel removes the check boxes from the filter list items.

20. Select the Northeast check box, and then click OK.
Excel filters the PivotChart.

CLEAN UP Save the RevenueAnalysis workbook, and then close it.

Creating Diagrams by Using SmartArt
As an international delivery company, Consolidated Messenger’s business processes are quite complex. Many times, chief operating officer Lori Penor summarizes the company’s processes for the board of directors by creating diagrams. Excel has just the tool she needs to create those diagrams: SmartArt. To create a SmartArt graphic, on the Insert tab, in the Illustrations group, click SmartArt to display the Choose A SmartArt Graphic dialog box.

Creating Diagrams by Using SmartArt

287

Clicking one of the buttons in the Choose A SmartArt Graphic 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. Clicking All displays every available SmartArt graphic type. The following table lists the nine types of diagrams you can create by using the Choose A SmartArt Graphic dialog box.
Diagram List Process Cycle Hierarchy Relationship Matrix Pyramid Picture Office.com Description Shows a series of items that typically require a large amount of text to explain Shows a progression of sequential steps through a task, process, or workflow Shows a process with a continuous cycle or relationships of core elements Shows hierarchical relationships, such as those within a company Shows the relationships between two or more items Shows the relationship of components to a whole by using quadrants Shows proportional, foundation-based, or hierarchical relationships such as a series of skills Shows one or more images with captions Shows diagrams available from Office.com

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.

288

Chapter 10

Creating Charts and Graphics

After you click the button representing the type of diagram you want to create, clicking OK adds the diagram to your worksheet.

While the diagram is selected, Excel displays the SmartArt Tools Design and Format contextual tabs. You can use the tools on the Design contextual tab to change the graphic’s layout, style, or color scheme. The Design contextual tab also contains the Create Graphic group, which is home to tools you can use to add a shape to the SmartArt graphic, add text to the graphic, and promote or demote shapes within the graphic. As an example, consider a process diagram that describes how Consolidated Messenger handles a package within one of the company’s regional distribution centers.

Creating Diagrams by Using SmartArt

289

The Text Pane, located to the left of the SmartArt graphic, enables you to add text to a shape without having to click and type within the shape. If you enter the process steps in the wrong order, you can move a shape by right-clicking the shape you want to move and then clicking Cut on the shortcut menu that appears. To paste the shape back into the graphic, right-click the shape to the left of where you want the pasted shape to appear, and then click Paste. For example, if you have a five-step process and accidentally switch the second and third steps, you can move the third step to the second position by rightclicking the third step, clicking Cut, right-clicking the first shape, and then clicking Paste. If you want to add a shape to a SmartArt graphic, to add a step to a process, for instance, click a shape next to the position you want the new shape to occupy and then, on the Design contextual tab, in the Create Graphic group, click Add Shape, and then click the option that represents where you want the new shape to appear in relation to the selected shape.

290

Chapter 10

Creating Charts and Graphics

Tip The options that appear when you click Add Shape depend on the type of SmartArt graphic you created and which graphic element is selected. For an organizational chart, the options are Add Shape After, Add Shape Before, Add Shape Above, Add Shape Below, and Add Assistant.

You can edit the graphic’s elements by using the buttons on the Format contextual tab as well as by right-clicking the shape and then clicking Format Shape to display the Format Shape dialog box. If you have selected the text in a shape, you can use the tools in the Font group on the Home tab to change the text’s appearance.
Tip The controls in the Format Shape dialog box enable you to change the shape’s fill color, borders, shadow, 3-D appearance, and text box properties.

In this exercise, you’ll create an organization chart, fill in the shapes, delete a shape, add a shape, change the layout of the diagram without changing the information it embodies, and change the formatting of one of the diagram elements. SET UP You need the OrgChart_start workbook located in your Chapter10 practice file folder to complete this exercise. Open the OrgChart_start workbook, and save it as OrgChart. Then follow the steps.

1. On the Insert tab, in the Illustrations group, click SmartArt.
The Choose A SmartArt Graphic dialog box opens.

2. Click Hierarchy.
The Hierarchy graphic subtypes appear.

Creating Diagrams by Using SmartArt

291

3. Click the first subtype (Organization Chart), and then click OK.
Excel creates the organization chart graphic.

4. In the Type your text here pane, in the first text box, type CEO, and then press the Down Arrow key. The value CEO appears in the shape at the top level of the organization chart.

5. In the SmartArt diagram, right-click the assistant box, located below and to the left of the CEO shape, and then click Cut. Excel removes the shape and moves the shapes on the third level of the organization chart to the second level.

6. Click the leftmost shape on the second level of the organization chart, and then type COO. type CIO.

7. Click the middle shape on the second level of the organization chart, and then

292

Chapter 10

Creating Charts and Graphics

8. Click the rightmost shape on the second level of the organization chart, and then type CFO.

9. Click the CFO shape. On the Design contextual tab, in the Create Graphic group, in the Add Shape list, click Add Shape Below. A new shape appears below the CFO shape.

10. In the new shape, type Comptroller. 11. On the Design contextual tab, in the Layouts group, click the second layout from the left on the second line of layouts. Excel applies the new layout to your organization chart.

12. Right-click the Comptroller shape, and then click Format Shape.
The Format Shape dialog box opens.

13. If necessary, click the Fill category.
Excel displays the fill options.

Creating Shapes and Mathematical Equations

293

14. Verify that the Solid fill option is selected, click the Color button and then, in the
Standard color area of the color picker, click the red square. Excel changes the shape’s fill to red.

15. Click Close.

CLEAN UP Save the OrgChart workbook, and then close it.

Creating 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.

294

Chapter 10

Creating Charts and Graphics

To add a shape to your worksheet, click the Insert tab and then, in the Illustrations group, click the Shapes button to display the shapes available. When you click a shape in the gallery, your mouse pointer changes from a white arrow to a thin black crosshair. To draw your shape, click anywhere in the worksheet and drag the mouse pointer until your shape is the size you want. When you release the mouse button, your shape appears and Excel displays the Drawing Tools Format contextual tab on the ribbon.

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. Dragging a handle on a side of the shape lets you drag that side to a new position; dragging a handle on the corner of the shape lets 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. To rotate a shape, select the shape and then drag the green circle at the top of the selection outline in a circle until the shape 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 contextual tab, in the Size group, typing the values you want in the height and width boxes.

After you create a shape, you can use the controls on the Format contextual tab to change its formatting. To apply a pre-defined style, click the More button at the bottom right corner of the Shape Styles group’s gallery and then click the style you want to apply. If none of the pre-defined styles are exactly what you want, you can 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 like before committing to a change.

Creating Shapes and Mathematical Equations

295

If you want to use a shape as a label or header in a worksheet, you can add text to the shape’s interior. To do so, select the shape and begin typing; when you’re done adding text, click outside the shape to deselect it. You can edit a shape’s text by moving the mouse pointer over the text. When the mouse pointer is in position for you to edit the text, 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. If you want to change the text’s appearance, you can use the commands on the Home tab or on the Mini Toolbar that appears when you select the text.

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 center. Aligning them vertically means they have the same right edge, left edge, or center. To align a series of shapes, hold down the Ctrl key and click the shapes you want to align. Then, on the Format contextual tab, in the Arrange group, click Align, and then click the alignment option you want. Distributing shapes moves the shapes so they have a consistent horizontal or vertical distance between them. To do so, select three or more shapes on a worksheet, click the Format contextual tab and then, in the Arrange group, click Align and then click either Distribute Horizontally or Distribute Vertically.

296

Chapter 10

Creating Charts and Graphics

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.

To change the order of the shapes, select the shape in the back, click the Format contextual tab, and then, in the Arrange group, click Bring Forward. When you do, Excel moves the back shape in front of the front shape. Clicking Send Backward has the opposite effect, moving the selected shape one layer back in the order. If you click the Bring Forward button’s arrow, you can choose to bring a shape all the way to the front of the order; similarly, by clicking the Send Backward button’s arrow, you can choose to send a shape to the back of the order. One other way to work with shapes in Excel is to add mathematical equations to their interior. As an example, a business analyst might evaluate Consolidated Messenger’s financial performance using a ratio that can be expressed using an equation. To add an equation to a shape, click the shape and then, on the Insert tab, in the Symbols group, click Equation, and then click the Equation Tools Design contextual tab on the ribbon to display the new interface for editing equations.

Creating Shapes and Mathematical Equations

297

Tip Clicking the Equation button’s arrow displays a list of common equations, such as the Pythagorean Theorem, that you can add with a single click.

Click any of the controls in the Structures group to begin creating an equation of that type. You can fill in the details of a structure by adding text normally or by adding symbols from the gallery in the Symbols group. In this exercise, you’ll create a circle and a rectangle, change the shapes’ formatting, reorder the shapes, align the shapes, add text to the circle, and then add an equation to the rectangle. SET UP You need the Shapes_start workbook located in your Chapter10 practice file folder to complete this exercise. Open the Shapes_start workbook, and save it as Shapes. Then follow the steps.

1. On the Insert tab, in the Illustrations group, click the Shapes button, and then click the oval. The mouse pointer changes to a thin black crosshair.

2. Starting near cell C3, hold down the Shift key and drag the mouse pointer to approximately cell E9. Excel draws a circle.

3. On the Format contextual tab, in the Shapes Styles group’s gallery, click the second style. Excel formats the shape with white text and a black background.

4. On the Insert tab, in the Illustrations group, click the Shapes button, and then click the rectangle shape. The mouse pointer changes to a thin black crosshair.

5. Starting near cell G3, drag the mouse pointer to cell K9.
Excel draws a rectangle.

6. On the Format contextual tab, in the Shapes Styles group’s gallery, click the first style. Excel formats the shape with black text, an orange border, and a white background.

298

Chapter 10

Creating Charts and Graphics

7. Click the circle and type 2011 Revenue Projections. Then, on the Home tab, in the Alignment group, click the Middle Align button. The text is centered vertically within the circle.

8. On the Home tab, in the Alignment group, click the Center button.
The text is centered horizontally within the circle.

9. Hold down Ctrl and click the circle and the rectangle. Then, on the Format contextual tab, in the Arrange group, click the Align button and then click Align Center. Excel centers the shapes horizontally.

10. Without releasing the selection, on the Format contextual tab, in the Arrange group, click the Align button and then click Align Middle. Excel centers the shapes vertically.

Creating Shapes and Mathematical Equations

299

11. Click any spot on the worksheet outside of the circle and rectangle to release the selection, and then click the rectangle.

12. On the Format contextual tab, in the Arrange group, click Send Backward.
Excel moves the rectangle behind the circle.

13. Press Ctrl+Z to undo the last action.
Excel moves the rectangle in front of the circle.

14. Click anywhere on the worksheet except on the circle or the rectangle. Click the rectangle and then, on the Insert tab, in the Symbols group, click Equation. The text Type Equation Here appears in the rectangle.

15. On the Equation Tools Design contextual tab, in the Structures group, click the
Script button, and then click the Subscript structure (the second from the left in the top row). The Subscript structure’s outline appears in the rectangle.

16. Click the left box of the structure and type Year. 17. Click the right box of the structure and type Previous. 18. Press the Right Arrow key once to move the cursor to the right of the word Previous and then, in the Symbols group’s gallery, click the Plus Minus symbol (the first symbol on the top row).

300

Chapter 10

Creating Charts and Graphics

19. In the Symbols group’s gallery, click the Infinity symbol (the second symbol on the top row).

20. Select all of the text in the rectangle and then, on the Home tab, in the Font group, click the Increase Font Size button four times. Excel increases the equation text’s font size.

CLEAN UP Save the Shapes workbook, and then close it. If you’re not continuing directly to the next chapter, exit Excel.

Key Points

301

Key Points
● You can use charts to summarize large sets of data in an easy-to-follow visual format. ● You’re not stuck with the chart you create; if you want to change it, you can. ● If you format many of your charts the same way, creating a chart template can save

you a lot of work in the future.
● Adding chart labels and a legend makes your chart much easier to follow. ● If your chart data represents a series of events over time (such as monthly or

yearly sales), you can use trendline analysis to extrapolate future events based on the past data.
● With sparklines, which are new in Excel 2010, you can summarize your data in a

compact space, providing valuable context for values in your worksheets.
● With a PivotChart, you can rearrange your chart on the fly, emphasizing different

aspects of the same data without having to create a new chart for each view.
● With Excel, you can quickly create and modify common business and organizational

diagrams, such as organization charts and process diagrams.
● You can create and modify shapes to enhance your workbook’s visual impact. ● The improved equation editing capabilities help Excel 2010 users communicate

their thinking to their colleagues.

Chapter at a Glance
Add headers and footers to printed pages, page 304

Prepare worksheets for printing, page 309

Print worksheets, page 318

Print parts of worksheets, page 322

Print charts, page 326