Too many functions used in MS Excel?

Question

I'm hoping someone out there can help me. I've got a spreadsheet that numbers a list of steps. There are several sections to the steps and each section has a section number (1, 2, 3, etc.). This number is listed in a cell and I'm using the following function to generate a numbering scheme of the form <sectionNum>.<stepNum>, where <stepNum> = 1 for the first step in each section.

I'm using the following function to auto-generate this listing:

=CONCATENATE($A$22,".",(ROWS($A$22:A97)-2))

Where $A$22 is the cell containing the section number (static text – no formula there) and A97 is the current cell to be numbered. In this case, there is a row containing a comment that is not to be numbered, so I subtract 2 so that the first <stepNum> = 1.

This worked very well for part-way through file. Then I hit another comment row and had to adjust the formula to subtract 3 instead of 2, to continue numbering. However, as soon as I tried to change the formula, the cell began acting like the data was text instead of a formula. I've checked to ensure there's not a ' at the beginning of the line and tried playing with the different formats for the cell, but nothing changes. I can't get it to evaluate the formula. Not only that, but I can't get any other formula I type into a cell to work, either. All show the same text version of the formula.

I'm trying to figure out whether I may have exceeded a limit of functions. I have gotten up to section 6 before hitting this problem and the number of cells using the formula for each section is:

1: 5
2: 4
3: 3
4: 1
5: 2
6: 79

The problem with worrying about whether I've gone over the usage limit is that Excel will still let me copy and paste an existing cell (which is still evaluating the function) into a new cell and evaluate it just fine. But it will switch to just showing the formula text even if I just COPY the function from the formula bar (or the cell contents, instead of the whole cell) and haven't pasted it anywhere yet. Pasting – or even just typing – the exact same function into another cell results in it just showing the formula text again. Editing the formula in any cell (even one that was previously working) results in the cell displaying the formula as text. And all I'm trying to do is replace that -2 with a -3!

I've used general numbering formulas (just adding 1 or 2 to the previous cell's content to get the current cell's content) and have been able to go several hundred rows deep with no trouble, so I'm somewhat skeptical that I have, in fact, hit a limit, but I want to rule that out, if possible.

I'm running MS Excel 2013 on a 64-bit Windows 10 OS. The file is on my desktop, so there shouldn't be any network-related issues. The only cells with formulas are the ones I've numbered – everything else is static text. There is a lot of color-coding of cells but, otherwise, it's a pretty plain-Jane spreadsheet.

I've tried entering formulas on other tabs with no luck. I've tried quitting and restarting Excel with no luck. I've tried rebooting my computer with no luck. I can't upgrade to a later version of Excel because this is a work computer and all updates have to come through IT – I don't expect the next one any time soon.

I'm just a little over 100 rows into this document and I expect it to be well over 1000 rows by the time I'm done. I shudder to think I'll have to manage all the numbering manually.

Answer 1

I have Excel sheets with more than twelve millions of formulas; I don't think there is any limit.

Your problem is probably that the cell format is set to 'Text', and whatever you enter is treated as text. Change the format to 'General', and edit the content afterwards (add an x and remove it), and it should work. Alternatively, you can copy a working formula into the field (which copies the format too), and edit it as needed.

Answer 2

100 rows are nowhere near the limit. Each speadsheet can have 1,048,576 x 16,384 cells, and there's no limit on the number of sheets in a workbook, so you can have billions, trillions (or even more) of formulas in your workbook.

Regarding your question, it's too broad. Without a demonstration I don't think anyone can answer it. But if the data automatically changes type when typing or copying then I think it's because some add-in misbehaved

---

As a side note, why do you use CONCATENATE? I've never find that I need to use it in my life. Change the formula to

=$A$22 & "." & (ROWS($A$22:A97)-2))

This is recommended by Excel:

Best practices

• Do this:

  Use the ampersand & character instead of the CONCATENATE function.

• Description:

  The ampersand (&) calculation operator lets you join text items without having to use a function.

  For example, =A1 & B1 returns the same value as =CONCATENATE(A1,B1). In many cases, using the ampersand operator is quicker and simpler than using CONCATENATE to create strings.

https://support.office.com/en-us/article/CONCATENATE-function-8f8ae884-2ca8-4f7a-b093-75d702bea31d

Another important note

Important: In Excel 2016, Excel Mobile, and Excel Online, this function has been replaced with the CONCAT function. Although the CONCATENATE function is still available for backward compatibility, you should consider using CONCAT from now on. This is because CONCATENATE may not be available in future versions of Excel.