How to fill blank cells with 0 or other specific value in Excel?
When working with large Excel datasets, it is common to encounter numerous blank cells scattered throughout your worksheet. These blanks can cause confusion or calculation errors, especially when totaling columns or performing data analysis. Instead of manually filling each blank cell one by one—a tedious and inefficient process for large tables—there are several practical techniques to quickly fill all the empty cells with a value of your choice, such as 0, "N/A", or any specific text. Below, this article provides a range of straightforward methods to fill blank cells efficiently, covering built-in Excel functions, formulas, VBA code, and specialized tools.
Fill blank cells with 0 or other specific value with Go To Special function
Excel’s Go To Special function offers a quick way to locate and fill blank cells inside a selected range. This method is useful when you need to directly overwrite all existing blanks with the same value in one action.
1. First, highlight the area containing the blank cells you want to fill. Selecting the relevant range ensures that only those cells are affected.
2. Go to the Home tab, click Find & Select and choose Go To Special…. The Go To Special dialog will appear; select the Blanks option under Select and click OK. All blank cells within your chosen range will be selected simultaneously. See screenshot:
3. Without clicking away, immediately type the value you want to insert (such as 0 or a specific word) into one of the selected blank cells, then press Ctrl + Enter together. Excel will fill every highlighted blank cell with your input. See screenshots:
 |  |  |
You can use this method to fill blanks with any value you like, including the number 0 or a custom label. This approach replaces any formulas in those blank cells with your fixed value, so use caution if your worksheet contains formulas you need to preserve.
Applicable when you want one fixed value for all blank cells and don’t need to preserve formulas in those cells. This method is fast for medium-sized ranges but may be time-consuming for extremely large datasets or non-contiguous selections.
Fill blank cells with 0 or other specific value quickly with Kutools for Excel
For those who use Kutools for Excel, the Fill Blank Cells feature greatly streamlines the process of filling empty cells. This utility enables several fill options—including fixed values, linear series, and value propagation—with just a few clicks, eliminating repetitive manual input and formula adjustments.
To use this tool after installing Kutools for Excel:
1. Navigate to the Kutools tab, then select Insert > Fill Blank Cells. Here is a related screenshot for reference:
2. Specify the range where blanks need to be filled. In the Fill Blank Cells dialog box, choose the Fixed Value option under Fill With. Enter the number, text, or symbol you wish to fill into the blank cells in the Filled Value box. You may also preview changes before confirming. See screenshot:
3. Click OK or Apply. Instantly, all selected blank cells are filled with your chosen value, as shown in the following images:
 | |  |
Kutools for Excel supports flexible options such as filling with fixed values, linear data, or copying adjacent cell values. It is suitable for handling large ranges and complex worksheets efficiently.
Click to Download and free trial Kutools for Excel Now!
Fill blank cells with 0 or other specific value with VBA code
If you are comfortable using macros, applying VBA code allows you to automate the process of filling blank cells with any value, which is particularly useful for repetitive tasks or processing very large ranges. Since VBA works directly on the worksheet, it provides more flexibility, such as prompting the user for which value to fill or performing conditional actions.
1. Select the data range containing blank cells you want to fill.
2. Click the Developer tab, then Visual Basic to open the Microsoft Visual Basic for Applications window. In the window, click Insert > Module, and paste in the following code:
Sub FillEmptyBlankCellWithValue()
Dim cell As Range
Dim InputValue As String
On Error Resume Next
InputValue = InputBox("Enter value that will fill empty cells in selection", _
"Fill Empty Cells")
For Each cell In Selection
If IsEmpty(cell) Then
cell.Value = InputValue
End If
Next
End Sub3. Click the 
button or press F5 to run the code. You will see a prompt where you can input any value, such as 0 or custom text to fill into the blank cells. See screenshot:
Tips: Type 0 in the dialog if you specifically want to fill blanks with zero. Make sure only blank cells get replaced; formulas in empty cells may be overwritten.
4. Click OK to execute and instantly fill all blank cells within your chosen range with the specified value.
VBA-based solutions are best when filling blanks in large datasets or when repeating the process across multiple sheets. However, always save your workbook before running new macros in case you need to revert changes due to errors.
Fill blank cells with value above / below / right / left with Kutools for Excel
Sometimes, you want each blank cell to inherit the value from an adjacent cell—such as the cell directly above, below, left or right. Kutools for Excel’s Fill Blank Cells makes these structured fills easy, which is helpful for organizing data with repeated headings or grouped entries.
1. Select your data range where you need to fill blanks by reference to other cells, then start the Fill Blank Cells feature as described above.
2. In the dialog, pick Based on values from the Fill with section, and select the specific direction: down, up, right or left. For example, “down” fills blank cells with the value from the cell immediately above. See screenshot:
3. Click Ok or Apply to complete the process and observe the blank cells populated as desired. Screenshots below show before-and-after states:
 | |  |
This approach works well for datasets where blank cells should logically repeat nearby values, such as category lists or grouped records.
Click to Download and free trial Kutools for Excel Now!
The Fill Blank Cells utility also supports filling cells with linear values in a single click. For additional information, see this guide.
Fill blank cells with 0 or other specific value using Excel Formula
Excel formulas can offer a dynamic way to handle blank cells. Using the IF function or similar expressions in a helper column, you can create logic that tests for blank or empty values and replaces them with your specified value. This is particularly useful if you want calculations or downstream processes to use the new values automatically, while leaving your original data unchanged.
You might use this approach in a situation where you want to preserve the original data and generate a processed column with blanks replaced by 0 or a custom string.
1. In a new blank column adjacent to your data, enter this formula in the first row (assuming your source data starts in cell A1):
=IF(A1="",0,A1)2. After entering the formula, press Enter to confirm. Then, copy the formula down for all rows matching your data range by dragging the fill handle.
If your data covers a different cell range, adjust A1 to match your first cell. This formula checks if the cell is blank using A1="". If it’s blank, the formula returns 0; otherwise, it returns the original value. You can substitute 0 with any value or text to meet your needs.
3. If you want to overwrite the original blanks, copy this helper column and paste it back as values over the original column (use Paste Special > Values).
This method is reliable for column-by-column processing and can be customized for different logical requirements. However, it does require adding a new column temporarily and manually pasting back results if you want to replace the source data.
If you need an automatic in-place fill, consider Go To Special or Kutools approaches instead.
Fill blank cells with 0 or other specific value using Power Query
Power Query, available in modern versions of Excel, provides robust tools for transforming and cleaning data when importing or reshaping tables. Among its features, you can quickly replace all blank or null cells with a custom value during the data transformation process. This solution is best for more complex datasets or when you want to automate data cleaning as part of a routine import.
Advantages: Power Query allows step-by-step changes to be tracked and reused, and avoids manual worksheet editing for frequently refreshed or imported data.
How to fill blanks with Power Query:
1. Select your data range and click Data > From Table/Range to bring your data into Power Query.
2. In the Power Query Editor window, select the target column. Right-click the column header and choose Replace Values.
3. In the dialog that appears, enter null (or leave blank if blanks are displayed) in the “Value To Find” box, and enter 0 or your desired value in the “Replace With” box. Click OK.
4. When finished, click Close & Load to bring your cleaned data back into Excel. All blanks will be replaced with your chosen value.
This approach works well for tables with multiple columns and helps minimize manual intervention. Take care to check your column types—sometimes Power Query interprets blanks as zeros or nulls differently depending on data types.
Related articles:
Fill blank cells with linear values
Fill blank cells with value above
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in