Contents x
    Add Excel formulas to your table
      • Print
      • Dark
        Light
      • PDF
      Contents

      Add Excel formulas to your table

        • Print
        • Dark
          Light
        • PDF
        Article summary

        Adding Excel Formulas that saves the result to the database

        You can setup a column in SQL Spreads to use Excel formulas to calculate what should be saved to the database.

        In the example below we have an Excel formula in the Totals column that summarizes the values in columns M1 to M12. The formula result in the Total column will be save to the Total column in the database:
        image.png

        To add a Formula to a column in SQL Spreads follow these steps:

        1. Click the Cog on the right-side of the column in the SQL Spreads Designer's Columns tab:
          image.png

        2. Check the Use the result from Excel formulas in the cells checkbox at the bottom of the shown Column details dialog:
          Use Excel Formulas in the column setting

        3. All the cells in the column will now be cleared to prepare for auto-populating your formula into all rows:
          image.png

        4. Enter your Formula into one cell in the column and press Enter.
          image.png

        5. The formula will now auto-populate into all rows in the Excel table.
          image.png

        Note:

        To protect your formula from being changed by other users, set the column to Read-Only by checking the checkbox in the Read-Only column in the Columns tab in the SQL Spreads Designer.

        Limitations when using formulas to calculate what is saved to the database

        • The formula result is only saved to the database when a column in the same row is modified.
        • Formulas that reference cells outside of the same row will not be saved to the database when the referenced cell is changed. To save the value in the Formula column, you will have to also change a cell in the same row as the formula.

        Adding Excel Formulas that are not saved to the database

        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.
        image.png

        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:
          image.png
        2. Then, clear all cells in the Table column where you would like to auto-populate the formula:
          image.png
        3. Enter your formula in the first row in the Excel table:
          image.png
        4. Press enter, and the formula should be auto-populated into all rows in the Excel table:
          image.png
        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:
          image.png
        Was this article helpful?
        What's Next

        SQL Spreads