3

I often create tables in Excel where some cells contain a lot of text while others just contain a single digit or some other short value. With wrap text in cells enabled and then a combination of increasing row height and autofit the width of the columns there is a lot of wasted space in my sheets.

I would like a "smart" function that minimizes the unused white space. Does something like that exist? Or is there a algoritm that is simple enough to perform manually even on bigger sheets?

A few samples of what I mean:

A lot of wasted space horizontally

Vertical wasted space

This is probably pretty good

With merged cells it is starting to get complex as seen in this and the next example

Second example with merged cells

Thinking a bit more about this I have realised that the algoritm I am looking for can't be too naive and could/need different optimising strategies:

  • Naive: In extreme cases it could end up making a cell with a lot of text very high, e.g., with just one letter or at least one word per line. Obviously that is not desirable.
  • Usually I believe a squarish shape of the whole active area would be the most efficient. In that case optimising the circumference is probably the best strategy. However, if you want to print on a rectangular paper or view on a rectangular screen, it would make sense to sacrifice some optimisation to make sure that the data fits on one paper/screen.
Improve this question
4
  • Hmm, I added short descriptions to each image but they don't seem to be visible. However, they are there if I try to edit my question. Well well, the images should be pretty self explanatory.
    – d-b
    Commented Feb 18, 2016 at 9:33
  • Just figuring out how many lines wrapped text takes up is hard enough. I found a method that turns off wrapped-text, checks the height, turns it back on, and checks the height again. You'd have to turn it off for all cells and check each cell individually. THEN, you'd have to adjust some columns and check each cell AGAIN. It could easily be a time-consuming optimization. I hope someone can think of a simpler way.
    – Engineer Toast
    Commented Feb 18, 2016 at 13:13
  • I strongly suspect that this is going to be very similar to the calculus solution - you want the entire thing to look as close to a square as possible. Combine "Make it look like a square" with lastrow, lastcol, and resize, and I think that's the right track
    – Selkie
    Commented Dec 26, 2017 at 22:42
  • What you are trying is practically not looking possible,, I mean to say any single method/Macro can't drive it. But one method you should try,, in MS-Word while making table height & width can be adjusted using Fit to text option,, so make Table and export to Excel !
    – Rajesh Sinha
    Commented Apr 28, 2020 at 6:02

1 Answer 1

Reset to default
0

You could use a VBA that takes the amount of chars in the cell, and use that number og ajust the column width.

Something like Max chars in column B -> Ajust column width to xx.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .