Excel and sorting

[carolynw]

I have a user with an Excel spreadsheet that has us both confused (easily done for me!). Someone sent it to him and when he tried to print it (it is a form for inputting data) - he got 222 pages, instead of one. He sent it to me and when I viewed it in page break preview, all of what looked like columns off to the right of the main form, were additional pages - all 221 of them. I have tried various methods of deleting them, none of them worked. What happens when I delete some of them is that some of the columns in the actual form disappear. Since I didn't develop the form, and neither did my user, we are confused. If anyone would like to look at the file, I would be more than happy to send it - and as always, thank you, thank you, thank you for your assistance!

 

[Rockn]

The solid lines of the page break preview are the printable area and the dotted ones are there the breaks are. It's pretty easy to fix, just place the pointer over the line....click and hold the left button down and drag the line to where you want it. Sounds like no one checked the page breaks before they sent it to you.

 

[carolynw]

Rockn,

Thank you for your speedy response - unfortunately I had already tried that and it didn't work. But just to be on the cautious side, I tried it again and it still didn't work - when I looked at the preview, it still showed the same number of pages, even after I moved the lines and should have eliminated at least 10 pages. It just isn't playing by the rules!

 

[carolynw]

Okay, I'm trying this again and if I start at the far right side and drag the line, I am eliminating the columns. I tried this the other day and it worked to a certain page number, then it messed up the form. But I will try it again and see what happens this time. Is there any other way than one page at a time. This is 222 pages and it is a slow process, one page at a time. If that is the only option, then you will find me at the spreadsheet if you need me! Thanks again!

 

[Gram123]

Go to the first cell containing data and drag to select all cells containing data.
When they're selected go to File, Print Area, Set Print Area.
You will then see a dashed line surrounding the data.

Go to Print Preview before you print and make sure the page count is more sensible!

HTH
Gram

 

[Anne Troy]

Carolyn:

send to [email protected]

I'll clean it up and tell you how I did it. I do this all the time.

 

[Max19]

Dreamboat, it might be more helpful for everyone at TSG if you would describe how to fix a problem rather than collect documents, format them, and send them back to the user. This is a forum for sharing ideas and information. It's not a personal business recruitment center.

 

[Anne Troy]

Actually, Max, it's a whole lot faster for the person with the problem if I can fix it.

You might also want to read the last statement in Carolyn's original post. This indicates to me that's she anxious for a fix.

 

[jbcalg]

this is fairly common in excel and is a good way to practice your doc formating - 2 simple steps

1. gram's way works - you've set your print area - it will use default settings - look at page preview

2. next step - once your print area is set, then format/adjust options

go to:
file
page set up

on page tab: scaling - 2 options
see what % is set, adjust
OR
adjust the 'fit to' #pages x #pages

make sure one or the other of those options are ticked off, then press ok

then play around till it makes sense based on the intent of the form - ie what is it supposed to look like, how many pages is it supposed to be with 222 columns

...and please don't send it to me LOL

 

[Anne Troy]

Here's what I emailed back to carolyn after fixing the file as a list of what was "wrong" with it:

Printing was set up to be centered vertically. Not good with a document that has headings and columns. (File-Page Setup-Margins tab)
Printing was set up to repeat the first 42 rows at the top on every page. (File-Page Setup-Sheet tab)
Printing was set up to repeat the first 10 columns at the right on every page.
Top and bottom margins were smaller than header and footer margins (the header text needs enough room to fit between the measurement of the header margin and the top margin--likewise the footer text between the bottom margin and footer margin); this causes header and footer to print "on top" (not above or below) the spreadsheet text. To calculate the space, subtract the header margin from the top margin, or subtract the footer margin from the bottom margin.
The page setup needed to be "fit to 1 by..." If you're not sure how long your doc will be, you generally set the width to fit to 1, and you DELETE the value in the next box, don't leave a 1 in there.

 

[carolynw]

Dreamboat et al,

Thank you for your help - I am grateful and will be working with my user to clear up these issues that you uncovered. As always, I am grateful for all your help - have a Happy Thanksgiving everyone!

Carolyn