How to Convert Zeroes to Dashes in Excel?

Sometimes in Excel, you just need to convert the zeros to dashes to represent that there is no value in the cell. We can do this task if we treat zero as a value. If we try to solve this manually, it can be good for small amounts of data, so we need a faster approach for large amounts of data. Read this tutorial to learn how you can convert zeros to dashes in Excel. We can complete this task using the find and replace function and formal cell operation.

Converting Zeroes to Dashes Using Find and Replace

We'll open find and replace first, then enter zero in find what and a dash in replace. Let's look at a simple procedure for converting zeros to dashes in Excel using find and replace.

Step 1

Let us consider an Excel sheet where the data in the sheet is similar to the data shown in the below image.

Then, press CTRL + F to access the find and replace menu.

Step 2

Then click on replace and type 0 in find what and – in replace with, then click on replace all as shown in the below image to complete our process.

CTRL + F > replace > 0 > - > replace all

Converting Zeroes to Dashes Using Format Cells

Here, we will use the Format option to complete the task. Let's go over a simple procedure for converting zeros to dashes in Excel using format cells.

Step 1

Let us consider the data that we used in the above example.

Then select the data and right-click, then select format cells to open a pop-up, click on custom, enter the type as 0;-0;—;@ and click OK to complete our process.

Select data > Right click > Format cells > Custom > Type > OK

Conclusion

In this tutorial, we used a simple example to demonstrate how you can convert zeros to dashes in Excel.

Pradeep Kumar

Updated on: 07-Mar-2023

1K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started