Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-01-2014, 06:43 PM
julia99r julia99r is offline changing dpi affects excel pages Windows 7 32bit changing dpi affects excel pages Office 2003
Novice
changing dpi affects excel pages
 
Join Date: Jun 2014
Posts: 9
julia99r is on a distinguished road
Default changing dpi affects excel pages

I recently had cause to change the dpi scale setting on my computer. In doing so I found that the margins and page breaks on all my saved Excel spreadsheets have moved so that what I had meticulously entered to fit within pages no longer does. The page breaks have move down and the margins move in so that what had fitted into one page now extends into next pages. I have tried every setting I can possibly think of but nothing works. As I need to be able to email my documents, if the recipient doesn't have the same DPI setting as myself the sheets won't be as I had set them up. Can anyone suggest a solution I am desperate. Using Windows 7, Office 2003, Graphics AMD Radeon HD 6450.
Reply With Quote
  #2  
Old 06-02-2014, 09:23 AM
BobBridges's Avatar
BobBridges BobBridges is offline changing dpi affects excel pages Windows 7 64bit changing dpi affects excel pages Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 674
BobBridges will become famous soon enoughBobBridges will become famous soon enough
Default

I've never fooled with the DPI setting—I'm not sure I even realized Excel has one—but it makes sense to me that if you change that, then your worksheets (etc) won't fit the margins in the same way. The margins are measured in inches or centimeters, so once you tell Excel to use more or fewer dots per inch, naturally the printed documents will be smaller or larger, respectively, and no longer fit the same into the printed margins.

If what you're asking for is some solution that will cause your documents to print at the same size at each user's station no matter what DPI setting those users have, it isn't obvious to me that there can or should be one. That's what DPI is.

Hm; is the DPI setting by workbook? If so, at least it'll get to your users set correctly, and if they change the DPI setting on your workbook after they've received it, they have only themselves to blame for the results. But if there's a single DPI setting covering the whole Excel application, it isn't clear to me what you can do. Maybe choose the lowest DPI setting that you think is likely to be on any other workstation—the fewest dots/inch likely, ie the largest dots—then set the margins etc to fit that. On other machines your pages will appear smaller, then, but they won't lap over onto other pages. But it's kludgy.

Why would you think other users would fool with DPI in Excel anyway?

...I wrote all the above, then reread your post and decided you probably aren't talking about a DPI setting in Excel. On your screen, maybe? That wouldn't change what fits in printed Excel margins, though. On your printer? Where?
Reply With Quote
  #3  
Old 06-02-2014, 03:37 PM
julia99r julia99r is offline changing dpi affects excel pages Windows 7 32bit changing dpi affects excel pages Office 2003
Novice
changing dpi affects excel pages
 
Join Date: Jun 2014
Posts: 9
julia99r is on a distinguished road
Default

thanks for your reply. The DPI setting I am referring to is for my screen which is located in the Control panel - Personalisation - Display - DPI settings. "Make it easier to see what's on your screen". And YES it does change what fits in printed Excel margins. I only found the problem out by accident when I changed monitors and needed to increase the appearance on screen because everything was too small. Immediately when I opened one of my excel worksheets I found that all the margins and pagebreaks had moved. Adjusting the "zoom" level in excel when viewing the worksheet has no affect. So as a test I changed the "Screen" DPI (in control panel) to the same % as they had been created in on the other monitor. And they all reverted back correctly with the margins and page breaks in the correct places. It doesn't matter what monitor I use the same thing happens.
This is a big problem for me because I have spent years gathering data and setting it out meticulously in these excel worksheets (not using a template) so that it fits precisely within pages. I need to be able to email the sheets and also save them to disc for others to view- but need them to be view and able to be printed as I had set them up. Now after finding this problem they are not going to be able to unless they have their computer setting exactly the same as mine. (The monitor is set to its default resolution which is the best view).
Reply With Quote
  #4  
Old 06-03-2014, 04:45 PM
whatsup whatsup is offline changing dpi affects excel pages Windows 7 64bit changing dpi affects excel pages Office 2010 32bit
Competent Performer
 
Join Date: May 2014
Posts: 137
whatsup will become famous soon enough
Default

Hi

In my opinion it's one of the biggest mistakes MS ever brought up with the OS. To make things worse, user is encouraged to adjust the screen to his/her convenience without advise which problems this will cause.

The original setting in windows is 96 ppi and ever was. The new feature to temper with the dpi-settings brings up a hell lot of problems in excel, page setups don't fit anymore, neither printareas, ... and this continous - even vba is affected (i.e. UserForm's manually startup-positions).

There is hardly a solution, whereas in vba you can code workarounds, the excel-features you talk about aren't ajustable. Therefore do yourself a favour and adjust the dpi to 100% which equals the 96 ppi.
Reply With Quote
  #5  
Old 06-04-2014, 03:57 AM
julia99r julia99r is offline changing dpi affects excel pages Windows 7 32bit changing dpi affects excel pages Office 2003
Novice
changing dpi affects excel pages
 
Join Date: Jun 2014
Posts: 9
julia99r is on a distinguished road
Default

ahhh, thank you so much for your reply !!! FINALLY, someone else understands & is aware of the problem I'm having. With all the forums I have put my problem to they either tell me I'm talking rubbish because changing DPI can't affect excel (even when I provide the hard evidence).... or they have no idea and say it must be a problem with my computer.
Just one thing though, I did change my DPI setting to 100% this morning, created a spread sheet similar to the others I had done and emailed it to my daughter. (Her DPI is set to 100% also). When I got to her place I opened the email from her computer and the margins and page breaks were all in the wrong places. So it seems even having DPI set to 100% doesn't alleviate the problem...anyway, i'll do as you suggest and keep it there, seems a sensible thing to do.
YES, I agree this a huge blunder on MS behalf when they DO encourage screen adjustments without any warnings.... crazy!!!!
So seems my years of work have mostly been a waste. I'll have to look around for a similar program to redo it all. Thank you again so much for your reply, now I can stop searching around & adjusting other settings.
Reply With Quote
  #6  
Old 06-04-2014, 11:11 AM
whatsup whatsup is offline changing dpi affects excel pages Windows 7 64bit changing dpi affects excel pages Office 2010 32bit
Competent Performer
 
Join Date: May 2014
Posts: 137
whatsup will become famous soon enough
Default

Well, the crucial adjustment is usually done during setup of the OS, when user is asked to adjust screen to his eyes. In doing so the OS will modify the ppi-setting, without mentioning it.
Later on user may wonder why things don't work as intended but won't connect the misbehaviour to the setup of OS. Instead will believe it's just a bug the software like excel comes with (though other software often doesn't like the modified ppi-setting as well). Therefore it's left like this, on top depending on the offset user maybe won't even regognize it. But in excel where the printarea only can be divided by entire cells, some millimeter can make a big difference. Therefore I guess most people don't know about and don't want to believe it if told so.

Anyway, concerning your new problem I must tell that I'm not an expert in doing printouts. So maybe others will share more advanced experience. For my part:
If you manually were setting pagebreaks and margins, I believe they should be shown on other computers the same way (maybe one printout is required to get excel to save the settings). In case you left the pagebreaks (and margins) to excel, there are a couple of other aspects to consider:
- Versions of excel (starting with version 2007 the compatibility of versions isn't anymore that granted as in former versions - though MS still tries to make people believe that)
- Printout and all related settings depend as well on the printer in use. Especially you must have a look at the settings about what kind of paper is choosen (e.g. Letter, A4, ... ).
- Have the computers installed the same OS? Especially if you mix Mac and Windows, they might not like each other (that's why Mac is using another ppi-setting by default - if I'm not mistaken)
- As ever, don't expect to much of excel when design is involved. If you adjusted printoutarea meticulously to the millimeter, I can imagine that on another computer the area easily can miss a column or row
Reply With Quote
  #7  
Old 06-04-2014, 04:09 PM
julia99r julia99r is offline changing dpi affects excel pages Windows 7 32bit changing dpi affects excel pages Office 2003
Novice
changing dpi affects excel pages
 
Join Date: Jun 2014
Posts: 9
julia99r is on a distinguished road
Default

Thanks again for all that detailed information.... It gives me a clearer understanding. I've played around with computer, Excel & Printer settings for long enough now to try to alleviate the issue (all in vein it seems)... so time to move on and find an alternative way around it by way of another program .....cheers !!
Reply With Quote
  #8  
Old 06-05-2014, 07:24 AM
whatsup whatsup is offline changing dpi affects excel pages Windows 7 64bit changing dpi affects excel pages Office 2010 32bit
Competent Performer
 
Join Date: May 2014
Posts: 137
whatsup will become famous soon enough
Default

What program have you got in mind?
If you want to upload a sample you're welcome, I would be interested to have a look at it.
Reply With Quote
  #9  
Old 06-05-2014, 08:14 AM
julia99r julia99r is offline changing dpi affects excel pages Windows 7 32bit changing dpi affects excel pages Office 2003
Novice
changing dpi affects excel pages
 
Join Date: Jun 2014
Posts: 9
julia99r is on a distinguished road
Default

I'm not sure what program yet, will have a look around. Included is a sample of a 5 page spread sheet for you to take a look at. I created it with a dpi setting of 125%.
Just one other thing. I've now got my DPI setting on 100% and have adjustment most text, icons etc so I can read them, because everything was so tiny. In Microsoft outlook I've made most of text bigger but when I open an email the print is soooo small. I want a bigger text size for all but so far I have only found I can do it one by one....To clarify, I only want to change the screen display, not the size of the font when sending/replying emails. Also in "Start" (at bottom left of screen) I've made the text bigger but the text in the drop down lists on the right are so tiny (eg favourites)..... Plus all pop up windows are extra small.
I can keep playing around with it but just thought you may know to save me the frustration...
Attached Files
File Type: xls sample 120dpi spreadsheet 5 pages.xls (54.0 KB, 12 views)
Reply With Quote
  #10  
Old 06-05-2014, 12:47 PM
whatsup whatsup is offline changing dpi affects excel pages Windows 7 64bit changing dpi affects excel pages Office 2010 32bit
Competent Performer
 
Join Date: May 2014
Posts: 137
whatsup will become famous soon enough
Default

Julia, are you sure the resolution of your monitor is set right? Depending the type of monitor you can change the resolution to a minor setting (via control panel --> Adjust screen resolution), which results in bigger textsize.

If you want to increase temporarly the size of text you can use the magnifier tool. Ask windows help if you don't know where to look for it.

... no other idea on this

I had a look at your file:
I guess you do adjust your pages to the pagebreaks excel is offering. This suggestions are merely done by the type of paper the printer is set to in order to observe the margins you set.
Now depending the computer I'm using here, the printout will vary:
Windows XP, Excel 2002 won't print your Pg.3 and 4 on one page
Windows 7 (dpi 100%, 96 ppi), Excel 2010 only won't print Pg. 4 on one paper, the others are fine
... but well that was using format A4 in the printer. What type of paper you are using?

Maybe you can try to setup printarea manually. Save a copy of your file before following this steps:
Have a look at
Menue ---> Insert ---> Names ---> define
In the dialog you will find a name (like) "printarea" refering to a range (probably):
=Sheets1!$B$1:$G$220
Mark this formula and replace it by:
=Sheet1!$B$1:$G$50,Sheet1!$B$52:$G$93,Sheet1!$B$95 :$G$138,Sheet1!$B$140:$G$179,Sheet1!$B$181:$G$218
Afterwards you set the margins of right and bottom to 0 (zero).

Have a look at the print preview to check if anything has changed. Still, only a printout will proove if the printer can handle it the way preview it shows.

I know that's quite a job, and probably only the start if you intend to send the whole file to other people. But unless you don't change the outlay I can't think of another way to get it right.

Edit:
Wrong formula, of course you have to replace it by:
=Sheet1!$B$1:$G$50,Sheet1!$B$53:$G$93,Sheet1!$B$96 :$G$138,Sheet1!$B$141:$G$179,Sheet1!$B$182:$G$218

Last edited by whatsup; 06-05-2014 at 04:36 PM.
Reply With Quote
  #11  
Old 06-05-2014, 06:33 PM
julia99r julia99r is offline changing dpi affects excel pages Windows 7 32bit changing dpi affects excel pages Office 2003
Novice
changing dpi affects excel pages
 
Join Date: Jun 2014
Posts: 9
julia99r is on a distinguished road
Default

Just regarding the resolution of my monitor, you asked if I have it right.
I currently have it set to 1280x1024 which is the default setting for my Benq FP731 17" monitor. In my resolution settings the other increments choices are
1280x960, 1280x800, 1280x768, 1280x720, (these make the display too narrow in depth)
1152x864 & 800x600 (but both bring up a message "out of range")
With the default resolution I am currently using of 1280x1024 everything is so tiny I have trouble seeing it. But the next increment without reducing the depth of the screen display is 1152x864 but that says its "out of range" is there any way I can have the resolution so it makes display just a little bigger than the recommended setting of 1280x1024 ?
I also have Catalyst control centre where there is an option to change the monitor resolution (I have it turned off at the moment - see attached picture). I did try changing resolution through Catalyst before but it distorted the screen display.
I want to now keep my DPI on 100% as you suggested but I am really having trouble seeing my monitors display.
Attached Images
File Type: jpg catalyst control centre.jpg (69.0 KB, 14 views)
Reply With Quote
  #12  
Old 06-06-2014, 09:41 AM
whatsup whatsup is offline changing dpi affects excel pages Windows 7 64bit changing dpi affects excel pages Office 2010 32bit
Competent Performer
 
Join Date: May 2014
Posts: 137
whatsup will become famous soon enough
Default

If set to the default setting then it's allright. If changing it slightly doesn't improve the view then it's not worth to do so, and you better leave it on the recommended setting.
That Catalyst thing I don't know, but if you tried it without positive results leave it alone. Based on my experience quite often it's not worthwhile to use other tools, often they cause problems on other ends.
In my opinion you are left alone with the options to wear glasses, using the magnifier-tool, tempering with the windows ppi adjustment or - not reading your screen

Using other software is a tiny option. If the software is reading the settings of OS rather then just assuming the OS is using 96 ppi you will remain with the same problems as you are encountering now. Furthermore if you don't set printareas manually but leave it to the program, the user you send the file to will end up with the problem.

An option to further use excel might be to set up printarea manually as I described yesterday. But I experienced that excel 2002 doesn't allow it the way I offered. Still it allows to setup the printarea by using vba. Are you familiar with macros, or at least in using them?

But first try the way as described, maybe excel 2003 will accept. Afterwards try the attached file and have a look with preview if the set printareas are ok - there should be shown 5 pages only. (Don't worry, the file contains a macro, you can either permit or deny it, it doesn't affect the settings)

Still: What type of paper you've got in use?
Attached Files
File Type: xls 120dpi-Julia99r-modified.xls (191.0 KB, 14 views)
Reply With Quote
  #13  
Old 06-06-2014, 03:53 PM
julia99r julia99r is offline changing dpi affects excel pages Windows 7 32bit changing dpi affects excel pages Office 2003
Novice
changing dpi affects excel pages
 
Join Date: Jun 2014
Posts: 9
julia99r is on a distinguished road
Default

I have my paper setting on A4. I don't change any other setting to do with margins page breaks or printer. I move the columns & row widths according to my data input requirement, I don't manually set pagebreaks or margin positions. I have tried doing so but when viewed using another DPI setting a blank page will appear between where I set the pagebreak and where excel has it by default.
I tried your suggestion several times of setting the page area (your corrected one) but received a formula error.
I can select a print area which will take care of the width, but as the sheet is multi-paged the pages will still overflow in depth onto the next rather than the way I created them.
I tried opening your attachment but it says its a .php file and to select which program I want to open it with. I selected Excel but it won't open. ( I did try to open the first file I sent you on here but got the same message come up).
I have not used macros before.
I have attached an Excel spreadsheet which I created with a DPI setting of 100%. Its 5 pages long with margins and pagebreaks displaying & printing correctly on my computer. Would you mind taking a look at it and letting me know if it displays as it should (margins & pagebreaks). If so, i'll just continue with 100% dpi and presume its correct for my other files. I will need to adjust all my other files to suit the new dpi setting but I guess most people have their setting on 100% rather than 125% so will just go with that.
Attached Files
File Type: xls XX sending attachment to check.xls (16.0 KB, 9 views)
Reply With Quote
  #14  
Old 06-06-2014, 04:11 PM
whatsup whatsup is offline changing dpi affects excel pages Windows 7 64bit changing dpi affects excel pages Office 2010 32bit
Competent Performer
 
Join Date: May 2014
Posts: 137
whatsup will become famous soon enough
Default

Julia, I guess everybody on the board has got that problem with the file downloads.
To download files from the forum choose "save as" and simply change the shown name to
.xls (or whatever is indicated on the link).
Then open the file via Windows Explorer.
Reply With Quote
  #15  
Old 06-06-2014, 04:33 PM
whatsup whatsup is offline changing dpi affects excel pages Windows 7 64bit changing dpi affects excel pages Office 2010 32bit
Competent Performer
 
Join Date: May 2014
Posts: 137
whatsup will become famous soon enough
Default

The preview of your last file shows the pages correctly on my computer, that's because I'm using the same ppi-setting.
But if you change your settings again to 125% and look at the preview you probably will see the pages got mixed up again.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Print selected pages as per excel value DingDang Word 0 03-17-2014 02:14 AM
changing dpi affects excel pages Reading Pane affects Outlook 2003 bhaza Outlook 2 01-12-2011 03:01 PM
Word 2003 affects on Browser viewing Xandr Word 0 01-09-2010 11:24 AM
Bulletizing one paragraph affects all paragraphs GoodWebby Word 0 10-10-2009 12:09 PM
How Do I Create Excel Web Pages With Java highbids Office 0 04-13-2006 06:53 PM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 07:20 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft