Untitled

Certain information set forth in this presentation may be “forward-looking information.” Except

for statements of historical fact, information contained herein may constitute forward-looking

statements. Forward-looking statements are not guarantees of future performance and undue

reliance should not be placed on them. Such forward-looking statements necessarily involve

known and unknown risks and uncertainties, many of which are and will be described in

Smartsheet’s ﬁlings with the US Securities and Exchange Commission, and these risks and

uncertainties may cause actual performance and ﬁnancial results in future periods to differ

materially from any projections of future performance or results expressed or implied by such

forward-looking statements. Although forward-looking statements contained herein are based

upon what Smartsheet management believes are reasonable assumptions, there can be no

assurance that forward-looking statements will prove to be accurate, as actual results and

future events could differ materially from those anticipated in such statements. Smartsheet

undertakes no obligation to update forward-looking statements except as required by law.

Smartsheet is a registered trademark of Smartsheet Inc. The names and logos of actual

companies and products used in this presentation are the trademarks of their respective

owners and no endorsement or aﬃliation is implied by their use.

Legal

Best Practices

to Optimize

Your Sheets

and Solutions

Bhanu Prakash

Principal Product Manager

We heard you need more

Conditional Formatting rules,

more Cross-sheet References,

wider sheets and many more

Optimize formula engine to make sheets

usable quickly

Formula-heavy sheets - instantly usable

upon load and memory optimization for

range references

Optimize sheet link infrastructure to scale for

lots of sheet links

Sheet links stay up to update up to 2x faster

than ever before

Optimize wider sheets by rendering only

columns in view. Optimize backend to allow for

larger sheets

Wider sheets (>40 column) load up to 6x faster

with faster scroll, and in-app experience such as

Find (Ctrl or Cmd + F). Larger sheets load up to

2x faster than before

Optimize conditional formatting to allow for more

conditional formatting rules

Up to 50% latency improvement in calculating

format formulas along with memory optimization

for your browser

Sheet Links

Formula Calc

Sheet Size Conditional Formatting Rules

We need sheets with more than 5000 rows

20000 Rows is coming !!

Go-do example

Top 10 ways to optimize your

sheets and solution for scale

and performance !!

SMARTER TODAY()

TODAY() – Sheets with lot of TODAY() are slow in performance

#1: Use TODAY() only once in a sheet or reference TODAY()

from other sheet

SMARTER TODAY() – Used

only once

TODAY() – Used in every cell

SMARTER TODAY() – Cell Linked from other Sheet

TODAY() – Used in every cell

Formulas: Range References

#2: Minimize the number of unique Range References

#3: Create Range References referred to by multiple formulas

Formula - Growing SUM TOTAL

SUM([Numeric Data]$2:[Numeric Data]@row)

This formula is inefficient because every new row with this formula creates a new,

unique, range reference

Formula - Growing SUM TOTAL

SUM([Total Optimized]3, [Numeric Data]4)

This formula is efficient because instead of counting all the numbers before the

current row's number every time, it is treated as a running total

Formula - Identify DUP for a growing range

IF(COUNTIFS([Product 1]$3:[Product 1]@row, [Product 1]@row,

[Product 2]$3:[Product 2]@row, [Product 2]@row,

[Product 3]$3:[Product 3]@row, [Product 3]@row) = 1,

"Y", "DUP")

This formula is inefficient because it has uses 3 separate, continually expanding

range references. Each new row is creating 3 new and unique range references

Formula - Identify DUP for a growing range

=[Product 1]3 + [Product 2]3 + [Product 3]3

Use Supplemental column to minimize unique range references

Formula - Identify DUP for a growing range

=IF(COUNTIF([Supplemental Column]$3:[Supplemental Column]@row,

[Supplemental Column]@row) = 1,

"Y", ”DUP")

This formula is optimized by utilizing a supplemental column. Using this column,

we reduced 3 range references to 1

Move Row Automation

#4: Use Move Row Automation -

automatically move rows from a source

sheet to a destination sheet at the right

moments

1. Cleaner sheets and better team focus

2. Saved time on administrative tasks

3. Faster distribution, fewer delays

Example Use Cases

• Automatically move rows added via form to the appropriate team’s work

tracking sheet based on the submitted item’s characteristics (e.g., geographic

location, price/value, type)

• Automatically move procurement request rows to an active IT project sheet

when approved

• Automatically move rows to archive sheet whose status is Complete and

when review status changes to Approved

• Automatically archive a row which has not been changed in the past 12

months

Cross-Sheet Reference

Reference columns or range of cells from another sheet

Run Calculations based on

data in another sheet

Reference Growing List

#5 Select complete columns when creating cross-sheet

reference - accounts for additional rows added to the sheet

Cell linking

Real-time propagation of data across destination sheets

Summary Roll up

VLOOKUP

VLOOKUP([Employee Name]@row,

{Employee Rates Range}, 4)

INDEX MATCH

INDEX({4. Employee Rates Range 1},

MATCH([Employee Name]@row,

{Employee Rates Range Name}))

VLOOKUP

VLOOKUP([Employee Name]@row, {Employee Rates Range}, 3)

Makes data from other column in the range visible in the sheet

VLOOKUP([Employee Name]@row,

{Employee Rates Range}, 3)

VLOOKUP Breaks by Inserting

a column or Moving column in

the source sheet

#6 VLOOKUP and INDEX MATCH

INDEX/MATCH

● INDEX/MATCH works even if you move columns around or insert

columns in the source sheet

● INDEX/MATCH only references the index column and return column.

It won’t make data from other column visible in the destination sheet

VLOOKUP

● If your solution doesn't need the above advantages, you can use

VLOOKUP, since it is a bit easier to learn

● You can quickly hit the 25,000 limit since it requires to select the

columns in between

#7 @cell

● Performs calculations in formulas that look at ranges such as

COUNTIF and SUMIF

● Used in place of criteria of formula

● Performs calculation on each row at the same time that the

primary function is evaluating the criteria in the range, making

formula more eﬃcient

Before

=SUMIF(Month:Month,3,[Opportunity Total]:[Opportunity Total])

After

=SUMIF([Close Date]:[Close Date],MONTH(@cell) = 3,[Opportunity

Total]:[Opportunity Total])

Dynamic

=SUMIF([Close Date]:[Close Date],MONTH(@cell) =

MONTH(TODAY()),[Opportunity Total]:[Opportunity Total])

Manage Errors

=IFERROR(SUMIF([Close Date]:[Close Date],MONTH(@cell) =

MONTH(TODAY()),[Opportunity Total]:[Opportunity Total],”Missing Date”)

#8 @row

● Automatically changes cell reference in a column

● Used in place of a cell reference: avoid formulas to recalculate —

improves performance with automatic changing of cell references

in a column for a large number of rows else it recalculates

every time

● Result: better sheet performance

● Useful if you have to copy the formulas

Stage1 = Stage@row

#9 Sheet Summary: Use one standard location on the

periphery of your sheet to store summary data, project-

and org-level KPIs, and general information

Smartsheet users work with a variety of information

TASK ASSIGNED DUE DATE STATUSCOSTS

Your primary project data

live in your sheets

But your projects are part

of a broader ecosystem

For greater visibility and accountability, your team needs the

context of KPIs, summary and portfolio data, and general information

Storing additional info can be a challenge

It must conform to column

data types

TASK ASSIGNED DUE DATE STATUSCOSTS

It might get filtered out of view

There isn’t a natural place for

it in card and calendar

views

Reporting can require

complex solutions using cell

links and multiple roll-up

sheets

Sheet Summary frees up your rows and time

Keep important info on

the periphery of your

sheet where column

types, sheet view, and

filters won’t affect it...

...but where it will remain

connected to your

project data in real time

TASK ASSIGNED DUE DATE STATUSCOSTS

Sheet Summary

Project Charter

Key Stakeholders

Quarterly Budget

Project Costs to Date

Remaining Funds

Key Project Dates

Project Health

ƒx =SUM(COSTS:COSTS)

#10 Lock Column to avoid breaking the solution

=IF(Complete@row = 1, "Gray",

IF([Due Date]@row >= TODAY(+7), "Green",

IF([Due Date]@row >= TODAY(+3), "Yellow",

"Red")))

Call To Actions

1. Use TODAY() only once in a sheet or reference TODAY()

from other sheet

2. Minimize the number of unique Range References

3. Create Range References referred to by multiple formulas

4. Use Move Row Automation to automatically move rows

from a source sheet to a destination sheet at the right

moments

5. Select complete columns when creating cross-sheet

reference to account for additional rows added to the sheet

Call To Actions

6. Use INDEX MATCH instead of VLOOKUP, when possible

7. Use @cell for efficient use of formulas

8. Use @row for better sheet performance

9. Use Sheet Summary for one standard location on the

sheet to store summary data, project and org-level KPIs

10. Lock Column to avoid breaking the solution

Q&A