Formatting elements: Column Width and Row Height (Nifty tricks)

Wrapping up this section on changing column widths and row heights, I wanted to share some tricks that I’ve picked up over the years, for getting columns to behave the way I want them to, with minimal effort on my part.

The first trick is about auto-sizing columns to something other than the largest entry. Generally, I don’t like manually resizing columns, either through the menus or through the headers. It’s tedious, and often the results aren’t quite what I want. But sometimes there’s just one or two entries that are significantly longer than the majority of the rest. One option is to simply pick one to represent all the rest, and then automatically resize the column to the best width for that cell, but that involves scanning through and finding the best one. A better option, when there’s only a few extra-long cells, is to wrap the text in those cells. Then, when you tell the column to automatically find the best width, it won’t consider those cells. The only real downside is that it is likely to automatically increase the height of the rows with the wrapped text. If that’s a problem, though, you can manually force those rows back to the regular height.

Which brings me to a second common problem: setting sizes back to the default. Sometimes you’ve changed something, and later you want to change it back to the default, and no matter what you try it never looks quite right. It might be sensible to try using the “default width” to change it back, but that only affects sizes that haven’t already been changed from the default. However, there’s still ways to do it. If you’re trying to put a column back to default width then one thing you can do is go into the default width menu, write down the width measurement, then go into the manual column width change menu and enter the measurement. Alternatively, whether it’s a row or a column, you can go into the manual change menu in a cell that’s at the right width/height, copy down the measurement, then go to the row/column you want to change, enter the same menu, and then enter the measurement that was in the correctly-sized one. These may sound like “well duh” solutions, but I’m embarassed to think about how much time and frustration I wasted before I thought of them.

Alternatively it’s worth noting that, although I’ve only talked about using paste-special as a way to paste the results of formulas, rather than the formulas themselves, it can also be used to paste formats. This is relevant because column width and row height are formats. Since it’s a column format, not an individual cell format, you have to select the whole column for it to work. It will also paste any and all other formats that happen to be in the column you’re pasting from, so keep an eye out for that.

The next two tricks are about making text wrap where you want it to, instead of at the end of the line. I’m addressing them here because a) I didn’t think of them when doing text wrapping, and b) it’s usually not relevant unless you’re also resizing the column width. The thing is, sometimes text doesn’t automatically wrap where you want it to. For example, let’s say you had a cell that contained both the ISBN-10 and the ISBN-13 for a book (for example, “974035300; 978-0974035307” — the first volume of Unshelved). If most of the cells in that column had only one or the other, there would be no point in making it wide enough for both. However, if you wrap the text and make it large enough for the ISBN-13, then if the ISBN-10 is first then the two lines might be “974035300; 978-” and “0974035307”. Not the easiest thing to read. And if you make it only as long as the ISBN-10, then all the thirteen-digit ISBNs will be broken onto two lines, which also isn’t easy to read. Fortunately there’s an easy solution I like to call force-wrapping, and there’s two ways to do it.

The first, simplest, and kludgiest way to force-wrap is to add a lot of spaces before the text you want starting the new line. In most programs, spaces at the end of a line are essentially invisible. You could have 100 spaces after the end of the first number, and the only way you could tell they were there is that the 2nd number would start on a new line instead of immediately after the first one. This is kludgey, though — if you end up making that column wider after all, it can end up looking very strange. Plus it makes the file a bit larger than it needs to be, but with modern file sizes a few extra spaces probably won’t even be noticable to most systems.

The second way to force-wrap is more complicated — copy a line break from a word processor, and paste it into the cell text. It does involve going into another program, and I don’t promise it will work in all systems though it works in most. To do this, start by going to whatever word processor came in the same suite as your spreadsheet program, and hitting the enter key. You might want to type something before and after it so that it’s easier to see, but that’s not absolutely required. Then highlight the line break — the character at end of the previous line, which wraps you around to the new line. If you know how to view codes it might be easier, but that’s also not required. Copy that character, then go back into your spreadsheet. Then, in the formula bar, paste it wherever you want the text to wrap.

Lastly, you can automatically resize columns to slightly larger than the largest entry if you want a little white space between the columns. To do this, just expand the longest entry, automatically re-size the column, and then remove the added padding. For example, you could put one or more spaces at the end of the longest text string, and as long as it’s not set to wrap text the spreadsheet should adjust the column to fit them. If you’re working with numbers you can change the format to show one or more additional decimal places. That’s something I haven’t covered yet, but I’ll definitely be discussing it sooner or later.

That’s it for column widths and row heights! Next week I’ll be back with how to know whether all these changes will make your tables print out the way you want them to, how to control the printing process in general, and why it’s important to do so.