Nothing wrong with this sheet, right?
Until you get to the end…
The teeny-tiny vertical scrollbar handle is usually a give away. Hundreds of rows tacked onto the end of the list to give the illusion that the whole column has been formatted.
The trouble with formatting columns as per the above image is that it causes the “last used row” to be hundreds of rows further down than necessary. Click print and you waste a forest. It could also cause your workbook file size to bloat! In any case the vertical scrollbar’s usefulness takes a serious blow.
The answer is to format not individual cells, but the whole column.
- Select the worksheet column (or columns). The short-cut key is Ctrl+Space. In the above example, we’d highlight columns A to F.
- Right-click the selection, and choose Format Cells…
- Apply formatting the way you want.
If we did this exercise on the above example, you would notice that the column headers would also be formatted the same as the content, which is often not what we want. The trick is to format them last.
So the general order of formatting goes:
- Format the whole sheet
- Format the whole column
- Format just the column header (label)
Hopefully this diagram explains it.
This method allows your worksheet to grow while maintaining consistent formats for new rows.