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

Disney Event with Chris Menard in September 2022
Chris Menard

Chris Menard is a Microsoft Certified Trainer (MCT) and is employed full-time as a Trainer for BakerHostetler, one of the nation’s largest law firms. Menard has a YouTube channel with over 800 technology videos covering Excel, Word, Zoom, Teams, Outlook, Gmail, Google Calendar, and other resources that over 12 million viewers have appreciated. Menard also does public speaking at conferences for CPAs and Administrative Professionals. Connect with Chris on LinkedIn at chrismenardtraining.com/linked or on YouTube at chrismenardtraining.com/youtube

Categories