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.
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 ExcelFind and replace all blank cells with VBA code
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 easily fill all blank cells in the selected range with a certain number or text in Excel, as shown in the demo below.
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
The Fill Blank Cells utility in Kutools for Excel offers a user-friendly and flexible way to fill blank cells in selected ranges with fixed values or according to various fill patterns (e.g., based on surrounding values). This approach is especially useful for users who want more advanced fill options or batch processing functionalities without writing macros or complex formulas.
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: This utility also allows you to fill blanks based on existing values in the range or in linear progression, offering high customization for various data-processing 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, different approaches are suitable for different types of data, Excel versions, and workflow needs. With built-in methods, double-check that only truly blank cells are impacted. When using VBA or third-party tools like Kutools for Excel, always back up your data beforehand. If none of the above methods fits your scenario, you may consider using advanced filtering or pivot tables to identify and manage blank cells. The key is to choose the method that ensures data integrity, meets your fill requirements, and fits your preferred Excel workflow.
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