One of the best features of tables is called “calculated columns”. Calculated columns help you enter and maintain formulas in Excel tables. To explain how this works, let me first add a formula to this data, which is not an Excel Table. A quick way to copy down the formula is to double-click the fill handle. Since every cell has a price, Excel copies the formula to the bottom. Next, I’ll add a formula to calculate a 7% tax. Again, I can double-click the fill handle to copy it down. This works pretty well, but what happens if I change the tax to 8% in the first cell? At this point, the first formula is out of sync with the rest. To make sure all formulas are consistent, I need to copy the formula down again. Now let’s look at the same formulas in an Excel table on the next sheet. When I enter the Total formula, the formula automatically fills the entire column. There is no need to copy it down. The same is true of the Tax formula. After I hit Enter, the formula is copied to the bottom of the column. Even better, if I edit a formula – for example, if I change the tax rate to 8% – this change is replicated throughout the column automatically. That’s how calculated columns work. Notice the autocorrect icon appears when using calculated columns. You can use this menu to navigate directly to Excel’s autocorrect options. Be aware that if you choose the Stop option, you’re actually turning off the fill formulas setting in Excel. If you do this, you’ll see your change revert to a local change. With the setting disabled, you’ll now see a prompt to “overwrite formulas” whenever you make a change to a table column that contains formulas. This works somewhat like calculated columns but with a manual trigger. To reenable fill formulas, you’ll need to visit the Auto Format options in the Proofing area.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
