Best way to speed up Smartsheets

Lucas Rayala · 03/16/21

Hi, our current tracking tools are too complicated for Smartsheets and things are slowing down. I'm not sure the best place to start to speed things up. We have about seven inter-related pages, including: one main master page that we source our static variables from, a few reports (these are sheets where I paste in data from other databases) that we source non-static information, a very complex sheet we use for our main working dashboard (although it is not a "Dashboard", it's just a sheet), and a few ancillary pages that we use as tools. Frankly Smartsheets is not the appropriate tool to track all this, but this process is going away in a year and I need to keep things going until then. Can someone give me a bullet list detailing how to best go about troubleshooting to optimize a Smartsheets ecosystem?

I have implemented a lot of complicated, likely resource-heavy formulas to implement our business logic, and we have a LOT of conditional formatting. I have a co-admin and I saw that they had recently added PAGES of new conditional formatting logic.

My co-admin and I have different philosophies regarding report usage as well. I am pro-reports because it keeps people off our main page and simplifies things for them (our main page has about 100 columns--most hidden, but it's still very big). I think the co-admin believes that reports slow things down. Ideas?

@Paul Newcome? You seem to be the resident guru here :)

Lucas Rayala · 12/05/21

I ended up simplifying my formulas, reducing/consolidating the number of conditional formats, and (the final working solution) creating an automated archiving system to reduce the number of rows. Because I need to do real-time checks on all past manufacturing lots, I created one archive that updates as soon as anything is loaded into the main sheet so I have an always-up-to-date list of manufacturing lots to reference. I have a main archive, however, that materials are moved to after they've been reviewed by our QA department and released to the manufacturing floor. The archiving reduced the rows from 2000+ to about 200, so the sheet doesn't have any issues running. Which is good, because we've continued to increase our reliance on this sheet for business functions, which in turn means our formulas and calculations continue to increase.

Andrée Starå · 03/16/21

Hi @Lucas Rayala

I hope you're well and safe!

I would start looking at the formula structure.

Update @row and @cell where possible if you haven't already.

I'd be happy to take a quick look.

Can you maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Lucas Rayala · 03/16/21

Hi Andree! All formulas are column formulas, so they all include @row/@cell references. I'll see if I can make a copy of the main spreadsheet and put some generic information in there.

Lucas Rayala · 03/16/21

Andree, do you have a quick answer regarding the report functionality? Does a simple report referencing one sheet slow down that sheet?

Andrée Starå · 03/16/21

@Lucas Rayala

As far as I know, reports don't affect the sheets in any way. It's just another view of a sheet or multiple sheets.

Make sense?

Marvin Daniels · 03/16/21

Hello! We have a sheet we use to track member payments. At around 5k rows it started slowing down and the form to submit new payments stopped working. I reported it because this sheet and the related resources are used by about 30 users every day and suddenly everything came to a halt.

Smartsheet reviewed my sheet and suggested the following: simplify conditional formatting (I admit it was a little out of control with about 30 rules) and replace VLOOKUP formulas with INDEX/MATCH formulas (we're doing numerous lookups from another sheet to pull assigned resources, client name, etc.).

Neither of these things solved my problem, so I got creative and created some additional sheets to help mitigate the row overload on the primary sheet. The two most active clients (S24, FF1) each got moved to their own sheets. Automation was put in the place to move appropriate rows when either of those two client numbers were entered by one of our clerks on the primary sheet. I hadn't used that the move automation previously, but works like a charm and led to the next change.

Also, I created an separate intake sheet to accept new payments via a form. As the primary sheet grew in row count, the original form became incredibly slow - maybe taking 30-45 seconds to submit each entry. Now payments are added to this intake sheet and moved once an hour using automation. Each payment submitted now probably takes 2 seconds.

All of this moving records got me thinking, so I added a payment rejection routine. When a user in our Eligibility department finds a check that has been routed incorrectly, they enter reject as the client number and automation moves that row to the rejection sheet, which will trigger notification to our Accounting department that they have a rejected check to review.

Below is my workflow diagram for record movement if a visual helps.

Marvin - Member Payment Processing - Record Movement.jpg

Paul Newcome · 03/17/21

@Lucas Rayala Conditional formatting will also have a large impact on sheet speeds.

Sheryl P · 03/18/21

@Lucas Rayala - We too struggle with the limits/limitations of large sheets 5000 + rows, 90 columns. Now that I've used SmartSheet long enough to understand the tool, as well as how my organization tends to use it -I would sit down and redesign "component" sheets, or use the move functionality that @Marvin Daniels describes. I have not heard that reports slow anything down - but yes conditional formatting, cross reference formulas, and we were told at one point having many/large attachments can slow things down. We were advised to update Chrome as we were a version or to back.

Anyone know where automations or datamesh jobs impact utilization? Does anyone at Smartsheet have a white paper of the topic of performance? Or perhaps examples of processes - "do this way" "not that way" that we may not even think about. Anything when you dig into an account map that helps identify bad processes or redundancies?

Thank you.

Sheryl

skarkhoff · 04/05/21

I'm in the same boat! I have a sheet that has a lot going on, DataMesh, Dynamic View input, cross-sheet formulas, conditional formatting, and a number of columns with "in sheet" formulas (5 of which were created because we ran out of space in one column and had to separate the formula out).

I would love insight into what functions slow things down more than others! There are times when it seems like I can barely scroll without lag time.

@Marino Anderson · 08/30/21

Please do not store banking / payment / personal info in Smartsheet.

Being a collaborative system, it is not PCI DSS compliant.

Spend the money to store this type of data in the appropriately certified systems that scale and protect and are based on purpose designed databases, business rules, reporting, user access, and interfaces.

Lucas Rayala · 12/05/21

I ended up simplifying my formulas, reducing/consolidating the number of conditional formats, and (the final working solution) creating an automated archiving system to reduce the number of rows. Because I need to do real-time checks on all past manufacturing lots, I created one archive that updates as soon as anything is loaded into the main sheet so I have an always-up-to-date list of manufacturing lots to reference. I have a main archive, however, that materials are moved to after they've been reviewed by our QA department and released to the manufacturing floor. The archiving reduced the rows from 2000+ to about 200, so the sheet doesn't have any issues running. Which is good, because we've continued to increase our reliance on this sheet for business functions, which in turn means our formulas and calculations continue to increase.

Best way to speed up Smartsheets

Best Answer

Answers

Help Article Resources

Categories