Excel formatting: force lengthy, wrapping column headers to narrowest width possible while constraining height of the header row|General Excel Questions & Answers|Excel Forum|My Online Training Hub

I regularly receive a series of simple (data only) Excel workbooks from a government entity. A typical worksheet includes several thousand records (in rows) with data spread across approximately 800 different fields (in columns). Other than basic cell borders and text-wrapping in the row that contains column headers, the workbooks arrive without any formatting; each column is set to a uniform width of 10.00 (65 pixels). Many of the column headers are extremely long in length and when text-wrapped in a 65-pixel-wide column, result in a very tall header row.

Upon receiving the data, I manually adjust column widths to force the header row to a shorter height, but a height which still keeps the full text of each column header in view. However, try not to make any single column wider than necessary... so it is not as simple as just forcing all columns to a very wide standard width. With 800+ columns with headers of varying lengths, this is an extremely time-consuming undertaking. I would like to find a way to automate this process.

Is there a way to automate this reformatting process? In other words, is there a way to limit the header row's height to a value I define and then have each column automatically adjust its width to the narrowest possible value that still enables the column's header to be completely visible (with wrapping) within the constraints of the defined header row height?

The attached worksheet is a scaled-down example of what I receive ('Sample Table 1'; cells B3:I8) and how I would like it to look after formatting ('Sample Table 2'; cells L14:S19). As you can see in Sample Table 2, columns P:S are as narrow as they can be so the column headers in row 14 are fully visible, but do not result in a height for row 14 that exceeds 38.25 (or three lines of wrapped text).

Any tips are greatly appreciated!

For reference, I am using Excel 2016 on a PC.