Help Screen

Issue: December 1995/January 1996
Section: Spreadsheet
Pages: 192-195

Contents

Multiline column head in any spreadsheet
Out, damned zero!
Filter unique values from 1-2-3
Convert formulas to values and number lists in Excel


Multiline column head in any spreadsheet

Q I often want to put a multiline heading at the top of a column in Excel 5.0. Is it possible to include more than one line of text in a single cell?

- Andrew Kelleher

A Certainly. Let's assume that you've entered your column headings in row 1. Click the row number to select the entire row, select Format-Cells, and click the Alignment tab at the top of the dialogue box. Select the alignment you want in the Horizontal and Vertical boxes, select the Wrap Text check box, and then click OK. You will end up with multiline column headings, as shown below.

You can use the same trick in the other Windows spreadsheets. In 1-2-3 for Windows, the command is Style-Align-ment. In Quattro Pro, right-click on the row number, then select Block Properties, and click Alignment in the resulting dialogue box.

In Excel only, you can force a new line in a cell entry by pressing <Alt>+<Enter> where you want the break.

Out, damned zero!

Q I have a 1-2-3 version 4.0 for DOS worksheet that contains many zeros, and I want to get rid of them. How can I make them disappear?

- Sanjay Shah, Internet

A It depends on what you mean by "disappear". Let's say you've got a simple cosmetic need: your formulas are limited to simple sums and other calculations that need not distinguish zeros from empty cells (as opposed to counts, averages and so on). Then all you need to do is format the cells to make the zero values invisible.

In 1-2-3 for DOS, select Worksheet-Global-Zero-Yes. In 1-2-3 for Windows, select Style-Worksheet Defaults, and deselect the Display zeros as check box.

In Quattro Pro 6.x for Windows, right-click the sheet tab at the bottom of the window, choose Display in the Active Page dialogue box, and click No under Display Zeros (in Quattro Pro 5.0, look for Display Zeros when you right-click on the page tab).

In Excel, select the columns that contain the values you want to format, choose Format-Cells, and click the Number tab. In the Category section, select Number, and in the Format Codes section, choose the format in which you want to display the nonzero values - for example, #,##0_);(#,##0) displays commas between thousands and hundreds, no decimal places and negatives in parentheses. Then click in the Code box, where the format code appears, and add the characters ;"" to the end of the code - for example, #,##0_);(#,##0);"". Click OK, and you are finished. These commands hide the zeros already in your worksheet, and they hide unwanted zeros that may creep in later.

If, on the other hand, your formulas utilise functions like @count or @avg that count the number of cell entries, you must do more than just disguise the zeros. Because these functions include cells that contain hidden zeros in their calculations, you have to get rid of the zeros altogether.

In Quattro Pro and Excel, you can easily eliminate hidden zeros with the Find and Replace command. If you're working in Quattro Pro for Windows version 5.0 or 6.0, select the columns or rows that contain the data, and choose Edit-Find and Replace. Enter 0 in the Find field, and don't enter anything in the Replace field. Click the Replace All button, and the zeros in your worksheet will be banished for good. The procedure is virtually identical in Excel, where the command is Edit-Replace instead.

Unfortunately, getting rid of zeros in a 1-2-3 worksheet isn't as straightforward as in Excel and Quattro Pro, because 1-2-3's Find and Replace command doesn't recognise numeric cell entries - only text strings and characters in formulas. You need to write a macro to remove the zeros. Try this one in any version of 1-2-3:

{if @cellpointer("type")="b"}{quit}

{if @cellpointer("contents")=0}{blank

@cellpointer("address")}

{down}{branch \Z}

Enter the macro into three convenient cells, select the first cell and use the command Range-Name to name it \Z. Then select the top cell in the column that contains your data, and press <Ctrl>+Z. The macro moves down the column, erasing each cell that contains a zero, and stops whenever it encounters a blank cell. Use this macro on each column that contains zeros you want to eliminate.

Filter unique values from 1-2-3

Q I'm trying to create price lists in Lotus 1-2-3 release 5.0. Our accounting package lets me produce a report that shows the quantity and amount we purchased from one company for each inventory item, as in the figure right.

Note that the price (amount di-vided by quantity) is constant for each of the items. I need to shrink this list so that it contains just those prices, as it does in this figure:

Writing formulas to calculate prices is not a problem, but is there a quick and easy way to remove the duplicate items from the list?

- Ken Hsu

A Instead of assuming you must remove data from the original table, think of your table as a database, and use a Query Table to extract a list of unique items. Then build formulas to calculate the prices. Just follow the steps illustrated in Figure 1.

Convert formulas to values and number lists in Excel

Q My colleagues and I track work orders in Excel 5.0. I routinely number the work orders by entering one number in cell A2, for example, then copying the formula =A2+1 down the column (as in range A2..A6 of Figure 2).

Some of my co-workers, however, enter the order numbers by hand (as in range A8..A12 of Figure 2).

When all our workbooks are combined into one and sorted, the results are unpredictable (see range D2..D11 of Figure 2).

My formulas are sorted according to their current numeric values, but when they are interspersed with the manually entered numbers, their values change because of their relative cell references.

Is there any way to convert the formulas into regular numbers without doing it by hand?

- Rob Yale

A To convert the formulas in the combined worksheet to values, click the letter of the column that contains the order numbers, select Edit-Copy, then select Edit-Paste Special, click the Values button in the Paste Special dialogue box, and click OK.

Alternatively, you could include the macro shown below in your workbook to number the work orders automatically.

This approach lets you eliminate items from the list merely by deleting their rows, and the spreadsheet doesn't renumber the work orders.

To try the example, enter the label Order Number into cell A1 and a 1 into cell A2 to start off the list. Select cell A2, and issue Insert-Name-Define to assign the name OrderNumbers to cell A2.

Select Insert-Macro-Module, then enter the commands shown in Figure 3 (you can omit the comment lines, which begin with apostrophes).

Go back to the worksheet, click the button tool to create a macro button, select ItemNumber in the Assign Macro dialogue box, and then click OK.

With the button selected, highlight the label Button 1 and replace it with Item Number.

Click this button to number each item.

- Richard Scoville


These Web pages are produced by Australian PC World © 1996 IDG Communications