Quickly fix columns that are missing data in Excel

When you import data into Excel, occasionally a column of data will only have a key field listed one time. Two examples would be "states" only showing once at the start of each data set (see image above), or Product Number IDs are only showing once. Trying to manipulate data is almost impossible to do unless you correct the worksheet.

Before we fix this, there are two keyboard shortcuts you should know. One is imperative because, without it, you cannot fix this quickly.

Two keyboard shortcuts

1. CTRL + G - to pull up Go To. Another method is the F5 key. You can also use the mouse to get to Go To. It is in the "Editing group" of the Home tab.
2. CTRL + Enter - does two things. It keeps you in the current cell you are working in, and for my example, Ctrl+Enter is used to fill all the selected cells with the same data I typed. Without CTRL + Enter my solution for quickly fixing your data cannot be done.

YouTube Video showing how to fix the missing data

Steps to correct missing data

1. Select the first cell with something in it down to the last cell that is blank in the range. In my example, you select A2 to A21.
2. Now do Ctrl + G.
3. Click Special.
4. Select Blanks.
5. Click OK.
6. Cell A3 is now the active cell. Type =A2 and press Ctrl + Enter. I do not think it is possible to fix this with the mouse.
7. Copy column A.
8. Right click Column A and Paste Values.

Now you can sort, filter, create a PivotTable, create a data validation list, and do advanced filters.

Chris Menard's blog post on this article

https://chrismenardtraining.com/fix-missing-excel-data/