Long Tables: The Smartsheet Table Structure That Makes Formulas Easier

One of the most common mistakes I see when people start using Smartsheet is building tables in a "wide" format.

In a wide table, different versions of the same piece of information are stored in separate columns. For example, you might create one column per year, one column per project status, or one column per region. It feels natural because that's often how we like to view data in a spreadsheet.

The problem is that this structure doesn't scale.

Instead, those different versions should usually be stored in rows, with a single column identifying the version. This is known as a "long" format table, and it's how relational databases are designed to store data.

When you're first building a solution, the difference may seem insignificant. But a few months later, when you need to reference that data from another sheet, build reports, or create formulas that depend on multiple criteria, the limitations of a wide table quickly become apparent.

Let's look at a simple example.

Imagine you're creating a reference table containing currency conversion rates for multiple years. Other sheets will use this table to convert costs and revenues into a common currency.

A common approach is to create columns for the source currency, target currency, and then a separate column for each year:

This works if your formula only needs to match the currencies. However, as soon as you want the formula to automatically select the correct year, the structure becomes difficult to work with.

A better approach is to store the year as data:

At first glance, this may look like more data because the year is repeated on every row. In reality, you're storing exactly the same information, just in a structure that Smartsheet can work with much more effectively.

With this format, you can easily retrieve the correct rate using an INDEX/COLLECT formula:

=INDEX(COLLECT({Currency - Rate}, {Currency - Target}, Currency@row, {Currency - Year}, YEAR([Start date]@row)), 1) * Cost@row

The formula can now select the appropriate conversion rate based on both the currency and the year, without requiring separate logic for each column.

As a general rule, whenever you're tempted to create multiple columns representing different versions of the same thing, ask yourself whether those values should actually be rows instead.

A well-structured reference table makes formulas simpler, reports more flexible, and your entire Smartsheet solution easier to maintain.

If you want to know more about reference tables, I covered this in last week's post: Smartsheet Reference Tables: Build Once and Use Them Forever

And if you need help designing a scalable Smartsheet architecture, feel free to get in touch using the form below.