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.
When I select all the rows, and use the AutoFit feature, either with the Ribbon command
or by double-clicking on the line between the row numbers,
it squashes everything together.
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.
The CHAR function, with code 10, inserts a line break, so this example adds 2 line breaks in the cell.
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)
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.
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.
Or, change the font to a smaller size, so there’s a little less white space.
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”
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.
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.
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.