To remove zero values without disturbing nearby cells, select the worksheet, press Ctrl+F, search for 0, choose Options, turn on Match entire cell contents, select Find All, select the results, close the dialog, and press Delete. Back up the workbook first, and check whether any zeroes are formula results: clearing those cells deletes their formulas too. If you mean deleting entire records that contain a zero, use a filter instead.
What You’ll Need
- A copy of your workbook or a saved backup. Clearing cell contents is not always easy to reverse after more changes have been made.
- Microsoft Excel for Microsoft 365 on Windows. The steps below use its standard Find and Filter commands.
- A clear decision about what “delete zero values” means for your data: clear only cells whose value is zero, remove complete rows based on a zero in a chosen column, or display blanks in place of zeroes without changing the stored values.
- If you are working with important data, know whether zeroes are entered values or results returned by formulas before removing them.
Step-by-Step
- Save a backup and choose the scope. Save the workbook with a different name, or make a copy of the sheet before editing. Decide whether you want to search only the active worksheet or the entire workbook. A workbook-wide search can affect sheets you are not currently viewing, while a sheet-only search leaves the other sheets alone. Also decide whether the goal is to clear individual zero cells or delete records. Clearing a cell leaves the rest of its row and column in place; deleting a row removes all data across that row.
- Check how zeroes are stored. Click a few cells that appear to contain zero and inspect the formula bar. A cell may contain a typed numeric zero, text that looks like 0, or a formula whose result is zero. A custom number format may also hide or alter what is displayed. These cases matter: clearing a formula removes the formula, and searching only for displayed values may not find a value that is hidden by formatting. If formulas are involved, decide whether you want to remove the formula or preserve it and change only how its result is displayed.
- Open Find and Replace. Click the worksheet you intend to edit. Press Ctrl+F on Windows to open the Find tab of the Find and Replace dialog. On Mac, press Command+F. Expand the search settings by clicking Options if they are not already shown. In the Find what box, enter 0. Do not use Replace All with a blank replacement as your first choice: a search for 0 can match part of values such as 10 or 0.25 unless you configure the search carefully.
- Set the search options carefully. Turn on Match entire cell contents so Excel looks for cells containing just zero rather than any number that includes a zero. Set Within to Sheet if you want to work only on the current worksheet, or Workbook if you deliberately want to search all sheets. For Look in, choose Values when you want to search the displayed results, including formula results that evaluate to zero. Choose Formulas only if you specifically want to search formula text; that setting does not mean “find every formula that returns zero.” If your Excel version shows additional search options, leave them unchanged unless you know you need them.
- Find every match and inspect the results. Click Find All. Excel lists the matching cells in the results area of the dialog. Review the list before changing anything. Check the sheet and cell references, and make sure the results are in the intended area. If the results include cells with formulas, clearing them will remove those formulas. If the list includes a value you meant to keep, cancel the operation and revise the scope or search settings rather than deleting first and trying to repair the data later.
- Select all the found cells. Click in the results list and press Ctrl+A on Windows to select all listed matches. On Mac, use Command+A if it selects the result list in your Excel version; otherwise select the results in the list and verify that Excel has highlighted the matching cells. Close the Find and Replace dialog without clicking another worksheet cell. The found cells should remain selected on the sheet. If you are unsure, check the selection and the status bar before proceeding.
- Clear the selected values. Press the Delete key. This clears the cell contents but does not shift neighboring cells, so surrounding rows and columns stay aligned. Do not choose Delete Cells or Delete Sheet Rows unless you intentionally want to move other cells or remove entire records. If your selection contains formulas, the Delete key removes them as well as their displayed zero results. If a warning or confirmation appears, read it and cancel if it does not match the change you intended.
- Verify the worksheet. Search again using the same exact-match settings and scope. If Find All reports no matches, the zero values in that scope have been cleared. Check several cells around the edited areas and review important totals, formulas, charts, and linked sheets. Clearing a zero input can change downstream calculations, and deleting a formula that returned zero can cause other formulas to behave differently. Save the workbook only after the result looks right; if it does not, use Ctrl+Z on Windows or Command+Z on Mac immediately to undo.
- Use a filter if you mean to remove complete rows. If each record is a row in an Excel table, click inside the table and use the filter arrow on the column that determines which records to remove. Filter the column to show only 0. Confirm that the visible records are exactly the ones you want to discard. Select those table rows, right-click, and choose Delete Table Rows. For a normal range rather than a table, use the filter arrows and carefully delete the visible worksheet rows; deleting sheet rows also removes anything else stored in those rows. Clear the filter afterward and check that the remaining records and totals make sense.
Optional: show blanks instead of deleting values. If you want a separate output that displays blanks for zeroes while leaving your source data intact, use a formula in a new column. With source values in A2:A100, enter =IF(AND(A2<>"",A2=0),"",A2) in B2 and fill it down. For example, if A2 contains 0, B2 displays blank; if A3 contains 7, B3 displays 7. The check for a nonblank source prevents an empty A cell from being treated as a zero for this purpose. This creates formula results, not genuinely empty cells. Keep the original column until you have reviewed the output.
Common Mistakes
Using Replace All without an exact match. Replacing every 0 with nothing can damage values such as 10, 20, or 0.5, and it may alter formulas if Excel is searching formula text. Use Find All with Match entire cell contents, review the results, and clear only the selected cells. If you do use replacement for a carefully controlled range, verify the options and preview the matches first.
Removing rows when only cells needed clearing. Deleting a whole row can remove names, dates, notes, and other data that have nothing to do with the zero. Conversely, clearing only a zero cell does not remove the record. Decide which action fits your data and use Delete for contents or Delete Table Rows for complete table records.
Deleting formula results by accident. A formula that evaluates to 0 is still a formula. If you clear its cell, the calculation is gone. Inspect representative matches in the formula bar and consider hiding zero display or creating a separate output column if the formula must remain.
Searching the wrong scope or setting. A search restricted to the current selection, a single sheet, or formula text can miss values elsewhere. Check the Within setting, clear an unintended selection if needed, and choose Values when looking for displayed results. Run a second search after the edit to confirm the intended scope.
Confusing a displayed zero with a stored zero. A cell might show a dash, a blank, or a rounded 0 because of its number format even though its underlying value is different. Click the cell and inspect the formula bar and number format before deleting anything. Searching for the displayed character may not find the underlying numeric value you expect.
Tips
Work on a copy when the range is large, shared, or connected to reports. If the workbook is stored in OneDrive or SharePoint, consider using version history as an additional recovery option, but do not treat it as a substitute for checking your edits. In a filtered table, confirm the filter criterion and the visible row count before deleting records. A zero in a quantity field may mean “none,” while a zero in a status or account field may be meaningful information.
If your only aim is to make zeroes disappear from the screen, changing the display is safer than clearing data. In Excel for Microsoft 365 on Windows, open File > Options > Advanced, find Display options for this worksheet, and clear Show a zero in cells that have zero value. This setting applies to the selected worksheet and hides zeroes there; it does not delete them. On Mac, Excel has a worksheet setting for displaying zero values in its preferences or sheet options depending on the version. Excel for the web may not offer all the same desktop options, so use the desktop app if you need that setting.
For a one-off cleanup, Find All is useful because you can inspect every match before clearing. For a repeatable process, make a clean copy of the source data and document the filter or criteria you used. Avoid selecting a broad range and pressing Delete without checking: if the range includes headers, formulas, or notes, those will be cleared too. After any bulk change, check key totals against a saved copy or another trusted source.
Troubleshooting
Find All returns no matches. Confirm that the search box contains 0, Match entire cell contents is on, and Within is set to the correct sheet or workbook. If the cell displays a zero produced by a formula, set Look in to Values. If a cell shows a dash or blank, inspect its actual value and formatting instead of assuming it contains numeric zero.
Values such as 10 or 0.25 appear in the results. The exact-cell option may be off, or Excel may be matching text differently than expected. Cancel without clearing, turn on Match entire cell contents, and run Find All again. Review the result references before selecting them.
Only some zeroes were removed. Some may be on another sheet, outside the selected range, or stored as text. Search the whole workbook only if that is intended. If text zeroes remain, inspect their cells and use a small test range to determine how Excel is interpreting them before applying another bulk edit. Avoid broad replacement until you have verified its effect on a copy.
The zeroes return after recalculation. Those cells probably contain formulas that still calculate to zero, or the workbook refreshes values from an external source. Clearing the result removes the formula temporarily, but a refresh or recalculation can restore the output. Keep the formula and use a display setting or a separate formula column if the goal is simply to hide zeroes.
You deleted more than intended. Immediately press Ctrl+Z on Windows or Command+Z on Mac, before making unrelated changes. If undo is no longer available, restore the saved backup or a suitable version from OneDrive or SharePoint history. Avoid saving over your only clean copy until you have verified the repaired data.
Frequently Asked Questions
Does pressing Delete remove a cell or just its value?
Pressing Delete clears the contents of selected cells but leaves the cells in place. It does not shift surrounding data. Use a cell-deletion or row-deletion command only when you intentionally want to rearrange the worksheet.
Can I delete only zeroes and keep 10 or 0.5?
Yes. In Find and Replace, enter 0, turn on Match entire cell contents, and use Find All to review the matches before clearing them. Without the exact-match option, a search may include values that merely contain the character 0.
Will this remove zeroes returned by formulas?
Find with Look in set to Values can locate formula cells whose displayed result is zero. Clearing those cells deletes their formulas. If the formulas need to stay, change the worksheet’s zero-display setting or create a separate output formula instead.
How do I delete entire rows where a column equals zero?
Filter the relevant table column to show 0, verify the visible records, then select and delete those table rows. For a regular worksheet range, deleting sheet rows affects every column in those rows, not just the filtered column.
Can I hide zeroes without deleting them?
Yes. Use File > Options > Advanced in Excel for Microsoft 365 on Windows and clear Show a zero in cells that have zero value for the selected worksheet. The values and formulas remain available for calculations; only their display changes.
Can I do this in Excel for the web or on Mac?
Find and filter features are available in Excel for the web and Excel for Mac, though dialog layouts and some options can differ. On Mac, use Command+F and Command+Z in place of the Windows Ctrl+F and Ctrl+Z shortcuts. For worksheet-wide zero display settings, the desktop version may offer options not present in the web app.
Related
- How to Copy the Upper Cell in Excel with a Shortcut
- Copy and Unhide Cells in Excel: Step-by-Step Guide
- Copy Text into Multiple Cells in Excel: A Quick Guide
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Ctrl+E in Excel: What the Shortcut Does and How to Use ItIn Excel for Microsoft 365 on Windows, Ctrl+E runs Flash Fill. Flash Fill recognizes a pattern in your…
- Create a Slicer in Excel: Setup, Filtering, and TipsTo create a slicer in Excel for Microsoft 365 on Windows, select a cell in an Excel table…
- Translation French English in Google Sheets: How It WorksTo translate French into English in Google Sheets, enter a source phrase in a cell and use the…
- Excel Payment Request: Create, Track, and Send OneAn Excel payment request is a spreadsheet you use to tell a customer or client what they owe,…
- MS Word Data Table: How to Create, Format, and Use ItIn Microsoft Word for Microsoft 365, make a data table by choosing Insert > Table, then selecting the…
- Wedding Budget Spreadsheet: Free Google Sheets SetupYes. You can make a wedding budget spreadsheet for free in Google Sheets with a personal Google account,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.