Excel Column formatting 2

[eamc]

Suppose I have an Excel 97 spreadsheet with some 3-4 narrow columns and 100-200 rows. Can I have Excel print 2 sets of 50-60 rows and 3-4 columns (side by side) on an 8 1/2 x 11 piece of paper? Or do I have to muscle the whole thing tediously into a word processing program?

Edwin

 

[DaleWatson123321]

Edwin,

Excel has longstanding limitations on its ability to print disjointed ranges - unlike Lotus 123. Microsoft simply REFUSES to address this problem, so in the meantime we just have to "grin and bear it", and come up with "workarounds" such as the following:

Off to the side of your first row of data, enter a simple formula - example =A101. Then copy it down and across to fill those cells opposite the range A1:C100. These formulas will then "pull" the data from those cells from rows 101 to row 200.

Then set your print settings to "Fit to 1 page".

If you happen to ALREADY have data to the right of the 3 columns of data, you can always place this set of formulas FAR off to the right, and then when you want to print the two sets of data, simply HIDE the columns in between.

This should be a reasonable solution. Please advise if there is anything you don't understand.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[eamc]

Thank you, Dale - what you suggest sounds great. I suppose I could also use a blank column to separate the two blocks. Thanks again!

 

[DaleWatson123321]

eamc,

I'm pleased that you feel my suggestion will work. Your idea of the blank column in between the two blocks of data sounds like a GOOD idea.

Depending on the extent to which you want to "format" the two separate columns of data, you might also consider using a format "line" around each block, and perhaps horizontal lines for each row. These additional suggestions are only intended as "food for thought".

Thanks for the feedback.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Guest_imported]

you could also try using the camera icon.

take a picture of the parts you want to move up to the side, then paste them where you want them to appear. Set your printrange so that it only includes the side by side pieces.

I tried it with some sample data and worked fine. If the other suggestion works for you fine, but something to play around with for possible future use.

 

[DaleWatson123321]

capriz,

Thanks for your contribution. It's probably a reasonable "workaround".

It continues to AMAZE me as to EXTENT to which Excel users will go to find WORKAROUNDS - for a HUGE LIMITATION in Excel - which Microsoft SHOULD NEVER HAVE HAD IN THE FIRST PLACE. They ONLY needed to look at LOTUS 123 - years ago - when they created a perfect example for Microsoft to follow.

The requirement for spreadsheet users to print disjointed ranges on ONE page is a fairly OBVIOUS requirement. A good analogy would be PowerPoint being designed such that it would NOT permit users to place any more than ONE object on a page.

For the record, in Lotus 123, users ONLY have to enter the disjointed ranges - one after the other, separated by a semi-colon.

Interestingly, one can ALSO do this in Excel. HOWEVER, Excel has a B_U_G - where it AUTOMATICALLY FORCES A PAGE-BREAK BETWEEN the Ranges. I don't see how ANYONE can NOT call this a " B_U_G ".

And the SOONER Microsoft starts "listening up" to its customer base, the BETTER. All Microsoft has to do, is simply REMOVE that one line of code which FORCES the page-break. This forced page-break simply does NOT make any sense, and should NEVER have been included in the first place.

Do you other Excel users actually understand how much easier spreadsheet life would be IF Microsoft were to FINALLY fix this longstanding BUG ? ? ?

Feedback and comments are invited...

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[eamc]

Replying to Dale: Actually I have 4 columns that I copy to the right of 4 for the first page. Since the main spreadsheet is ABCD, I just select those for sorting, and the FGHI always keep up. The one bothersome thing is that a cell that says =A1 shows a 0 if A1 is empty, no matter what I do. I do have some numerical 0 values, so I cannot change the view zero option for the entire sheet.

Reply to Capriz: In Excel 97 I don't think I have a camera
icon?

Regards to all

 

[Dreamboat]

eamc: When you don't want to see a zero, put something like this:

=if(a1=0,"",a1)

That means, in English: If a1 is equal to zero, don't show anything, otherwise, show me a1.

The camera, hmmm. I've never even heard of it, but found it.

Go to Tools-Customize. Click on the Commands tab. On the left-hand column, click on Tools. On the right-hand column, scroll down to the Camera and drag that icon up onto your toolbar.

http://www.VictimPhotos.com

http://www.PiersonTech.com

Brainbench MVP for Microsoft Word
techsupportgirl@home.com

 

[DaleWatson123321]

eamc,

Dreamboat's formula ... =if(a1=0,"",a1) ... will definitely work, and her suggestion to place the camera icon on your toolbar is also a good idea.

Let me add to her suggestion about placing icons on your toolbar...

One of the VERY BEST things ALL Excel users can do to DRAMATICALLY SPEED UP your work in Excel... is to:

1) Do as Dreamboat suggests ...place on your toolbar "icons" which you want to have EASY access to, AND...

2) Especially for the frequently used (commands associated with these) icons, CHANGE the "icons" to "Text Only (Always)" - so that you can then activate the command by using the "Alt" key and a Letter (generally known as a "keyboard shortcut&quot.

For example, when I want to change a ROW HEIGHT, all I do is... hit "ALT H" ...i.e. hold down the "Alt" key and hit "H". Another example... for COLUMN WIDTH, I use "Alt W".

To change an "icon" so that you can use a "keyboard shortcut" such as I just described, do this... While in "Tools - Customize", right-click on the icon, and one of the options will be "Text Only (Always)". Check this off.

Then go to the "Name" (3rd option from the top), and change the name to suit your needs. For example, you might want to shorten the name, so it fits better on your toolbar. Also, you will probably want to change the location of the "&" character. The character you place AFTER the "&" is the keyboard character which you will then be able to use with the "Alt" key. As I mentioned earlier, for changing Row HEIGHT, I use "Alt H", and I have converted my toolbar "icon" in this case to simply "H", and for Column Width, I use simply "W". This takes up far less space, and allows for placement of additional icons/commands on my toolbar.

GUARANTEED, once you get used to using these keyboard shortcuts, your speed in Excel will increase DRAMATICALLY, and you'll wonder why this VERY USEFUL and EFFICIENT practice (of using keyboard shortcuts for common commands) was not stressed in Microsoft Courses.

Thanks to Dreamboat, as always.

Regards ...Dale Watson dwatson@bsi.gov.mb.ca