How to Add Calculation Columns to Excel Tables

How to Add Calculation Columns to Excel Tables

In some cases you would like to add extra columns to the Excel table that are not saved to the database. These type of columns can be aggregrations, percentage comparisons or other columns that are calculated based on the entry in the other columns.

Calculation columns will have to be added to the right-side of the Excel table, after your last database column.

In the cases below, SQL Spreads will re-create the Excel table and then any calculation columns will be removed:

• When the key columns are changed
• When the pivot-key or pivot-value columns are change in a pivoted setup
• When a new table is selected

Auto-populate formulas in an Excel table column

Excel can auto-populate formulas in an Excel table, but there are a few things to keep in mind to get Excel to auto-populate the formulas also into new rows:

1. First, make sure that the number format of the column's cells is not set to Text. If the cell's number format is set to Text, Excel will not calculate the formula, so select eg General:
   
2. Then, clear all cells in the Table column where you would like to auto-populate the formula:
   
3. Enter your formula in the first row in the Excel table:
   
4. Press enter, and the formula should be auto-populated into all rows in the Excel table:
   
5. Test to add a new row at the bottom of the Excel table and check that the formula is inserted into the new row: