Sorting shaded rows
Modify defaults in Excel 5.0
Control print settings with a 1-2-3 macro
Move from 2-D to 3-D
Q I have an Excel 5.0 workbook that contains a list of teams in an intramural football league and their total points for the season. I'd like to print the list with every other row shaded so that it's easier to read. But each week, when I sort the list by points, the shading gets sorted, too. Then, before I can print, I must go back and redo the row shading. How can I do this more efficiently?
- James Pape
A I've got a trick that works well in Excel 5.0 and Quattro Pro 6.0. Create one range name that includes just the odd rows, and another with just the even rows. Then, after you've sorted the list, select each set of rows and set its colour. To see how this is done, first enter some sample data: enter a list of names in column A and a list of random numbers in column B, then follow the steps outlined below and illustrated in Figure 1. (Note that this technique will not work in 1-2-3, since that program does not let you assign a range name to a collection of ranges.)
1. Select row 2 by clicking on its row number, then press and hold the <Ctrl> key and select rows 4, 6, 8, 10 and so on. Select Insert-Name-Define, type blue into the Names in Workbook box, and click OK. Repeat to assign the name yellow to the odd-numbered rows.
2. To sort, click a cell in the column of numbers, then click the Sort Descending button on the standard toolbar.
3. Now select the cells you want to shade blue: click the down arrow beside the name box and select Blue.
4. Next, click the down arrow on the Colour tool and select a
light blue. Then repeat this procedure
in order to colour all of the odd-numbered rows
yellow.
In Quattro Pro, the procedure is similar: to name the rows, select them as described above and use the command Block-Names. To sort the list, click the Speedsort button. To colour the rows, click the Shading button on the Format toolbar.
Q Before I print an Excel workbook, I go to the Page Setup dialogue box; turn off the default headers, footers and gridlines; and centre the report horizontally on the page. Is there some way to change the defaults so I don't have to do this every time? And while we're at it, how do I change the default font and number format for all my new worksheets?
- Jim Tuckerman
A Before I tell you how to change the defaults, ask yourself, "Do I really want all my new workbooks to have these settings?" If the answer is yes, create a template that has all the settings you want, and save it with the name book.xlt in the xlstart subdirectory, which you'll find in the msoffice directory if you installed Excel as part of the Office suite, or in the Excel program directory if you installed it as a stand-alone.
To set up the template, begin in a new worksheet. If you want the new page settings to apply to all sheets in your workbooks, you need to select them all: click the Sheet1 tab at the bottom of the window, then click the right tab scrolling button that's located to the left of the tab, hold down the <Shift> key, and click on the last sheet tab (usually Sheet16).
Now select File-Page Setup, click the Header-Footer tab, and select (none) in both the Header and Footer drop-down lists. Click the Margins tab and select Horizontally in the Center on Page section. Then click the Sheet tab, deselect Gridlines and click OK.
To change the default settings for numbers, fonts and other formats, select Format-Style, select Normal in the Style Name drop-down box, if necessary, then click Modify and make your changes. When you're finished, click OK twice. All the cells assume the new formatting.
Save your template: select File-Save As; enter book in the File Name box; select Template in the Save File as Type list and specify the xlstart directory.
Suppose you don't want all your new workbooks to have these settings - only your quickie, one-page scratch workbooks. Just save the template with a name like quickie.xlt and put it into a directory other than xlstart. When you want to create a new quickie workbook, open the template. You'll get a new document called quickie1.xls with all the page and font settings ready to go. When you're ready to save the workbook, specify a new file name.
Q I make extensive use of 1-2-3 macros to select various documents with different print settings from the same file, and I've run into problems since upgrading from release 3.1 to 4.0 for Windows. In release 3.1, I could select the paper tray for individual pages of a report by including the command :PCBU (which stands for the menu commands :Print-Configuration-Bin-Manual). These commands are obsolete in release 4.0, and I haven't been able to find an equivalent. Is there a solution?
- Bill Anderson
A Unfortunately, I don't know a work-around for release 4.0. You can fix the problem with an upgrade to release 5.0, which lets you control many program settings with its {set} command. (Release 4.0's {set} command allows you to specify page setup options such as margins, but doesn't let you specify the printer bin.)
The syntax is {set info_component;info_value}. Info components encompass a wide range of settings, from sort ranges to window control to worksheet defaults. In release 5.0, for example, the command {set "Printer-setup-bins";"Manual Feed"} selects manual feed on an HP LaserJet 4.
To learn more about info components, select Help-Contents, click Macros and select Info Components. Read up on the {set} command, then click Info Component Categories to find individual descriptions.
Q I'm currently moving old worksheets from Quattro Pro 4.0 into Excel 5.0, and I want to reorganise them to take advantage of Excel's multisheet structure. When I open an old file in Excel, all the data appears on a single sheet; to make the file easier to navigate, I'd like to move sections of it onto other sheets in the workbook.
But when I move a section containing formulas that refer to another section, the formulas lose their references. For instance, if I have a formula that reads =SUM(A1:D12) and I move the data in range A1:D12 to Sheet2, the formula reads =SUM(#REF!). Is there an easy way around this, or must I edit each formula?
- Kathy Sherrieb
A Ouch! This is definitely a problem for anyone making the transition to Excel 5.0 from any 2-D spreadsheet - Quattro Pro 4.0 for DOS and earlier, 1-2-3 release 2.x or previous versions of Excel - and Excel 5.0 offers no ready solution. I'll suggest a few techniques that will help, but the best method might be to use another 3-D spreadsheet as a stepping stone to Excel.
One thing you can do is modify all your formulas so they include sheet references. For instance, instead of =SUM(A1:D12), edit the formula to read =SUM(Sheet!A1:D12). If you move this formula or the entire range A1:D12 to another sheet, the formula won't lose its range reference.
To add sheet references, you need to edit each formula, but some selection and editing tricks can help. Try this: begin by making a backup copy of your worksheet. Then select Edit-Go To, click Special in the Go To dialogue box, select the Formulas button in the next dialogue box and click OK. Now select Edit-Replace, type ( in the Find What field, type (Sheet1! in the Replace With field, and click Find Next. Examine the active cell formula on the formula bar; if its first parenthesis precedes a cell or range reference, click Replace.
Continue through all the formulas on the sheet, then repeat the operation, searching for other operators such as =, + and so on.
But the easiest way to reorganise your 2-D worksheets into Excel 5.0 is to enlist the aid of another 3-D spreadsheet, such as 1-2-3 release 3.x or higher, or Quattro Pro for Windows, which accommodates cross-sheet moves much more gracefully. If you can get your hands on one of these products, follow this procedure:
1. Save your Quattro Pro 4.0 worksheet as a .wk1 file.
2. Open the file in 1-2-3 or Quattro Pro for Windows, and reorganise it using the appropriate commands. In 1-2-3 insert the correct number of sheets. In both spreadsheets, use the Edit-Cut and Edit-Paste commands to move the formulas across sheets. As you reorganise your data, the formula references adjust accordingly.
3. When you finish, save the file in the .wk3 format (in Quattro Pro for Windows, select the file type 1-2-3 v. 3.x in the Save As dialogue box). When you open the .wk3 file in Excel, all your formula references will be intact.
- Richard Scoville