Display Multiple Charts on a Single Chart Sheet

SAN FRANCISCO (05/01/2000) - A Microsoft Corp. Excel chart can appear embedded in a worksheet or reside in a separate Chart sheet. Here's a trick for storing multiple charts on a single Chart sheet.

Create charts as usual, placing them in a worksheet. Select any blank cell in the worksheet and press ; this creates an empty Chart sheet to hold the embedded charts. Reactivate your worksheet, click an embedded chart, and select Chart*Location to display the Chart Location dialog box. Choose As object in, and specify the empty Chart sheet. Excel will transfer the embedded chart to your Chart sheet.

Select your remaining charts, and use Chart*Location to move them to the Chart sheet. Now you can arrange and size the charts any way you like. Putting multiple charts on a single Chart sheet lets you use the View*Sized with Window command (available when the Chart sheet is active) to scale the charts to the window size and dimensions. When the window is resized, the charts adjust to fill the window automatically.

Display a Worksheet Name

Can I create a formula to display the name of the worksheet? I'm familiar with Excel's CELL() function, but it shows the workbook's full path.

Ken Fernandez, Fremont, California

When you print a worksheet, you might want the sheet's name displayed in a cell in case you need to refer to the original sheet. Oddly, Excel does not offer a function that displays the sheet's name. As you noted, the CELL() function comes close. The formula =CELL("filename") displays the workbook's full path, along with the worksheet name.

The actual string "filename" is the required parameter.

For example, this formula might return something like C:\WINDOWS\Desktop\[Budget.xls]Sheet2. Getting Excel to return only the sheet name requires a more complex formula that strips off everything except the name of the sheet:

=MID(CELL("filename"),FIND("]",CELL("filename"))+1,LEN(CELL("filename"))-FIND("]",CELL("filename")))Color Marks the SpotExcel's conditional formatting feature (available in Excel 97 or later) offers an easy way to apply special formatting to cells if a particular condition is met. This feature is even more useful when you understand how to use a formula in your conditional formatting specification.

Conditional formatting highlights students who scored higher on the second test. This formatting is dynamic; if you change the test scores, the formatting adjusts automatically.

To apply conditional formatting, select range A2:C15 and choose Format*Conditional Formatting. The Conditional Formatting dialog box will appear with two input boxes. In the first box, choose Formula Is, press , and enter =$C2>$B2 (meaning, if C2 is greater than B2). Click Format and choose a format to distinguish the cells (I used yellow shading). Click OK, and the formatting will be applied.

The conditional formatting formula is evaluated for each cell in the range. The trick here is to use mixed cell references (the column references are absolute, but the row references are relative). To see how this works, activate any cell within the range and choose Format*Conditional Formatting so you can examine the conditional formatting formula for that cell. You'll find that cell A7, for example, uses this formula: =$C7>$B7.

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).

Too Many Sheets?

By default, each new Excel workbook begins life with three worksheets. You can, of course, add more sheets to the workbook or delete sheets you don't need. The unused sheets don't occupy additional memory or increase file size, but I generally don't like them in my workbooks. A better approach is to change the default. Select Tools*Options and click General in the Options dialog box. Then change the setting for 'Sheets in new workbook'. Now all new workbooks will have the number of sheets you specified.

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 C2Microsoft

Show Comments