Explanation of "Too Many Cells Referenced" Error - 25M Restriction

Lucas Rayala

Just wanted to take a minute to share a quick analysis regarding the "Too many cells referenced" error. Smartsheet limits your cell references to 25M within a single sheet. Depending on the size of your sheet, that is either plenty of references or not nearly enough.

The Crux of the Issue

The main issue is the use of column formulas that reference other columns. Consider this situation, where the formula column ("New") checks if boys names popular in 2021 ("2021") have been popular for the last decade ("Decade"):

=IF(CONTAINS([2021]@row, Decade:Decade), "No", "Yes")

This table is made of 10 rows, so the column formula is generated 10 times, one for each row. You have two reference types in this column formula; an @row reference ("[2021]@row") and a column reference ("Decade:Decade").

• In the column formula, "[2021]@row" generates 10 cell references, because each of the 10 formulas refers to the single 2021 entry on that row (10 formulas x 1 entry = 10 references).
• In contrast, the "Decade:Decade" portion of the formula generates 100 reference, because each of the 10 formulas refer to each of the 10 entries in the Decade column (10 formulas x 10 entries = 100 references).

Takeaway: @row cell refences grow linearly--use as many as you want. column:column references grow exponentially -- these are what will restrict your page. Again, as your number of rows increase, the number of cells referenced grows exponentially.

Below is a chart that breaks this down by sheets with a given number of rows.

• "Rows": rows in a sheet
• "Cell References": how many cells a single column:column refence will reference in a column formula.
• "# of Refs Allowed": how many column:column references you can make on a sheet with that number of rows

As you can see, at 100 rows you can use as many column:column references as you please. However, once you get to 5,000 rows, you essentially can't use column:column references in a column formula. A single column:column reference will blow through your entire allotment of 25M cell references and Smartsheet will not allow you to save until you fix this. Because of this restriction, for sheets that require heavy formula usage, I archive my data so that I keep under about 300 rows in a given sheet.

Paul H

Thank you for that explanation. Ran into this road block a couple of week back while analyzing a data set, good to start understanding why.

The sheet is currently @ 9645 rows with one column formula working. In that column only 3648 return a value the rest are blank.

Leibel S

@Paul Newcome @Genevieve P.

Does this 25M limitation include cross sheet referenced cells?

Paul Newcome

I believe it does although there is the limit on how many cross referenced cells you can have in general. Isn't it something like 100,000 cells or 100 unique references (whichever comes first)? 100,000 is barely a drop compared to 25,000,000.

Leibel S

@Paul Newcome

the 100,000 cell limit on cross sheet references is more of a loading data limit (as opposed to a calculation limit). For example: I can have a cross sheet reference bring in 10,000 cells and reference it in 20 columns.

At this point I find the 25M limit a a much bigger problem.

Paul Newcome

@Leibel Shuchat I think the 100,000 limit is more 100,000 different cells and not necessarily how many times they are referenced.

I have run into the 25M limit a handful of times, and it always takes quite a bit of work to get around it.

Prestole

Have you verified this with testing? I have created a sheet with 20,000 rows in 10 columns. Then in the the 11th column I use =Row@Row + "_" + SUM([Col1]:[Col1],[Col2]:[Col2],[Col3]:[Col3], ... ,[Col10]:[Col10]). I apply this formula in the 11th column as a column formula.

All said and done, I do not get the "Too many cells referenced" error.

The reason I am exploring this is that I do get the error on another sheet, and I am trying to better understand techniques that I can use to prevent it. To do that I want to force the error to occur. Does anyone know of a way to determine the current count of cell references in sheet? (Not the managed references, this is all in one sheet).

Leibel S

@Prestole

The "Too many cells referenced" error, is when to calculate a given formula in a cell Smartsheet needs to evaluate more then 25M Cells.

This will generally happens in below example:

Column B formula looks up entire Column A

Column C Formula looks up entire Column B

Now for every cell in column C to calculate it needs to review every cell in column multiplied by every cell in column A.

Now imagine you add in one more formula that is looking up Column C it would need to calculate C*B*A.

This would max the limit at ~292 rows.

Lucas Rayala

@Prestole, I did a bunch of analysis at the time I wrote this but I don't know if the same kinds of limits exist, or if they've improved how they do data lookups. When I saw your example, I figured the reason you didn't get an error was just that each cell in your example does much the same thing. I assume you can write an algorithm to reduce the computation/lookups in instances like that. However, I went into a sheet with 8,000 full lines of variable data and threw a bunch of indexes at it that would require each cell in a column do several different calculations and lookups based off variable data in the @row. I set up three columns with similar formulas, but changed a few things in each so it couldn't piggyback off previous results. I eventually crashed the browser page but I never triggered the Smartsheet limit, and I should have had about half a billion cell references. I looked at Smartsheet's help pages and I don't see the error mentioned anymore. @Paul Newcome or @Genevieve P. -- am I thinking of this wrong or did something change?

Prestole

@Lucas Rayala Thanks for your quick reply.

Yeah, I wasn't sure if it was caching the result in some way in my example. It could explain why I don't see the error in my testing ground. The limit still exists in some way, but I am trying to reproduce it consistently so that I can then try to structure my sheet setup (helped columns, etc.) so that I can take advantage of any caching or similar optimizations that SmartSheet has made.

To give more context on when I am receiving the error: It is related to some nested if statements which were all calling 3 different sets of 1000 rows 5 times. I can pull the 5 column calculation out into a separate column to reduce those references to a single set of 3. I just wasn't sure if that would solve it, and I haven't had the time to test. Additionally, the column formula was performing some cumulative math based on the preceding rows. I am not sure if the conditional logic used to sum the previous rows prevents any optimizations because each sum is effectively a different set of data?

@Leibel S This is an interesting point. I will test this and see if I can make it fail predictably.

Lucas Rayala

@Prestole, @Genevieve P., @Paul Newcome, @Leibel S -- disregard my last comment -- when I went back to the page today I got the 25 million error -- @Prestole, I believe it just takes some time for the error to kick in. That's likely why you didn't see it with your test (but also potentially your calcs may be so generic they're getting handled as one, but I think that's not as likely now). I did some additional testing -- now that I've tripped the error, it seems to be doing real-time analysis of my page and I can get it to trip consistently after I go over 25M references. It's not smack on 25M, but it's close. It may be that they don't want to run this check all the time, because it takes processing power. Maybe it runs once a day for all sheets, or for sheets that have reached some computational threshold. If you get on the naughty list, they may just have it running continuously on your page. Just a guess.

Paul Newcome

@Lucas Rayala It does take time for the sheet to fully load on the back-end. The delay may have simply been that it hadn't quite fully loaded everything yet but is working faster now because of the data stored on your computer's cache and whatnot.

Lucas Rayala

@Paul Newcome -- that makes sense -- thanks for clarifying!

Jake Gustafson

@Paul Newcome - in one of your previous comments you mentioned that "...it always takes quite a bit of work to get around it." I'm living that 'dream' over the past few weeks. How did you go about working around the limitations of 25 million cell references? I've been trying to clean up formulas to reduce cell references, but haven't been able to get my Move Rows automation to be consistently successful yet. My next approach is likely to be creating some helper columns to concatenate two (or maybe three) columns into a single to then change my cell reference to this one concatenated column, rather than 2-3 individual columns. Hoping that'll allow things to work again. Any thoughts/insight would be helpful. I don't like living in the 25MM club.

https://community.smartsheet.com/discussion/comment/324818#Comment_324818

Lucas Rayala

@Jake Gustafson, I have a number of tricks I use to get around the references.

1.) To the best of my ability, I avoid using any column references within a big sheet--sometimes you can look upstream to where your data is coming from and find it's possible to perform a calculation at the row level at an earlier stage. I've even created pages that I use to perform my calculations, when necessary, then (ideally) use DataMesh to write them back, or use a cross-sheet reference to pull them in (beware the 100K barrier, of course).

2.) When you create your sheet, construct your data so it is "row level" instead of using parent/child relationships. When you create parent/child relationships that are meaningful to the logic of your page, you will probably need to create computationally-intense formulas to group all those relationships.

3.) If you have a column formula (or part of a column formula) that is calculating the same value for every cell (such as getting a "Max" value from another column), take that portion of the formula and put it in the sheet summary tab. Then reference the sheet summary formula using the "#" notation (such as "MaxValue#"). This way you only do this calculation once instead of at every cell.

To help cut down on cross-sheet references:

1.) If you have DataMesh, consider copying your reference over instead of using a formula. I've found this also helps keep your formulas from blinking/erroring out when Smartsheet hiccups. There's a slight downside in that, even with the "Immediate" update option, the DataMesh config tends to take a few minutes to update.

2.) If you are pulling a lot of data from a single source page, consider creating a helper column on the source page which concatenates all the data you need at a row level (if this is possible). Then, create a corresponding helper column on the main sheet that references that helper column. At the row level on your main sheet, refer to this helper column using @row formulas instead of creating a ton of cross-sheet references.

Paul Newcome

@Jake Gustafson Concatenation is how I usually end up getting around the limit, but even that still can present challenges or just doesn't work depending on your exact needs.