Hands on: Improve spreadsheet presentation

A spreadsheet doesn’t have to be a dull grid

Spreadsheets are designed to record, sort, calculate and store data. There are, however, times when you need to add graphics in order to communicate the information more effectively.

You might, for example, like some personalised scorecards for a golfing holiday with friends. To save time later, incorporate their names into a Custom List.

On a clean worksheet, enter the first name in cell A2 and drag it down the column to enter the other names. Enter Out, In and Total in B1:D1. Highlight those ranges in turn and format for the font, size and colour you prefer. Highlight the complete table and choose, Format, Cells, Patterns and select a background colour. Put the second Office XP disc in your DVD drive.

Select Picture amd then Clip Art on the Insert menu. Search in the Clip Art Task Pane for an appropriate illustration and add it to the worksheet. Now you can highlight the complete scorecard and choose Print Area, Set Print Area on the File menu, and print as many copies as you need.

Interactive shapes
Sometimes you need to graphically emphasise a point. Autoshapes can help here. On the Standard Toolbar click the Drawing tool; or choose View, Toolbars, Drawing. Click Autoshapes, Stars and Banners, Explosion 1. Drag this shape out on to a worksheet. Right-click on the shape. Choose Add Text and type something in the shape. Right-click the edge of the shape and choose Format Autoshape.

Under the Font tab pick the size, colour and style of font you wish to use. Under the Colors and Lines tab choose the fill colour and outline colour for the shape. Under the Alignment tab choose Center for the Horizontal and Vertical alignment, and check the Automatic size box.

This adjusts the shape to fit your text. If you right-click on the shape and enter a cell address in the Formula Bar, you can have an interactive entry in it. The attached PDF shows an explosion shape with data from cell G5. If the value in G5 is changed, the value in the shape will change.

You can choose Sheet, Background on the Format menu and choose a picture or pattern appropriate to the document. In this example it’s marbling. Clip Art offers a range of photographs, line art, cartoons and symbols. But that’s just one way to brighten up worksheet data. Wordart is another. It’s a good solution for creating logos you can use in any Office application. You can easily transfer it to a web page, too.

Take a screen grab. Paste it into a photo editor and save it as a .jpg file. In your web page code include an instruction like

<IMG alt=”logo”

src=”logo.jpg” border=0>

Dramatic graphs
It’s often not realised that graphics can be substituted for the vertical bars in a column chart. Not that you would want to do it regularly, but a cover of an annual report might use an image based on a chart. To try it out enter Bulbs, Plants and Shrubs in the range B2:B4; and 41,321, 105,456, and 125,311 in C2:C4. Enter Spring in C1.

Highlight B1:C4. Click the Chart Wizard. Choose the first Column chart type. In Step 2, choose Series in: Rows. Click the Next buttons until Step 4 where you can choose to have the chart on a new sheet. Right-click the vertical axis and choose Format axis. Under the Scale tab change the Maximum Y-Scale to 140,000.

Right-click any column, choose Format Data Series and under the Options tab, adjust the Overlap and Gap width options so the columns become fatter and the space between them less. Under the Patterns tab, click the Fill Effects button, the Picture tab, and the select Picture button. Here you can choose a photo from your My Pictures folder.

Alternatively, close the Format Data Series dialogue box and left-click on a column and choose Picture, Clip Art on the Standard Toolbar.

This displays the Clip Art search Task Pane and you can make your choice from your own Clip Art collection, disc 2 of Office, or download a graphic from the web. Repeat this for each column.

To format the background of the chart click on it and choose Format Plot area, Patterns Area and a colour. Click the Fill Effects button and a Variant. This will grade the colour from dark to light. Right-click on the Plot Area. Choose Chart Options, Data Labels. Check, Label Contains: Series name. This will display the labels in B2:B4 above the columns.

This obviates the need for a Legend, so you can right-click that object and choose, Clear.

Smartart
Excel 2007 offers all the graphical features described above plus a new one. Smartart has been created to help you arrange Autoshapes into organisational charts, flow charts, decision trees and relationship diagrams with a designer’s flourish. Here’s a simple example, a healthy-eating pyramid, though don’t get hung up on the content. Look elsewhere for dietary advice. Under the Insert tab choose Smartart.

This offers you dozens of designs initially broken down into groups: lists, process diagrams, cyclical patterns, hierarchical diagrams, relationships, matrixes and pyramids. Choose the basic pyramid. It appears on the worksheet along with a graphical toolbar offering variations. Choose the last one called Brick Scene. Click Text Pane, which is an option to the left of this toolbar.

Initially, this dialogue box offers three lines into which you can type text, because there are three levels in the basic pyramid. If you just press Enter on the first line instead of typing text it will add another line in the box and another layer in the pyramid. Repeat that and you now have five layers in the pyramid.

Type Sweets on the top line in the box, Milk and cheese on the second line, and so on. Point to the light rectangular outline of the Smartart object and drag the shape out to enlarge it. Right-click on a layer of the pyramid and choose a font. For example try Ariel Narrow Bold.

This right-click menu also offers alignment options, vertically and horizontally, and a Format Painter brush, so you can position the text in the shape and copy the formatting to the other layers. The toolbar of pyramid options under the Design tab has an option called Change Colors. If nothing suits here, right-click on a layer, choose Format Shape, Fill, Solid Fill and pick a colour.

To add a title to the pyramid, under the Insert tab choose Wordart. Type in the title. At the top of the Wordart object outline is a little green dot. You can drag this to left or right and tilt the Wordart object, so it’s parallel to the pyramid.To remove the worksheet gridlines click the Office Button, top-left of the screen, then the Excel options button at the bottom.

Next press Advanced, Display options for this worksheet, and remove the checkmark opposite, Show gridlines. Click OK. What a palaver! It was all so much quicker just choosing Options on the Tools menu in earlier versions. That’s progress for you.