Blank cells to display absolute zero "-"?

[dgibney]

Without using a formula in the cell or VBA, is there a built-in way (Data Validation?) for all blank cells in a named range to display "-"? I have been using the code below but it only addresses one column at a time and I want a means to cover the entire range. It is preferable to avoid VBA if possible, using Excel's built-in features. Any ideas? If not, how would I convert this code to cover the entire named range? Thanks for your help! David

Please Login or Register  to view this content.

[FDibbins]

2 reasons not to use CF...
1. if the range is really large, CF will start to slow you down
2. CF applies cosmetic changes to the appearance of a cell, and Im not sure there is any way to have - shown that way.

There may be something in the excel settings, but I cannot find it, so far

[Beamernsw]

Probably not what you want, way too simple. But how about right click "format cells" / "Custom" and select:
"_-* #,##0_-;-* #,##0_-;_-* "-"_-;_-@_-" .....from the list?

[dgibney]

Thanks for your response. Unfortunately there are several columns that need to have different number decimal points of accuracy and I'd really like to find a more global solution, something that would cover the entire range/table, which is pretty big. We may have to go with a macro/VBA solution but we're trying to limit the number of macros use, instead employing built-in functionality of Excel.

Thanks again for your input! David

[FDibbins]

It would probably help if you could upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[ChemistB]

You can select your range (CNTRL SHFT 8) and then F5 to activate the GO TO window
Then Special> Blanks, Then enter - and hold down CNTRL + ENTER

[shg]

is there a built-in way (Data Validation?) for all blank cells in a named range to display "-"?

Blank cells, by definition display nothing.

Why does your design require this?