Fix Excel ColumnWidth and Width Values Not Matching
Question details
The user needs to accurately set a column's width in points, but Excel's ColumnWidth and Width properties do not match or scale linearly.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Attempting to standardize column dimensions programmatically using VBA, where exact point measurements are required for reporting or printing.
- Observed behavior
- Setting the ColumnWidth property (character-based) results in unpredictable read-only Width values (points-based) due to font metrics, rounding, and internal units.
Save a backup of your workbook containing the target layout, and ensure that the Developer tab is enabled to run VBA macros.
Use an Iterative VBA Macro to Match Width
Use a VBA script that increments the ColumnWidth in tiny steps until the read-only Width property reaches your desired point value.
Excel's ColumnWidth property is determined by the width of the '0' character in the normal font, whereas the Width property is a read-only measurement strictly in points.
Because of internal rounding and font rendering variations, a direct mathematical conversion formula is completely unreliable. The most effective workaround is an iterative scripting approach.
Press Alt + F11 on your keyboard to launch the Visual Basic for Applications editor in your spreadsheet.
Click on 'Insert' in the top menu bar, then select 'Module' to create a blank script window.
Write a loop that incrementally increases the target column's ColumnWidth by a small amount (e.g., 0.1) and continuously checks if the Range.Width matches or exceeds your desired points.
Press F5 or click the Run button to execute the script and automatically adjust your column to the closest possible point width.
Run Macro Scripts Easily with WPS Spreadsheet
WPS Office offers robust support for VBA macros, allowing you to use iterative scripts to precisely manage column widths. Enjoy the same advanced developer features in a lighter, faster application.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your .xlsm or .xlsx workbook.
- 2. Access Developer Tools: Navigate to the Developer tab on the top ribbon and click on the Visual Basic icon.
- 3. Apply your Column Width Macro: Paste your iterative VBA script into a new module to perfectly align your column widths, then press Run.
Frequently Asked Questions
Why is the Excel ColumnWidth property measured in characters?
Excel historically designed column widths to fit a specific number of characters of the default system font (specifically the '0' character) to make standard data entry and reading more predictable.
Can I change Excel's column width unit to inches or centimeters?
In the Normal view, the unit remains in characters. However, if you switch to the Page Layout view via the View tab, you can adjust column widths using inches or centimeters via the ruler or the Column Width dialog box.
What is the maximum column width I can set?
The maximum ColumnWidth limit in Excel is 255 characters. If your iterative VBA script tries to exceed this threshold, it will trigger an error and halt execution.
