Editor’s note: Formulas are a helpful way to compile data and gather insights, but they can be tedious to manage. In this article, our Product Marketing Manager Morgan Smuck introduces column formulas in Smartsheet and shares three ways this new feature can save you time and prevent formula frustration.
Today, companies are managing larger amounts of data than ever before. In recent months especially, organizations and individuals are relying on data to answer critical health, safety, and business continuity questions. Being able to uncover insights from data and quickly take action on that information has arguably never been more important.
Using formulas in sheets is one way you can collect actionable insights from your data. But while formulas are helpful, they can be tedious to manage.
We’ve all likely experienced the frustrations of working with formulas at some point — one small error leads to incorrect results, someone accidentally edits or removes your formula, or a new row gets randomly added in your sheet and breaks the formula you so carefully copied down every row.
Luckily for you, Smartsheet just made managing your formulas easier than ever — eliminating formula frustration.
Introducing column formulas
With our latest column formula release, licensed sheet owners and admins can enter their formula once in a cell, and quickly convert it to a column-level formula, which uniformly applies the formula to every cell in the column.
Working at the column level, rather than at the individual cell level, can help you scale your Smartsheet solutions — and reclaim precious time throughout your workday.
Column formulas add three key benefits to your sheet experience:
1) Eliminate manual, tedious workarounds.
While working with formulas over the years in a variety of different tools, I have often found them extremely fragile to work with, especially when I’m dealing with large amounts of data or have multiple collaborators.
When working on big projects, I have to constantly check the accuracy of my formulas to ensure my results are correct. For me, this usually involves a series of manual “health checks” to ensure a formula is propagating correctly in each cell, repeatedly saving my sheet, and granting edit access to as few people as possible. If that sounds familiar, you’re not alone. I find that everyone who deals with formula solutions at the single-cell level develops their own manual system to protect their work.
Column formulas help reduce the need for manual, tedious workarounds, because only licensed sheet owners and admins can create and edit formulas at the column level. For all other users, it is a read-only experience — preventing accidental deletion or editing of any formulas.
You can work more confidently knowing your formulas are untouched, and results are propagating as you want them to be, without as many manual sanity checks.
2) Save valuable time setting up your formula solutions.
Previously, when working with formulas in Smartsheet, you probably relied on drag-filling your formula down every row of your sheet, or copying and pasting the formula from row to row. This is especially time-consuming for anyone working with massive data sets on larger sheets. If you’ve used Smartsheet forms to capture data on a sheet, you know that rows get added to the top or bottom of sheets, which adds more time spent checking to make sure the formula persists in those added rows.
Using formulas at the column level saves you the time you would spend on manual formula workflows. Now, you can apply your formulas to every row in a column in just a few clicks. As long as your column formula is set up, no matter where rows get added into your sheet via forms, copying and pasting, or by moving rows from sheet to sheet, the column formula will automatically apply to every row that gets added in a column.
You can now reclaim valuable time throughout your workday to focus on what matters most.
3) With more reliable results on your sheets, you can confidently take action on your data faster than ever.
Knowing that your sheet is free from cell-level errors or other formula mishaps gives you more confidence in your data. That means you and your team can take action on critical data insights even faster. And using automated workflows in Smartsheet eliminates manual, repeatable tasks that take up time throughout your day. Simply set up a rule based on the underlying column type and the result of your column formula.
Another great way to visually organize the data on your sheets is to use conditional formatting, which can also be applied to the underlying column type and results of the column formula. This helps teams quickly see at-risk, completed, or over-budget items, for example.
Get going with column formulas
For more information about formulas in Smartsheet, visit our Learning Center and read our help article to learn more about working with column formulas.
