Smartsheet: Formulas

Name: Smartsheet: Formulas
Author: Darren Mullen

Learn the fundamentals of Smartsheet formulas in order to build more complex formulas on your own

Preview the first lesson free — get full access to all 3 lessons.

Course: On-Demand

Hello, my name is Darren Mullen, and in these lessons you will learn about Smartsheet formulas, including the fundamentals of formula syntax in Smartsheet, referencing ranges, and how to use Smartsheet summary fields and sheet formulas. I want to start with presenting the syntax for using formulas in Smartsheet. Let's start with covering how to reference individual cells. Cells are referenced by their column name and either row number or use of the @row operator. Let's see how that works. By concatenating the first and last name into the Employee Name column. In the first row of the Employee Name column, I will start with an equals sign, which is how all formulas in Smartsheet start. I will then reference the First Name column and if a column name includes a space, it must be placed inside brackets, like this. Now I will add the row number of one. Notice how it changed colors, and it put a box around the cell that I'm referencing. I will then use the plus sign to concatenate a space by placing it within double quotes. Then I will add another plus sign and reference the Last Name cell. Notice how I did not need to put the Last Name column in brackets, since it doesn't have a space in the column name. It is okay to use the brackets if you wish, and Smartsheet will remove them if they are not necessary. If I drag this formula down the rows in the column, it will auto increment the row number. Alternatively, and the way recommended by Smartsheet is to use the @row operator. This formula can also be dragged down the rows in the column, that I will show you now how to use the @row operator instead of using the row number reference. So I'm going to replace the row number references here with the @row operator. The @row operator is more efficient for Smartsheet when calculating formulas, and it must be used when creating column formulas. Speaking of which, instead of having to drag this formula into every row in the column, I can make it a column formula instead. This has the advantage of ensuring that the formula is applied on every row in the column. So to make this formula a column formula, I right click on the cell, and in the menu I choose Convert to Column Formula. When a column formula is in a column, you'll see the little formula symbol underneath the column name. On the other hand, an example of when you would want to reference the row number is when you want to create an absolute reference. Absolute references can be created for both rows and columns by adding the dollar sign in front of the column name to create an absolute column reference, and the dollar sign in front of the row number to create an absolute row number reference. The purpose of absolute references is to ensure that the cell reference remains the same in all rows and columns that you copy it to. I will convert the column formula back into a cell formula like this. Click on a cell, and choose Convert to Cell Formula. I will now add the dollar signs in front of the column names and replace the @row operators with a dollar sign followed by the row number. I will now drag the formula down the rows and across some columns. No matter where I drag the formula, it will always point to the same cells. Now that you know some fundamentals of Smartsheet formulas, you are ready to start building more complex formulas. To do so, it is helpful to know all of the Smartsheet functions available. These can be found on Smartsheet's website. Smartsheet's website includes a function list that allows you to look for functions by keyword, or you can look for functions by the type of function. So you can look up for advanced functions, functions that operate on dates, functions that operate on hierarchies, logic, functions that calculate numeric values, as well as operate on text. Thanks for watching. Stay tuned for the next lesson, where I will show you how to reference ranges in formulas. Thanks for watching.

Beginner Provider : Darren Mullen 3 Lessons · 14m in Arabic, German, English, Spanish, French, Portuguese, Chinese

Course Description

If sheets are the building blocks in Smartsheet, formulas are used to give life to the structure. In these lessons you will learn the fundamentals of Smartsheet formulas. This includes the proper syntax needed to create formulas, how to find Smartsheet functions, how to reference ranges, and how to use summary fields in sheet formulas.

Learning the fundamentals of proper formula syntax is key as it will allow you to not only create formulas, but also troubleshoot why a formula isn’t working as intended. We’ll also show you where to go to find up-to-date information on Smartsheet functions to create complex, fit-for-purpose formulas.

What You'll Learn

Create Smartsheet formulas using proper formula syntax
Reference cells and ranges correctly within formulas
Find Smartsheet functions to build complex, fit-for-purpose formulas
Use sheet summary fields within sheet formulas
Troubleshoot why a formula isn't working as intended
Locate up-to-date information on Smartsheet functions

Key Takeaways

In Smartsheet, sheets are the building blocks while formulas give life to the structure.
Learning proper formula syntax is key because it enables both creating formulas and troubleshooting why a formula isn't working as intended.
The course covers referencing cells, referencing ranges, and using summary fields in sheet formulas.
Smartsheet provides a source for up-to-date information on its functions to help create complex, fit-for-purpose formulas.

Frequently Asked Questions

What will I learn in this Smartsheet: Formulas course?

You will learn the fundamentals of Smartsheet formulas, including the proper syntax needed to create formulas, how to find Smartsheet functions, how to reference ranges, and how to use summary fields in sheet formulas.

What lessons are included in this course?

The course includes three lessons: Formula Basics, Referencing Ranges in Formulas, and Sheet Summaries in Formulas.

Why is learning proper formula syntax important?

Learning the fundamentals of proper formula syntax is key because it allows you not only to create formulas but also to troubleshoot why a formula isn't working as intended.

What skills does this course cover?

The course covers skills including Smartsheet, Formula Language, Array Formulas, Excel Formulas, Spreadsheets, and Microsoft Excel.