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.