Good Fences Make Good Spreadsheets, Part 3

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:


So I urge you not to use headers for your top border rows. It is better to use blank rows and columns as the fences for your blocks of calculations.

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.

Related Posts: Good Fences Make Good Spreadsheets, Part 1; Part 2; A Big, Avoidable Mistake

See Also: Tables vs. Fences

Advertisements
This entry was posted in Spreadsheet Tips and tagged . Bookmark the permalink.

3 Responses to Good Fences Make Good Spreadsheets, Part 3

  1. Pingback: Good Fences Make Good Spreadsheets, Part 2 | The Well-Tempered Spreadsheet

  2. Pingback: Good Fences Make Good Spreadsheets, Part 1 | The Well-Tempered Spreadsheet

  3. Pingback: A Big, Avoidable Mistake | The Well-Tempered Spreadsheet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s