-1

I have an excel file with empty cells in the Company Column How do I replace the empty cells with "N/A" ? Is this the correct/best approach? If not, what is a better approach in preparing the data for analysis

  • 1
    Select the column > Data > Filter > then remove all checkbox in the filter except for Blanks and fill those with "n/a". – shinjijai Apr 6 '18 at 19:34
  • 1
    Please ask only one question at a time. Avoid questions like “Is this the correct/best approach?” and “What is a better approach?” because they are opinion based. If you need to ask something like that, explain what sort of analysis you are going to do and what your objectives are. – G-Man Says 'Reinstate Monica' Apr 6 '18 at 19:51
  • @G-Man +1, the value used to represent missing values depends entirely on the tools you're using for analysis. SPSS uses the ".", R uses the "na", no matter what you use, changing between the representative value is often trivial. – user881561 Apr 6 '18 at 19:59
  • Replacing blanks with "N/A" isn't necessarily an improvement. Whatever is in the cell can be handled by filtering or testing. You may be creating an unnecessary step for yourself that doesn't make analysis any easier. – fixer1234 Apr 6 '18 at 23:48
  • JOhn , what do you mean by replacing missing cells? And one more you are suppose to replace blank Cells with "N/A" ! Don't you think Title of OP is different to the issue,, Brother,, do some corrections first !! – Rajesh S Apr 7 '18 at 9:33
1

The fastest and easiest approach is to use Excel's built-in Go-To functionality.

  1. Select the range that you need to fill (don't select the whole column though!)
  2. In the ribbon, Home > Find & Select > Go To Special
  3. Select Blanks: the empty cells in the range will be selected
  4. Press F2 to edit the content of one of the cells (the others will be greyed out)
  5. Write N/A and press CTRL + Enter

All empty cells should now contain N/A.

Note: if you meant error #N/A, not the text N/A, then you should write =NA() instead.


You can also summon this window by pressing F5, and then click on Special.

enter image description here

0

In a separate (preferably adjacent) column, paste this formula into the cell in the same row as the first cell of the your data column:

=IF(ISBLANK(A1),"N/A",A1)

Replacing both "A1"s with the reference to aforementioned first cell. Drag the formula down (which should update the relative reference). Newly-created column should appear as intended, then simply copy that column, paste over original column (Right-click, Paste Special, select Values), delete the new column.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.