Web Worker Daily

A few posts back I made the point that many computer users, including lots of web workers, reach for Microsoft Excel to do much more than just standard spreadsheets. Many people reach for it as their preferred way to produce charts and graphics, or do graphical prototyping. In this post, I’ll collect some useful ways to do Excel graphics and charts more efficiently, and make them look better.

Speed Charts. Before you start going to work on formatting a chart and making it look pretty, take a peek at it. Just select the data you’re going to chart and hit F11 to get a nice big view of it. Use the tabs at the bottom of the sheet to return to your original view. You’ll most likely see a column chart. If you want a different default chart type, right click on a finished chart that you like, and select Chart Type. Then click the Set as Default Chart button.

Add Patterns, Colors and Labels to Charts. In an Excel column chart, double click on a bar to bring up formatting choices. Select patterns, colors and more for your columns. This also works with other chart types.

Using Autoshapes. Most people who use the Drawing toolbar in Excel make use of only the most basic tools, such as arrows for annotation. Did you know that Excel has extensive shapes and object types that you can insert in your sheets? Go to View/Toolbars and bring up the Drawing toolbar. Click on the Autoshapes choice at the left of the toolbar, and experiment with some of the drop-downs you see. These can really dress up your sheets. You can also keep the result of a formula in your Autoshape. For example, if you have the value $1,000 entered in cell A2, click within your Autoshape, go to the Formula bar atop your sheet, and enter this: =$A$2. The value will show up in your Autoshape, and change whenever you change the value in cell A2.

Save Your Custom Chart Types. Do you have a frequently recurring type of chart that you do in Excel, such as a custom way to show budget numbers, or maybe a nice looking combination chart? First, right-click on the chart and select Chart Type. In the Custom Types tab, select User Defined. Use the Add button to name your chart type and add it to Excel’s Chart Wizard so that you can grab it just as easily as you grab, say, a column chart type.

Share Comments with Charts. To share comments with others when reviewing charts and data, right-click on any cell in the data that you’re charting and select Insert Comment. You’ll get a yellow sticky square that you can type your comment in, and the cell will display a little red triangle in the upper right corner. When others hover their mice over the triangle, they’ll see your comment.

Color Your Tabs. If you produce workbooks in Excel that have a lot of tabs, you can make the tabs stand out from each other by coloring them. Right click on a tab, and select Tab Color. Pick a color and click OK.

Break Free of Standard Chart Types. Not only do most Excel users not use some of the more eye-catching chart types in the product, they often don’t know they exist. In Excel, right-click on a chart and select Chart Types. Atop the dialog box, select Custom Types. You’ll see many slick and unusual chart types, including the always dependable exploding pie:

Do you have any good Excel tips?