Fill Blank Cells With the Values Above in Google Sheets (Array Formula)

Google Sheets doesn’t offer a menu command to fill blank cells in with the values from the cell above in a column.

So if you want to perform such a task, you must depend on a regular formula in Google Sheets.

With a non-array formula, it’s very simple to fill blank cells in a column with the value just above. That doesn’t mean there is no Array Formula for this.

Let me share the non-array formula with you first as this can give you an idea about what’s ahead in this tutorial.

Non-array formula to copy the value above in blank cells — image # 1

Here I have used the below formula in cell C2 and copied it down.

=if(len(B2),B2,C1)

The formula repeats the values in blank cells from non-blank cells above.

Irrespective of the data type, the formula must be able to copy the value in the cell above and fill the blank cells down.

What about an array formula to fill blank cells with the values from the non-blank cell above?

We can mainly follow two approaches.

Using any one of the lookup functions.
Using SCAN Lambda function (new method).

We will start with a lookup function, but the latter seems simple to implement.

Auto Fill Blank Cells With the Values Above in Google Sheets – LOOKUP

As you may know, the LOOKUP formula takes a search_key(s), search range, and result range as its arguments.

=LOOKUP(search_key, search_range|search_result_array, [result_range])

We have only values in column B to use. That we can use as the result_range.

Let’s manipulate the existing values in column B to use them in Lookup as the serach_keys and search_range (see the syntax above).

Sample Data (going to use as the result_range in my Lookup formula):

Sample data acts as result range in Lookup — image # 2

Let me walk you through the steps to code a Lookup array formula to fill blank cells with the value from the cell above.

Steps to Follow

1. Virtual Search_Keys:

My sample data starts from row # 2. So, use the below ROW formula to generate row numbers from row # 2 to the last row in the Sheet.

=ArrayFormula(row(A2:A))

These numbers will act as the search_keys in Lookup.

For the time being, enter the above formula in cell C2.

Virtual search keys and search range in Lookup — image # 3

2. Virtual Search_Range:

In this step, we can create a virtual column that contains the row numbers of the values in column B.

=ArrayFormula(row(A2:A)/if(B2:B<>"",TRUE,FALSE))

For the time being, enter this formula in cell D2.

Remember, our earlier formula is in cell C2. We will ‘combine’ these formulas in the next (final) step.

3. Array Formula That Fills Blank Cells With the Values Above:

First, see the generic formula.

=arrayformula(lookup(step # 1 formula,step # 2 formula,B2:B))

The array formula that fills blank cells with the value from the cell above will be as follows.

=ArrayFormula(lookup(ArrayFormula(row(A2:A)),ArrayFormula(row(A2:A)/if(B2:B<>"",TRUE,FALSE)),B2:B))

You can shorten this formula as there is no need to use multiple ArrayFormula functions in a combination formula.

=ArrayFormula(lookup(row(A2:A),row(A2:A)/if(B2:B<>"",TRUE,FALSE),B2:B))

This formula has one more issue. The output is in infinite rows!

Find the row number of the last value and limit infinite formula — image # 4

Additional Tips

How to cut short the formula expansion? I want the formula to expand up to the last non-blank cell.

Here is the formula to find the last cell containing a value in a column (here column B).

=ArrayFormula(MATCH(2,1/(B:B<>""),1))

Want to know more about this tricky formula? Here you go – Address of the Last Non-Empty Cell Ignoring Blanks in a Column in Excel.

The above formula works well in Google Sheets too. See how I am going to use this in our formula.

Final Formula:

=ArrayFormula(if(row(B2:B)<=MATCH(2,1/(B:B<>""),1),lookup(row(A2:A),row(A2:A)/if(B2:B<>"",TRUE,FALSE),B2:B),))

Lookup to fill blank cells with the values above in Google Sheets — image # 5

Lookup Logic in Auto Filling Blank Cells With the Value Above

The search_keys are the row numbers in column C and the search_range is in column D (please scroll up and refer to image # 3).

Wherever the search_keys appear in the search range, the corresponding values from B2:B (result_range) will be returned.

In some cells, the search_keys (C2:C) are not available in the search_range (D2:D).

In that case, the formula follows the below Lookup feature (via Docs Editors Help).

If search_key is not found, the item used in the lookup will be the value that’s immediately smaller in the range provided.

Hope you could understand it.

Auto Fill Blank Cells With the Values Above in Google Sheets – SCAN

The SCAN is one of the LAMBDA helper functions (LHF), which is known for returning the running total of a column in one go.

We can use that function to fill blank cells between the non-blank cells in a column.

We can replace the above LOOKUP formula (image # 4) with the below SCAN formula.

=scan(0,B2:B,lambda(a,v,if(v="",a,v)))

To limit the expansion of the formula similar to image # 5, we can choose the below logic.

The below REDUCE (another LHF) can find the row number that corresponds to the last value cell in column B.

=reduce(0,B:B,lambda(a,v,(if(v="",v&a,row(v)))))

So replace B2:B with indirect("B2:B"&reduce(0,B:B,lambda(a,v,(if(v="",v&a,row(v)))))) in the above formula.

=scan(0,indirect("B2:B"&reduce(0,B:B,lambda(a,v,(if(v="",v&a,row(v)))))),lambda(a,v,if(v="",a,v)))

That’s all about how to fill blank cells in a column with values from the cell above.

16 COMMENTS

Dan October 15, 2022 At 2:03 am

Thank you! Just saved me hours (after spending hours trying to figure this out)! Building something critical for my job and now I can look like the hero, not the zero.

- Prashanth October 15, 2022 At 2:07 pm
  
  Hi, Dan,
  
  Thanks for your feedback.
  
  I’ll update this post ASAP to include a simple formula using the new SCAN function.
  
  Please check back after 1-2 days.
  
Bryan February 26, 2022 At 10:22 pm

Thanks for this formula.

I have a question because after inputting this formula, my data stops at the last result range column that has data.

There are more rows below that which are empty.

Is there something I can tweak to fill the last few rows? TIA

- Prashanth February 27, 2022 At 10:53 am
  
  Hi, Bryan,
  
  I have given two formulas. One (under step # 3) is for infinite rows, and the other (the first formula from bottom to top) is for just up to the last non-blank row.
  
  It seems you are using the latter one.
  
  In that case, if the column in question (range in use) is B2:B, and you want the formula to return values up to row # 500, go to B500 and tap the spacebar.
  
  That will expand the formula up to that row.
  
Eddy A September 22, 2021 At 11:45 am

Your formula is great but, I try to apply it based on a column that has to be the same.

It skips empty values instead of always looking above.

I’ve created an example sheet here: — address removed by admin —

Please advise how this can be done? Thank you!

- Prashanth September 23, 2021 At 8:42 am
  
  Hi, Eddy A,
  
  The reference column (cell range) in the MATCH part of the formula is important.
  I have used that formula part to find the cell address of the Last Non-Empty Cell Ignoring Blanks in a Column.
  
  I have corrected the formula in your sheet.
  
ramiro September 11, 2021 At 8:19 am

How could this be done in the same column?

- Prashanth September 11, 2021 At 11:02 am
  
  Hi, ramiro,
  
  Select the result, right-click and copy.
  
  Then right-click, paste special and apply paste value in the column you want.
  
Rafael November 5, 2020 At 11:18 pm

Hello Prashanth,

It was a very good example/explanation!

I have just one question:
– What modification would you have to do in order to copy the last line instead of the first one?

To make it easier, I created one shared document on the link:
— LINK REMOVED BY ADMIN —

What I need is to obtain the row number associated with the first and last line of each title, but using array formulas on the first row.

- Prashanth November 6, 2020 At 10:45 am
  
  Hi, Rafael,
  
  I have inserted the below two formulas.
  
  First Row:
  
  =ArrayFormula(if(B5:B16<>"",row(B5:B16),))
  
  Last Row:
  
  =ArrayFormula(IFNA(VLOOKUP(B5:B16,{lookup(row(B5:B16),if(len(B5:B16),row(B5:B16)),B5:B16),ROW(G5:G16)},2,1)))
  
  I know you could read the first formula. Regarding the second formula, I would try to write a detailed tutorial soon. In the meantime, you can read my below tutorial to understand some of the logic used.
  
  How to Use Sumif in Merged Cells in Google Sheets.
  
Trang May 11, 2020 At 9:11 pm

Thank you for sharing this mind-blowing formula. However, I wonder if there is any way to fill the value for a certain number of times? For example, suppose I just want to fill the “test” values down the column for 14 times.

- Prashanth May 12, 2020 At 8:42 am
  
  Hi, Trang,
  
  Here is a similar post/tutorial.
  
  How to Insert Duplicate Rows in Google Sheets.
  
Josh February 16, 2020 At 12:54 pm

Thanks a lot, this helped!

One small thing I noticed is that in the final formula, you could have just written;

It’s a bit cleaner that way. I tested it and it works.

- Prashanth February 16, 2020 At 7:01 pm
  
  Hi, Josh,
  
  Thanks for your feedback!
  
Rhys Johnston January 6, 2020 At 2:05 am

Tonnes of useful information on here! Thank you, sir/madame.

Dustin December 7, 2019 At 8:40 am

Thank you so much!! Really appreciate the explanation as well. Amazing!