Introduction
Issue you see: nobody changes the size of a table/chart within an Excel file but when running an update to these exports in PowerPoint/Word the exports change size.
If you want a short answer to this kind of issue then for all users in a team:
- Keep the zoom level in Excel/PowerPoint/Word at 100%
- Set the scaling of Windows to 100%
When using Excel, and especially the export feature, it is important to note that the size of cells/texts/charts in Excel changes depending on the computer. It depends on several factors, including two in particular:
- The zoom level in Excel
- The DPI scaling in Windows
Also note that even if you do so, exporting will never yield an exact same result, read this article to know more.
Impact in Excel
When we say that the size of a cell or a text depends on the zoom level or the DPI scaling, we are not just saying that their rendered size is different. We are saying that their actual size is different.
For instance, if we focus on the zoom level in Excel, one would expect a 99 pixels wide cell in 100% zoom level to appear twice as small in a 50% zoom level. Also, we could expect the actual width of the cell to remain 99 pixels for other purposes (like the size to use when copy-pasting for instance).
Unfortunately, this is not the case. In the 50% zoom level, the width of the rendered width of the cell will most likely be 44 pixels. Worse, Excel will think the cell has an actual width of 98 pixels!
100% Zoom
70% Zoom
Things are much worse when you start taking into account the variations in font size. Font sizes being typically much smaller integers than the width of a column in pixel, rounding can have very drastic effects on the aspect in Excel (more details in the "Impact on fonts' size" section).
Once again, we cannot stress enough how important to understand that there is not a single definitive way an Excel document will look on any machine. What you see on your screen will be sometimes slightly, sometimes glaringly different from what your co-workers will see. This is before even taking into consideration exporting to PowerPoint, or printing to physical paper, which are also lossy data conversion. We are only talking about Excel here.
As there is no single way an Excel workbook look, we can accept that we can only try our best to limit the difference between machines.
Impact on exports in PowerPoint/Word
When updating a chart in PowerPoint, the height can change slightly, and when exporting with the 'Resize Chart Height' option enabled, the resulting image might not exactly match the shape.
This happens simply because the size of an Excel chart is linked to the zoom setting in the workbook view. In the example below, you can see how the scaling setting (and ultimate chart size) in Excel varies when moving the Excel workbook zoom from 100% to 80%.
Impact of font setting
When you change the zoom level Excel tries to render the size of the cells and text accordingly. In order to keep the appearance of the cell and the text crisp, it rounds to an integer value.
Since the font size is typically quite small, there is a big risk of deviation when rounding. Thus, the text might look too small or too big for its cell, which will have a blank space at the end of it, or overlap on/under the next column. This is especially true when working with a wide cell and/or a small font size.
Example scenario: suppose your zoom level in Excel is 100%, and let's consider a 100 pixels wide cell filled with text, using a font size of 10 pixels. When you change your zoom level to 85%, the cell is rendered on your screen as an 85 pixels wide cell, and the font is rendered in size 8 (instead of the theoretical 8.5). This will cause the text to appear less wide than the cell. N.B. the way Excel calculate the different sizes is more complicated, but the reasoning holds.
100% Zoom - normal 66% Zoom - the text overlaps
Scaling
All of these issues can also be caused by the screen resolution. You can access these settings by right-clicking on the desktop, and choosing 'Display settings':
Fix for these issues
- Keep the zoom level in Excel/PowerPoint/Word at 100%
- Set the scaling of Windows to 100%
Also note that even if you do so, exporting will never yield an exact same result, read this article to know more.