Excel Charts: The Layout of the Land

SAN FRANCISCO (05/26/2000) - The layout of a chart can make a big difference in its legibility. Consider two charts. Both use the same data. One contains two data series, one for each region. The other uses six data series, one for each month. Which is better? There is no correct answer. If your goal is to make month-by-month comparisons, the top chart is a better choice. If you want to emphasize trends in each region, the bottom chart works better.

When you create a new chart, you start by selecting the data to be plotted.

Unless you tell Microsoft Corp.'s Excel otherwise, it makes some assumptions to determine how your data is plotted. If the number of rows in your selected range exceeds the number of columns, the program uses the columns for the data series. If the number of columns in your range exceeds or equals the number of rows, then Excel uses the rows for the data series.

In Step 2 of Excel's Chart Wizard dialog box, you can specify how the data will be plotted. Choose either Rows or Columns for the 'Series in' option (you'll be able to preview your choice). To change the way the data in an existing chart is plotted, select the chart by clicking it, then choose Chart*Source Data. In the Source Data dialog box, click the Data Range tab and then make your selection.

Restrict Cursor Movement to Unprotected CellsThe formulas in my worksheet use values in several input cells. I've unlocked the input cells and protected the sheet so the user can't change the formulas.

Can I set things up so the cell cursor moves only to the input cells?

J. Crewes, St. Louis

Yes. You've already unlocked your input cells and ensured that all other cells are locked. By default all cells are locked, but you can change that by using the Protection tab of the Format Cells dialog box. Select the cells to be changed and choose Format*Cells. In this case, the input cells are unlocked and all other cells are locked.

Protect the worksheet in Excel 97 by using Worksheet*Protect or Tools*Protection*Protect Sheet (you can specify a password to keep others from "unprotecting" the sheet). Once the sheet is protected, press Tab to move the cell pointer to the next unlocked cell.

This does not prevent the user from selecting unlocked cells using the cursor keys. To make those cells unselectable, change the worksheet's EnableSelection property. Select View*Toolbars*Control Toolbox to display the Control Toolbox toolbar. Click the Properties button to display the Properties box for the worksheet, then click the cell labeled xlNoRestrictions and use the drop-down list to change the EnableSelection property to xlUnlockedCells. Close the Properties box. As long as the worksheet is protected, users cannot select the locked cells on the worksheet.

This procedure does not save the EnableSelection property setting with the workbook. To create a simple macro that turns this setting on when the workbook is opened, press Alt-F11 to activate the Visual Basic Editor. Locate your workbook name in the Project window, and double-click it to expand its listing.

Then double-click the item labeled ThisWorkbook and enter the VBA code. This macro executes whenever the workbook is opened and sets the EnableSelection property of Sheet1 to xlUnlockedCells. The technique can be circumvented by changing the EnableSelection property to its default value (xlNoRestrictions).

Few users know about this dodge, however.

Send questions and tips to john@j-walk.com. We pay $50 for published items.

Contributing Editor John Walkenbach is the author of Excel 2000 Formulas (IDG Books, 2000) and maintains The Spreadsheet Page (www.j-walk.com).

Closing All Workbooks

If you find yourself with many workbooks open at the same time, you may be interested in a "hidden" command that will close all workbooks in one fell swoop. The trick is to press Shift while you click the File menu. When you do so, the Close command turns into the Close All command. When you select this menu item, Excel will close all of the currently open workbooks. If any of them have not been saved, you'll get the standard prompt asking if you want to save your workbooks.

Join the newsletter!


Sign up to gain exclusive access to email subscriptions, event invitations, competitions, giveaways, and much more.

Membership is free, and your security and privacy remain protected. View our privacy policy before signing up.

Error: Please check your email address.

More about Microsoft

Show Comments