Creating Charts and Graphics in Microsoft Excel 2013

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.

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, the pointer changes from a white arrow to a thin black crosshair. To draw your shape, click anywhere in the worksheet and drag the pointer until your shape is the size you want. When you release the mouse button, your shape appears and Excel displays the Format tool tab on the ribbon.

Click to view larger image

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; when 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. To rotate a shape, select the shape and then drag the white rotation handle at the top of the selection outline in a circle until the shape is in the orientation you want.

After you create a shape, you can use the controls on the Format tool tab to change its formatting. To apply a predefined style, click the More button in the lower-right corner of the Shape Styles group’s gallery and then click the style you want to apply. If none of the predefined styles are exactly what you want, you can use the Shape Fill, Shape Outline, and Shape Effects options to change those aspects of the shape’s appearance.

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 pointer over the text. When the 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. Horizontal shape alignment means that the shapes are lined up by their top edge, bottom edge, or center. Vertical shape alignment means that 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 tool 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 tool tab and then, in the Arrange group, click Align and then click either Distribute Horizontally or Distribute Vertically.

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 tool 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 arrow, you can choose to bring a shape all the way to the front of the order; similarly, when you click the Send Backward 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 by using a ratio that can be expressed with 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 Design tool tab to display the interface for editing equations.

Click to view larger image

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 workbook located in the Chapter09 practice file folder to complete this exercise. Open the workbook, and then follow the steps.

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

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

3. On the Format tool 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 pointer changes to a thin black crosshair.

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

6. On the Format tool 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.

   

   Click to view larger image

7. Click the circle and enter 2014 Revenue Projections. Then, on the Home tab, in the Alignment group, click the Middle Align button. Excel centers the text vertically within the circle.

8. On the Home tab, in the Alignment group, click the Center button. Excel centers the text horizontally within the circle.

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

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

    

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 tool 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 Design tool 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.

    

    Click to view larger image

16. Click the left box of the structure and enter Year.

17. Click the right box of the structure and enter 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 in the top row).

19. In the Symbols group’s gallery, click the Infinity symbol (the second symbol in 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

Close the Shapes workbook, saving your changes if you want to.