Results 1 to 7 of 7

Thread: Excel (2000)

  1. #1
    Star Lounger
    Join Date
    Oct 2001
    Location
    Ellettsville, Indiana, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Excel (2000)

    There are no easy answers, but they are well covered in <!post=this,81359>this<!/post> thread.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    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.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Excel (2000)

    Nice idea!
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    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>
    Beryl M


  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    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 ...
    Beryl M


Posting Permissions

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