Fill blank cells with value above / below in Excel (Full guide)
When setting up data in Excel, it is common to leave a blank cell when its data is the same as the cell above. This makes the table look neater and more aesthetically pleasing, but it can also cause some problems, such as being unable to use Excel's SUBTOTAL or Pivot Table features. In this case, we need to fill the blank cells. Here are some tricks to fill blank cells with the value above in Excel.
Compare methods for filling blank cells from above or below
Both methods can fill blank cells with values from neighboring cells, but the workflows are quite different. The formula method uses Excel's built-in tools but requires several steps and creates formulas first, while Kutools for Excel fills the selected blanks through a dedicated tool without creating temporary formulas.
| Method | Workflow | Final result | Best for |
|---|---|---|---|
| Formula + Go To Special | Select blanks, enter a reference formula, press Ctrl + Enter, then convert formulas to values with Paste Special | Filled values after the formulas are converted | Users who prefer a built-in Excel solution and do not mind the extra steps |
| Kutools for Excel | Select the range, choose Fill Blank Cells, select Based on values and the fill direction, then apply | Blank cells are filled directly without creating temporary formulas | Quickly filling many blanks from above, below, left, or right |
Fill blank cells with the value from above or below using a formula
With this method, please do as the following steps:
1. Select the range that contains blank cells you need to fill.
2. Click "Home" > "Find & Select" > "Go To Special…", and a "Go To Special" dialog box will appear. Check the "Blanks" option. See screenshot:
3. Click "OK", and all of the blank cells have been selected. Then input the equal sign "=" into the active cell A3 without changing the selection. Point to the cell above or below with the up or down arrow key or just click on it. See screenshot:
4. Press "Ctrl" + "Enter", Excel will copy the respective formula to all blank cells.
5. At this point, the filled contents are formulas rather than fixed values. To convert them, select the whole range and copy it, then press "Ctrl" + "Alt" + "V" to open the "Paste Special…" dialog box. Select "Values" under "Paste" and "None" under "Operation". See screenshot:
6. Click "OK". All of the formulas have now been converted to fixed values.
Fill blank cells with the value from above or below using Kutools for Excel
If you want to fill blank cells without selecting blanks separately, entering formulas, and then converting those formulas to values, Kutools for Excel's Fill Blank Cells feature provides a much faster solution. It can fill empty cells based on values from above, below, left, or right through one dialog.
After installing Kutools for Excel, please do as follows:
1. Select the range that contains the blank cells, and click Kutools > Insert > Fill Blank Cells. See screenshot:
2. In the Fill Blank Cells dialog box, choose Based on values in the Fill with section. Then choose the direction you need under Options. For example, select Down to fill each blank cell with the value above it.
3. Click OK or Apply. The blank cells are filled directly with the corresponding values, without creating temporary formulas or requiring a separate Paste Special step.
 |  |  |
Both methods can fill blank cells with neighboring values and make the data easier to use with features such as SUBTOTAL and Pivot Tables. The formula method is useful when you want an entirely built-in Excel solution, while Kutools for Excel is more convenient when you need to fill many blank cells quickly or want the flexibility to fill from different directions.
Fill blank cells with value above / linear values / specific value
Related articles:
How to fill blank cells with linear values in Excel?
How to fill blank cells with 0 or other specific value in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in