How to use Structured Referencing in a Spreadsheet

One of the best ways to make use of all that Sourcetable offers is to use structured referencing when creating your reports and analyzing your data. This allows you to manipulate the data you selected in any data table in your workbook on one sheet. Structured Referencing is easier to use, easier to read, allows for a larger range of data, and your spreadsheet will work faster. Your reports and formulas aren’t limited to the data that you have copied and pasted to the current sheet as long as you use structured referencing.

What exactly is Structured Referencing?

Structured referencing is when a user references the data table names and the column names in their formulas instead of using explicit cell references. For a data table named "TableName" the structured referencing and explicit cell references are below:

Data Table - Customer

While creating formulas in a spreadsheet, Sourcetable will automatically suggest the column names once the table name is typed allowing you to see what data is available to use.

Auto filling the column names ensures the user doesn’t have to remember the exact name of each column making it a little easier to use.

Naming tables and columns

When using structured referencing a couple of changes can make it much easier. First changing the name of the data table from Table1 to a name that tells you the data it contains will be extremely helpful with a large amount of data. To change the name of a table click the current name and it will become an input field. Then type the new name and hit enter.

Clicking out of the input field will not cause your new name to save. This is to ensure that users do not accidentally change the name of one of your data tables. There are some rules for naming the data tables:

1. No explicit cell references - you cannot use a cell reference in the name.
2. Be Unique - Data table names must be unique. It is necessary to keep the data distinct.
3. Short and sweet - Keep the table names shorter than 255 characters.
4. No spaces - If a space is necessary use an underscore _ or a dash -.
5. No special characters - Use the ordinary alphabet or numbers only.

Once the data table has a correctly formatted name you can stop using explicit cell references and level up.

Using structured referencing

With structured referencing you refer to the table name in a formula like this: TableName[column_name], which stands in for a particular explicit cell reference.

Data Table - Customer

In the data table above, named Customer, a user would get total orders by putting in =SUM(Customer[orders_count]). Start with what you want to do, whether it is count, sum, or average, then use parentheses and put in the data table name with brackets containing the column name that you want. If you are multiplying or dividing simply use the data table name and column name in place of cell references. If a user wanted to divide the total number of orders by the count of customers then they would create this formula:

=((SUM(Customer[orders_count])/(COUNT(Customer[last_name]))

Sourcetable makes referencing more than one data table easy. Just use the data table name and column name like before.

=((SUM(Customer[total_spent])/(COUNT(Table2[customer_id]))

Structured referencing makes reports more performant and easier to use with no copy and pasting duplicate data. More data at your finger tips means improved in-depth analysis. Now you can easily create one page reports from multiple sources and integrations that update automatically with Sourcetable.

Ready to try Sourcetable?