Distinguishing hard coded numbers from formulas

codyswan

New Member
Joined
Mar 10, 2002
Messages
22
I would like a simple way to identify hard coded numbers versus formulas in spreadsheets. I suspect what I want has to do with conditional formatting. I would like to easily see which of hundreds of cells are hard coded. If they could display in a different color or font, that would be great. I am trying to avoid doing a search for the = sign. Some of the cells should be hard coded and some should be formulas. I'm looking for ones that are out of place, like on number in a column of formulas which has been overwritten.

thanks
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Choose Edit>GoTo>Special. Select a choice from the dialog box. Click OK
This message was edited by lenze on 2002-09-05 14:29
 
Upvote 0
Is this too simple to use the Tools / Options / View / Formulas setting to see what is hard coded and what is a formula?

Bob
 
Upvote 0
On 2002-09-05 17:29, bobf wrote:
Is this too simple to use the Tools / Options / View / Formulas setting to see what is hard coded and what is a formula?

Bob

This is a good step. I guess I can turn it off an on so that I can see which ones are formulas and then look at the values being calculated by the formulas.
 
Upvote 0
If you go with lenzes suggestion THEN
either color the cells or make bold etc then
you will be able to distinguish visually which cells have formulas and which don't,
rather then viewing the whole sheets as formulas.
 
Upvote 0
Put this UDF in a standard module (UDF from Aaron Blood http://geocities.com/aaronblood):

Function IsFormula(Cell As Range) As Boolean
IsFormula = Cell.HasFormula
End Function

1. Select cell A1.
2. Go to Format menu/Conditional Formatting.
3. For condition 1, formula is = isFormula(A1)
4. While in Conditional Formatting, select Format/Font and then select a font color from the drop down Color menu (pick say dark grey).
5. OK, OK.

Then:
1. Select cell A1.
2 Right click your mouse, select Copy.
3. Drag your cursor across the range of cells to conditional format.
4. Right click, Paste Special/Formats/OK.

All formulas will be shown in dark grey.

Regards,

Mike
This message was edited by Ekim on 2002-09-06 01:25
 
Upvote 0
I would like a simple way to identify hard coded numbers versus formulas in spreadsheets. I suspect what I want has to do with conditional formatting. I would like to easily see which of hundreds of cells are hard coded. If they could display in a different color or font, that would be great. I am trying to avoid doing a search for the = sign. Some of the cells should be hard coded and some should be formulas. I'm looking for ones that are out of place, like on number in a column of formulas which has been overwritten.

thanks



Hi,

Use this formula to highlight the number in the column,

=NOT(ISFORMULA(G7)) in Conditional formatting option.

1. Select cell Range.
2. Go to Format menu/Conditional Formatting.
3. select : "Use a formula to determine which cells to format" option
3. Use this formula =NOT(isFormula(A1))
4. While in Conditional Formatting, select Format/Font and then select a font color from the drop down Color menu (pick say dark grey).
5. OK, OK.

Hope it will useful,

Br,
Kamar
 
Upvote 0
Something tells me that the ISFORMULA function wasn't around in 2002 when codyswan posted the question :ROFLMAO:
Just for reference ISFORMULA appeared on Windows machines first in Excel 2013 and so won't work with earlier versions.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top