Fix missing Excel data using Go To and Ctrl + Enter by Chris Menard

Posted on:  05/08/2017
Fix missing Excel data using Go To and Ctrl + Enter by Chris Menard

When you import data into Excel, occasionally a column of data will only have a key field listed one time. An example would be States only showing once at the start of each data set. See the image below.

Missing states

Notice column A. It only displays the state for the first row in each group.

The problem with this is we can’t use the following Excel features:

  1. Subtotals
  2. Filters
  3. Advanced Filters
  4. Subtotals
  5. Database functions
  6. PivotTables
  7. Charts

There are two ways to fix this. One is to AutoFill. The problem is when my client called me about this, it wasn’t States it was Product ID and they had over 48,000 rows. That is a lot of Autofilling. Plus, if you Autofill, you can screw this up.

Here are steps on how to fix the missing data

Honestly, it is easier to watch the video, but here are the steps.

  1. Select the first cell with something in it down to the last cell that is blank but shouldn’t be blank. In the screenshot above, I would start selecting at A2
  2. Now do either Ctrl + G or F5.
  3. Click Special.
  4. Select Blanks.
  5. Click OK.
  6. Type =A2 and press Ctrl + Enter. Ctrl + Enter is a keyboard trick to put the same thing in every cell, but since we have the blanks selected, it replaces with the referenced cell above from our formula =A2.
  7. Copy the column. Example column A.
  8. Paste Values.

YouTube video on CTRL + Enter to fix missing data

Animated Gif showing CTRL + Enter used

Animated GIF showing CTRL and Enter being used

Chris Menard

Chris Menard is a Microsoft Trainer (MCT) and works as a full-time Trainer at BakerHostetler - one of the largest law firms in the US. Chris runs a YouTube channel with 900+ technology videos that cover various tools such as Excel, Word, Zoom, Teams, Gmail, Copilot, Google Calendar, and Outlook. To date, the channel has helped over 20 million viewers. Menard also does 2 to 3 public speaking events yearly, presenting at the Administrative Professional Conference (APC), the EA Ignite Conference, the University of Georgia, and CPA conferences. You can connect with him on LinkedIn at https://chrismenardtraining.com/linkedin or watch his videos on YouTube at https://chrismenardtraining.com/youtube.

Categories