Good Fences Make Good Spreadsheets, Part 1 discussed the idea of using border rows or border columns to surround blocks of cells. The borders assure that totals will include any new cells that are inserted into the block. Part 2 in the series described another benefit of borders: they prevent some copying operations from running too far and damaging the totals.
In this installment, I make two suggestions for implementing border rows.
1. Don’t Use Your Head(ers)!
Let’s take the example from Part 2, but without a blank border row above the block of calculations. Here the headers in Row 2 are included in the ranges for the totals at the bottom. It can seem like a good idea to use the headers as the top “fence” for a block of cells. After all, any rows we might later insert beneath the headers will automatically be included in the totals.
You can see that the SUM function in D9 references the range D2:D8. Unfortunately, the header in D2 has a numeric value, 5%. This leads to a subtle error of $.05 in the total in D9. To get the correct total, insert a blank row for the top border and make sure the ranges used by the totals begin with that row:
2. Don’t Starve Your Border Rows!
Here’s another variation on the same example. The height of Row 9 has been reduced to 2 units for esthetic reasons. I used to do this. I made border rows that were one or two units high. I often filled them with black or gray backgrounds to create underlines. But very thin rows are unhealthy. Notice that the sum of the principal in C10 doesn’t make sense. It should be $100,000:
How did this happen? The problem is that Row 9 is too thin to reveal its contents. If the row had a normal height (15 units), we could see that the block of calculations had been copied into the bottom border row:
I find that a height of 6 units is good for border rows. It is thin enough for an attractive layout, but thick enough to alert us to any stray contents in the row:
This underscores the obvious point that your rows (border or otherwise) should never be so thin that they obscure important information.
Note: border rows may not be necessary if you use Excel tables. Please see Tables vs. Fences for more information, and watch for future posts on this topic.
Copyright 2011. All Rights Reserved.
See Also: Tables vs. Fences