Format cells

[jpinto521]

Hi All,

I would like your help on the following.

I have an excel workbook with several tab that point between them. One particular is capturing the data from other tab cells and is formatted as custom currency: "$        500", for instance. When there is no values on the captured cells, it shows "$       -". How can I change this to have the cell blank?

All the cells on this tab have a formula that points to other cells on different tabs.

Thanks in advance.

[#2]

@jpinto521 

To change the custom currency format to show blank when the cell is empty, you can use the following steps:

1. Select the cells that you want to format.
2. Right-click and select “Format Cells” from the context menu.
3. In the “Format Cells” dialog box, select “Custom” from the list on the left.
4. In the “Type” text box, enter #,##0.00;-#,##0.00; 
5. Click “OK” to close the dialog box.

This will format your cells with a custom currency format that shows blank when the cell is empty.

I hope that helps!

NikolinoDE

[#3]

@NikolineDE,
Thank you.
It really works on a blank cell but i cannot apply it to a cell with values because it will change the format whenever there is a value on a cell.
Best regards,
jpinto521

[#4]

A file (without sensitive data), as well as information about the Excel version, operating system, storage medium, etc. would be advantageous to submit a quick solution here.

[#5]

@jpinto521 

Just modify a bit format which @NikolinoDE  suggested. You have references on other cells and all you numbers have accounting format. Select entire range, Ctrl+1, Number -> Custom. It looks like

Now remove everything between second and third semicolons. The rest of custom format will be like

Apply this format, it hides zeroes in the grid.

[#6]

@Sergei Baklan 

Thank you for your input as well.

Already solve my problem by using this format _($* #,##0_);[Red]_($* (#,##0);"".

Best Regards,

[#7]

Dear NikolinoDE,
I understand what you are saying. Nevertheless as this was just a formatting thing never cross my mind all that.
Anyway, using your own example I managed to solve my problem by custom formatting like this _($* #,##0_);[Red]_($* (#,##0);""
Thank you for your help on this.
Best Regards,

[#8]

@jpinto521 

Thank you for the update. In general you don't need "" at the end. Actual value will be zero in any case, format only hide it. Both ...;"" and ...; work the same way.