Using Shapes in Excel 2007
Using shapes such as arrows and rounded rectangles in Excel 2007 can really help you emphasise important data in your spreadsheets. It can allow you to lead users of your spreadsheets to specific part of your spreadsheet quickly, or it can simply give your spreadsheet (or specific parts of your spreadsheet) more visual impact.
I use shapes heavily in my Excel 2007 documents. In particular, I use the rounded rectangle, the line arrow and boxed arrows a great deal. The rounded rectangle I use to create buttons with hyperlinks and the arrows I use to draw the readers attention to specific parts of the worksheet. I will illustrate firstly how to create a button with a hyperlink and then I will show how to get the best out of my old friend the red arrow. Examples of shapes are shown below
Both the rounded rectangle and the red arrow are created using the Shapes button on the Insert tab within the Illustrations group.
The rounded rectangle is used when I have something I want people to click (for example a hyperlink). In my example, a hyperlink will be embedded in the rectangle and activated when the user clicks on the rectangle. It can easily be sized to look like a button, so people will naturally click on it to return to the top of a document.
The red arrow to emphasise text. The line arrow I use to highlight a specific part of a chart for example and the box arrow is used to highlight something in its entirety (for example my button, or something I really want someone to fill in).
Using a rounded rectangle as a button containing a hyperlink
In the first example, we are going to create a button using the round rectangle shape that when clicked will take the user of our spreadsheet back to the top of the worksheet.
First we have to create the rounded rectangle.
- To create it, select the Shapes button.
- Select the rounded rectangle from the Rectangles section.
- The cursor will change to a + inviting you to size and place the rectangle as you desire.
- You will now have a blue rounded rectangle.
- Next can add text to it by simply clicking on it and typing, or right clicking on it and selecting Edit Text.
Now we create the hyperlink at the top of the document for use in our button.
- First, right click on a cell at the top of the worksheet and select Hyperlink from the Insert tab in the Links group.
- Select Link to: Place in This Document.
- Change the Text to Display to something meaningful (in the screen shot below you can see I chose Top imaginatively).
Now that we have a hyperlink set up for the top of the document you now need to add the hyperlink to the button.
This is achieved by right clicking the rounded rectangle and selecting Hyperlink.
Select Place in This Document on the left hand side and your newly created hyperlink at the top of this worksheet should be selectable
Finally, to make our rectangle look a bit more like a button, I gave it a bevel.
To do this, right click on your rectangle and select Format Shape
Click on 3-D Format and then the Top Button / Bevel as shown below
Your button now has a nice 3D effect and is ready for use.
The red arrow shape
I love using the red arrow in Excel 2007 (and Word 2007 for that matter). Whenever I want to draw someone’s attention to something important on a worksheet, I use a red arrow to point to it. It is admittedly not very subtle, but it is very effective.
The Shapes button has two groups of arrows available. The first is under Lines and the second is under Block Arrows.
I use both arrow styles, but for slightly different uses.
The arrow from the Lines group of shapes
I typically use this arrow when I want to point out something within a chart or other object. As you can see from the picture below, I am trying to draw attention to one particular value on a graph that I feel is important.
To create such a red arrow, select the Shapes button and then the arrow from the Lines group.
- To turn it around, grab one end of the arrow and rotate it to the angle you like.
- Click on the arrow in the middle to move it in its current form.
- To change the colour or make the arrow bigger, right click on it and select Format Shape. My arrows are normally 6pts in width (feel free to use whatever width you feel looks best).
- You can also change the colour using the Line Colour tab as well as an array of other formatting changes
Using the arrow from the Block Arrows group of shapes
I use the block arrows when I want to point out something in its entirety. For example, if I wish to draw attention to the button I created above, I would use a block arrow. To create a block arrow
- Click on the Shapes button and select your chosen arrow from the Block Arrow group
- As before, use the cursor to position and size the arrow
- Also right click and use the Format Shape option to colour and otherwise format your arrow.
- To rotate you arrow, click on it and select the green dot at the tip of the arrow as shown below. The cursor will change to a circle and you can rotate your arrow until it is in the desired position.
The below picture shows how I would use a block arrow to draw attention to my button. It is very hard to miss I think you will agree!
I hope that by following through my two examples of how I utilise shapes you have been encouraged to use shapes in your own Excel 2007 workbooks. I am sure there are literally thousands of uses for shapes; their usefulness is bound only by your imagination. I have shown how to incorporate buttons and links into worksheets using rounded rectangles as well as the very useful red arrow in both of its manifestations. I hope that you have found this hub useful and that you have enjoyed reading it as much as I enjoyed writing it.
I also have a number of other hubs on aspects of Excel 2007, covering everything from Conditional Formatting to creating charts and graphs. I have an Index hub which also covers how I successfully transitioned from Excel 2003 to 2007 as well as outlining my other Excel 2007 hubs which can be found here