Fill Blank Cells with the Next Non-Empty Value in Google Sheets

Published on

When working with Google Sheets, you may encounter blank cells that need to be filled with the nearest non-empty value from below. Unlike the more common top-down fill method, this approach pulls data upward—making it useful for structured datasets, reports, and cleaning up imported data.

In this tutorial, you’ll learn how to fill blank cells with the next non-empty value automatically using built-in Google Sheets functions. Whether you’re dealing with missing labels, structured tables, or gaps in your data, this method will save you time and effort—without manual dragging.

Formula to Fill Blank Cells with the Next Non-Empty Value

Google Sheets provides a formula-based approach to automatically fill empty cells with the next available value below. Here’s how:

=ArrayFormula(XLOOKUP(ROW(range), ROW(range)*(range<>""), range, "", 1, 1))

When using this formula, replace range with the actual column reference containing your data.

Example: Auto-Fill Blank Cells with the Next Non-Empty Value

Assume you have a list of names in column A with blank cells in between. To fill those blank cells with the next available name, enter this formula in any empty column, ideally in the same row as your dataset:

=ArrayFormula(XLOOKUP(ROW(A1:A), ROW(A1:A)*(A1:A<>""), A1:A, "", 1, 1))

This will automatically pull data upward, filling all blank cells with the next non-empty value.

Example showing how to fill blank cells with the next non-empty value in Google Sheets using XLOOKUP

How This Formula Works

This formula uses XLOOKUP, which follows this syntax:

XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])
  • search_key: ROW(A1:A) – Generates row numbers for the given range.
  • lookup_range: ROW(A1:A)*(A1:A<>"") – Creates a lookup range that returns the row numbers of non-empty cells.
  • result_range: A1:A – The result range containing the values to be returned.
  • missing_value: "" – Specifies what to return if no match is found (not relevant in this case).
  • match_mode: 1 – Matches the next greater row number when an exact match is not found. This is key to filling blank cells with the next non-empty value.
  • search_mode: 1 – Searches from top to bottom.

Frequently Asked Questions

1. Can I fill blank cells in Google Sheets without dragging?
Yes! This formula automatically fills empty cells without manual dragging or copy-pasting.

2. Does this method work for numbers as well as text?
Absolutely. You can use this technique for both text and numerical values in Google Sheets.

3. What if my data has gaps at the bottom?
If there are blanks at the bottom of your dataset, they will remain empty because there is no lower value to pull from.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Top N Products and Top N Sellers in Google Sheets (Top N of Top N)

If you’ve ever wanted to compare which sellers contribute the most to your top-selling...

Use XLOOKUP in a Structured Table in Google Sheets (Single and Multiple Conditions)

This tutorial is for users who want to apply XLOOKUP inside a structured table...

Reset SCAN by Another Column in Google Sheets and Excel

Resetting SCAN function results based on values in another column is a topic of...

How to Get the Fastest Time for Each Person in Google Sheets

Whether you’re tracking race results, event times, or any other timed activities, finding the...

More like this

Top N Products and Top N Sellers in Google Sheets (Top N of Top N)

If you’ve ever wanted to compare which sellers contribute the most to your top-selling...

Use XLOOKUP in a Structured Table in Google Sheets (Single and Multiple Conditions)

This tutorial is for users who want to apply XLOOKUP inside a structured table...

How to Get the Fastest Time for Each Person in Google Sheets

Whether you’re tracking race results, event times, or any other timed activities, finding the...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.