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

4 Answers 4

up vote 7 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
  • 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

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

I had a problem where i had zero's with a leading apostrophe (sourced from another reporting system) shown like this '0 (I haven't used quote marks to avoid confusion). these problem '0s where mixed up with thousands of other normal numbers so fixing one by one would have been extremely tedious. I wanted this to show as the number zero i.e. 0 (again no quote marks) so that an NPV formula would see the 0 as a number and not text. Doing a replace doesn't work because the apostrophe is a special character in Excel which makes everything following text, irrespective of the cell format. There are two solutions: 1) Just type the number 1 in a cell and then do a copy>paste special>multiply against all the numbers including the problem '0 and the '0 becomes a 0. 2) Do a goto>special with all the cells you wish to look at already highlighted (otherwise it will find headings etc) and then select 'constant' and then 'text' and then OK. Then immediately type 0 then hit CTL+Enter simultaneously (instead of just enter) and all the cells selected by goto as text will become 0.

Good luck

share|improve this answer
    
Welcome. You have some good information here, but the way it's written it may seem like it doesn't answer the question that was asked. I assume you are talking about applying a method similar to what you used to this problem (changing 0 to -). To make this a good answer, edit it so that it is a direct solution to the problem asked rather than an analogous one. You don't need to refer to your own problem, just write the solution to this one. –  fixer1234 Nov 13 at 5:59

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.