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
The fastest and easiest approach is to use Excel's built-in Go-To functionality.
- Select the range that you need to fill (don't select the whole column though!)
- In the ribbon,
Find & Select>
Go To Special
Blanks: the empty cells in the range will be selected
- Press F2 to edit the content of one of the cells (the others will be greyed out)
N/Aand press CTRL + Enter
All empty cells should now contain
Note: if you meant error
#N/A, not the text
N/A, then you should write
You can also summon this window by pressing F5, and then click on
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:
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.