Replace Blank Cells with Zero in Excel for the Web
Question details
User needs to replace blank cells with zero in Excel for the web, but the web version lacks the desktop Find & Replace options for selecting blank cells.
- Product
- Microsoft Excel for the web
- Device & OS
- not provided
- Scenario
- Editing a worksheet in Excel for the web and wanting to fill all empty cells in a selected range with 0.
- Observed behavior
- Excel for the web does not provide the same Find & Replace or Go To Special options to select and fill blank cells, so the blank cells are not changed directly.
Confirm that you have editing permission for the workbook and select only the exact range that should have blank cells changed to zero. If the Automate tab is not visible in Excel for the web, use the WPS Spreadsheets method described below.
Run an Office Script to Fill Selected Blank Cells with Zero
Use this method when Excel for the web has the Automate tab available and you want to update only blank cells in the selected range.
This Office Script uses getSpecialCells(ExcelScript.SpecialCellType.blanks) to identify only empty cells, so formulas, text, and existing numbers remain unchanged.
Sign in to your Microsoft account, open the workbook, and click the sheet tab that contains the blank cells.
Drag to select the cells you want to update, or click the first cell and Shift-click the last cell in the range.
Click the Automate tab in the ribbon and choose New Script. Delete the default code in the Code Editor.
Paste the following script and click Run: function main(workbook: ExcelScript.Workbook) { const emptyAreas = workbook.getSelectedRange().getSpecialCells(ExcelScript.SpecialCellType.blanks); emptyAreas.getAreas().forEach(area => { area.setValue(0); }); } The script changes only blank cells in the selected range.
Use WPS Spreadsheets to Fill Blank Cells with Zero Without Scripts
Use this alternative when the Automate tab is unavailable or when you prefer a no-script desktop method.
Fill Blank Cells with Zero in WPS Spreadsheets Without Office Scripts
WPS Spreadsheets includes the desktop-style Go To Special command that Excel for the web lacks, letting you select all blank cells in a highlighted range and fill them with 0 in one action.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, click Spreadsheets, and open the .xlsx workbook you need to update.
- 2. Select the range to update: Drag over the cells that should have blanks filled with zero.
- 3. Go to special blanks: Press Ctrl+G, click Special, choose Blanks, and click OK to highlight all empty cells in the selected range.
- 4. Type zero and fill all blanks: Type 0 on the keyboard, then press Ctrl+Enter to fill every highlighted blank cell with zero.
- 5. Save the workbook: Click the Save icon or press Ctrl+S to keep the changes in the same .xlsx format.
Frequently Asked Questions
Why does Excel for the web not have a Find and Replace option for blank cells?
Excel for the web offers fewer advanced Find & Select and Go To Special options than the desktop application, so you need an Office Script or a spreadsheet tool such as WPS Spreadsheets.
Will the Office Script change cells that contain formulas or text?
No. The script uses ExcelScript.SpecialCellType.blanks, so it only changes empty cells in the selected range and leaves formulas, text, and numbers unchanged.
What can I do if the Automate tab is missing in Excel for the web?
The Automate tab must be available or enabled by your organization. If it is missing, you can use WPS Spreadsheets and choose Home > Find and Select > Go To > Special > Blanks to fill blank cells with zero.
How do I fill blank cells with zero in WPS Spreadsheets?
Select the range, press Ctrl+G, click Special, choose Blanks, click OK, type 0, and press Ctrl+Enter to fill all selected blank cells at once.
Does filling blank cells with zero affect blank cells outside my selected range?
No. Only empty cells within the selected range are updated, whether you use the Office Script in Excel for the web or the Go To Special method in WPS Spreadsheets.
