How to find and replace all blank cells with certain number or text in Excel?
When working with Excel, you may often encounter ranges that contain blank cells interspersed with data. Leaving blank cells unattended can cause issues in data analysis, chart generation, and downstream calculations. To ensure data consistency or for further processing, you may want to find and replace all these empty cells with a specified value, such as a number, text label, or placeholder.
This article introduces multiple practical methods to locate and fill blank cells in Excel, suitable for different scenarios. You can choose the approach that best matches your workflow needs, ranging from built-in Excel features to VBA and professional add-ins. Each method comes with parameter explanations, potential issues, and practical tips for error-free operation.
Compare methods for finding and filling blank cells
Find and replace all blank cells with Find and Replace function
Other Built-in Excel Methods – Use Go To Special to fill blank cells quickly
Easily fill all blank cells in a range with Kutools for Excel
Find and replace all blank cells with VBA code
Compare methods for finding and filling blank cells
All four methods can fill blank cells with a specified number or text, but they differ in workflow and flexibility. Find and Replace and Go To Special are convenient built-in Excel options, VBA provides programmable automation, while Kutools for Excel offers a dedicated Fill Blank Cells tool that can handle fixed values as well as other common filling patterns.
| Method | Best for | Workflow | Key consideration |
|---|---|---|---|
| Find and Replace | Quickly replacing true blanks with one fixed value | Leave Find what empty, enter the replacement value, and click Replace All | Built into Excel, but does not replace cells containing formulas that return "" |
| Go To Special | Explicitly selecting all true blank cells before filling them | Select Blanks, type the desired value, then press Ctrl + Enter | No formulas or add-ins, but requires several manual selection steps |
| Kutools for Excel | Quickly filling blanks in selected ranges with flexible fill options | Select the range, open Fill Blank Cells, choose Fixed value, enter the value, and apply | The same tool also supports filling based on surrounding values or linear values |
| VBA | Repeated or customized automation | Insert and run a macro, then select the range and specify the fill value | Flexible and reusable, but requires VBA code and macro permissions |
Find and replace all blank cells with Find and Replace function
Excel’s Find and Replace function allows you to quickly fill all blank cells in the selected range with your desired content, such as a number or text. This method is simple, direct, and does not require advanced Excel skills. However, it is most suitable for ranges where blank cells are truly empty (not containing invisible formulas or spaces).
1. Select the range containing the blank cells you wish to fill. To quickly select a data range, click any cell within the data, then press Ctrl+A. Once selected, press Ctrl + H together to open the Find and Replace dialog box.
2. In the dialog that appears, make sure the Replace tab is active. Leave the Find what field empty, then enter your specified value (such as a number or string) into the Replace with field. Click Replace All to execute. See the screenshot below:
3. After clicking, a prompt will notify you of how many replacements were made. Click OK to confirm.
Once the operation is complete, all blank cells in your selection will be filled with the value you entered. This approach is highly efficient for moderate datasets but may not replace cells with formulas that return "" (empty text). Always double-check for such cases, as these will not be found using Find and Replace alone.
Easily fill all blank cells with a certain value in Excel:
Kutools for Excel's Fill Blank Cells utility helps you directly fill all blank cells in the selected range with a certain number or text. Unlike methods that require you to find or select blank cells separately, the dedicated tool handles the filling process from one dialog and also supports other fill patterns when needed.
Download and try it now! (30-day free trial)
Other Built-in Excel Methods – Use Go To Special to fill blank cells quickly
“Go To Special” provides a highly efficient way to select all blank cells in a range at once, which is particularly helpful when you want to fill all blank cells simultaneously with the same value. This method works for both small and large ranges and is appropriate when you prefer not to use formulas or VBA, and want a straightforward bulk fill operation.
1. Select the range (or entire column/row) that contains blank cells you wish to fill.
2. Press F5 to open the Go To dialog, then click Special. In the Go To Special dialog, select Blanks and click OK. All blank cells in the selected range will now be highlighted.
3. With all blanks now selected, type the value or text you wish to fill (such as 0 or "N/A"), then press Ctrl + Enter. This action fills each blank cell at once.
Tips: This method works best for true blank cells and will overwrite any previously existing content in the blanks. If formulas in certain cells return empty text (""), they will not be detected as blanks; you may need an alternative approach for these cases. Always confirm your range before executing the fill to avoid unintentional data changes.
Applicable Scenario: Ideal for quick, bulk fill operations without needing extra columns or complex tools.
Advantages: Uses Excel's built-in functionality, requires no formulas or add-ins.
Disadvantages: Cannot process non-empty “blank-looking” cells (such as those with formulas outputting empty strings).
Easily fill all blank cells in a range with Kutools for Excel
If you frequently need to handle blank cells and want to avoid switching between Find and Replace, Go To Special, formulas, and macros, Kutools for Excel's Fill Blank Cells utility provides a dedicated solution. For this task, simply use the Fixed value option to fill all blanks in the selected range with the same number, text, or placeholder.
1. Select the range with blank cells you want to fill, then go to Kutools > Insert > Fill Blank Cells.
2. In the Fill Blank Cells dialog box, select the Fixed value option under Fill with, enter your desired value (number or text) into the Filled value field, and then click OK.
All selected blank cells will instantly be populated with the value you specified, as shown in the screenshot below.
Note: The Fill Blank Cells utility is not limited to fixed values. You can also fill blanks based on existing values in the range or with linear values, allowing the same tool to handle several common blank-cell filling scenarios. If your worksheet uses filters, consider clearing them before filling blanks to ensure all intended cells are included.
If you want to have a free trial (30-day) of this utility, please click to download it, and then go to apply the operation according above steps.
Find and replace all blank cells with VBA code
For users who need a more automated or powerful approach, utilizing VBA allows for efficient batch processing of blank cells, especially in larger datasets or repeatable processes. This method is especially suitable if you frequently need to fill blanks across varying ranges, as you can customize the code for your workflow.
1. Open the VBA editor by pressing Alt + F11.
2. In the Microsoft Visual Basic for Applications window, click Insert > Module, and paste the VBA code provided below into the module window.
VBA code: Replace blank cells with certain content
Sub Replace_Blanks()
Dim xStr As String
Dim xRg As Range
Dim xCell As Range
Dim xAddress As String
Dim xUpdate As Boolean
On Error Resume Next
xAddress = Application.ActiveWindow.RangeSelection.Address
Set xRg = Application.InputBox("Please select a range", "Kutools for Excel", xAddress, , , , , 8)
Set xRg = xRg.SpecialCells(xlBlanks)
If (Err <> 0) Or (xRg Is Nothing) Then
MsgBox "No blank cells found", , "Kutools for Excel"
Exit Sub
End If
xStr = Application.InputBox("Replace blank cells with what?", "Kutools for Excel")
xUpdate = Application.ScreenUpdating
Application.ScreenUpdating = False
For Each xCell In xRg
xCell.Formula = xStr
Next
Application.ScreenUpdating = xUpdate
End Sub3. To run the code, press F5 or click the Run button (
). A dialog box will prompt you to select the target range containing blank cells, then click OK.
4. Next, in the dialog box that appears, enter the value or text you want to fill into all blank cells and confirm with OK. See screenshot below:
Within seconds, all blank cells in your selection will be filled as specified.
Note: If there are no blank cells in the selected range, a prompt will notify you of this. Also, if your data has formulas resulting in empty strings, these will typically not be recognized as blanks by the macro. Remember to save your workbook before running the VBA code, especially on important datasets, to avoid unintended results and to facilitate undoing actions if needed.
In summary, Find and Replace and Go To Special are practical built-in options when you simply need to fill true blank cells with one fixed value. VBA provides more flexibility when the task needs to become part of an automated workflow. For regular blank-cell processing, Kutools for Excel offers a more versatile point-and-click solution because the same Fill Blank Cells tool can handle fixed values, neighboring values, and linear fills without formulas or macro code.
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