Differences in column width between Excel 2011 for Mac and later versions

You may notice when you open an old workbook that was saved using Excel 2011 for Mac, the column widths and/or row heights have some differences when compared to the same file opened using later versions of Excel for Mac. This is due to intentional changes that were made to improve the consistency between Excel for Mac and Excel on other platforms such as Windows. The differences observed between Excel 2011 vs. later versions for Mac are also observed between Excel 2011 for Mac and Excel 2010 (and later versions) for Windows.

Note: The same layout differences are no longer an issue between Excel for Windows compared to more recent versions of Excel for Mac than Excel 2011.

Technical notes:

Excel determines the width of columns based on the character width of the font used in the “Normal” cell style, and the units for column width are "character width". A "character width" is dependent on the font and font size specified in the "Normal" cell style. If the font is narrow or set to a small font size, then a default column will appear narrower than if the font is wider and/or set to a larger font size. By adjusting the size or the font in the "Normal" cell style, the widths of all columns will be recalculated and adjusted accordingly.
Why is there a difference between Excel 2011 and later versions of Excel for Mac? The character width in pixels is reported to Excel by the operating system. If the given font width (in pixels) is not a whole number, it is adjusted to a whole number by Excel, since partial pixels cannot be displayed. Excel 2011 adjusts to the next higher integer, but later versions of Excel round to the nearest integer, which aligns with Excel for Windows. For font sizes where the value is rounded down by Excel 2016 and later, the column widths will be different.

For example, if a font character width is reported as 6.48 pixels by the operating system, Excel 2011 would use 7 pixels as the character width, but later versions of Excel for Mac and Excel for Windows would use 6 pixels as the character width. If a column is 10 characters wide, it would be 10 pixels wider in Excel 2011 than in later versions of Excel for Mac and Excel for Windows.

In many sheets, these differences are insignificant. However, there are scenarios where the differences are more noticeable, such as when you have a large number of columns to fit on a printed page. It is possible that rounding combined with the slight difference in column width calculation can lead to noticeable differences. For more information about how Excel determines column widths, go to Description of how column widths are determined in Excel.
The macOS API used by Excel to find the character width for a given font was changed due to deprecation of the API used by Excel 2011. The replacement API in some cases reports a different character width than the legacy API, which could cause differences that are unavoidable by Excel.
Column widths are shown as the number of characters or in physical units (inch or cm) which in turn is computed as a whole number of pixels. The actual column width may be rounded so that it becomes a whole number of pixels. For example, if the user adjusts a column width from 0.08” to 0.09”, the column width may not change, since both values may round to the same whole number of pixels. For this reason, columns widths set in physical units may not fit exactly in the expected physical page width. For example, if you set column widths to 1 inch, it may not result in exactly 7 columns fitting on a page that is 7 inches wide.
Regarding the difference in row height between Excel 2011 and later versions of Excel for Mac, Excel will automatically adjust the row height to fit the font and font size for values in any given row, unless the row height is specified manually by the user. Some padding (empty space) is added to the row height to fit the given font and font size. The amount of padding used is slightly different between Excel 2011 and later versions of Excel for Mac. These differences were introduced to bring closer alignment between Excel on Mac and Windows, so that there is a more consistent experience when sharing files.

STATUS: WORKAROUND

Each of these methods may work in different situations, depending on the content and purpose of the workbook.

In a case where the column widths throughout the workbook are either wider or narrower than expected, you can change one setting which will cause all column widths to be recalculated (scaled), to avoid the need for manually resizing columns. Specify a font and/or font size in the “Normal” cell style that has the same character width in both Excel 2011 and later versions. To find such a font and font size, use a newer version of Excel for Mac to open a document for which you know the expected layout in Excel 2011. Change the font and/or font size in the “Normal” cell style and then check whether the layout matches what you expect. You may need to try several fonts and/or font sizes to find one that gives the expected results.
To avoid a situation where columns are wider than expected and cause an additional page or pages to show to the right of the desired page (in Page Layout View or when printed), you can set the "Scale to Fit" options to fit to 1 page wide, and "automatic" height. This should shrink the columns as necessary, so they fit on a single page width. These options are found on the Page Layout tab of the ribbon and also can be found in the Page Setup dialog. In the Page Setup dialog, set the "Tall" field to blank by deleting the value. This means that printed area will be sized to 1 page wide, but the number of pages "tall" will be adjusted as necessary to fit the given width.
Regarding the units of measure for columns between Excel 2011 and later versions of Excel for Mac:
1. Column width is shown in the same units of measure when in Page Layout View.
2. In Normal view, the column width is shown in characters and pixels rather than physical units, since the physical units don't directly apply to that view. To see the physical units (inches/cm), you can switch to Page Layout View.
Regarding the units of measure for rows between Excel 2011 and later versions of Excel for Mac:
1. Row height is shown in the same units of measure when in Page Layout View.
2. In Normal view, the row height in later versions of Excel for Mac is shown in points/pixels rather than inches/cm. To see the units in inches or centimeters, you can switch to Page Layout View.
Resizing rows and columns - multiple rows or columns can be resized at the same time by selecting the desired rows/columns and then dragging the edge of one row or column to the desired height/width. All the selected rows or columns will be set to the given size. The increments allow adjustments as fine as a single pixel, as shown here.
In some cases, images may seem to be stretched or squeezed (losing aspect ratio). To restore the image to the original size as it was when it was inserted into the file, you can select the image, go to the Picture format pane and click the Reset button.

If this is required for a large number of images, it can be accomplished with VBA (macro). Use the ShapeRange.ScaleHeight and ShapeRange.ScaleWidth methods to reset or change the size of the objects. For example, to reset a selected shape to the original size while keeping the top left corner in its current position, use "Selection.ShapeRange.ScaleHeight 1, msoTrue, msoScaleFromTopLeft".