Fill Blank Cells Quickly Based on Values Above or Below in Google Sheet

Working with spreadsheets that contain blank cells can be frustrating—especially when you’re dealing with large datasets. Fortunately, Google Sheets provides simple yet powerful ways to fill empty cells with data from the cells above or below. Whether you’re preparing a report, organizing survey data, or cleaning up messy imports, this trick can save you loads of time.

This beginner-friendly tutorial will walk you through step-by-step methods to automatically fill blank cells using built-in Google Sheets features and formulas. Say goodbye to manual copy-pasting!

Why You Might Need to Fill Blank Cells

Blank cells often appear when:

• Data has been exported from another system with merged cells
• You’ve sorted or filtered information and need to restore missing values
• You want to prep data for analysis or charts, which don’t handle blanks well

Filling empty cells with values from above or below helps make your dataset complete, consistent, and ready for formulas or pivot tables.

Real-Life Example: Cleaning Up a Product List

Let’s say you’ve imported product data that looks like this:

Category	Product
Fruit	Apple
	Banana
	Orange
Vegetables	Carrot
	Broccoli

Notice how the “Category” column only shows the first value in each group? We want to fill the blank cells with the values above so that every row is complete.

Method 1: Use an Array Formula to Fill Blanks Based on the Cell Above

This method dynamically fills blank cells using a formula.

Steps:

1. Insert a new column next to your data (if you want to keep the original intact)
2. In the new column, enter this formula in the first row (assume data starts at A2):
   =ARRAYFORMULA(IF(A2:A="", IFERROR(VLOOKUP(ROW(A2:A), FILTER({ROW(A2:A), A2:A}, A2:A<>""), 2, TRUE), ""), A2:A))
3. This will “fill down” the last known non-blank value

Note: This solution works great if you want a live, formula-based result that adjusts with your data.

Method 2: Use Find and Replace with Google Apps Script (One-Time Fill)

If you prefer to fill blank cells once and remove the formulas, you can use a simple script.

Steps:

1. Go to Extensions > Apps Script
2. Paste the following script:

function fillBlanks() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getDataRange();
  var values = range.getValues();

  for (var i = 1; i < values.length; i++) {
    for (var j = 0; j < values[i].length; j++) {
      if (values[i][j] === "" && values[i-1][j] !== "") {
        values[i][j] = values[i-1][j];
      }
    }
  }

  range.setValues(values);
}

3. Click the disk icon to save, then click the play ▶️ button to run it

This will permanently fill in all blank cells with the value from the cell above in each column.

Bonus Tip: Use Fill Down Feature (Manual Method)

If your dataset isn’t too large, you can use this manual approach:

1. Select the blank cells under a value
2. Press Ctrl + D (Windows) or Cmd + D (Mac) to fill them with the value above
3. Repeat for each section of your data

Quick Cheat Sheet

Method	Best For	Dynamic or Static?
ARRAYFORMULA + VLOOKUP	Live, auto-updating values	Dynamic
Google Apps Script	One-time fill for entire dataset	Static
Ctrl/Cmd + D	Small datasets, quick fixes	Static

Blank cells don’t have to slow you down. With just a few formulas or clicks, you can quickly fill them with the correct values based on the rows above or below. Whether you’re working with product categories, dates, labels, or any repeated data, these methods will help you clean up your sheet in seconds.

Give them a try in your next project—you’ll love how much time and effort they save!