Forum Discussion
Autofit Columns to Data Not Headings
Hello,
I have a large table with some long headers, but small data e.g. header = "Suggested Order Quantity", data = single figures. I would like to wrap the text in the headers, and then autofit all columns to the data so that I can see more on one page. Is this possible? I am trying to build this into a macro, so if anyone knows how to code this that would be amazing!
If I wrap and then autofit, the columns go to the width of the full header length. I also tried setting all columns to a small width, then wrapping and autofitting, but it did the same.
Or do I just have to set each column width individually?
Any help greatly appreciated, thanks!
4 Replies
I came to this thread thinking of the same question. Right now, in office 365 Excel, I can highlight the row filled with data (empty columns may not be affected), and then go to the ribbon, clicking Home > Cells > Format > Autofit column width, and all columns will be auto adjusted. If any column remains the same, that could be due to empty cells or cells with empty spaces. Simply highlight another row with data you want and autofit likewise.
How about re-orienting the headers as shown here?
Just out of curiosity, how many columns are there in this sheet? I'm wondering about whether a different design altogether might be in order
- Thanks, I didn't know you could do that! How is it done?
There are 45 columns and about 4-5k rows of data. We will never be able to see it all in one page but the most important data is near the start (The users asked for all these columns otherwise I would leave many out!)Thanks, I didn't know you could do that! How is it done?
It's done by using the menu under Format....Cells...Alignment
(Presumably that's where you set it to "Wrap text"--just another of the choices. Off there in the top right corner, you can set the angle of the text)
There's another feature, and I think it might be useful (if you're not aware of it). You can freeze the header rows so they always appear even as you scroll down the screen toward the bottom of the table. Similarly, if applicable, you can freeze the left-most column. It's all there under the "View" toolbar. Off to the right in this screen capture.
