Sheet Size

Jeremy Michels

I have sheets that vary in size but most lean to the larger size due to the amount of formulas in the sheet. I am looking to see if on average people are choosing to have more sheets and less size or less sheets and fill the sheets to the top. I have done alot of work to all of my sheets and some have had to be divided. If you are having greater ease of success in the smaller sheets are you doing alot of work in many sheets to keep your system flowing. The amount of linking would be extensive for me and not sure if I want to make the change. 

PPS Solutions

Hi Jeremy, it varies, we have some clients with small 50 task row sheets x several hundred sheets to clients with less than a dozen sheets with almost a thousand task rows all heavily interconnected with formulae.   My instinct says that  several hundred sheets is probably a waste and searching for individual projects amongst a large set can be a pain even when using the search facilities.  The eg 50 task rows scenario which lists standard tasks "per job" perhaps ought to be dealt with via 50 columns representing those tasks with a job/project per row.  All depends on customer confidentiality and collaboration requirements as to which approach is best.

JamesR

Sheet size is an objective issue.  Firstly you need to understand your potential data size as each Smartsheet has limits that it can hold.  These limits are quitr large, however are physical.  The ability to utilise the full physical limists is greatly influenced by two major components.

• Formulae
• Conditional Formatting
• With Formulae the more formulae you use and the more complexed they are (nested multiple times and or related to other formulae) the sooner you will start to see performance issues.  There is no way of establishing when you are getting too big excepet in lower performance, updates, saves and refreshes.

With Conitional Formatting this is also a consideration.  Highlighting full rows rather than certain cells has a higher overhead and can cause performance issues.

 

Combine the both together then the physical limit is "pie in the sky".

 

Therefore, know your data, know your potential datasize and know your need for both Formulae and conditional formatting and cut your cloth to suit your needs.

 

Good luck

Jeremy Michels

The question still remains what is th best practice. I think in this issue that is varies to the needs of each user. The one thing that I have experienced is the process I have gone through. I just dove into the sheets and learned and changed and learned and changed. I could see it beig very helpful for Smartsheet to have a system in place to help guide in knowing what is the best fit for your system. If I had known certain things at the beginning I would have saved massive time. A form that could be filled out and have it generate a best practice direction on what type of work flow would be amazing. Most of us that are living in Smartsheet are more aware of what to do. But how long did it take to get there. I have massive sheets that we are having to divide constantly to keep them working. The factor for us is not the sheet size ability as much as the use with other apps. The interface between the two can overwhelm the software. Thanks for the input. 

JamesR

Its all about design, planning and business objective. Start with a "Pen and Paper" approach.

I also use a data entity table to quantify the data needs. Also have a clear understanding of what the output from your system should look like and or do.

Be prepared to create a prototype to test your ideas with the need to start again with what you have learnt the pros and cons.

 

Chris Winfield

I agree with you Jeremy.

 

For those who are used to working with this kind of system or databases in general the principle behind a "pen and paper" structural exercise is fine and clear. However, what is less clear is which general approach and structure might be best suited for different ways of working with smartsheet specifically. 

 

A set of "best practice" sheet structures for different purposes would be really useful. 

 

Even better would be a set of best practice template "sheet collections" including pre-linked cells. For instance a master project summary sheet linked to 20 project sheet templates. The number of templates (with pre-linked cells) could even be user determined.

JamesR

Chris,

There are a range of Templates available to browse and use if suitable.  With regard to a Sheet collection with links I agree thy to would be useful, however have been impractical as this would have had t be done in a worksheet and they have only reciently rolled ut the ability to Save as New Worksheets and Folders.  The only caviate I would put is like nything that you get "Off The Shelf" they inevitably require tweeking to suit your particular situation, but in principle I agree, and Smartsheet may now expand thier range of templates accordingly.

Jim Hook

My Smartsheets are generally small by some standards, mainly less than a thousand rows and maybe a dozen or so columns. At that size I rarely experience performance problems. However, on several occasions I have run into the 5000 incoming link limit per sheet forcing me to modify an approach to a particular application. 

Dan McCoy

Jeremy,

 

We ran into the issue of making and remaking sheets over and over until eventually we found a happy medium. We primarily use templates that were created by first writing down all the processes, sub-processes, and sub-sub-processes (is that a word?) and from there created our templates. The templates are the processes and any relevant tasks assigned to that process take place within that particular sheet. We wanted it to be detailed but not so detailed that it became more work to use Smartsheet (we are trying to become more efficient by using the program after all). What we ended up doing is putting some of the more fine details into word documents and made them attachments to the templates, sort of a standard operating procedures manual in each template. That way when the template is copied into a new workspace the document is always there. This is primarily intended as new people join the team they have a "manual" to refer to while the veterans already know what goes into the task. I would say on average our templates are no more than 50 lines even with heavy use of logic formulas.

Jeremy Michels

I would love to see Some tools set per industry. Really refine for the user. I have asked before for a form that would prebuild your base sheets when you sign up. Business data link added to streamline the upload. Pre designed sheets per your personal form to get you running. 

John Sauber

We have many modest-sized sheets (<500 rows, <10 columns, no dependencies, basic hierarchy), and these perform responsively.

 

We also have one sheet which is >1200 rows, >30 columns and >1000 dependencies. In our specific case, cell linking across this information broken up into smaller sheets is a non-starter due to our dependency structures. This large sheet is much more laggy than most sheets, especially on older machines, and especially on Internet Explorer (Windows). For this reason, we have rolled out Chrome to the organization for enhanced Smartsheet performance, and it's very noticeable. We also get around this by hiding most of these columns at the administrative level, and showing them in reports, instead.

 

Additionally, in our large sheet, we have some formulas which are >250 characters long, and we are constantly shuffling the row order. We have experienced, and continue to experience, bugs with certain large formula types not following rows as they move around, so when things grow to a great complexity, some things seem to become a little more unstable in Smartsheet.

 

Additionally, if there are simultaneous edits to large sheets with many rows being moved around by different users, we have experienced frustrating issues with needing to refresh before making any changes, as not doing so will cause data overwrites for edits which have been made and saved by other individuals. Since we make a lot of edits all of the time, needing to refresh before each one is a chore.

 

Overall, I would say that our large sheet is benefiting us a great deal, but it comes at a fair price with respect to stability, and simultaneous edit usability.

JGL

I thought I read somewhere (or maybe dreamt it) that SS was working on better support for large sheets. In our case, we have a very large Excel sheet that we are using to update the status on inventory. As everybody here knows, Excel's sharing functionality is crap, so we are having a lot of data integrity problems.

The size of the file varies, but can be over 8000 lines with some vlookups and formula. Managing this in one SS is not feasible. I tried splitting the sheets into smaller chunks, but anything over about 2000 lines seemed to be slow and laggy. The next step would be to break it down into even smaller chunks and use a template to maintain consistency across the sheets. But even doing this, some chunks would be well over 1000 lines, which doesn't seem to sit well with SS.

Travis

Hi JGL, we are working to improve the scalability of sheets and I will pass your feedback along to our product team!

JGL

Still having major issues with even moderately sized sheets. I just had to break apart a sheet with 250 rows x 20 columns. It had just a few linked cells, half a dozen formulas and some minor conditional formatting. In the middle of a copy down or other simple operation the tab would hang and, in some cases, freeze the browser entirely.

 

I am forced to use MS Exploder because that is all our IT dept has approved for use, but even at home on my faster machine using Firefox, the interface was very laggy. I haven't tried Chrome yet. Will do that tonight from home to see if it makes a difference at all.

 

This is disappointing because the site has some major advantages over multiple shared Excel files, but I'm going to have a very tough time even demonstrating how these sheets work to collaborating users if I have to keep killing my browser and/or refreshing the screen.  

Jim Hook

You should not be having trouble with a sheet that size. I use several every day that are in the range of 250 rows and 25 columns with hundreds of incoming and outgoing links and thousands of fairly complex formulas. I do have one sheet that is very slow to save and it has about 1400 rows and 31 columns. There are several hundred nested IF formulas with a lot of references and that turns out to be the root cause of the slowness. Anytime you insert or delete a row near the top of the sheet, for example, it has to edit all the cell formulas in the sheet that have references to the area below the change. Simply adding or deleting a row near the top can result in a 45 second save time using Chrome while it adjusts all the formula references. If I just make some edits without moving a row around the sheet saves in about three seconds. I use this sheet to keep track of monthly revenue on active projects. At the end of a project I make a copy of the first row of the project and it's 14 children rows full of formulas and paste it into the archive area near the bottom of the sheet. Then I copy/paste-special-values the rows just copied to save the final values but not the formulas. Eliminating no-longer-used formulas near the bottom of the sheet did improve things quite a bit.

JGL

It is heartening to know that this works for some people!

 

It is baffling. I'm assuming it is some combination of browser misbehavior/incompetence and my company network firewall being overly protective/slow.

 

My formulas are not that complicated. This is the longest one:

=ROUND([Thickness (in)]1, 3) + " " + [Min-Nom]1 + " X " + [Width (in)]1 + IF(SteelForm1 = "Coil", " X C", [Length (mm)]1)

There are 6 similar columns with formulae x 250 rows.

But the problems don't necessarily occur when I'm performing an operation that impacts these cells. It can be a totally unrelated operation that sets it off.

 

I will play around with this on Chrome at home tonight to see if there are any significant differences in speed.