Microsoft Excel

Fix missing Excel data using Go To and Ctrl + Enter

Chris Menard

07 May 2017 — 2 min read

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.

_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

CTRL + Enter to fix missing data in Excel by Chris Menard - YouTube

Animated Gif showing CTRL + Enter used

Animated GIF showing CTRL and Enter being used

Count Unique Values in Excel with UNIQUE, SORT, and COUNTIF Functions Tutorial

Table of Contents * 📋 Why this method works and when I use it * 📋 Setting up the table * 🔎 Extract distinct values with UNIQUE * ↕️ Sort the unique list with SORT * 🔢 Count occurrences using COUNTIF * ✅ Why tables beat ranges for this task * 🔁 When to use this instead of a pivot table * 🔧 Quick checklist to

Financial Ratios - Formula Completion in Excel using Copilot

Writing formulas in Excel can be time-consuming and challenging, even for experienced users. Selecting the right function, referencing the correct cells, and ensuring proper syntax often slows down workflows and leads to errors. Many users spend extra time troubleshooting mistakes, searching for examples, or trying to recall the exact formula

Excel Formula Completion - Using XLOOKUP

I tested how well Excel's formula completion with Copilot handles a multi-step calculation that would normally require a nested function. The goal was simple: calculate profit by subtracting the total cost of units sold from total revenue, with unit costs stored in a separate lookup table. Instead of

Copilot in Teams Meetings without Transcription: Use AI While Respecting Privacy

Microsoft Teams has a great Copilot feature. A Microsoft 365 license is required for this feature. In a Teams meeting, I need AI assistance during a conversation, but the participants do not want the call recorded or transcribed. There are plenty of situations where privacy matters: a vendor asks for