For 1st part of your problem, the Cell format you are trying can be used, also this may be used.
As per my knowledge to replace
Dash, Excel doesn't provides any Custom Cell Format, but yes there are few other methods.
- Select cells, press CTRL+G.
- Select Blanks option.
- Finally press Ctrl+Enter.
Select required cell range.
- Press Ctrl+H
- For Find what leave the field empty.
- For Replace with type
- Finally press Replace all.
You may use this VBA macro as Module.
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "Replace Blanks & Zeros"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
If Rng.Value = "" Or Rng.Value = 0 Then
Rng.Value = "-"
Rng.HorizontalAlignment = xlCenter
This fills all cells contains blank & Zeros in the selected range with Dash sign.
- Either press Alt+F11 or Right click the Sheet TAB and hit View Code, to open VB editor.
- From Insert menu hit Module.
- Copy & Paste this code.
- Save the Workbook as Macro Enabled.
- Finally RUN the macro.
- Suppose you are getting ZERO after applied a formula.