Setting up your columns

In the Columns tab in the SQL Spreads Designer you can setup how your SQL Server table columns will be shown in Excel.

Video - Setup your columns and rows

Show or hide columns

Use the checkboxes in the Show column to select which columns to show in Excel.

Use the header checkbox to select/unselect all.

Set columns to Read Only

Use the checkboxes in the Read Only column to select which columns should be locked for editing in Excel.

Use the header checkbox to select/unselect all.

Rename columns

Update the Header texts that are shown in Excel by editing the name for each column in the Designer.

Rearrange column order

To rearrange the order of the columns in Excel, use "drag and drop" to move the columns up and down in the designer.

Move column to Top or Bottom of the table

If you have many columns in a table, you might want to quickly move a column to either the top or bottom of the table. To move a column to the top or bottom of a table, you can right-click on the left hand side of the Header Text column and select one of the options "Move to Top" or "Move to Bottom".

Adding Drop-Down lists to a column

SQL Spreads can show database populated Drop-Down lists in the cells in Excel to restrict the end-users to only select from a pre-defined list of values.

The Drop-Down lists can also show a text value to the user but insert a corresponding key value into the database column.

You can read more about setting up Drop-Down lists in the article Lookup key columns and replace with texts.

Dynamically set columns to Read-Only

This new feature allows you to dynamically lock SQL Server columns from being edited based on a formula or the value in an Excel cell. For example, in the scenario below, past months are locked when entering forecast values:

These are the steps required to dynamically set a column to Read-Only:

1. Go to the Columns tab in the SQL Spreads Designer. Click the new Dynamic Column Locking icon located below the list of columns:
   
2. In the dialog that appears, select the sheet and specify the cell reference containing the list of columns to be set as Read-Only:
   
3. Click OK.
4. In the specified cell, enter a comma-separated list of column numbers that should be set to Read-Only:
   

Other Column settings

There are a number of additional column settings that can be reached by clicking the small Cog on the right-side of the Column tab in the SQL Spreads Designer:

Database Column details

To show details about a database column, you can hover over the Cog in the right-most column:

You can also click the Cog to view the Column Details dialog where you can see the column's details at the top:

Specifying a default value

Using Default values requires SQL Spreads Pro or higher plan

You can set a default value for a column to use when the user does not enter any value when inserting new rows.

The Default value can be used for editable columns, and also for Read-Only, Hidden and Drop-Down list columns.

Hide a column used in a Tree filter from the columns in Excel

In cases when you filter the data loaded from SQL Server either by a Tree filter or by using the values from one or several cells, you can hide that column from the spreadsheet as the user has already specified the value in that column through the filter selection.

When you hide a filtered column, you specify which value that should be inserted into that database column by adding a SQL Spreads parameter into the Use a parameter value field:

You can read more about hiding filtered columns in this Hide a filtered column from the spreadsheet article.