4 Quick Tricks to Fill Blank Cells in Excel with N/A

Just think about a survey, where people don’t care to answer some particular question or show reluctance. In this case, the cells for the particular question remain blank in the dataset. However, Plenty of blank cells took place in the dataset. Some leave the data perfunctorily without making answers, some don’t like to answer or there are no activities to insert values. In contrast, a petulant reporting boss may engender resentment while reviewing the dataset. Probably your boss at least expected to fill the blank cells with N/A in Excel.

So, sometimes we need to show values instead of blank cells in the Excel dataset as well as the pivot table. we are going to elucidate several methods to fill blank cells with N/A in Excel. Stay tuned with us and you will find us gregarious.

📁 Download Excel File

To practice please download the Excel file from here:

Learn to Fill Blank Cells in Excel with N/A with These 4 Methods

In this article, we will explore 4 easy approaches to fill blank cells with N/A in Excel. Following that we considered a data set named Sales Data of Amigo Super Store- 2022 containing Seller, Month, Products, Quantity, and Amount. The dataset also has 6 columns and 12 rows including some blank cells. To insert N/A text in the dataset and Pivot Table, we are going to expound on the use of the Pivot Table Options feature, the Find and Replace feature, and VBA Macro. Let’s get started.

Finding the blank cells and replacing them with N/A text is the simplest way to insert N/A in the blanks. Using the Find and Replace feature one can insert N/A texts in the blanks within a second. Please follow the steps below.

⬇️⬇️ STEPS ⬇️⬇️

• Primarily, select the B5:F12 range.
• Then expand the drop-down list of the Editing option from the Home menu of the Top Ribbon.
• Next, navigate to the Find and Select tool to select the Replace option.

• Thus a box named Find and Replace appears.
• Then, leave blank in the Find what cell and write N/A in the Replace with cell.
• After that hit the Replace All button.

• Therefore, all the N/A text was inserted in the 12 blank cells of the dataset.

📕 Read More: 4 Ways to Leave Blank If Cell Contains Zero with Excel Formula

Using the Go To special feature you can easily ferret out the blanks and by typing the suitable text you can insert them into the blanks. Please check out the below procedure.

⬇️⬇️ STEPS ⬇️⬇️

• Initially, select the B5:F12 range.
• Secondly, expand the drop-down list of the Editing option from the Home menu of the Top Ribbon.
• Thirdly, navigate to the Find and Select tool to select the Go To Special option.

• Thus, a box named Go To Special appears. And select Blanks from the Select section.
• After that, hit the Ok button.

• Therefore, all the blanks showed up automatically.
• Then, type N/A is a cell.
• Next, Holding the Ctrl key, press Enter button.

• In the end, we achieve the N/A texts in all the blank cells of the dataset.

Navigating the PivotTable option from the Context Menu, one can easily insert N/A values in the blank cells. But in this case, to get the pivot table option we need to make a Pivot Table first. Please follow the steps below.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly, select the data B5:F12 range.
• Secondly, click on the drop-down icon to pull down the list of the Pivot Table feature from the Insert menu to create a Pivot Table.
• Thirdly, Select From Table/ Range option from the Pivot Table feature.

• Now, a box named PivotTable from table or range appears.
• Then, select $B$5:$F$12 for the table range cell from the Dataset worksheet.
• Next, select New Worksheet and hit the OK button.

• Therefore we get the Pivot Table.
• Then, data appears by clicking on Seller, Month Product, Quantity, and Amount from the PivotTable Fields of the Side Ribbon.

• Again, select the A6 cell.
• Then, Right-click on the Mouse to get a context menu list.
• Next, select the PivotTable Options from the Context Menu list.

• Thus, a box named PivotTable Options appears.
• Further, From the Layout & Format option, insert N/A in For Empty Cells Show cell.
• After that, click on the OK button.

• In the end, N/A texts were inserted in all the blank cells of the pivot table.

Generating a VBA code, one can insert any text automatically. By getting the Developer option in the Top Ribbon, you can generate a program on your own.  Please follow the necessary steps below.

⬇️⬇️ STEPS ⬇️⬇️

• Check first if your Developer option is available or not.
• Secondly, to enable the Developer option, Right-Click on the mouse at the Top Ribbon and select Customize the Ribbon.

• Thirdly, click on the Developer option and hit the OK button.

• Now Developer mode is on.
• Then, click on the Visual Basic feature.
• Next, to insert the VBA code, click on Module from the Insert menu.

• Now, input the following code in the VBA module of the Worksheet.
• After that, select the dataset range B6:F12 and click on the Run icon.

Code

Sub Fill_Blank_Cells_with__NA_in_Excel()
Dim Area_Selection As Range
Dim Insert_NA As String
On Error Resume Next
Insert_NA = InputBox("Insert Value in The Blanks", _"Fill Blanks")
For Each Area_Selection In Selection
If IsEmpty(Area_Selection) Then
Area_Selection.Value = Insert_NA
End If
Next
End Sub

• Finally, we obtain the result that fills with N/A in the blank cells of the dataset.

How to Fill Blank Cells with Dash or Zero (0) in Excel

Finding the blank cells, one can replace them with Not only N/A text but also 0, Dash, necessary texts, or values following the 1st method. Please follow the steps below.

⬇️⬇️ STEPS ⬇️⬇️

• Primarily, select the B5:F12 range.
• Then expand the drop-down list of the Editing option from the Home menu of the Top Ribbon.
• Next, navigate to the Find and Select tool to select the Replace option.

• Thus a box named Find and Replace appears.
• Then, leave blank in the Find what cell and write 0 in the Replace with cell.
• After that hit the Replace All button.

• Therefore, 0 values are inserted in the blank cells.

• Again in the Find and Replace box, leave blank in the Find what cell and write Dash(-) in the Replace with cell.
• After that hit the Replace All button.

• Similarly, we obtain Dashes in the blank cells of the dataset.

📕 Read More: 4 Quick Ways to Insert 0 If Cell Is Blank in Excel

📄 Important Notes

🖊️  Enable the Developer menu first to execute VBA code.

🖊️  Select the range first while using Find and Replace feature. Otherwise, malfunction will take place.

📝 Takeaways from This Article

📌  Go To Special option to find out blank cells.

📌  Find and Replace feature to replace any texts or values.

📌  VBA Macro to fill the blanks with texts or values.

📌  Pivot Table Options feature to insert text or values in a Pivot Table.

Conclusion

In this article, we construe various methods to fill blank cells with NA in Excel. I hope you enjoyed your learning and will be able to insert any text or values in the blanks. Any suggestions, as well as queries, are appreciated. Don’t hesitate to leave your thoughts in the comment section. For better understanding and new knowledge, don’t forget to visit www.ExcelDen.com.

Related Articles

• 3 Approaches to Make Empty Cells Blank in Excel
• How to Determine and Count If Cell Is Not Blank in Excel
• 10 Quick Tricks to Remove Blank Lines in Excel
• 9 Quick Tricks to Delete Row If Cell is Blank in Excel
• How to Get Blank Cell Instead of Zero Using Formula in Excel