Quickly set consistent column width throughout the whole table

I think many people know how to AutoFit column width in Excel.  In case you don’t, you can simply move your cursor in between the column headers, when you see a cross with left-right arrow, double-click.  Then the column width will be adjust to fit the longest text string underneath the column.

However, AutoFit may not result in a nice-looking table as it gives you columns with inconsistent column width like the one below:


See, Column B and C are of different width because of the difference of the table headers.  Actually we want a consistent width for Column B and C, so we manually adjust it and have the following result (I have also make Column D a bit wider):


Then I want the rest of the table (Column E to J) follows the same width of Column B to D.  Instead of adjusting the column width one by one, we can use Copy, Paste Special -> Column Width:

1) Select any range from B:D, in this case B1:D1 is select, then COPY


2) Select any range from Column E to J, in this example, E1:J1 is selected, then Paste Special


3) Select “Column Width”


6) As simple as that.  Now you have consistent width throughout your table.


7) Now you just need to Wrap the Text in cell E2 and H2 to make it look nicer:


8) This is the final product


Hint: This could be very helpful when you copy and paste a well-formatted table to different columns as the Pasted table will follow the destination columns width.  You can do the pasting again with “Column Width” (You may refer to this blogpost for instruction).  It saves you time to adjust the columns width again and again…🙂

This entry was posted in Excel Tips and tagged , . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s