Description

how to get – instead of 0 in excel ?

**Transforming Zeros into Dashes in Excel: A Comprehensive Guide**

Handling zeros in your Excel worksheets can be a bit cumbersome, especially when you want a cleaner, more readable format for your data presentation. In this guide, we’ll explore various methods to replace zeros with a dash (-) in your Excel data. This change not only improves the visual appeal of your spreadsheet but can also aid in the effective communication of your data analysis.

### Method 1: Using Number Format to Display Dashes

Excel offers a simple and elegant way to change the way zero values are displayed using the number formatting option. Here, the goal is to replace 0 with a dash (-) directly in the cell display, without changing the actual data value.

1. **Select the Cells**: Highlight all the cells in your worksheet where you wish to display dashes instead of zeros.
2. **Right-click the Selection**: A menu will appear. Choose ‘Format Cells.’
3. **Custom Category**: In the Format Cells dialog box, navigate to the ‘Custom’ category. You will see a text box labeled ‘Type.’
4. **Modify the Number Format**: Inside the ‘Type’ box, type `#;-#;;-` or `0;[Red]-0;;-` to represent a number or a negative number or an empty cell or a dash when the value is 0. Here, Excel’s format syntax is used: `Positive values; negative values; zeros; text`.
5. **Apply**: Click ‘OK’ to apply the new format to your selected cells.

### Method 2: Using Find and Replace

Another straightforward way to visually change zeroes to dashes is via Excel’s ‘Find and Replace’ feature.

1. **Find and Replace**: Click on ‘Home,’ in the ‘Editing’ group, click ‘Find & Select,’ then ‘Replace.’
2. **Find What**: In the ‘Find what’ box, just enter ‘0’.
3. **Replace with**: In the ‘Replace with’ box, type a dash ‘-‘.
4. **Replace All**: Click ‘Replace All’ to replace all zeros with dashes. Excel will display a summary of how many replacements were made and a report is shown.

### Method 3: Using Conditional Formatting

Conditional formatting can also be used for a similar purpose, though this method colors the cells that contain zero values instead of displaying a dash.

1. **Select your data range**.
2. **Conditional Formatting under Home**: Choose ‘Conditional Formatting’ > ‘Highlight Cells Rules’ > ‘Equal To.’
3. **Apply Formatting Style**: Set it so that cells ‘Equal to: 0’ will be formatted according to your specifications.
4. **Format Cells**: Choose formatting options like font color, background color, or even apply a formula to further modify appearance (though it won’t literally convert 0 to a dash).

### Method 4: Using VBA if you have a more complex transformation

For more advanced users, a VBA macro can be written and set up to automatically convert all zeros into dashes throughout an entire worksheet with a simple run of the macro.

### Conclusion

These methods provide a range of options to change how zeros are displayed in your Excel worksheets, either temporarily for viewing purposes or permanently if that’s what your data requires. Using a dash instead of a zero can make your data more readable and align better with certain data presentation standards or preferences. Choose a method that aligns best with your workflow and data needs.