Putting a dash in an empty cell

[megmoo75]

Does anyone know how to format an Excel cell so that when it's empty it just displays a dash? The cell will still be included in various math functions, so it can't have it as a value - it needs to be a formatting option. I've seen this before, but I can't figure out how to do it. Any help would be greatly appreciated!

Thanks!

 

[ChrisBurch]

Try something like:-

Put a formula in the cell that sets the blank to zero - =IF(A1,"",0)

Then set the cell format

#,##0.00;[RED]-#,##0.00;-

The first component is for positive numbers, the second for negatives, and the third is the case for zero.

Chris

IT would be the perfect job......if it didn't have users!!!

 

[AnotherHiggins]

I think the only built-in format that displays that way is Accounting. Go to Format > Format Cells then on the Number tab, select Accounting from the list on the left. On the right, you can select the number of decimal places and whether you want a currency symbol to appear (I think you'll want to select None for symbol).

If that doesn't display negatives the way you like, then just pick any other format that looks the way you like (except for the zeros). Once selected, go to Custom and simply add [COLOR=blue white];-[/color] to the end of whatever appears in the Type box at the right.

Good luck!

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[AnotherHiggins]

Sorry, I just realized that I misread the question. The answer I provided displays all zeros as a dash, not all blank cells.

I'm afraid you're out of luck as far as formatting empty cells to display a dash.

I assume you want to do this for purely aesthetic reasons? If you just want printouts to look this way, you can select the range that will be visible on a printout, then go to Edit > Go To > Special then select Blank. Now type 0 and then [Ctrl]+{Enter].

WARNING: Do not attempt to fill all spaces with zeros because that would likely choke your system (we're talking about 256 columns X 65536 rows. That's 16,777,216 cells!)

ChrisBurch: one problem with your answer: where do you put the formula? You can't put "=IF(A1,"",0)" in A1 - that would create a circular reference. If you're trying to fill all empty spaces with a zero, why bother with a formula?

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[1726]

I can't see how to apply a format like this to an empty cell, but I can offer an alternative fudge.

If you place a space in a cell to make it look empty you can use a conditional format to apply strikeout font attribute, this will then look like a dash.

However, having text in cell can have horrible side effects!

 

[DaleWatson123321]

I would suggest Conditional Formatting is worthy of consideration, but WITHOUT entering any data (space characters).

Steps:

1) Highlight the cells you require with this type of formatting. If you perhaps want to apply this to ALL cells in the Sheet, click what I refer to as the "All Cells" point - to the left of the "A" of Column A, and above the "1" for Row 1.

2) From the menu: Format - Conditional Formatting

3) Change "Cell Value Is" to "Formula Is"

4) In the space opposite Formula Is, enter this formula:
=ISBLANK(A1)

5) While still in the "Conditional Formatting" window, click the "Format" button.

6) Click the "Patterns" tab, and then choose a light color and/or a "Pattern" - see the bottom-left-corner and click the down-arrow opposite "Pattern".

I hope this helps.

Regards, Dale Watson

 

[Davidprince]

I have had the same problem with Excel and the only solution I found was to enter the following in a cell and then copy to each cell I wanted the dash in:
' -

David