Set Row Height With REPT Function

Thanks to AlexJ for suggesting a great use for the REPT function – setting a minimum row height. He uses this technique to add a bit of spacing in his tables, so they’re easier to read.

You can watch the steps in this video (or watch it on YouTube), and the step-by-step instructions are below the video.

Add Space in an Excel List

For example, here is my To Do list, with a few items to work on, around the house. Most of the Task Descriptions are short, and fit in a single line.

reptrowheight02

When I select all the rows, and use the AutoFit feature, either with the Ribbon command

reptrowheight10

or by double-clicking on the line between the row numbers,

reptrowheight11

it squashes everything together.

reptrowheight01

The list is readable, but it would be easier on the eyes with a bit of white space at the top and bottom of each row.

Yes, it’s a small detail, but if you’re staring at a long list all day, it could make a real difference in how blurry your eyes are, at the end of the day!

Use REPT To Set Row Height

With AlexJ’s trick, you can add another column in the table, with a REPT formula.

=REPT(CHAR(10),2)

The CHAR function, with code 10, inserts a line break, so this example adds 2 line breaks in the cell.

reptrowheight03

See the Characters

You can hide the Spacing column, after you’ve set up the formula, so it doesn’t appear in the printed version.

If you want to see something in the cell, you could add a character, such a a Pipe, so you remember that the cell isn’t empty

=REPT("|" & CHAR(10),2)

reptrowheight04

NOTE: After you add the formula, you might have to autofit the rows again, to see the effect.
OR, select the spacing column, and turn Wrap Text off, then on again.

reptrowheight12

Adjust the Settings

You can adjust the REPT formula, and the cell formatting, to control the row height. For example, change the number_times argument to 1, so the row height is shorter.

reptrowheight05

Or, change the font to a smaller size, so there’s a little less white space.

reptrowheight06

Pictures Over Cells

AlexJ sent me his REPT formula at the perfect time, because I found another need for it – setting a cell height for pictures.

In Excel, you can paste a picture onto a worksheet, but it floats over the sheet – it’s not really in a cell. If you want the pictures to move with the cell

  • make sure that the picture’s top left corner is inside the cell
  • set its properties for “Move but don’t size with cells”

reptrowheight09

If the pictures are properly positioned, they will sort with their row. But, if you do an Autofit on the row height, the picture height is ignored, and all the pictures can end up in a messy pile.

reptrowheight08

Set Row Height for Pictures

Instead of leaving the picture cells empty, use a REPT formula to set a minimum row height – I used 6 as the number_times argument in this table.

reptrowheight07

Now I can autofit the rows, and they will never get shorter than the minimum height set by the REPT formula in the Picture column.

Download the Sample File

You can download the sample file, to see how AlexJ’s technique works. Go to the AlexJ Sample Files page on my website, and in the Functions section, look for FN0001 – Set Minimum Row Height. The zipped file is in xlsx format, and does not contain macros.

______________

You may also like...

19 Responses

  1. Bryan says:

    I personally like my table rows to be as short as possible so I can fit as much information on the screen as possible (my scrolling finger gets tired before my eyes do!), but I will definitely have to keep that trick in mind for pictures!

  2. Genius. Sometimes I want to autofit columns, but not below some minimum number regardless of the text size. I should use REPT in a hidden row for that too.

  3. Exshail says:

    Great Idea. Another workaround is to Increase font size of empty cells (not to be use) in hidden columns.

    • Tony Gaul says:

      I increase the font size of empty cells too. This rept trick may be good for high rows.

      • Exshail says:

        The difference between above two method is that with rept formula we have to autofit the rows whereas increasing\decreasing font size instantly adjust the row height.

        Rept formula will be good to have Table (in excel-2003,2007 List) with equal row height with one go.

  4. Arshad Mahmood says:

    Thanks, that’s a great & interesting idea.

  5. Ian Wainwright says:

    This is almost sneaky – I love it! Thank you.

  6. PRAVEEN NETHA.R says:

    not a useful function than auto fit for all rown in one click, or seting row height to all rows in one setting

  7. dmcnab says:

    I sometimes use drop-down menus in a list but they are very hard to read—other times, I have used Deb’s formulas for combo boxes etc that make the lists very readable, but when you use the combo box methodology, you cannot word wrap (ie: have multiple lines of text) in a cell and you cannot use the Auto-Complete function in Excel…..could REPT make these kind of lists more readable?

  8. Peter Griffiths says:

    Thanks for the tips. I too have used Font size to adjust the Row Height.

    What I would really like though is the ability to copy any pages ‘Row Heights’ onto another page, including Hidden Rows. I find the copy paste special, column widths, extremely useful. Is there a way to do the same for Row Heights?

    Cheers, and thanks to Debra and all contributors.

    • dmcnab says:

      Hi Peter Griffiths…..you might check out the ASAP Utilities tool collection, from Bastien Mensink… http://www.asap-utilities.com

      It is free for personal use, and it is really helpful….tons of little tools that make working with the Excel program much more efficient.

  9. Maxime Manuel says:

    Priceless, priceless. Thank you for this tutorial Debra.

  10. Peter Griffiths says:

    Hi dmcnab, Many thanks for the suggestion. I do in fact have a paid for version as the asap utilities is very good value (shame they don’t do a 64 bit version). I was not aware that they had anything much to do with Copying row heights, but I will check it out again, it is a big menu. To anyone else out there I have to recommend the ASAP UTILITIES add-on, especially alongside Debra’s CONTEXTURES ribon. Both are excellent value and save an enormous amount of time (and frustration). Thanks again.

  11. Claudio says:

    Who knows?
    This trick can be useful in unexpected times!

    Thank you for sharing!

  12. Amartesh Singh says:

    Tell Me How You Put Picture in cell Not Mention In Formula And also youtube Video

  13. Martin Sowinski says:

    I have an inquiry. My work has me create 4,000 cell long tables filled with text. Some cells only have a single line of text while others have up to 5 lines. When using auto fit and converting over to adobe some of the words get partially cut off. Is there a way to manipulate the row height per line of text in the box?

    Thank you

  1. October 16, 2014

    […] Set Row Height With REPT Function […]

Leave a Reply

Your email address will not be published. Required fields are marked *