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.

Dataset named Sales Data of Amigo Super Store- 2022

method

1. Fill Blank Cells with N/A Using Find and Replace Feature in Excel

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.

Select Replace option from Find and Select 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.

Find and Replace box to fill blank cells with NA in Excel.

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

Blank cells were filled with NA in Excel.

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

method

2. Select Blank Cells Using Go To Special Feature and Fill with N/A

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.

Use of 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.

Go To Special box to find out the blanks.

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

Insert N/A to fill in a blank cell and holding Ctrl, Press Enter to fill others.

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

Blank cells filled with NA in Excel.

method

3. Fill Blank Cells with N/A Value Below in Excel Pivot Table

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.

Converting the dataset into a Pivot Table.

  • 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.

PivotTable from Table or Range box to create Pivot Table into a new Worksheet.

  • 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.

Pivot Table with necessary data.

  • 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.

Pivot Table Options from the Context menu.

  • 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.

Pivot Table options box to fill with NA in the Excel blank cells.

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

Filled with N/A in the blank cells of Excel Pivot Table.

method

4. Utilize VBA Macro to Fill Blank Cells with N/A in Excel

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.

Right-click on the mouse to select Customize the Ribbon.

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

Enabling the Developer option in the Top Ribbon.

  • 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.

VBA Module to insert VBA code 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

Selecting the dataset, and executing the Excel VBA code to fill the blank cells with NA.

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

Filled with NA in all the blank cells of Excel.


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.

Replace option to fill in the blanks.

  • 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.

Fill in the blanks with zero.

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

Zero 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.

Fill in the blanks with dashes.

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

Dashes inserted in the blanks.

📕 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

(Visited 34 times, 1 visits today)
MD Tanvir Rahman

MD Tanvir Rahman

Hello, I am Tanvir. Graduated from BUET. Had a year-long Experience at Operation of Vitacan Industries Ltd. I found Excel is the most unique software to reduce both time and paper. Now, I am an Excel enthusiast, love to play with data and publish content.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo