Microsoft Excel X may be a champ with numbers, but when it comes to displaying numerical information graphically, Excel’s standard chart formats don’t pack a lot of punch. But this doesn’t have to be the case. In just a few minutes, you can make your data look great, without help from another application. Here are some easy techniques for creatively using Excel X’s built-in tools to give your charts more visual impact.
If you haven’t created a chart in Excel before, type a few numbers into a worksheet and select them. Click on the toolbar’s Chart Wizard button (the one with the magic wand). (Choose View: Toolbars: Standard to display the button if it isn’t visible.) Now pick a chart type in the dialog box and click on Finish. Voilà — this wizard makes creating charts quick and easy, and it’s where you’ll start for these projects.
One way to make a bar (or column) chart more appealing — especially to viewers who dislike numbers — is to fill the bars with pictures.
Start with any bar chart, and click on one of the bars to select its data series. Choose Format: Selected Data Series, and click on the Patterns tab. Click on the Fill Effects button, select the Picture tab, and click on the Select Picture button.
Now you’re ready to choose an image. Look for something straightforward and easy to recognize — simple clip art works very well. Avoid photos unless the file is very small (a maximum of 200 pixels wide). Once you’ve found an appropriate image, select Insert, choose the Stack option, and then click on OK twice. A series of stacked images will fill the bars. Use the Stack option instead of Stretch — stretching tends to distort the image. Repeat for the other data series in your chart.
There’s no option for using Microsoft’s built-in clip art in a chart. You’ll have to use a workaround. Click on a cell in the worksheet (not the chart) and choose Insert: Picture: Clip Art. Control-click on the image and choose Cut. Now click on a bar to select all the bars in that data series, and choose Edit: Paste. (If the picture stretches, choose Format: Selected Data Series and then click on the Patterns tab. Click on Fill Effects, select the Picture tab, choose Stack, and click on OK twice.)
With your picture in place, widen the bars to make the images larger. Click on one of the bars, choose Format: Selected Data Series, and then click on the Options tab. Lower the Gap Width setting (which controls the space between bars) by moving the arrows, and check the results in the preview window. Click on OK when you’re satisfied with what you see.
Area charts — line charts with shading beneath the lines — work well when you want to show how data changes over a period of time. For example, you can use them to show changes in sales or expenses over the course of a year. Unfortunately, it’s all too easy for one area to block out the area behind it. You can avoid this by making the areas transparent.
First, use the Chart wizard to format your data as an area chart. Once Excel has generated the chart, click on the front area to select it. Choose Format: Selected Data Series, and click on the Patterns tab. Click on the Fill Effects button and then on the Gradient tab.
Excel gives you a few choices. Under the Colors heading, choose a single color or two colors — the first gives you a monochromatic gradient, and the second lets you blend colors. You can also select Preset to use a premade gradient. If you prefer a solid color, select the One Color option in the Gradient tab and choose your color. Move the Dark/Light slider to the middle to make a solid color.
Finally, adjust the Transparency settings, which control the opacity of the color in the gradient fill. The first value applies to the top chart area; the second, to the bottom. In our example, we used the Early Sunset preset gradient for the front area and Late Sunset gradient for the back. If you use high levels of transparency, your charts will be readable even with the most-psychedelic color combinations (for each gradient, we chose Transparency settings of 85 and 15 percent).
Remove the Excess Removing extras will streamline your chart so it’s less cluttered and easier to read. For example, you can simplify a 3-D area chart by removing its floor, walls, and axes.
Click on the colored background (the chart’s wall) and press the delete key to remove it. Repeat this for the floor. Next, remove the lines from the axes (leaving the values) — click on each in turn, choose Format: Selected Axis, select the Patterns tab, and choose None as the Lines setting. Click on OK.
As with any other chart type, you can add a background photo. Choose an image that complements the data you’re presenting and that won’t make the chart hard to read. If your chart is wider than it is high, choose an image that’s the same shape. To add the photo, click on the chart’s outer border and choose Format: Selected Chart Area, and then click on the Patterns tab. Click on the Fill Effects button, choose the Picture tab, and click on Select Picture. Click on your photo file, choose Insert, and then click on OK twice.
All the Tricks in the Book
You can do more to your charts than add a picture or tweak a gradient. In this example, you’ll use some of the techniques I’ve looked at, as well as some new ones. My idea was to create a chart that looked custom drawn. I still had the benefit of using data from an Excel worksheet, but the chart better fits the whimsical style of my fictitious company.
To get the same effect with your data, select it and click on the Chart Wizard button. From the Standard Types tab, click on the Cone chart and choose the last of the subtypes. Click on Finish.
To remove elements you don’t want, click on each in turn and press the delete key. Next, to select a cone, click on it (this selects everything in that data series) and then pause before clicking on it once more. Choose Format: Selected Data Point and click on the Patterns tab. Choose a color from the Area part of the dialog box.
To adjust the cones’ size and placement, click on the Options tab and fine-tune the settings for Gap Depth (the space between the front and back cones), Gap Width (the distance between each cone in a row of cones), and Chart Depth (the size of the bottom of the cones) until you get a result you like.
My settings were 90, 40, and 130, respectively. Click on OK to finish. Now click on the other cones in your chart to choose their colors.
Spin a Chart When you create 3-D charts in Excel, you’ve got one more trick up your sleeve. You can rotate the chart on multiple axes to get interesting effects. Use this feature to rotate a chart with overlapping series, and you can see each series more clearly.
To try it, control-click on your chart and choose 3-D View. The dialog box that appears shows the chart as a wire-frame drawing. Click on the arrows to rotate it around the axes. Click on Apply to see the effect on your chart. When you have a result you like, click on OK.
When you’re done, add labels. Click on the Text Box tool in the Drawing toolbar, and draw a text box beside a cone. Type a description in the box, control-click on the line’s outside border, and then choose Format Text Box. If you don’t want the box to have a border, click on the Colors And Lines tab, set the Line color to No Line, and click on OK. Copy this text box by dragging it with the option key pressed. Create one for each cone, and edit them as required.
For a final touch, I made the chart area transparent (by clicking on the chart area, choosing Format: Selected Chart Area, clicking on the Patterns tab, and setting Area to None). I then placed a filled free-form shape — drawn using the free-form tool in the Drawing toolbar’s Lines collection, and grouped with a similar line shape — behind it. To do this, control-click on a shape you’ve made and then select Arrange: Send Backward from the contextual menu.
Your Turn Now You don’t have to break out Adobe Photoshop when you want to create visually powerful charts. Combine your own design ideas with the techniques I’ve shown you here to create knockout Excel charts every time.