Excel page break not working

[Spydah]

I've created page breaks in Excel spreadsheets many times, but all of a sudden the feature isn't working in Excel 365.  Any suggestions?  I go to Page Break Preview, where everything is on the one page.  I then position the cursor about halfway down the sheet in Column A, go the Page Layout and click on Insert Page Break.  Absolutely nothing happens.  Even if I select the Remove Page Breaks option beforehand it still doesn't work.

Is there a bug in Excel 365?  How can I overcome it?

[#2 · Accepted solution]

@Spydah

On the Page Layout tab of the ribbon, click the little arrow in the lower right corner of the Page Setup group.

In the Page tab of the Page Setup dialog, check whether the sheet has been set up to fit into 1 page wide by 1 page tall.

In the Sheet tab of this dialog, check whether a print area has been specified.

[#3]

@HansVogelaar 

Many thanks.  That did solve it.  I didn't realise there was a setting, accessible only via the ribbon, which specified 1 page wide by 1 page tall and that it overrides the equivalent option within the print settings.

[#4]

Thanks for asking the question! I had never come across this situation before and it was driving me nuts!

[#5]

I need a little more information as I still cannot make it work. I have a sheet with A-L columns and 65 rows. Print area is set to that. I want to see rows 1-38 on one page and 39-65 on a 2nd page. If left unconstrained, the sheet takes 9 pages to print. If I set print to fit 1 page wide by 2 pages tall, the page break occurs at row 52, no matter what I do. This happens when I follow your guide. Do you know what I might be doing wrong?

[#6]

@Stuart_Miller 

You'll have to set Height to Automatic instead of 2 pages. The manually inserted page break below row 38 should then work.

[#7]

@HansVogelaar 

Thank you, Hans.  That did the trick.

[#8]

@HansVogelaar It looks like you’ve been very helpful, not just to me but to others with similar problems.  Surely that’s a fault with Excel if the obvious page break settings can be in conflict with, and be overruled by, some hidden settings?

[#9]

@Spydah Perhaps Excel should display a warning if you try to specify conflicting settings, but I don't think it's a bug. The default setting for Width and Height is automatic. It requires manual intervention to change them to (for example) 1 page wide by 2 pages tall. By doing so, one effectively tells Excel to override other pagination settings...

[#10]

@HansVogelaar Under Page Setup/Page/Scaling, there isn't an option for 'Automatic' as it just gives me a dropdown box to insert figures for both 'width' and 'tall'.

This may be because I'm using the Mac version but am having exactly the same issue as Stuart.

Any ideas?

[#11]

@RCully I suspect that you are looking under File, Page Setup. There you will indeed not find the "Automatic" option. But, 100% sizing is the same as automatic.

But, look on the Page Layout ribbon. There you should see the Automatic options for Height and Width in the Scale to Fit group.

If not, please state your Excel version. Can't really remember if this is something that existed in older versions of Excel for Mac.

[#12]

@Riny_van_Eekelen Thanks for your perseverance!

On the Page Layout ribbon, automatic is definitely shown for both instances - but the problem persists.  However, after going to Page Setup/Page/Scaling and reducing (after hit and miss) to 45%, the page breaks where I want it to break whilst keeping just one page wide.  With this Scaling at 100%, and Automatic showing, it is back to trying to print 8 pages (instead of one wide and two high).

I'm on Version 16.64 Excel which is part of the Microsoft suite for Mac.

As someone else said, this should be made easier.......  Roy

[#13]

@RCully But your initial post was that you didn't have the "Automatic" setting.  Now you mention that Excel doesn't automatically print 1 high by 2 wide. Well, isn't that obvious?

Played around a little with page setup and it seems that "Automatic" scaling sets the pages to max 640 pixels wide and max 960 pixels high. Leaving it to Automatic will, in your case, print 8 by 8. If that's not what you want you need to override the Automatic setting and explicitly tell Excel to print 1 wide by 2 high. And when you have done that, you will notice that the scaling percentage will have automatically been set to whatever scaling achieves that. No need to do that by trial and error.

[#14]

@Riny_van_Eeklen Yes, my initial post did say I didn't have the auto setting - but that was because I wasn't looking in the correct place. From your previous post, I now know where this is.
I have gone back and set auto on and 1 wide by 2 tall and it reverts to the original query (not mine) whereby it prints as requested (1x2) but it doesn't take notice of the page break so it breaks where it wants to (after the page break) as opposed to where I want it to (at the page break).
Someone else suggested that a warning should be shown that altering the width x tall figures which I now tend to agree with as it would have stopped having to create a post with this question.
Thanks again and I have now set it back to auto and 45% scaling. It works as I want it to so all's well with the world (for this at least)

[#15]

@Riny_van_Eekelen due to me not knowing how to reply to you tag name, I don't think you read my previous response.  But thanks for your perseverance anyway.

[#16]

@RCully I did see your previous post but didn't see it as a question. Therefore, I did not respond. In case you want to send me a direct message, right-click on the circle with my picture in it and select Message.

[#17]

@HansVogelaar This doesn't work. It's a simple spreadsheet - a list, and I've tried to do what I normally do for any spreadsheet that needs a one-row header line and one page break. When I insert the page break, Excel thinks the text from the SECOND row of the spreadsheet is being repeated (along with the first row) and overwrites the text on the second page. I've tried all the 'solutions' and work arounds with no success. Microsoft needs to fix this. I'd attach a screen shot, but this forum won't allow it.

[#18]

@Linda_Mermaid 

If you wish, click on my user picture and send me a message. You should be able to attach a screenshot, or even better, a sample workbook to the message.

[#19]

Yes I agree. Some message to indicate the existing over-ride would be quite helpful.@HansVogelaar 

[#20]

@Spydah 

I have tried numerous things to drag and drop the page breaks. The arrows on the excel spreadsheets to drag and drop do not show. They were working. I reset, checked my settings, shut off and back on the computer, still do not function any suggestions?