Delete All Zeros in Excel: Values, Cells, or Display

How-to basics · Updated November 20, 2026

To remove cells whose entire value is zero in Excel, select the relevant range, use Find with Match entire cell contents turned on, choose Find All, select the results, and clear their contents. If you mean deleting whole rows that contain zero, or removing the zero digit from numbers such as 105, use a different method. Save a copy first: clearing cells, replacing digits, and deleting rows can change data or formulas.

What You’ll Need

  • A workbook open in Microsoft Excel for Microsoft 365 on Windows.
  • A clear idea of what you mean by zeros: cells equal to zero, complete rows containing zero, zero digits inside other values, or zeros that are visible but should only be hidden.
  • A backup or duplicate worksheet if the data is important. Clearing contents and deleting rows are not reliably reversible after you close the workbook or make further changes.
  • If you want to remove zero entries from a table, identify the column or range to check before starting. This helps avoid removing unrelated zeros elsewhere in the sheet.

Step-by-Step

Method 1: Clear cells whose complete value is zero

Use this method when you want a zero cell to become blank but want to keep its row, column, surrounding data, and cell formatting. It can find numeric zeros and cells containing the text 0. If a formula displays zero, clearing the cell also removes the formula, so review the results before you clear anything.

  1. Save a safety copy. Choose File > Save As and make a duplicate workbook, or duplicate the worksheet by right-clicking its tab and choosing Move or Copy, then selecting Create a copy. Work on the copy if you are uncertain which zeros should be removed.
  2. Select the search area. Click and drag to select the cells you want to inspect. To check the whole current worksheet, click a cell and leave the selection unrestricted; the Find dialog can search within the sheet. A selected range is safer when the workbook has many unrelated sections.
  3. Open Find. Press Ctrl+F on Windows. In the Find and Replace dialog, choose Options if the search settings are not already visible. Set Within to Sheet, Search to By Rows, and Look in to Values. The exact dialog labels can vary slightly by Excel update.
  4. Set an exact match. In Find what, enter 0, then turn on Match entire cell contents. This prevents a search for zero from matching a value such as 105 or 20. If the option is unavailable in the dialog view, expand the options before searching.
  5. Review every match. Click Find All. Excel lists the matching cells. Check the list and its locations before changing anything. Pay particular attention to cells containing formulas: when Look in is set to Values, a formula that currently evaluates to zero may be found too.
  6. Select the results and clear them. Click in the results list, press Ctrl+A to select the listed matches, and close the Find dialog. Confirm that the matching cells are selected on the sheet. Choose Home > Clear > Clear Contents. This removes the entries but keeps the cells and their formatting; it does not shift other cells or delete rows.
  7. Check the result. Look over the affected range and use Undo immediately if the wrong cells were selected. Save the workbook only after confirming that the correct entries are blank and needed formulas remain intact.

On a Mac, use Command+F to open Find and Command+A to select all items in the results list; the ribbon command is still Home > Clear > Clear Contents. In Excel for the web, available Find options and selection behavior can differ. If you cannot select all Find results as described, use a filter on the relevant column or open the workbook in the desktop app.

Method 2: Delete entire rows that contain zero

Choose this method when a zero in a specific field means the complete record should be removed. Deleting a row removes every value in that row, not just the zero. Make a copy first, especially if the sheet contains formulas, totals, or data linked to other sheets.

  1. Check the structure. Make sure the data has a single header row and that the column you want to check has a clear heading, such as Quantity or Status. If the zero is only one of several reasons to remove a record, decide how those conditions should be handled before filtering.
  2. Turn on filters. Click a cell in the data range and choose Data > Filter. Use the dropdown arrow in the column heading for the field that should equal zero.
  3. Show only zero entries. In the filter menu, clear Select All, select 0, and apply the filter. If the list includes both a numeric zero and text stored as 0, check how Excel displays the values and whether both should count. Avoid selecting a blank item by mistake.
  4. Review the visible records. Confirm that every visible row is meant to be deleted. Filtering may reveal zeros in records that should stay, for example a completed transaction with a zero balance. Do not proceed based only on the number of results.
  5. Delete the filtered records. Select the visible row numbers for the records, not the header, then choose Home > Delete > Delete Sheet Rows. Check that the filtered records have been removed and that the remaining rows are still present. Use the filter dropdown to clear the filter when finished.

If you want to keep each row and remove only the zero in the filtered column, select the visible cells in that column and use Home > Clear > Clear Contents instead. Be careful with filtered selections: check the highlighted cells before clearing or deleting, since a mistaken selection can affect data outside the intended field.

Method 3: Remove the zero digit from values

Sometimes the request means removing every character 0, even when it is part of a larger value. For example, replacing 0 with nothing could change 105 to 15 and 2.05 to 2.5. This is not the same as deleting cells whose value equals zero, and it can change the meaning of numbers, dates, codes, and identifiers. Use it only on a copied range when that change is genuinely intended.

  1. Select only the cells where zero characters should be removed. Do not select the entire sheet unless you have checked every section and understand the consequences.
  2. Press Ctrl+H on Windows to open Find and Replace. Enter 0 in Find what and leave Replace with empty.
  3. Inspect the options. Turn off Match entire cell contents if you intend to remove zeros inside longer values. Set Look in to Values if the target is the displayed cell content. Replacing zeros in formula text instead can alter formulas, so do not choose that setting for ordinary data cleanup.
  4. Choose Find Next first and inspect examples of what Excel finds. If the previewed matches include values you must preserve, cancel and narrow the selection.
  5. Choose Replace All only after checking the scope, then inspect representative cells and totals. On a Mac, use Command+H if that shortcut opens Find and Replace in your Excel version; otherwise open it from the Home tab. Keep the original copy in case the replacements change important values.

Method 4: Hide displayed zeros without deleting them

If the worksheet simply looks cluttered, preserve the values and formulas and hide zero displays instead. In Excel for Windows, choose File > Options > Advanced. Under Display options for this worksheet, select the intended sheet and clear Show a zero in cells that have zero value, then choose OK. This setting applies to that worksheet, not necessarily every sheet in the workbook. To show the zeros again, return to the same setting and turn the checkbox back on. Excel for Mac places worksheet display preferences in its Excel settings or preferences, and the exact location can vary by release; look for the View settings for the current sheet.

Common Mistakes

  • Confusing blank cells with zero values. A cell displaying 0 is not empty. Conversely, a blank cell may look like zero if a formula, custom number format, or surrounding layout makes it appear that way. Check the formula bar and cell contents before choosing a cleanup method.
  • Removing zeros from larger numbers unintentionally. Find and Replace without Match entire cell contents may change 100 to 1, or alter values such as 0.25. Use exact matching when the goal is to clear only cells equal to zero.
  • Deleting formula results by accident. A formula can currently return zero and still be needed later. Find set to Values may locate that result. Clearing the cell removes its formula, so inspect matches or use a display-only option if you merely want to hide the zero.
  • Deleting a row when only one cell should change. A row may contain names, dates, or other values that need to be retained. Choose Clear Contents for a cell-level cleanup; use Delete Sheet Rows only when the whole record should go.
  • Including the heading in the selection. When filtering, do not select or delete the header row. Deleting it can break filters and make the data harder to interpret.
  • Assuming Undo will always save the day. Ctrl+Z can reverse many recent actions, but not every action remains undoable after closing, running other operations, or using some external data workflows. A saved duplicate is more dependable.

Tips

  • Start with a small, representative range and confirm the outcome before applying the same process to a large worksheet.
  • Use Match entire cell contents for a precise search for the value 0. It is the key setting that distinguishes a cell equal to zero from a value that merely contains a zero digit.
  • Use Clear Contents rather than the Delete key on selected cells when you want to preserve formatting and avoid shifting neighboring cells. The Delete command on the Home tab can offer options that remove cells or rows, so read the command before confirming.
  • For a structured Excel table, filtering by its column heading is often easier to review than searching the whole sheet. Keep a copy of the original table if you may need to restore deleted records.
  • If you need to keep the formula but show a blank when its answer is zero, edit the formula or use a display format rather than clearing the cell. Test the change on a copy, because changing a formula can affect downstream calculations.
  • After removing entries, check totals and dependent calculations. A blank cell and a zero value can behave differently in some formulas, charts, and reports.

Troubleshooting

Find All returns no results. Confirm that the search is within the intended sheet or selected range, that Find what contains 0, and that Match entire cell contents is enabled for exact zero matches. Check Look in: a cell may contain a formula whose displayed answer is zero, or the visible 0 may be text. Also check whether the worksheet is protected or the relevant cells are in a different sheet.

Excel finds more cells than expected. Stop before clearing. Check whether Match entire cell contents is off, whether the search is looking at formulas rather than displayed values, and whether the selected range includes unrelated sections. Narrow the range and run Find All again. Inspect any formula cells individually.

Zeros remain after clearing. The remaining display may come from formulas, another sheet, or a number format rather than the cells you cleared. Search each relevant sheet separately. If a formula still returns zero, clearing a different cell will not change it; decide whether to change the formula or hide zero displays.

The filter does not show the zero you expect. Check for values stored as text, blanks, or values with decimals that are displayed rounded to 0. A number such as 0.004 may look like 0 because of its number format but is not equal to zero. Increase the displayed decimal places or inspect the formula bar before deleting records.

The wrong cells or rows changed. Press Ctrl+Z immediately on Windows, or Command+Z on Mac, if the action is still available. If it is not, close without saving only if you are certain the changes have not been saved and you do not need other recent edits. Otherwise restore from the duplicate workbook or a version history copy.

Frequently Asked Questions

Can I delete every zero from every worksheet at once?
Find and Replace can be scoped to a workbook in some Excel dialog versions, but a workbook-wide replacement is risky because it may affect formulas, codes, and unrelated sheets. Search and review each sheet or selected range separately unless you have a verified copy and a clearly defined rule.

Will clearing a zero cell delete its formatting?
No, Home > Clear > Clear Contents removes the cell entry or formula while retaining formatting such as fill color and borders. Clear All removes formatting too, so it is not the right choice when you only want to empty the cell.

How can I remove zero-valued cells but keep formulas that currently return zero?
Do not clear Find All results without reviewing them: formulas returning zero can be included when searching values. The safest choice is to inspect the matches and clear only confirmed constants, or hide zero displays if the aim is visual. Excel does not turn a formula cell into a blank while preserving that same formula unchanged.

Does filtering and deleting zero rows affect formulas?
It removes the entire selected records, which may change totals or formulas that refer to those rows. Review the formulas and dependent reports afterward. Save a copy first, and use Clear Contents instead if you only intend to empty one field.

Can I make zeros invisible without changing the values?
Yes. Turn off Show a zero in cells that have zero value in the worksheet’s Advanced display options on Windows. The values remain available to formulas, and you can restore their display by turning the option back on.

What if a value looks like zero but is not exactly zero?
It may be a small decimal rounded by the cell’s number format. Increase the number of displayed decimal places or select the cell and inspect the formula bar. Do not delete it as an exact zero unless its underlying value is actually zero.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.