Sane way to edit with Excel formulas?
Is there any sane way to work with large and complicated Excel formulas?
For a simple =IF(A1>B2);C3;C4 or something similar, just having it chained behind each other is OK, but if we are getting into lengthy statements¹, this gets unsightly, and more importantly, hard to follow real quick.
E.g. =IF(A3=OFFSET(A3;1;1);IF((E3>50);D3;IF((E4>50);D4;""));J8) is really annoying to decipher, or worse, to fix.
How can I make this more readable?
Splitting it up and indentation helps, and with alt + enter I can add new lines, which can even begin with spaces, so the above becomes
=IF(A3=OFFSET(A3;1;1);
IF((E3>50);
D3;
IF((E4>50);
D4;
""));
J8)
which is much better. However, it's still lacking. I have to add all spaces by hand (and without tab=4 spaces²), so some autoindent like in about every editor would be helpful. But OK, I can work 80's style if I must, but why on earth is the font not monospaced? Who thought that was a sane idea? And what about comments? And code highlighting? Somehow I can't believe that MS flagship product is stuck in the past³.
So how do I get (some) of the above helpers, if that's possible at all?
¹ Yes, I know, Excel might be the wrong tool for that, but I'm probably not the only person that is wrangling with some historic Excel sheets that have long grown to sizes that even some dedicated database software can't handle anymore
² I'm used to python, so just see this as an example, and I'm well aware of the tabs vs. spaces discussion
³ OK, I lied, I can totally believe that. Maybe Office 1080 will have an instance of Chrome running in the formal box for this purpose…
microsoft-excel worksheet-function text-editors user-interface
asked Feb 14 at 13:14
JC_CLJC_CL
1141
add a comment |
Is there any sane way to work with large and complicated Excel formulas?
For a simple =IF(A1>B2);C3;C4 or something similar, just having it chained behind each other is OK, but if we are getting into lengthy statements¹, this gets unsightly, and more importantly, hard to follow real quick.
E.g. =IF(A3=OFFSET(A3;1;1);IF((E3>50);D3;IF((E4>50);D4;""));J8) is really annoying to decipher, or worse, to fix.
How can I make this more readable?
Splitting it up and indentation helps, and with alt + enter I can add new lines, which can even begin with spaces, so the above becomes
=IF(A3=OFFSET(A3;1;1);
IF((E3>50);
D3;
IF((E4>50);
D4;
""));
J8)
which is much better. However, it's still lacking. I have to add all spaces by hand (and without tab=4 spaces²), so some autoindent like in about every editor would be helpful. But OK, I can work 80's style if I must, but why on earth is the font not monospaced? Who thought that was a sane idea? And what about comments? And code highlighting? Somehow I can't believe that MS flagship product is stuck in the past³.
So how do I get (some) of the above helpers, if that's possible at all?
¹ Yes, I know, Excel might be the wrong tool for that, but I'm probably not the only person that is wrangling with some historic Excel sheets that have long grown to sizes that even some dedicated database software can't handle anymore
² I'm used to python, so just see this as an example, and I'm well aware of the tabs vs. spaces discussion
³ OK, I lied, I can totally believe that. Maybe Office 1080 will have an instance of Chrome running in the formal box for this purpose…
microsoft-excel worksheet-function text-editors user-interface
asked Feb 14 at 13:14
JC_CLJC_CL
1141
2
I think you answered it yourself, Excel is a spreadsheet application, not a programming tool. I copy long formulas to notepad++ to split them when I really need to.
– Máté Juhász
Feb 14 at 13:19
1
True, but it does contain a full programming environment, so having some additional tools for that seem logical. And some already exist. I.e. I can get cells color coded (instead of keywords) and it can tell me which part of a formula does what, when I click on it, so the basics are already there. It just seems very inconsequential.
– JC_CL
Feb 14 at 13:28
I think you might be happier writing and applying these formulas in VBA. It has a more 'coding' interface and allows for commenting, tab spacing, and monospaced fonts.(Not sure about code highlighting).
– Emily Alden
Mar 5 at 19:35
add a comment |
Is there any sane way to work with large and complicated Excel formulas?
For a simple =IF(A1>B2);C3;C4 or something similar, just having it chained behind each other is OK, but if we are getting into lengthy statements¹, this gets unsightly, and more importantly, hard to follow real quick.
E.g. =IF(A3=OFFSET(A3;1;1);IF((E3>50);D3;IF((E4>50);D4;""));J8) is really annoying to decipher, or worse, to fix.
How can I make this more readable?
Splitting it up and indentation helps, and with alt + enter I can add new lines, which can even begin with spaces, so the above becomes
=IF(A3=OFFSET(A3;1;1);
IF((E3>50);
D3;
IF((E4>50);
D4;
""));
J8)
which is much better. However, it's still lacking. I have to add all spaces by hand (and without tab=4 spaces²), so some autoindent like in about every editor would be helpful. But OK, I can work 80's style if I must, but why on earth is the font not monospaced? Who thought that was a sane idea? And what about comments? And code highlighting? Somehow I can't believe that MS flagship product is stuck in the past³.
So how do I get (some) of the above helpers, if that's possible at all?
¹ Yes, I know, Excel might be the wrong tool for that, but I'm probably not the only person that is wrangling with some historic Excel sheets that have long grown to sizes that even some dedicated database software can't handle anymore
² I'm used to python, so just see this as an example, and I'm well aware of the tabs vs. spaces discussion
³ OK, I lied, I can totally believe that. Maybe Office 1080 will have an instance of Chrome running in the formal box for this purpose…
microsoft-excel worksheet-function text-editors user-interface
asked Feb 14 at 13:14
JC_CLJC_CL
1141
Is there any sane way to work with large and complicated Excel formulas?
For a simple =IF(A1>B2);C3;C4 or something similar, just having it chained behind each other is OK, but if we are getting into lengthy statements¹, this gets unsightly, and more importantly, hard to follow real quick.
E.g. =IF(A3=OFFSET(A3;1;1);IF((E3>50);D3;IF((E4>50);D4;""));J8) is really annoying to decipher, or worse, to fix.
How can I make this more readable?
Splitting it up and indentation helps, and with alt + enter I can add new lines, which can even begin with spaces, so the above becomes
=IF(A3=OFFSET(A3;1;1);
IF((E3>50);
D3;
IF((E4>50);
D4;
""));
J8)
which is much better. However, it's still lacking. I have to add all spaces by hand (and without tab=4 spaces²), so some autoindent like in about every editor would be helpful. But OK, I can work 80's style if I must, but why on earth is the font not monospaced? Who thought that was a sane idea? And what about comments? And code highlighting? Somehow I can't believe that MS flagship product is stuck in the past³.
So how do I get (some) of the above helpers, if that's possible at all?
¹ Yes, I know, Excel might be the wrong tool for that, but I'm probably not the only person that is wrangling with some historic Excel sheets that have long grown to sizes that even some dedicated database software can't handle anymore
² I'm used to python, so just see this as an example, and I'm well aware of the tabs vs. spaces discussion
³ OK, I lied, I can totally believe that. Maybe Office 1080 will have an instance of Chrome running in the formal box for this purpose…
microsoft-excel worksheet-function text-editors user-interface
microsoft-excel worksheet-function text-editors user-interface
asked Feb 14 at 13:14
JC_CLJC_CL
1141
asked Feb 14 at 13:14
JC_CLJC_CL
1141
asked Feb 14 at 13:14
JC_CLJC_CL
1141
asked Feb 14 at 13:14
JC_CLJC_CL
1141
asked Feb 14 at 13:14
JC_CLJC_CL
1141
1141
2
I think you answered it yourself, Excel is a spreadsheet application, not a programming tool. I copy long formulas to notepad++ to split them when I really need to.
– Máté Juhász
Feb 14 at 13:19
1
True, but it does contain a full programming environment, so having some additional tools for that seem logical. And some already exist. I.e. I can get cells color coded (instead of keywords) and it can tell me which part of a formula does what, when I click on it, so the basics are already there. It just seems very inconsequential.
– JC_CL
Feb 14 at 13:28
I think you might be happier writing and applying these formulas in VBA. It has a more 'coding' interface and allows for commenting, tab spacing, and monospaced fonts.(Not sure about code highlighting).
– Emily Alden
Mar 5 at 19:35
add a comment |
2
I think you answered it yourself, Excel is a spreadsheet application, not a programming tool. I copy long formulas to notepad++ to split them when I really need to.
– Máté Juhász
Feb 14 at 13:19
1
True, but it does contain a full programming environment, so having some additional tools for that seem logical. And some already exist. I.e. I can get cells color coded (instead of keywords) and it can tell me which part of a formula does what, when I click on it, so the basics are already there. It just seems very inconsequential.
– JC_CL
Feb 14 at 13:28
I think you might be happier writing and applying these formulas in VBA. It has a more 'coding' interface and allows for commenting, tab spacing, and monospaced fonts.(Not sure about code highlighting).
– Emily Alden
Mar 5 at 19:35
2
2
I think you answered it yourself, Excel is a spreadsheet application, not a programming tool. I copy long formulas to notepad++ to split them when I really need to.
– Máté Juhász
Feb 14 at 13:19
I think you answered it yourself, Excel is a spreadsheet application, not a programming tool. I copy long formulas to notepad++ to split them when I really need to.
– Máté Juhász
Feb 14 at 13:19
1
1
True, but it does contain a full programming environment, so having some additional tools for that seem logical. And some already exist. I.e. I can get cells color coded (instead of keywords) and it can tell me which part of a formula does what, when I click on it, so the basics are already there. It just seems very inconsequential.
– JC_CL
Feb 14 at 13:28
True, but it does contain a full programming environment, so having some additional tools for that seem logical. And some already exist. I.e. I can get cells color coded (instead of keywords) and it can tell me which part of a formula does what, when I click on it, so the basics are already there. It just seems very inconsequential.
– JC_CL
Feb 14 at 13:28
I think you might be happier writing and applying these formulas in VBA. It has a more 'coding' interface and allows for commenting, tab spacing, and monospaced fonts.(Not sure about code highlighting).
– Emily Alden
Mar 5 at 19:35
I think you might be happier writing and applying these formulas in VBA. It has a more 'coding' interface and allows for commenting, tab spacing, and monospaced fonts.(Not sure about code highlighting).
– Emily Alden
Mar 5 at 19:35
add a comment |
0
active
oldest
votes
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1405688%2fsane-way-to-edit-with-excel-formulas%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1405688%2fsane-way-to-edit-with-excel-formulas%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
2
I think you answered it yourself, Excel is a spreadsheet application, not a programming tool. I copy long formulas to notepad++ to split them when I really need to.
– Máté Juhász
Feb 14 at 13:19
1
True, but it does contain a full programming environment, so having some additional tools for that seem logical. And some already exist. I.e. I can get cells color coded (instead of keywords) and it can tell me which part of a formula does what, when I click on it, so the basics are already there. It just seems very inconsequential.
– JC_CL
Feb 14 at 13:28
I think you might be happier writing and applying these formulas in VBA. It has a more 'coding' interface and allows for commenting, tab spacing, and monospaced fonts.(Not sure about code highlighting).
– Emily Alden
Mar 5 at 19:35