Excel: How to fill blank cells with N/A using three methods
Table of Contents
Looking for a simple way to fill empty cells with “N/A” in your Excel sheets? You’ve come to the right place. Excel has several straightforward methods to help you do just that, making sure no cell is left blank.
In this easy-to-follow guide, we’ll walk you through three quick steps to add “N/A” to any empty spaces in your spreadsheet. Whether you’re new to Excel or just looking for a quick fix, this guide is perfect for keeping your data neat and complete. So, without wasting another second, let’s get straight into it!
AMD launches latest Ryzen 9 9950X3D & 9900X3D CPUs!
AMD's highly anticipated Ryzen 9 9950X3D and 9900X3D chips have finally arrived! Below, we will be listing all the latest listings from the web's biggest retailers.
- AMD Ryzen 9 9950X3D
- AMD Ryzen 9 9900X3D
- AMD Ryzen 9 9950X3D - CA
- AMD Ryzen 9 9900X3D - CA
- AMD Ryzen 9 9950X3D - Newegg
*Stock availability and pricing subject to change depending on retailer or outlet.
How to replace blank cells with N/A in Excel
Here’s a straightforward look at our Excel dataset, where you’ll notice some cells under the ‘Position’ column are empty. These blank spaces can make data analysis a bit tricky, as they represent missing information. To address this, we will fill these blank cells with “N/A,” which means “Not Available.”
Method 1: Use the Replace function to fill empty cells with N/A
If you’ve ever needed to account for missing data in Excel, filling blank cells with “N/A” is a standard approach. In our example dataset, we can see that some cells under the ‘Position’ column lack entries. To ensure that our dataset is fully populated and that any gaps are clearly marked as “Not Available,” we can use the Replace command in Excel. This technique is particularly useful if we want to quickly identify cells that are intentionally left without information
- Start by selecting the cells in the ‘Position’ column where some data is missing, specifically the range C4:C13 in our sheet.
- Next, head over to the Home tab. In the Editing group, you’ll find the ‘Find & Select’ button. Click on it and choose ‘Replace’ from the dropdown menu.
- The ‘Find and Replace’ dialog box will pop up. Leave the ‘Find what’ field blank because we’re targeting empty cells. In the ‘Replace with’ field, type “N/A.”
- Click on ‘Replace All’. This action will prompt Excel to search for all blank cells in the selected range and fill them with “N/A.”
- Click on OK after the dialogue box with the number of N/A replacements appears.
After completing these steps, all the previously empty cells in our dataset’s ‘Position’ column will display “N/A,” signifying that the information is unavailable. This change will complete our dataset and ensure that any analysis performed on the data takes into account these intentionally empty fields.
Method 2: Add ‘N/A’ to empty Excel cells with Go To Special
We can also use the ‘Go To Special’ feature in Excel. Here’s how to do it for the range C3:C12 in our dataset:
- Begin by highlighting the cells C3:C12, which includes the entire ‘Position’ column where data might be missing.
- Go to the ‘Home’ tab on the Excel ribbon. In the ‘Editing’ group on the far right, find and click on ‘Find & Select,’ then choose the ‘Go To Special’ option from the dropdown menu.
- Click ‘OK,’ and Excel will return you to your worksheet with all blank cells now selected.
- With the blank cells selected, type “N/A” into the active cell, which is usually the first blank cell in your range.
- Without clicking anywhere else or deselecting the cells, press ‘Ctrl’ + ‘Enter.’ This will fill all selected blank cells with “N/A.”
By following these steps, you’ve now successfully filled all blank cells within the selected range with “N/A,” making it clear that the data is intentionally unavailable in those spots. This can help in further data processing or analysis tasks, ensuring that empty cells are properly accounted for.
Method 3: Automating ‘N/A’ entry in empty cells with VBA
For our dataset, here’s how to fill the blank cells with “N/A” using VBA (Visual Basic for Applications):
- Press ALT + F11 to open the VBA editor.
- Go to Insert > Module to create a new module.
- Copy and paste the following VBA code into the module:
Sub FillBlanksWithNA()
Dim ws As Worksheet
Set ws = ActiveSheet
With ws
‘ Change the range according to your dataset
Dim rng As Range
Set rng = .Range(“C4:C12”)
‘ Loop through each cell in the range and set the value to N/A if the cell is empty
Dim cell As Range
For Each cell In rng
If IsEmpty(cell.Value) Then cell.Value = “N/A”
Next cell
End With
End Sub
- Close the VBA editor and go back to your Excel worksheet.
- Press ALT + F8, select FillBlanksWithNA, select your workbook, and then click ‘Run’.
After running this script, all the blank cells in the ‘Position’ column within the range C3:C12 will be filled with “N/A”. This VBA approach is especially useful if you have a large dataset and want to automate the process of filling in blanks.
Conclusion
To wrap it up, filling in the blanks with “N/A” in Excel helps keep your data clear and analysis-ready. We’ve gone over three straightforward methods: using the Replace feature, the Go To Special command, and a bit of VBA magic. These steps are easy to follow and can make a big difference in how you manage and interpret your data.
Want to learn more about Excel? If yes, then give these guides a read:
- How to remove gridlines in Excel
- How to convert Excel to PDF
- How to use VLOOKUP to pull the last match in Excel
About the Author