1. ## Excel (2000)

I have an entire column of numbers (20000 rows). When I go to print I have way too many sheets because the data is in that one column. Is there a way to adjust the data into several columns across without having to cut and paste?

2. ## Re: Excel (2000)

There are no easy answers, but they are well covered in <!post=this,81359>this<!/post> thread.

3. ## Re: Excel (2000)

<P ID="edit" class=small>(Edited by macropod on 19-Sep-02 10:06. Simplified formula)</P>Hi Tanya,

The easiest way I can think of is to use a formula on another worksheet in the same workbook. If, for example, your data are in ColumnA on Sheet1, you could put the following formula in A1 on any other worksheet (say, Sheet2):
=OFFSET(Sheet1!\$A\$1,(MOD(ROW()-1,80))+(COLUMN()-1)*80,)
Copy this formula down to row 80, then the whole column across to column IP. The result will be a multi-column report 80 rows deep, with 20,000 lines of data.

If you can't fit 80 lines to a page on your report (or you think the print's too small), double the number of rows you're going to use (eg 60*2=120) and replace the '80' parameter in the formula with that. The copy the formula down to the doubled number (120), then the whole column across as far as you need. The reason for the doubling, is that once you go below 78 rows, you can't fit 20,000 records into Excel's 256 columns, so you need to increase the depth.

Cheers

PS: You might also want to tell Excel to 'Hide zero values' so that you don't get 0s printed for the empty cells on the last page.

Nice idea!

5. ## Re: Excel (2000)

Hi there - please tell me - how do you "tell Excel to hide zero values"? Is this only in XL2000 or is it available in XL97 too? I've been trying to work out how to do this in a 97 spreadsheet for ages ...

<img src=/S/sad.gif border=0 alt=sad width=15 height=15> if you say it's only XL2000 I'm going to cry! <img src=/S/weep.gif border=0 alt=weep width=21 height=16>

6. ## Re: Excel (2000)

Don't cry - it's available in Excel 97 too! Select Tools/Options..., View Tab. The lower half of the dialog window contains the Window Options. Uncheck the "Zero Values" check box, then click OK.
Note that this setting applies to the active worksheet window only, not to all windows.

7. ## Re: Excel (2000)

<img src=/S/smile.gif border=0 alt=smile width=15 height=15> Thank you! <img src=/S/joy.gif border=0 alt=joy width=23 height=23>

<img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15> Mind you, the spreadsheet I was looking at had blanks in existing cells, but put zeros in any new cells - do you think this could have been done using this checkboox? It doesn't seem to accept a change to selected cells only ...

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•