Centering cells on printed page

I work for a small business that uses Excel 2010 for our monthly work schedule. It's laid out like a typical monthly calendar, in landscape mode, with perhaps half an inch of blank space to the sides. (The margins are actually set to zero.) I'm not the one who created this spreadsheet; I inherited it from the previous manager, who in turn got it from her own predecessor.

I'm a bit of a perfectionist and it bugs me that I cannot get the darn thing to center properly when I print it. I also need that blank space on the left-hand side to hand write notes to individual staff. I've gone into the "Margins" menu and checked the boxes to center both vertically and horizontally, and it centers vertically just fine, but it still starts at the far left edge of the page. I have noticed that when unchecking the boxes to center the cells, the print preview scoots the tiniest bit further up and left. The preview and the printed version are exactly the same whether or not I set a print area.

I don't believe the problem is a simple user error because I'm using a separate spreadsheet, one which I did create myself from scratch, and it centers on the page just fine. I'm really not willing or talented enough to create an entire new schedule from scratch, and besides that, it has the entire year already filled in, including past months.

Thanks for any help you can give.

Hi,
can you post an example of the worksheet?
go to advanced/attachments etc

Where can I upload .xlsx files for sharing? I made a generic copy of the calendar I'm having trouble with, but I can't find anywhere that will allow me to upload and share it. (I miss the days of easy access to Rapidshare, MegaUpload, and 4shared...)

When you reply to my my post, on the same place where you replied you will see a button "go Advanced", click it and your post will have more options. one of the options has a picture of a 'paperclip', click it and hit "add file". then click "select file", there you can browse and select the desired workbook. Next, you click "upload" this will upload the document into your post. You also have an option to "insert inline' which will make your post visible directly on your posted reply to me.

Thanks so much, I didn't even notice that! (It's been awhile since I've posted on a forum.) One more question, though. How do you change or remove the password from a protected worksheet? I tried following the instructions on the Microsoft website, both whether I removed or changed, I still had to type in the same password to open the calendar. (I don't even know why it's password'd. It's not like we have confidential information in it. It's just people's initials...)

Sorry for being such a pain. I appreciate your help and patience.

You are not a pain! I am glad to help.
Question, is it the sheet that is protected which prompts you to enter a passcode every time you want to do some changes to that specific sheet or is it the actual workbook that need a passcode in order to use it?

Thanks. I'm glad there are still polite, considerate people on the Internet! Sadly, most forums these days are quite the opposite.

The actual workbook requires a password when I first open it. None of the individual cells are protected; once it's open, I can change, delete, or save as I please. All of the spreadsheets that were on the computer when I started have the same password and the previous woman who worked here told me that Excel required a password every time it was opened, but the new workbook I created the other day and the one someone emailed me a few weeks ago do not request a password to open or edit. I'd like to just do away with the passwords altogether. There's nothing in any of these workbooks that's the least bit private, and anyway, on one side of my computer is the printed copy of the calendar I'm working on and on the other side is a sticky note with the Excel password on it.

I suppose I could just start from scratch and make a whole new calendar, but like I said, there are about three years' worth of schedules in this file, and I just don't have that kind of time. (I work in direct care and my time is better spent with the residents than retyping something that's already been done.) Plus, it doesn't solve the basic problem of not knowing what I'm doing. Excel is my weak point when it comes to office-type computer functions (I actually learned data input on Microsoft Works back in early high school, and I've probably used Excel less than half a dozen times since I graduated 15 years ago), but it's something I need to learn. I was hired partly because I know more about computers than my boss. Now that our state has finally entered the Information Age, they're doing everything from budgeting to mileage in Excel and emailing it to my boss, who then calls across the room for me to come and help her figure it out. It's slowly coming back to me--I even remembered on my own how to do basic functions like sums and made a small budget from scratch--but this is something I haven't learned in the first place. So thanks again for all your help.

Wow! I hear!
Excel is fascinating and VBA is a whole different world. You can do just about anything with it. You definitely do not have to start from scratch
To solve, open your main workbook and hit "save as", right next to the save button you will see "tools", select the drop down and hit "general options", on the "password to open" clear the passcode and press "ok". Then save your workbook (you can either save as a different name or overwrite the original workbook). the next time you open the workbook you should not be prompted to enter a passcode.
Hope this helps

That worked, thanks! You're right, it's fascinating and I'm kind of a nerd anyway. I like for everything to be well-organized, neat, and pretty. I've revamped most of our paperwork and put a lot of things into the computer already that people were doing by hand. Some things are best done the old-fashioned way, but for the simple, routine, repetitious things I do on a regular basis, it's sooo much faster and easier to let the computer do the heavy lifting (and it makes fewer mistakes than I do, to boot!).

Anyway, here's a sample of the calendar I'm having trouble with. It's just the current month's worksheet to save on file size. I've set the custom margins to center the cells horizontally and vertically and I've set the print area to be just the cells I want printed (in case it's somehow printing invisible cells on the right-hand side of the page) and it seems to center vertically just fine, but horizontally it just will not move away from that left-hand edge.

Test.xlsx

Hi.
Sorry for the late response.
I opened your workbook on my computer and the print areas are just fine. Everything is exactly horizontal and vertical. What version excel are you using? Not sure if that should make a difference but i did have a similar experience which actually cost time and money; part of my work is sending sales orders to my warehouse manager and i found that when i set the print area end at the bottom of my page my warehouse manager would complain that an extra page gets printed out each time. With the enormous amount of sales orders being sent it obviously added up all the extra unused papers. The solution was to end the page break two or three cells before the end of the page.
My take on this matter would be to start a new sheet on your workbook and copy the calender (not as values) onto the newly added sheet by selecting the area of the calender and then try and set the margins again. If i do it on my end and send it to you it might not be evenly centered as opposed to you doing it on your excel version.
I hope this helps, if it doesn't please don't hesitate to reach out to me again.
Regards.

If you are having trouble creating a new sheet or selecting to paste let me know

That's okay. I was away from my computer until this morning myself. Anyway, I'm using Excel 2010. I finally gave in and copied and pasted the May calendar into a new file and although the print preview was still off-center, when I actually printed it out, it centered perfectly, so I guess I'll just have to suck it up and redo the whole thing. I'm still curious why the original won't print correctly (and neither did the test copy I uploaded here), but I figured out how to copy and paste with the source formatting intact so I don't have to do TOO much work to format the new sheets. I do wish I could figure out what was wrong with the original workbook, but at least I'm learning how the program works, and that was my goal. Plus, if I start from scratch, maybe I can figure out how to do something else that's been bugging me. I have to hand write the name of each house on the edges of the page (which is part of the reason I wanted the calendar to center properly; there was barely enough room to squeeze in one letter on the one I just did). I tried adding a tiny column to the original calendar (I literally only need room for 2 characters) and it threw the whole thing off.

Thanks again for all your help, I really appreciate it!

You're welcome.
But not so fast....i didn't mean that you should redo the whole workbook, what i meant was to add a sheet to your existing workbook and and then copy the calendar into it and then delete the old sheet. My hunch is that there is something wrong with the formatting on the sheet as opposed to the entire workbook.
To add a sheet on your workbook just click on the right-most tab on the bottom which resembles a sheet with a yellow star on the upper left side. Then select the entire area of you calender, hit copy, then go to the newly added sheet and paste with all the formatting.
I did the work for you on this attachment Test 2.xlsx but I'm not so sure it will solve your problem as the formatting might be slightly different on your excel version.
I'm happy to help so let me know if this solved your issue.

P.S. if you are satisfied with my response please click on the * which you will find to the left of this post underneath my name.
Thanks!

Thanks for the rep!

If i understand you correctly you are looking for a formula to display the current year, try "=YEAR(TODAY())" in your cell, this formula will display the current year and will update as the years progress. if you want current date just add "=TODAY()".
A side note, take a look at this calender 2015-Monthly-Calendar.xlsx i found online, might help you with the print areas etc. i understand it's not customized to you spreadsheet but maybe you can get some ideas from it.

Best regards.

One more thing, if you feel this thread has been solved then just go up on top where you will see a grey bar, hit "thread tools" and select "this thread has been solved".

That's really cool! It didn't even occur to me that you could do that.

One more question and then I think I'm done. I copied the old April calendar into my new workbook and it was back up in the top left corner again. I deleted absolutely everything except the outline of the calendar and printed it out. It was centered correctly, so I added back in all of the text. Still correct. I added in some clipart I'd used to denote specific dates (Easter, a dance our residents are attending, etc.). Still fine. I added in the text boxes and clipart around the top of the calendar and now it's off again. What gives? I have the text boxes and clipart within the borders of the top cell.

Oh, one more thing. Is it possible to either split a cell or insert a two-cell table into one Excel cell? One of our houses has two staff and I'm wanting to split that cell so that one set of initials is aligned to the left side of the cell and the other set is aligned to the right. Right now I'm using the spacebar to spread them as far apart as possible, but since I'm not using a fixed-width font, it doesn't always line up perfectly.

Hi,
I searched the net on your questions and here is what i came out with:
Impossible to split a cell in half, you can split the content but not the cell. And in regards to tables - tables are made completely out of cells, a cell cannot be made out of a table (was a good idea though). Possible solution: maybe merge two columns together so that when you look at the cells you will see one column of data merged into two columns and the cell you want split you can just unmerge that specific cell and now it will be as if you have two cells in one cell so-to-speak.
As for the issue with inserting textboxes and clipart i must say this really beats me. The only possible, farfetched, theory i can think of is that under the picture properties there are three option: "move and size with cells, move and don't size with cells, don't move or size with cells", maybe play around with the properties to see if any of them would have an effect on your margin.

That's brilliant! It would totally work. I can't believe I didn't think of that myself. The obvious answer always eludes me.

I checked the clipart and textboxes and all of them say "move and don't size with cells" so I'll play around with the settings (maybe next month when I print out the May schedule since my boss commented on the amount of paper and ink I've been wasting on this project). As much as I didn't want to start the whole thing from scratch, it might be a good idea after all, just so I can learn how to do it. I like to customize things, and make them pretty. The staff have already commented on how much better our forms, reports, and other printed materials look since I've been in the office.

Thanks again, I really appreciate all your help. I'll go ahead and mark the thread as solved, I think I've got all my questions answered now.

You're very welcome

I went ahead and printed out a test copy after all. It came out perfectly! The two-column idea came out exactly the way I've been wanting it to. I didn't put the clipart back in yet, but I can live without that if I have to. I'll play around with that next month.