How to minimize wasted space in cells - optimize row height/column width?

d-b

New Member
Joined
May 20, 2009
Messages
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



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.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Hi,

Select all the rows which you want to adjust

Press Alt+O+R+A


Select all the columns which you want to adjust

Press Alt+O+C+A

Regards,

Ashok Pai
 
Upvote 0
Hi,

Select all the rows which you want to adjust

Press Alt+O+R+A


Select all the columns which you want to adjust

Press Alt+O+C+A

Regards,

Ashok Pai

Just tried it, nothing happened. What is this commando supposed to do?
 
Upvote 0
Hi,

Alt+O+C+A adjusts column width

Alt+O+R+A adjusts row height

do not press the + sign for columns type Press ALT key then OCA
for rows type Press ALT key then ORA

Regards,

Ashok Pai
 
Upvote 0
Hi,

Alt+O+C+A adjusts column width

Alt+O+R+A adjusts row height

do not press the + sign for columns type Press ALT key then OCA
for rows type Press ALT key then ORA

Regards,

Ashok Pai

You obviously didn't read my question.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top