Tables vs. Fences

In Good Fences Make Good Spreadsheets, Part 1 and Part 2, I advocated the use of border rows and columns to manage blocks of data for SUM calculations. These borders, or “fences”, ensure that the totals will always reflect any new rows or columns.

A recent LinkedIn discussion (membership in the Excel Blackbelts group is required) brought up the idea of using Excel tables instead of fences. “Tables” in Excel are distinct from “Data Tables”, which are great for scenario analysis. A table, on the other hand, can help you organize a block of data where the columns represent different categories. You can create headers and totals for each column. The totals update automatically when you insert new rows, solving the problem described in Good Fences Make Good Spreadsheets, Part 1. If you need to revise the formulas in a column, all you need to do is change one cell, and the rest of the column will update automatically, solving the problem described in Good Fences Make Good Spreadsheets, Part 2.

The Excel table can be an excellent way to manage a traditional database, where each row is a record and each column is a field (category). You can easily sort or filter the data, and you can select which columns get totals or other summary calculations such as averages.

Tables can also be useful for blocks of calculations, but you should be careful. In particular, watch out for horizontal totals – these are not managed as well as vertical totals. I will discuss these and other considerations in a future post.

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

6 Responses to Tables vs. Fences

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

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

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

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

  5. Pingback: Fast Formulas #3: The Spreadsheet | The Well-Tempered Spreadsheet

  6. Pingback: A Tool that Would Have Helped Reinhart and Rogoff | 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