How to tell if an Excel cell has a formula or is hardcoded?
Does anyone have a special tip (non-VBA strongly preferred) to tell systematically if a cell has a hardcoded value or is a derived formula?
Am dealing with some data that has messy subtotals embedded in it and am trying to see if I can separate out the raw lines.
microsoft-excel
edited Nov 22 '11 at 18:57
sblair
11.4k64068
asked Nov 22 '11 at 18:00
YGAYGA
68961322
add a comment |
Does anyone have a special tip (non-VBA strongly preferred) to tell systematically if a cell has a hardcoded value or is a derived formula?
Am dealing with some data that has messy subtotals embedded in it and am trying to see if I can separate out the raw lines.
microsoft-excel
edited Nov 22 '11 at 18:57
sblair
11.4k64068
asked Nov 22 '11 at 18:00
YGAYGA
68961322
Does the "Show Formulas" mode (Ctrl + `) help?
– sblair
Nov 22 '11 at 18:56
It does, but I wouldn't consider it "systematic" - requires me to hand-blast away the subtotal lines.
– YGA
Nov 22 '11 at 19:51
add a comment |
Does anyone have a special tip (non-VBA strongly preferred) to tell systematically if a cell has a hardcoded value or is a derived formula?
Am dealing with some data that has messy subtotals embedded in it and am trying to see if I can separate out the raw lines.
microsoft-excel
edited Nov 22 '11 at 18:57
sblair
11.4k64068
asked Nov 22 '11 at 18:00
YGAYGA
68961322
Does anyone have a special tip (non-VBA strongly preferred) to tell systematically if a cell has a hardcoded value or is a derived formula?
Am dealing with some data that has messy subtotals embedded in it and am trying to see if I can separate out the raw lines.
microsoft-excel
microsoft-excel
edited Nov 22 '11 at 18:57
sblair
11.4k64068
asked Nov 22 '11 at 18:00
YGAYGA
68961322
edited Nov 22 '11 at 18:57
sblair
11.4k64068
asked Nov 22 '11 at 18:00
YGAYGA
68961322
edited Nov 22 '11 at 18:57
sblair
11.4k64068
edited Nov 22 '11 at 18:57
sblair
11.4k64068
edited Nov 22 '11 at 18:57
sblair
11.4k64068
11.4k64068
asked Nov 22 '11 at 18:00
YGAYGA
68961322
asked Nov 22 '11 at 18:00
YGAYGA
68961322
asked Nov 22 '11 at 18:00
YGAYGA
68961322
68961322
Does the "Show Formulas" mode (Ctrl + `) help?
– sblair
Nov 22 '11 at 18:56
It does, but I wouldn't consider it "systematic" - requires me to hand-blast away the subtotal lines.
– YGA
Nov 22 '11 at 19:51
add a comment |
Does the "Show Formulas" mode (Ctrl + `) help?
– sblair
Nov 22 '11 at 18:56
It does, but I wouldn't consider it "systematic" - requires me to hand-blast away the subtotal lines.
– YGA
Nov 22 '11 at 19:51
Does the "Show Formulas" mode (Ctrl + `) help?
– sblair
Nov 22 '11 at 18:56
Does the "Show Formulas" mode (Ctrl + `) help?
– sblair
Nov 22 '11 at 18:56
It does, but I wouldn't consider it "systematic" - requires me to hand-blast away the subtotal lines.
– YGA
Nov 22 '11 at 19:51
It does, but I wouldn't consider it "systematic" - requires me to hand-blast away the subtotal lines.
– YGA
Nov 22 '11 at 19:51
add a comment |
9 Answers
9
active
oldest
votes
Use Conditional formatting. Choose the "Use a formula to determine which cells to format" option and type " =NOT(Cellhasformula) ". This will format anything that is hardcoded.
answered Mar 28 '13 at 0:53
StephenStephen
8611
7
So this wasn't the full answer but it's actually quite elegant. The full answer is on: j-walk.com/ss/excel/usertips/tip045.htm The key is that you 1st need to create a name (via Formulas -> Name Manager) called "Cellhasformula" that "refers to" the mystical formula =GET.CELL(48,INDIRECT("rc",FALSE)). As the link says, the formula uses GET.CELL, which is in the XLM macro language (VBA's predecessor) and can't be used directly in a worksheet. The "48" arg makes GET.CELL return 'True' if the cell has a formula. The INDIRECT basically creates a ref to each cell in the selected range.
– YGA
Oct 10 '13 at 22:07
I've edited the answer to include those details. Thanks @YGA
– Alain
Apr 30 '18 at 14:57
add a comment |
Select the cells in question, press Ctrl+G and then select Special to get the following dialog box:
Then specify the type of cells you want and click OK and only those types of cells will remain selected.
answered Nov 22 '11 at 23:48
Mike FitzpatrickMike Fitzpatrick
14.5k33540
add a comment |
As of Excel 2013, you can do:
=ISFORMULA(reference)
Here the complete documentation
answered Feb 8 '16 at 19:40
David K.David K.
8111
add a comment |
=iferror(Formulatext(cell),"Not Formula")
or to show only the formulas:
=iferror(Formulatext(cell),"")
edited Mar 31 '15 at 15:14
fixer1234
19k144982
answered Mar 31 '15 at 7:37
JackJack
311
For anyone using LibreOffice Calc instead of Excel, replace formulatext() with formula().
– fixer1234
Mar 31 '15 at 7:51
add a comment |
Two other methods are to
- Use a mapping tool such as my Mappit! addin (which can be run as a trusted addin)
- You can use the very useful XLM / Range Names workaround which offers an real time colouring of hardcoded cells, formulae, linked cells etc. This doesn't require any programming knowledge to deploy unlike VBA
answered Nov 23 '11 at 9:59
brettdjbrettdj
1,7271622
add a comment |
I know you said non-VBA preferred, but if you end up with no other solutions, the Range object has a HasFormula property.
Returns: True if all cells in the range contain formulas; False if none of the cells in the range contains a formula; null otherwise.
Source: http://msdn.microsoft.com/en-us/library/bb208626%28v=office.12%29.aspx
answered Nov 22 '11 at 19:12
Patrick SeymourPatrick Seymour
7,4022131
add a comment |
If you want to store the Excel file in a macro-free workbook format, you should avoid both VBA and macros (i.e. the XL4/XLM approaches suggested in other answers). In this answer, I made the assumption of a macro-free Excel file.
If you use MS-Excel 2013, you can use:
=ISFORMULA(reference)
If you use older MS-Excel versions (e.g. 2010, 2007), there is no true function to determine if a cell contains a formula. However, you can approximate it using:
=IF(OR(ISBLANK(reference);CELL("prefix";reference)<>"");FALSE;IF(CELL("type";reference)="l";TRUE;"MAYBE"))
The function above returns:
- TRUE for cells containing a formula wich results in a String data.
- FALSE for cells containing a String literal or is blank.
- "MAYBE" for cells containing a boolean, a number or a date, no matter if this value is literal or the result of a formula.
answered Dec 17 '14 at 14:46
Julien KroneggJulien Kronegg
1113
add a comment |
Well past the original date, but if it's of use to anyone, I managed to get around this by selecting the range of cells, then using Replace (Ctrl+H), setting it to 'Look in' "Formulas" and replace an equals = with an apostrophe-equals '=
This brought out all the formulas, but obviously would also convert things like if $C$1 contained =if($A1=$B1,"Match","Different") to '=if($A1'=$B1,"Match","Different")
Notice the A1'=B1 in the middle of the formula which may be problematic, but still means you get to see the formulas, albeit non-pragmatically. Could use the SUBSTITUTE function to change it back, so $D$1 contains =SUBSTITUTE(C1,"'=","="), then just copy out to Notepad and paste back into $E$1
The other way would be to 'show formulas' but that would show formulas for every cell, not just a selected range.
answered Apr 6 '17 at 17:53
AubsAubs
363
add a comment |
Function is_formula(c As Range) As Boolean
is_formula = Left(c.Formula, 1) = "="
End Function
answered Apr 20 '17 at 13:57
George SkeltonGeorge Skelton
1111
Can you add s short explanation to tell us what is going on here? :)
– bertieb
Apr 20 '17 at 14:20
Just stick it in a VBA module, although I realize now the OP didn't want VBA. But anyway, that's why I use.
– George Skelton
Apr 20 '17 at 17:31
That's not really an explanation
– bertieb
Apr 20 '17 at 18:41
add a comment |
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%2f360369%2fhow-to-tell-if-an-excel-cell-has-a-formula-or-is-hardcoded%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
9 Answers
9
active
oldest
votes
9 Answers
9
active
oldest
votes
active
oldest
votes
active
oldest
votes
Use Conditional formatting. Choose the "Use a formula to determine which cells to format" option and type " =NOT(Cellhasformula) ". This will format anything that is hardcoded.
answered Mar 28 '13 at 0:53
StephenStephen
8611
7
So this wasn't the full answer but it's actually quite elegant. The full answer is on: j-walk.com/ss/excel/usertips/tip045.htm The key is that you 1st need to create a name (via Formulas -> Name Manager) called "Cellhasformula" that "refers to" the mystical formula =GET.CELL(48,INDIRECT("rc",FALSE)). As the link says, the formula uses GET.CELL, which is in the XLM macro language (VBA's predecessor) and can't be used directly in a worksheet. The "48" arg makes GET.CELL return 'True' if the cell has a formula. The INDIRECT basically creates a ref to each cell in the selected range.
– YGA
Oct 10 '13 at 22:07
I've edited the answer to include those details. Thanks @YGA
– Alain
Apr 30 '18 at 14:57
add a comment |
Use Conditional formatting. Choose the "Use a formula to determine which cells to format" option and type " =NOT(Cellhasformula) ". This will format anything that is hardcoded.
answered Mar 28 '13 at 0:53
StephenStephen
8611
7
So this wasn't the full answer but it's actually quite elegant. The full answer is on: j-walk.com/ss/excel/usertips/tip045.htm The key is that you 1st need to create a name (via Formulas -> Name Manager) called "Cellhasformula" that "refers to" the mystical formula =GET.CELL(48,INDIRECT("rc",FALSE)). As the link says, the formula uses GET.CELL, which is in the XLM macro language (VBA's predecessor) and can't be used directly in a worksheet. The "48" arg makes GET.CELL return 'True' if the cell has a formula. The INDIRECT basically creates a ref to each cell in the selected range.
– YGA
Oct 10 '13 at 22:07
I've edited the answer to include those details. Thanks @YGA
– Alain
Apr 30 '18 at 14:57
add a comment |
Use Conditional formatting. Choose the "Use a formula to determine which cells to format" option and type " =NOT(Cellhasformula) ". This will format anything that is hardcoded.
answered Mar 28 '13 at 0:53
StephenStephen
8611
Use Conditional formatting. Choose the "Use a formula to determine which cells to format" option and type " =NOT(Cellhasformula) ". This will format anything that is hardcoded.
answered Mar 28 '13 at 0:53
StephenStephen
8611
answered Mar 28 '13 at 0:53
StephenStephen
8611
answered Mar 28 '13 at 0:53
StephenStephen
8611
answered Mar 28 '13 at 0:53
StephenStephen
8611
8611
7
So this wasn't the full answer but it's actually quite elegant. The full answer is on: j-walk.com/ss/excel/usertips/tip045.htm The key is that you 1st need to create a name (via Formulas -> Name Manager) called "Cellhasformula" that "refers to" the mystical formula =GET.CELL(48,INDIRECT("rc",FALSE)). As the link says, the formula uses GET.CELL, which is in the XLM macro language (VBA's predecessor) and can't be used directly in a worksheet. The "48" arg makes GET.CELL return 'True' if the cell has a formula. The INDIRECT basically creates a ref to each cell in the selected range.
– YGA
Oct 10 '13 at 22:07
I've edited the answer to include those details. Thanks @YGA
– Alain
Apr 30 '18 at 14:57
add a comment |
7
So this wasn't the full answer but it's actually quite elegant. The full answer is on: j-walk.com/ss/excel/usertips/tip045.htm The key is that you 1st need to create a name (via Formulas -> Name Manager) called "Cellhasformula" that "refers to" the mystical formula =GET.CELL(48,INDIRECT("rc",FALSE)). As the link says, the formula uses GET.CELL, which is in the XLM macro language (VBA's predecessor) and can't be used directly in a worksheet. The "48" arg makes GET.CELL return 'True' if the cell has a formula. The INDIRECT basically creates a ref to each cell in the selected range.
– YGA
Oct 10 '13 at 22:07
I've edited the answer to include those details. Thanks @YGA
– Alain
Apr 30 '18 at 14:57
7
7
So this wasn't the full answer but it's actually quite elegant. The full answer is on: j-walk.com/ss/excel/usertips/tip045.htm The key is that you 1st need to create a name (via Formulas -> Name Manager) called "Cellhasformula" that "refers to" the mystical formula =GET.CELL(48,INDIRECT("rc",FALSE)). As the link says, the formula uses GET.CELL, which is in the XLM macro language (VBA's predecessor) and can't be used directly in a worksheet. The "48" arg makes GET.CELL return 'True' if the cell has a formula. The INDIRECT basically creates a ref to each cell in the selected range.
– YGA
Oct 10 '13 at 22:07
So this wasn't the full answer but it's actually quite elegant. The full answer is on: j-walk.com/ss/excel/usertips/tip045.htm The key is that you 1st need to create a name (via Formulas -> Name Manager) called "Cellhasformula" that "refers to" the mystical formula =GET.CELL(48,INDIRECT("rc",FALSE)). As the link says, the formula uses GET.CELL, which is in the XLM macro language (VBA's predecessor) and can't be used directly in a worksheet. The "48" arg makes GET.CELL return 'True' if the cell has a formula. The INDIRECT basically creates a ref to each cell in the selected range.
– YGA
Oct 10 '13 at 22:07
I've edited the answer to include those details. Thanks @YGA
– Alain
Apr 30 '18 at 14:57
I've edited the answer to include those details. Thanks @YGA
– Alain
Apr 30 '18 at 14:57
add a comment |
Select the cells in question, press Ctrl+G and then select Special to get the following dialog box:
Then specify the type of cells you want and click OK and only those types of cells will remain selected.
answered Nov 22 '11 at 23:48
Mike FitzpatrickMike Fitzpatrick
14.5k33540
add a comment |
Select the cells in question, press Ctrl+G and then select Special to get the following dialog box:
Then specify the type of cells you want and click OK and only those types of cells will remain selected.
answered Nov 22 '11 at 23:48
Mike FitzpatrickMike Fitzpatrick
14.5k33540
add a comment |
Select the cells in question, press Ctrl+G and then select Special to get the following dialog box:
Then specify the type of cells you want and click OK and only those types of cells will remain selected.
answered Nov 22 '11 at 23:48
Mike FitzpatrickMike Fitzpatrick
14.5k33540
Select the cells in question, press Ctrl+G and then select Special to get the following dialog box:
Then specify the type of cells you want and click OK and only those types of cells will remain selected.
answered Nov 22 '11 at 23:48
Mike FitzpatrickMike Fitzpatrick
14.5k33540
answered Nov 22 '11 at 23:48
Mike FitzpatrickMike Fitzpatrick
14.5k33540
answered Nov 22 '11 at 23:48
Mike FitzpatrickMike Fitzpatrick
14.5k33540
answered Nov 22 '11 at 23:48
Mike FitzpatrickMike Fitzpatrick
14.5k33540
14.5k33540
add a comment |
add a comment |
As of Excel 2013, you can do:
=ISFORMULA(reference)
Here the complete documentation
answered Feb 8 '16 at 19:40
David K.David K.
8111
add a comment |
As of Excel 2013, you can do:
=ISFORMULA(reference)
Here the complete documentation
answered Feb 8 '16 at 19:40
David K.David K.
8111
add a comment |
As of Excel 2013, you can do:
=ISFORMULA(reference)
Here the complete documentation
answered Feb 8 '16 at 19:40
David K.David K.
8111
As of Excel 2013, you can do:
=ISFORMULA(reference)
Here the complete documentation
answered Feb 8 '16 at 19:40
David K.David K.
8111
answered Feb 8 '16 at 19:40
David K.David K.
8111
answered Feb 8 '16 at 19:40
David K.David K.
8111
answered Feb 8 '16 at 19:40
David K.David K.
8111
8111
add a comment |
add a comment |
=iferror(Formulatext(cell),"Not Formula")
or to show only the formulas:
=iferror(Formulatext(cell),"")
edited Mar 31 '15 at 15:14
fixer1234
19k144982
answered Mar 31 '15 at 7:37
JackJack
311
For anyone using LibreOffice Calc instead of Excel, replace formulatext() with formula().
– fixer1234
Mar 31 '15 at 7:51
add a comment |
=iferror(Formulatext(cell),"Not Formula")
or to show only the formulas:
=iferror(Formulatext(cell),"")
edited Mar 31 '15 at 15:14
fixer1234
19k144982
answered Mar 31 '15 at 7:37
JackJack
311
For anyone using LibreOffice Calc instead of Excel, replace formulatext() with formula().
– fixer1234
Mar 31 '15 at 7:51
add a comment |
=iferror(Formulatext(cell),"Not Formula")
or to show only the formulas:
=iferror(Formulatext(cell),"")
edited Mar 31 '15 at 15:14
fixer1234
19k144982
answered Mar 31 '15 at 7:37
JackJack
311
=iferror(Formulatext(cell),"Not Formula")
or to show only the formulas:
=iferror(Formulatext(cell),"")
edited Mar 31 '15 at 15:14
fixer1234
19k144982
answered Mar 31 '15 at 7:37
JackJack
311
edited Mar 31 '15 at 15:14
fixer1234
19k144982
edited Mar 31 '15 at 15:14
fixer1234
19k144982
edited Mar 31 '15 at 15:14
fixer1234
19k144982
19k144982
answered Mar 31 '15 at 7:37
JackJack
311
answered Mar 31 '15 at 7:37
JackJack
311
answered Mar 31 '15 at 7:37
JackJack
311
311
For anyone using LibreOffice Calc instead of Excel, replace formulatext() with formula().
– fixer1234
Mar 31 '15 at 7:51
add a comment |
For anyone using LibreOffice Calc instead of Excel, replace formulatext() with formula().
– fixer1234
Mar 31 '15 at 7:51
For anyone using LibreOffice Calc instead of Excel, replace formulatext() with formula().
– fixer1234
Mar 31 '15 at 7:51
For anyone using LibreOffice Calc instead of Excel, replace formulatext() with formula().
– fixer1234
Mar 31 '15 at 7:51
add a comment |
Two other methods are to
- Use a mapping tool such as my Mappit! addin (which can be run as a trusted addin)
- You can use the very useful XLM / Range Names workaround which offers an real time colouring of hardcoded cells, formulae, linked cells etc. This doesn't require any programming knowledge to deploy unlike VBA
answered Nov 23 '11 at 9:59
brettdjbrettdj
1,7271622
add a comment |
Two other methods are to
- Use a mapping tool such as my Mappit! addin (which can be run as a trusted addin)
- You can use the very useful XLM / Range Names workaround which offers an real time colouring of hardcoded cells, formulae, linked cells etc. This doesn't require any programming knowledge to deploy unlike VBA
answered Nov 23 '11 at 9:59
brettdjbrettdj
1,7271622
add a comment |
Two other methods are to
- Use a mapping tool such as my Mappit! addin (which can be run as a trusted addin)
- You can use the very useful XLM / Range Names workaround which offers an real time colouring of hardcoded cells, formulae, linked cells etc. This doesn't require any programming knowledge to deploy unlike VBA
answered Nov 23 '11 at 9:59
brettdjbrettdj
1,7271622
Two other methods are to
- Use a mapping tool such as my Mappit! addin (which can be run as a trusted addin)
- You can use the very useful XLM / Range Names workaround which offers an real time colouring of hardcoded cells, formulae, linked cells etc. This doesn't require any programming knowledge to deploy unlike VBA
answered Nov 23 '11 at 9:59
brettdjbrettdj
1,7271622
answered Nov 23 '11 at 9:59
brettdjbrettdj
1,7271622
answered Nov 23 '11 at 9:59
brettdjbrettdj
1,7271622
answered Nov 23 '11 at 9:59
brettdjbrettdj
1,7271622
1,7271622
add a comment |
add a comment |
I know you said non-VBA preferred, but if you end up with no other solutions, the Range object has a HasFormula property.
Returns: True if all cells in the range contain formulas; False if none of the cells in the range contains a formula; null otherwise.
Source: http://msdn.microsoft.com/en-us/library/bb208626%28v=office.12%29.aspx
answered Nov 22 '11 at 19:12
Patrick SeymourPatrick Seymour
7,4022131
add a comment |
I know you said non-VBA preferred, but if you end up with no other solutions, the Range object has a HasFormula property.
Returns: True if all cells in the range contain formulas; False if none of the cells in the range contains a formula; null otherwise.
Source: http://msdn.microsoft.com/en-us/library/bb208626%28v=office.12%29.aspx
answered Nov 22 '11 at 19:12
Patrick SeymourPatrick Seymour
7,4022131
add a comment |
I know you said non-VBA preferred, but if you end up with no other solutions, the Range object has a HasFormula property.
Returns: True if all cells in the range contain formulas; False if none of the cells in the range contains a formula; null otherwise.
Source: http://msdn.microsoft.com/en-us/library/bb208626%28v=office.12%29.aspx
answered Nov 22 '11 at 19:12
Patrick SeymourPatrick Seymour
7,4022131
I know you said non-VBA preferred, but if you end up with no other solutions, the Range object has a HasFormula property.
Returns: True if all cells in the range contain formulas; False if none of the cells in the range contains a formula; null otherwise.
Source: http://msdn.microsoft.com/en-us/library/bb208626%28v=office.12%29.aspx
answered Nov 22 '11 at 19:12
Patrick SeymourPatrick Seymour
7,4022131
answered Nov 22 '11 at 19:12
Patrick SeymourPatrick Seymour
7,4022131
answered Nov 22 '11 at 19:12
Patrick SeymourPatrick Seymour
7,4022131
answered Nov 22 '11 at 19:12
Patrick SeymourPatrick Seymour
7,4022131
7,4022131
add a comment |
add a comment |
If you want to store the Excel file in a macro-free workbook format, you should avoid both VBA and macros (i.e. the XL4/XLM approaches suggested in other answers). In this answer, I made the assumption of a macro-free Excel file.
If you use MS-Excel 2013, you can use:
=ISFORMULA(reference)
If you use older MS-Excel versions (e.g. 2010, 2007), there is no true function to determine if a cell contains a formula. However, you can approximate it using:
=IF(OR(ISBLANK(reference);CELL("prefix";reference)<>"");FALSE;IF(CELL("type";reference)="l";TRUE;"MAYBE"))
The function above returns:
- TRUE for cells containing a formula wich results in a String data.
- FALSE for cells containing a String literal or is blank.
- "MAYBE" for cells containing a boolean, a number or a date, no matter if this value is literal or the result of a formula.
answered Dec 17 '14 at 14:46
Julien KroneggJulien Kronegg
1113
add a comment |
If you want to store the Excel file in a macro-free workbook format, you should avoid both VBA and macros (i.e. the XL4/XLM approaches suggested in other answers). In this answer, I made the assumption of a macro-free Excel file.
If you use MS-Excel 2013, you can use:
=ISFORMULA(reference)
If you use older MS-Excel versions (e.g. 2010, 2007), there is no true function to determine if a cell contains a formula. However, you can approximate it using:
=IF(OR(ISBLANK(reference);CELL("prefix";reference)<>"");FALSE;IF(CELL("type";reference)="l";TRUE;"MAYBE"))
The function above returns:
- TRUE for cells containing a formula wich results in a String data.
- FALSE for cells containing a String literal or is blank.
- "MAYBE" for cells containing a boolean, a number or a date, no matter if this value is literal or the result of a formula.
answered Dec 17 '14 at 14:46
Julien KroneggJulien Kronegg
1113
add a comment |
If you want to store the Excel file in a macro-free workbook format, you should avoid both VBA and macros (i.e. the XL4/XLM approaches suggested in other answers). In this answer, I made the assumption of a macro-free Excel file.
If you use MS-Excel 2013, you can use:
=ISFORMULA(reference)
If you use older MS-Excel versions (e.g. 2010, 2007), there is no true function to determine if a cell contains a formula. However, you can approximate it using:
=IF(OR(ISBLANK(reference);CELL("prefix";reference)<>"");FALSE;IF(CELL("type";reference)="l";TRUE;"MAYBE"))
The function above returns:
- TRUE for cells containing a formula wich results in a String data.
- FALSE for cells containing a String literal or is blank.
- "MAYBE" for cells containing a boolean, a number or a date, no matter if this value is literal or the result of a formula.
answered Dec 17 '14 at 14:46
Julien KroneggJulien Kronegg
1113
If you want to store the Excel file in a macro-free workbook format, you should avoid both VBA and macros (i.e. the XL4/XLM approaches suggested in other answers). In this answer, I made the assumption of a macro-free Excel file.
If you use MS-Excel 2013, you can use:
=ISFORMULA(reference)
If you use older MS-Excel versions (e.g. 2010, 2007), there is no true function to determine if a cell contains a formula. However, you can approximate it using:
=IF(OR(ISBLANK(reference);CELL("prefix";reference)<>"");FALSE;IF(CELL("type";reference)="l";TRUE;"MAYBE"))
The function above returns:
- TRUE for cells containing a formula wich results in a String data.
- FALSE for cells containing a String literal or is blank.
- "MAYBE" for cells containing a boolean, a number or a date, no matter if this value is literal or the result of a formula.
answered Dec 17 '14 at 14:46
Julien KroneggJulien Kronegg
1113
edited Dec 17 '14 at 14:56
answered Dec 17 '14 at 14:46
Julien KroneggJulien Kronegg
1113
answered Dec 17 '14 at 14:46
Julien KroneggJulien Kronegg
1113
answered Dec 17 '14 at 14:46
Julien KroneggJulien Kronegg
1113
1113
add a comment |
add a comment |
Well past the original date, but if it's of use to anyone, I managed to get around this by selecting the range of cells, then using Replace (Ctrl+H), setting it to 'Look in' "Formulas" and replace an equals = with an apostrophe-equals '=
This brought out all the formulas, but obviously would also convert things like if $C$1 contained =if($A1=$B1,"Match","Different") to '=if($A1'=$B1,"Match","Different")
Notice the A1'=B1 in the middle of the formula which may be problematic, but still means you get to see the formulas, albeit non-pragmatically. Could use the SUBSTITUTE function to change it back, so $D$1 contains =SUBSTITUTE(C1,"'=","="), then just copy out to Notepad and paste back into $E$1
The other way would be to 'show formulas' but that would show formulas for every cell, not just a selected range.
answered Apr 6 '17 at 17:53
AubsAubs
363
add a comment |
Well past the original date, but if it's of use to anyone, I managed to get around this by selecting the range of cells, then using Replace (Ctrl+H), setting it to 'Look in' "Formulas" and replace an equals = with an apostrophe-equals '=
This brought out all the formulas, but obviously would also convert things like if $C$1 contained =if($A1=$B1,"Match","Different") to '=if($A1'=$B1,"Match","Different")
Notice the A1'=B1 in the middle of the formula which may be problematic, but still means you get to see the formulas, albeit non-pragmatically. Could use the SUBSTITUTE function to change it back, so $D$1 contains =SUBSTITUTE(C1,"'=","="), then just copy out to Notepad and paste back into $E$1
The other way would be to 'show formulas' but that would show formulas for every cell, not just a selected range.
answered Apr 6 '17 at 17:53
AubsAubs
363
add a comment |
Well past the original date, but if it's of use to anyone, I managed to get around this by selecting the range of cells, then using Replace (Ctrl+H), setting it to 'Look in' "Formulas" and replace an equals = with an apostrophe-equals '=
This brought out all the formulas, but obviously would also convert things like if $C$1 contained =if($A1=$B1,"Match","Different") to '=if($A1'=$B1,"Match","Different")
Notice the A1'=B1 in the middle of the formula which may be problematic, but still means you get to see the formulas, albeit non-pragmatically. Could use the SUBSTITUTE function to change it back, so $D$1 contains =SUBSTITUTE(C1,"'=","="), then just copy out to Notepad and paste back into $E$1
The other way would be to 'show formulas' but that would show formulas for every cell, not just a selected range.
answered Apr 6 '17 at 17:53
AubsAubs
363
Well past the original date, but if it's of use to anyone, I managed to get around this by selecting the range of cells, then using Replace (Ctrl+H), setting it to 'Look in' "Formulas" and replace an equals = with an apostrophe-equals '=
This brought out all the formulas, but obviously would also convert things like if $C$1 contained =if($A1=$B1,"Match","Different") to '=if($A1'=$B1,"Match","Different")
Notice the A1'=B1 in the middle of the formula which may be problematic, but still means you get to see the formulas, albeit non-pragmatically. Could use the SUBSTITUTE function to change it back, so $D$1 contains =SUBSTITUTE(C1,"'=","="), then just copy out to Notepad and paste back into $E$1
The other way would be to 'show formulas' but that would show formulas for every cell, not just a selected range.
answered Apr 6 '17 at 17:53
AubsAubs
363
answered Apr 6 '17 at 17:53
AubsAubs
363
answered Apr 6 '17 at 17:53
AubsAubs
363
answered Apr 6 '17 at 17:53
AubsAubs
363
363
add a comment |
add a comment |
Function is_formula(c As Range) As Boolean
is_formula = Left(c.Formula, 1) = "="
End Function
answered Apr 20 '17 at 13:57
George SkeltonGeorge Skelton
1111
Can you add s short explanation to tell us what is going on here? :)
– bertieb
Apr 20 '17 at 14:20
Just stick it in a VBA module, although I realize now the OP didn't want VBA. But anyway, that's why I use.
– George Skelton
Apr 20 '17 at 17:31
That's not really an explanation
– bertieb
Apr 20 '17 at 18:41
add a comment |
Function is_formula(c As Range) As Boolean
is_formula = Left(c.Formula, 1) = "="
End Function
answered Apr 20 '17 at 13:57
George SkeltonGeorge Skelton
1111
Can you add s short explanation to tell us what is going on here? :)
– bertieb
Apr 20 '17 at 14:20
Just stick it in a VBA module, although I realize now the OP didn't want VBA. But anyway, that's why I use.
– George Skelton
Apr 20 '17 at 17:31
That's not really an explanation
– bertieb
Apr 20 '17 at 18:41
add a comment |
Function is_formula(c As Range) As Boolean
is_formula = Left(c.Formula, 1) = "="
End Function
answered Apr 20 '17 at 13:57
George SkeltonGeorge Skelton
1111
Function is_formula(c As Range) As Boolean
is_formula = Left(c.Formula, 1) = "="
End Function
answered Apr 20 '17 at 13:57
George SkeltonGeorge Skelton
1111
answered Apr 20 '17 at 13:57
George SkeltonGeorge Skelton
1111
answered Apr 20 '17 at 13:57
George SkeltonGeorge Skelton
1111
answered Apr 20 '17 at 13:57
George SkeltonGeorge Skelton
1111
1111
Can you add s short explanation to tell us what is going on here? :)
– bertieb
Apr 20 '17 at 14:20
Just stick it in a VBA module, although I realize now the OP didn't want VBA. But anyway, that's why I use.
– George Skelton
Apr 20 '17 at 17:31
That's not really an explanation
– bertieb
Apr 20 '17 at 18:41
add a comment |
Can you add s short explanation to tell us what is going on here? :)
– bertieb
Apr 20 '17 at 14:20
Just stick it in a VBA module, although I realize now the OP didn't want VBA. But anyway, that's why I use.
– George Skelton
Apr 20 '17 at 17:31
That's not really an explanation
– bertieb
Apr 20 '17 at 18:41
Can you add s short explanation to tell us what is going on here? :)
– bertieb
Apr 20 '17 at 14:20
Can you add s short explanation to tell us what is going on here? :)
– bertieb
Apr 20 '17 at 14:20
Just stick it in a VBA module, although I realize now the OP didn't want VBA. But anyway, that's why I use.
– George Skelton
Apr 20 '17 at 17:31
Just stick it in a VBA module, although I realize now the OP didn't want VBA. But anyway, that's why I use.
– George Skelton
Apr 20 '17 at 17:31
That's not really an explanation
– bertieb
Apr 20 '17 at 18:41
That's not really an explanation
– bertieb
Apr 20 '17 at 18:41
add a comment |
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%2f360369%2fhow-to-tell-if-an-excel-cell-has-a-formula-or-is-hardcoded%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
Does the "Show Formulas" mode (Ctrl + `) help?
– sblair
Nov 22 '11 at 18:56
It does, but I wouldn't consider it "systematic" - requires me to hand-blast away the subtotal lines.
– YGA
Nov 22 '11 at 19:51