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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
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
 

bobf

New Member
Joined
Sep 4, 2002
Messages
1
Is this too simple to use the Tools / Options / View / Formulas setting to see what is hard coded and what is a formula?

Bob
 

codyswan

New Member
Joined
Mar 10, 2002
Messages
22
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.
 

Ivan F Moala

MrExcel MVP
Joined
Feb 10, 2002
Messages
4,209

ADVERTISEMENT

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.
 

Ekim

Well-known Member
Joined
Jun 30, 2002
Messages
1,416

ADVERTISEMENT

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
 

tkamar2000

New Member
Joined
May 8, 2018
Messages
1
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
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,233
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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.
 

Forum statistics

Threads
1,144,274
Messages
5,723,447
Members
422,497
Latest member
dougy99

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
Top