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.

______________