How to Automatically Fill Blank Cells with Values Above in Excel
Question details
The user needs to populate blank cells in a column with the value immediately above them to create variable-sized repeating groups without manually dragging each one.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Organizing and cleaning a dataset where category codes are only entered in the first row of each group, leaving empty cells below that must be filled for accurate data sorting and analysis.
- Observed behavior
- Blank cells exist below each distinct value, requiring a bulk operation to copy the respective upper values down to their corresponding empty spaces.
Before applying this method, ensure your data range does not contain hidden characters or spaces inside the visually blank cells, as this will prevent Excel from recognizing them as truly blank.
Use the 'Go To Special' Feature to Fill Blanks
This is the most efficient method to fill all blank cells in a dataset with the value immediately above them in a single operation, avoiding manual dragging.
By utilizing the 'Go To Special' feature alongside a relative formula, you can isolate empty cells and command Excel to look at the cell directly above each one.
Highlight the specific column or the exact data range that contains both the values and the blank cells you want to fill.
Press F5 or Ctrl+G on your keyboard to open the 'Go To' dialog box, then click the 'Special...' button located at the bottom left.
In the Go To Special menu, select the 'Blanks' radio button and click 'OK'. This will keep only the empty cells highlighted in your selection.
Without clicking anywhere else, check the formula bar or the active highlighted cell (e.g., G7). Type an equals sign (=) and click the cell immediately above it (e.g., =G6), or just type the cell reference manually.
Press Ctrl+Enter simultaneously. This confirms the formula for the active cell and automatically applies the relative formula to every other selected blank cell, filling them with the values above.
Easily Fill Blank Cells in WPS Spreadsheet
WPS Office offers a powerful, user-friendly Spreadsheet application that fully supports advanced data manipulation features like 'Go To Special'. You can use the exact same shortcuts to clean up datasets and fill blank cells effortlessly.
- 1. Select Data: Open your spreadsheet in WPS Office and highlight the column containing the blanks.
- 2. Find Blanks: Press Ctrl+G to open the Go To dialog, select 'Blanks', and click 'Go To'.
- 3. Enter Formula: Type '=' and press the Up arrow key to reference the cell immediately above.
- 4. Fill Automatically: Press Ctrl+Enter to apply the formula across all selected blank cells instantly.
Frequently Asked Questions
Why did Ctrl+Enter overwrite my existing data instead of just the blanks?
This happens if you skip the 'Go To Special > Blanks' step. If the entire column remains selected when you press Ctrl+Enter, Excel applies the formula to every cell. Always ensure only the blank cells are isolated before typing the formula.
Can I use this method to fill values to the right instead of downwards?
Yes. Select your data row, use F5 to select Blanks, type '=' and press the Left arrow key to reference the cell to the left. Then press Ctrl+Enter to fill the blank cells horizontally.
What should I do if 'Go To Special' says 'No cells were found'?
This means your visually empty cells actually contain hidden characters, spaces, or zero-length strings (often caused by exporting data from other software). You will need to use the Find and Replace tool or the TRIM function to clear those cells completely before selecting Blanks.
