Automatic Completion of cells with blank cells above.

Question

I know its not possible in MS Excel to Automatic Completion of cells with blank cells above.

But there must be a workaround to enable this function.

It seems that MS is working backwards and not pushing forward.

Thanks in advance.

Answer 1

Dear @grayb,

Thank you for your question. As a long‑time Excel user myself and someone who works with it daily, I completely agree with you that Excel still has room to become more flexible and user‑friendly, especially for everyday data‑entry scenarios like this.

It helps to first clarify one important distinction in Excel that often causes frustration:

AutoComplete is what happens while you type: Excel only suggests or completes values when there is continuous, non‑blank data above the current cell. If there’s a blank cell above, AutoComplete intentionally stops. This behavior is by design, and there’s currently no setting to change it.

Blank cells often indicate a logical break in data, and Excel avoids making assumptions that could silently introduce incorrect values. Instead, Microsoft has focused automation improvements on AutoFill, formulas, Power Query, and now Copilot, rather than changing AutoComplete’s core behavior.

AutoFill, on the other hand, is Excel’s intended tool for automation. It does not depend on whether cells above are blank. AutoFill works based on selected cells, patterns, formulas, or fill operations you explicitly trigger.

So, while AutoComplete can’t be “enabled” across blank cells, Excel does provide reliable workarounds depending on what you want to achieve.

1. Fill blank cells with the value from the cell above (most common need)

This is very useful for structured data and reporting.

Select the range that contains blanks.

Press Ctrl + G (or F5) > click Special > choose Blanks > OK.

Without clicking anywhere else, type = and press the Up Arrow key.

Press Ctrl + Enter.

All blank cells will be filled with the value from the cell above.

(You can then copy > paste values if you want to remove formulas.)

2. Use formulas instead of AutoComplete

If your data is dynamic or part of a calculation: =IF(A2="", A1, A2)

Fill this down and Excel will automatically inherit the value above when a cell is blank.

3. Use AutoFill explicitly

If you’re repeating values or patterns:

Enter the value once and select the cell,

Use the fill handle or Ctrl + D to fill down.

This works regardless of blank cells above.

4. For larger or recurring datasets

If this is something you do often with imported or structured data:

Power Query > Fill Down is a robust, repeatable solution and works very well in Excel for Business environments.

• Select any cell in your data range
• Go to Data > From Table/Range (confirm headers if prompted)
• In Power Query Editor, select the column you want to fill
• Go to Transform > Fill > Down
• Click Close & Load

Excel will return a new table where all blank cells are filled correctly.

I hope this information helps point you in the right direction. If you run into any issues while trying the steps, or if something still doesn’t feel quite right, please don’t hesitate to reach out again. I’ll do my best to support you however I can.  

Looking forward to hearing back from you with any updates or additional details. 

Warm regards, 

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".    

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.   

Answer 2

Excel’s built‑in AutoComplete only works when there is a non‑blank cell above in the same column. There is no setting to change this behavior directly, but there are practical workarounds depending on what “automatic completion” is needed for:

1. Use Auto Fill instead of AutoComplete
   Auto Fill does not depend on the cell above being filled; it uses the selected cells as a pattern:
   1. Enter the starting value(s) in one or more cells.
      • For example, type 1 in A1 and 2 in A2 for a numeric series, or just 2 in A1 for a repeated value.
   2. Select those cell(s).
   3. Drag the fill handle (small square at the bottom-right corner of the selection) down or across to fill additional cells.
   4. If needed, click Auto Fill Options and choose the desired fill behavior.
   This works even if there are blank cells above the range being filled.
2. Use formulas and fill them down/right
   If the goal is to repeat or calculate values automatically, enter a formula in the first cell and then fill it:
   • On the web: select the cell with the formula and drag the fill handle down or to the right.
   • Or use keyboard shortcuts:
     • Ctrl+D to fill the formula down in a column.
     • Ctrl+R to fill the formula to the right in a row.
   Ensure workbook calculation is set to Automatic so formulas recalculate when filled:
   1. Select File > Options.
   2. Select Formulas.
   3. Under Calculation options > Workbook Calculation, choose Automatic.
3. Control or disable AutoComplete if it interferes
   If AutoComplete is not helpful because of blanks or mixed data, it can be turned off so that Auto Fill and formulas are the primary automation tools:
   1. Click File > Options.
   2. Click Advanced.
   3. Under Editing options, clear Enable AutoComplete for cell values.

These approaches do not change the core AutoComplete rule about blank cells above, but they provide ways to fill and repeat data automatically regardless of blanks.

---

References:

• Turn automatic completion of cell entries on or off
• Fill data automatically in worksheet cells
• Fill a formula down into adjacent cells