Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I want to change the formatting on an excel sheet so that 0's will show up as dashes for only a specific block of cells on a sheet. Does anyone know how to do this?

Note that I do not want this formatting for the entire sheet, only for a certain block of cells.

share|improve this question
add comment

3 Answers

up vote 6 down vote accepted

If you only want to change the formatting, not the content, of the cells you should look at the Accounting format type, but with no symbol selected.

All cells with a value of 0 will be displayed as - while keeping the value 0 in the cell.

But Accounting does introduce some "special" spacing in to the cell. So instead I'd suggest using a custom format. Custom formats can be split in to up to four sections, divided by a semi-colon, meaning you can define a format as such:

Positive values;Negative values;Zero values;Text values

So, if you happen to need a percentage to two decimal places, but replacement of zeros, you could do:

0.00%;-0.00%;"-";@

Where "-" will entirely replace all zero values and @ will display text as-is.
Make sure you remember to prefix the second value (negative numbers) with a - symbol!

If you don't have a format string handy for the positive and negative entries, note that you can obtain the format string for any standard type by first selecting the desired type on the format page and then moving directly to the custom view.

To implement:

  • Select all the cells / rows / columns required.
  • Right-click and choose Format Cells...
  • Select Custom (or maybe Accounting)
  • Enter the format string required
share|improve this answer
    
This is really close, but I am already displaying the numbers as percentages. Is there a way to get the zero replacement of the accounting format with the display properties of one of the other formats? –  chuck taylor Nov 24 '10 at 17:05
    
Try swapping both instances of the word General with the format string that you require - if you need to find the format string just select the required format normally and then select custom, and it should give you the format string you need to then transplant in. –  DMA57361 Nov 24 '10 at 17:07
    
Actually, I've just discovered it's simpler than this - updating my answer.... –  DMA57361 Nov 24 '10 at 17:09
    
Worked like a charm, thanks much for your help. –  chuck taylor Nov 24 '10 at 17:19
add comment
  • Select a block of cells
  • Edit > Replace...
  • In the 'Find what' field, type 0
  • In the 'Replace with' filed, type '-'
  • Hit 'Replace All'
share|improve this answer
    
That would only do the change once, I am looking for a setting to apply to part of the worksheet that will remain consistent even if I change the values in the cells. –  chuck taylor Nov 24 '10 at 17:06
add comment

You could use conditional formatting to do this. If you use Office 2007, on the Home tab, there is the conditional formatting button in the styles section. From the dropdown you select "Manage Rules". From there, add a new rule and select "Format only cells that contain. Change the targeting to "equal to 0" and you can then setup the formatting any way you like. If you want a bunch of dashes, just go to custom and type in whatever symbols you want it to display instead of 0. Finally, change the Applies to for the column that has the percentages (C column: =$C:$C).

You could also use an IF statement, but that takes up a lot more space and only works when you calculate the percentage.

Hope this helps

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.