Too many functions used in MS Excel?












4















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.










share|improve this question




















  • 1





    That's way, way too much information. Sometimes the user ( you and me ) inadvertently add a character ( space for example ) to a formula and now it turns to text. Fix only the first formula. Make sure not extra spaces in the first character position = , correct -2 to -3. Copy down.

    – ejbytes
    Apr 26 '17 at 23:46











  • I have not experienced having the cell changed to text just from adding a space to a formula. And I add them all the time, for the sake of readability. I just gave it a try but it didn't seem to fix anything.

    – user722556
    Apr 27 '17 at 22:23
















4















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.










share|improve this question




















  • 1





    That's way, way too much information. Sometimes the user ( you and me ) inadvertently add a character ( space for example ) to a formula and now it turns to text. Fix only the first formula. Make sure not extra spaces in the first character position = , correct -2 to -3. Copy down.

    – ejbytes
    Apr 26 '17 at 23:46











  • I have not experienced having the cell changed to text just from adding a space to a formula. And I add them all the time, for the sake of readability. I just gave it a try but it didn't seem to fix anything.

    – user722556
    Apr 27 '17 at 22:23














4












4








4


1






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.










share|improve this 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.







microsoft-excel worksheet-function microsoft-excel-2013






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Mar 10 at 2:40









phuclv

10.5k64295




10.5k64295










asked Apr 26 '17 at 23:15









user722556user722556

232




232








  • 1





    That's way, way too much information. Sometimes the user ( you and me ) inadvertently add a character ( space for example ) to a formula and now it turns to text. Fix only the first formula. Make sure not extra spaces in the first character position = , correct -2 to -3. Copy down.

    – ejbytes
    Apr 26 '17 at 23:46











  • I have not experienced having the cell changed to text just from adding a space to a formula. And I add them all the time, for the sake of readability. I just gave it a try but it didn't seem to fix anything.

    – user722556
    Apr 27 '17 at 22:23














  • 1





    That's way, way too much information. Sometimes the user ( you and me ) inadvertently add a character ( space for example ) to a formula and now it turns to text. Fix only the first formula. Make sure not extra spaces in the first character position = , correct -2 to -3. Copy down.

    – ejbytes
    Apr 26 '17 at 23:46











  • I have not experienced having the cell changed to text just from adding a space to a formula. And I add them all the time, for the sake of readability. I just gave it a try but it didn't seem to fix anything.

    – user722556
    Apr 27 '17 at 22:23








1




1





That's way, way too much information. Sometimes the user ( you and me ) inadvertently add a character ( space for example ) to a formula and now it turns to text. Fix only the first formula. Make sure not extra spaces in the first character position = , correct -2 to -3. Copy down.

– ejbytes
Apr 26 '17 at 23:46





That's way, way too much information. Sometimes the user ( you and me ) inadvertently add a character ( space for example ) to a formula and now it turns to text. Fix only the first formula. Make sure not extra spaces in the first character position = , correct -2 to -3. Copy down.

– ejbytes
Apr 26 '17 at 23:46













I have not experienced having the cell changed to text just from adding a space to a formula. And I add them all the time, for the sake of readability. I just gave it a try but it didn't seem to fix anything.

– user722556
Apr 27 '17 at 22:23





I have not experienced having the cell changed to text just from adding a space to a formula. And I add them all the time, for the sake of readability. I just gave it a try but it didn't seem to fix anything.

– user722556
Apr 27 '17 at 22:23










2 Answers
2






active

oldest

votes


















4














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.






share|improve this answer
























  • That was what I was thinking, but this was causing me such a headache trying to figure it out I had to at least consider the possibility. I had tried to copy a working cell to a 'broken' cell, but as soon as I tried to update it, it just broke again. However, I just checked and some of the cells causing the problem (not all by a long shot) are formatted as Text. Not sure how or why (I inherited this spreadsheet), but I've changed them to General and it does seem to be helping. Thanks for the idea and cross your fingers for me that it keeps working! :)

    – user722556
    Apr 27 '17 at 22:39













  • it your file has such a huge spreadsheet then it'll be much better and efficient to use array formulas. Or better yet, use a real database

    – phuclv
    Feb 15 at 16:01



















0














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.







share|improve this answer


























  • That's not the question he asked

    – Aganju
    Apr 27 '17 at 11:56






  • 1





    True, but it's good information to have, nonetheless. I just tried it and it works on some of the early cells but, for the ones having the problem, it doesn't help.

    – user722556
    Apr 27 '17 at 22:25












Your Answer








StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














draft saved

draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1203732%2ftoo-many-functions-used-in-ms-excel%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























2 Answers
2






active

oldest

votes








2 Answers
2






active

oldest

votes









active

oldest

votes






active

oldest

votes









4














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.






share|improve this answer
























  • That was what I was thinking, but this was causing me such a headache trying to figure it out I had to at least consider the possibility. I had tried to copy a working cell to a 'broken' cell, but as soon as I tried to update it, it just broke again. However, I just checked and some of the cells causing the problem (not all by a long shot) are formatted as Text. Not sure how or why (I inherited this spreadsheet), but I've changed them to General and it does seem to be helping. Thanks for the idea and cross your fingers for me that it keeps working! :)

    – user722556
    Apr 27 '17 at 22:39













  • it your file has such a huge spreadsheet then it'll be much better and efficient to use array formulas. Or better yet, use a real database

    – phuclv
    Feb 15 at 16:01
















4














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.






share|improve this answer
























  • That was what I was thinking, but this was causing me such a headache trying to figure it out I had to at least consider the possibility. I had tried to copy a working cell to a 'broken' cell, but as soon as I tried to update it, it just broke again. However, I just checked and some of the cells causing the problem (not all by a long shot) are formatted as Text. Not sure how or why (I inherited this spreadsheet), but I've changed them to General and it does seem to be helping. Thanks for the idea and cross your fingers for me that it keeps working! :)

    – user722556
    Apr 27 '17 at 22:39













  • it your file has such a huge spreadsheet then it'll be much better and efficient to use array formulas. Or better yet, use a real database

    – phuclv
    Feb 15 at 16:01














4












4








4







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.






share|improve this answer













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.







share|improve this answer












share|improve this answer



share|improve this answer










answered Apr 27 '17 at 0:12









AganjuAganju

8,62131437




8,62131437













  • That was what I was thinking, but this was causing me such a headache trying to figure it out I had to at least consider the possibility. I had tried to copy a working cell to a 'broken' cell, but as soon as I tried to update it, it just broke again. However, I just checked and some of the cells causing the problem (not all by a long shot) are formatted as Text. Not sure how or why (I inherited this spreadsheet), but I've changed them to General and it does seem to be helping. Thanks for the idea and cross your fingers for me that it keeps working! :)

    – user722556
    Apr 27 '17 at 22:39













  • it your file has such a huge spreadsheet then it'll be much better and efficient to use array formulas. Or better yet, use a real database

    – phuclv
    Feb 15 at 16:01



















  • That was what I was thinking, but this was causing me such a headache trying to figure it out I had to at least consider the possibility. I had tried to copy a working cell to a 'broken' cell, but as soon as I tried to update it, it just broke again. However, I just checked and some of the cells causing the problem (not all by a long shot) are formatted as Text. Not sure how or why (I inherited this spreadsheet), but I've changed them to General and it does seem to be helping. Thanks for the idea and cross your fingers for me that it keeps working! :)

    – user722556
    Apr 27 '17 at 22:39













  • it your file has such a huge spreadsheet then it'll be much better and efficient to use array formulas. Or better yet, use a real database

    – phuclv
    Feb 15 at 16:01

















That was what I was thinking, but this was causing me such a headache trying to figure it out I had to at least consider the possibility. I had tried to copy a working cell to a 'broken' cell, but as soon as I tried to update it, it just broke again. However, I just checked and some of the cells causing the problem (not all by a long shot) are formatted as Text. Not sure how or why (I inherited this spreadsheet), but I've changed them to General and it does seem to be helping. Thanks for the idea and cross your fingers for me that it keeps working! :)

– user722556
Apr 27 '17 at 22:39







That was what I was thinking, but this was causing me such a headache trying to figure it out I had to at least consider the possibility. I had tried to copy a working cell to a 'broken' cell, but as soon as I tried to update it, it just broke again. However, I just checked and some of the cells causing the problem (not all by a long shot) are formatted as Text. Not sure how or why (I inherited this spreadsheet), but I've changed them to General and it does seem to be helping. Thanks for the idea and cross your fingers for me that it keeps working! :)

– user722556
Apr 27 '17 at 22:39















it your file has such a huge spreadsheet then it'll be much better and efficient to use array formulas. Or better yet, use a real database

– phuclv
Feb 15 at 16:01





it your file has such a huge spreadsheet then it'll be much better and efficient to use array formulas. Or better yet, use a real database

– phuclv
Feb 15 at 16:01













0














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.







share|improve this answer


























  • That's not the question he asked

    – Aganju
    Apr 27 '17 at 11:56






  • 1





    True, but it's good information to have, nonetheless. I just tried it and it works on some of the early cells but, for the ones having the problem, it doesn't help.

    – user722556
    Apr 27 '17 at 22:25
















0














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.







share|improve this answer


























  • That's not the question he asked

    – Aganju
    Apr 27 '17 at 11:56






  • 1





    True, but it's good information to have, nonetheless. I just tried it and it works on some of the early cells but, for the ones having the problem, it doesn't help.

    – user722556
    Apr 27 '17 at 22:25














0












0








0







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.







share|improve this answer















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.








share|improve this answer














share|improve this answer



share|improve this answer








edited Feb 15 at 16:26

























answered Apr 27 '17 at 4:38









phuclvphuclv

10.5k64295




10.5k64295













  • That's not the question he asked

    – Aganju
    Apr 27 '17 at 11:56






  • 1





    True, but it's good information to have, nonetheless. I just tried it and it works on some of the early cells but, for the ones having the problem, it doesn't help.

    – user722556
    Apr 27 '17 at 22:25



















  • That's not the question he asked

    – Aganju
    Apr 27 '17 at 11:56






  • 1





    True, but it's good information to have, nonetheless. I just tried it and it works on some of the early cells but, for the ones having the problem, it doesn't help.

    – user722556
    Apr 27 '17 at 22:25

















That's not the question he asked

– Aganju
Apr 27 '17 at 11:56





That's not the question he asked

– Aganju
Apr 27 '17 at 11:56




1




1





True, but it's good information to have, nonetheless. I just tried it and it works on some of the early cells but, for the ones having the problem, it doesn't help.

– user722556
Apr 27 '17 at 22:25





True, but it's good information to have, nonetheless. I just tried it and it works on some of the early cells but, for the ones having the problem, it doesn't help.

– user722556
Apr 27 '17 at 22:25


















draft saved

draft discarded




















































Thanks for contributing an answer to Super User!


  • Please be sure to answer the question. Provide details and share your research!

But avoid



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1203732%2ftoo-many-functions-used-in-ms-excel%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







Popular posts from this blog

flock() on closed filehandle LOCK_FILE at /usr/bin/apt-mirror

Mangá

Eduardo VII do Reino Unido