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.
6 Answers
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 valuesSo, 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
- Highlight the Cells that are not formatted incorrectly
- Then right click on those cells and choose "Format Cells"
- Choose "Custom" from the Category Menu
- In the "Type" field paste this code
_($* "-"??_);_($* "-"??_);_($* "-"??_);_(@_)
The first set of parenthesis identify how you want positive values to be treated, the 2nd set of parenthesis identify how you want negative values to be formatted, the 3rd set of parenthesis identify how you want zero values to be formatted, and the last set of parenthesis identify how you want text values to be formatted. You need to address all 4 types of values in order for all the bases to be covered. If you do not want the zeros to be reflected as a dash then simply replace the dashes shown in the code above to be a 0 or 0.00 or however you would like them to appear. Hope this helps!
- Select a block of cells
- Edit > Replace...
- In the 'Find what' field, type 0
- In the 'Replace with' filed, type '-'
- Hit 'Replace All'
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
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
1- Select the cells you want to change.
- Ctrl + 1
- "Number" Tab => "Custom"
- #;#;- (Enter in the "Type" bar)
- OK (or enter)
