One unique feature of Tables is their ability to remember formulas in a column. Under certain conditions, when you enter a formula it will be entered into the entire column. I see this as a nice feature, although some see it as a hindrance. Chances are if you’re in the latter category, you’re probably not using Tables as they were meant to—which is ok too. If you want to learn more about Tables (besides this website), check out this blog post from Jon Acampora. And of course, there’s a cool book on the subject (chapter 4 is dedicated to Table formulas). I typically call this method of entering formulas my “set it and forget it” method, because I only need to enter it once for each column. One formula to rule them all. We call this feature a calculated column.
These are special in Excel because, when Excel enters them, every row in that column will receive the same formula. Whether you expand or contract your Table, the formula will always be in the data body range of that column. Here is an article from Microsoft explaining how to use them. Note this will only work in the current file formats. I don’t say “new” because they’ve been out for about 7 years now. Those formats are XLSX, XLSM, XLSB, XLAM, XLTX, and XLTM, while XLS file formats (Excel 2003 and prior) will not behave the same way. The “magic” here is because of how the data is written into the XML of the files. There is a special child node in the XML which is specific if Excel adds it as a calculated column.
Why does this happen?
This is a great question. It’s not always obvious when this should happen or when it shouldn’t. With a newly created Table it’s easy, because it’s fresh and nothing has been entered there, so the behavior (of entering a formula once and it being entered in every row of that column) is expected. Think of the column as either being “clean” or “dirty”.
If a column has no data in it, it’s considered clean. If there are different types or values of data, it’s considered dirty (for our expressed example here). To stop a formula from becoming a calculated column it’s as easy as typing text in any cell of a column, then adding a formula in a different row of the same column. Excel see’s the different data and won’t automatically put the formula in the entire column. Or more precisely, Excel won’t add it as a calculated column. You will, however, see a button show up on the bottom-right corner of the cell. Clicking it will give you the option which says Overwrite all cells in this column with this formula. Clicking this button will, as advertised, overwrite the columns data and the formula will be added as a calculated column. Otherwise it will be handled as a dirty column, thus not become a calculated column.
Can you prove it?
Yup. Glad you asked. 😉 To check this, I’m assuming you have a file saved which contains a Table and includes a calculated column.
- Save and close the file.
- Change the file extension (i.e. XLSX, XLSM) to “zip”. (If you don’t see file extensions, look here.) You’ll need to click Yes to change it.
- Open the zip file.
- Navigate to the folder path filename.zip\xl\tables
- Find your Table (names aren’t consistent with names given in the Excel file, but in the order they’ve been added to the workbook) and open it.*
* I use Notepad++ to view XML files, but Notepad works just fine. In the picture below is a typical Table being read as [stored] XML (using Notepad++). You can see the highlighted line shows the “calculatedColumnFormula” node which contains the formula. All calculated columns are stored in 2007 structured reference format in order to keep forwards/backwards compatibility.