Function Problems

How to Repeat Names Until an Adjacent Excel Cell Is Blank

Amos Gikunda Oct 15, 2026 868 views

Question details

The user needs a method to dynamically repeat names in an Excel list as long as the adjacent cell contains a value, and stop when the adjacent cell becomes empty.

How to Repeat Names Until an Adjacent Excel Cell Is Blank

Product: Excel
Device & OS: not provided
Scenario: Automating data entry where a specific name (like Adam, Barbara, or Cole) needs to be copied down a column corresponding to populated data in an adjacent column, adapting to new names added later.
Observed behavior: The user is looking for an efficient workflow—either via formulas or macros—to achieve conditional repetition based on the blank or non-blank status of neighboring cells.

Before you start

Ensure your dataset does not contain merged cells, as they can interfere with formula autofill and the Go To Special feature. If you decide to use the VBA method, remember to save your file as a Macro-Enabled Workbook (.xlsm).

Solution 1Recommended

Use Go To Special and a Simple Formula

This is the most efficient and recommended method to fill blank cells in a column with the value from the cell directly above them.

By selecting only the blank cells in your names column, you can enter a single formula that references the previous name. This instantly fills the gaps based on your adjacent data.

Select the target range

Highlight the entire column or specific range containing the names, making sure to include all the blank cells you want to fill.

Open Go To Special

Press F5 on your keyboard to open the 'Go To' dialog box. Click on the 'Special' button at the bottom left, select the 'Blanks' option, and click OK. Now only the blank cells are selected.

Enter the formula

Without clicking anywhere else, type an equals sign (=) and press the Up arrow key on your keyboard. This creates a formula referencing the cell immediately above the active blank cell.

Apply to all blanks

Press Ctrl + Enter (Windows) or Command + Return (Mac) to apply this formula to all selected blank cells simultaneously.

Paste as values (Optional)

To finalize the data and remove the underlying formulas, select the entire column, copy it (Ctrl + C), right-click, and choose 'Paste as Values'.

Tip: Pasting as values is highly recommended if you plan to sort or filter the data later, as leaving the formulas might scramble the names when the row order changes.

Solution 2

Use an IF Formula in a Helper Column

If you want to keep your original data untouched, you can use a helper column to generate the repeated names based on the adjacent values.

Solution 3

Use a VBA Macro for Complex Layouts

For scenarios involving complex repetition counts, dynamic row insertions, or very large datasets, a VBA script provides full automation.

Free Spreadsheet Software

Automate Data Entry with WPS Spreadsheet

WPS Spreadsheet provides powerful built-in features like 'Go To Special' and robust formula support, making it incredibly easy to repeat names and fill blank cells. It is a fantastic free alternative for handling complex data layouts.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your Excel workbook.
2. Access the Go To Special feature: Highlight your column, press Ctrl + G, click 'Special', and select 'Blanks'.
3. Fill the data instantly: Type =, press the Up arrow, and hit Ctrl + Enter to fill all gaps with the correct repeated names.

Fully compatible with Microsoft Excel formats (.xlsx, .xls, .xlsm).Built-in Go To Special tool for instant blank cell selection and filling.Supports advanced VBA macros for custom workflow automation.Lightweight, fast, and completely free to use.

microsoft office alternative - wps office

Frequently Asked Questions

Can I stop repeating names if the adjacent cell has a specific text instead of just being blank?

Yes. You can modify the IF formula to check for a specific value. For example, use =IF(B2="Stop", "", A2) to halt the repetition when the word 'Stop' appears in column B.

Why does Ctrl + Enter overwrite my data instead of filling blanks?

This happens if you haven't isolated the blank cells first. Ensure you have used the 'Go To Special' > 'Blanks' feature so that only the empty cells are actively selected before you type your formula and press Ctrl + Enter.

Will the formula method update automatically if I add new names later?

If you paste the results as values, it becomes static text and will not update automatically. If you leave the formulas active or convert your data range into a formal Table (Ctrl + T), the formulas can dynamically apply to new rows.

Excel & Data Problems