How to Fill Blank Excel Cells with Their Column Header in WPS Office
Question details
The user needs to fill blank cells in a selected spreadsheet range with the value from each cell's corresponding column header.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a selected range that contains multiple blank cells and wants each blank cell to display the header value from its own column without filling them manually.
- Observed behavior
- Blank cells remain empty in the selected range, and filling each blank cell individually is slow and error-prone.
Open the workbook in WPS Office Spreadsheets and confirm that the header row is the first row of the selected range. After selecting the target range, note the column letter of the active blank cell so you can use the correct formula reference.
Fill blank cells with their column header using WPS Go To Special
Use this method when you have a large selected range and need every blank cell to pick up the header from its own column in one action.
WPS Spreadsheets selects only the blank cells in the range, so the formula you enter is applied only to those cells. Use a relative column reference such as =C$1 so each blank cell refers to row 1 of its own column.
Select the range that contains blank cells, for example C2:DL157, in your WPS Spreadsheets worksheet.
Press Ctrl+G or F5 to open the Go To dialog, click Special, choose Blanks, and click OK. WPS selects only the blank cells in the range.
Check which column the active blank cell is in. If it is in column C, type =C$1; if it is in column F, type =F$1. Adjust the column letter to match the active cell.
Press Ctrl+Enter instead of Enter or Tab. WPS fills every selected blank cell with the header value from its own column.
Fill Blank Spreadsheet Cells with Column Headers in WPS Office
Use WPS Spreadsheets' Go To Special feature to select all blank cells in a range and populate them with the matching column header formula in one step. WPS Office is free, lightweight, and fully compatible with Microsoft Excel workbooks.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, open the Excel workbook, and select the worksheet that contains blank cells.
- 2. Select the target range and open Go To Special: Highlight the range, press Ctrl+G or F5, click Special, select Blanks, and click OK.
- 3. Enter a relative column header formula: Type =C$1 or the matching column header reference for the active blank cell.
- 4. Press Ctrl+Enter to apply: Press Ctrl+Enter so WPS fills all selected blank cells with the header from their own column.
Frequently Asked Questions
Does this method work in WPS Office Spreadsheets?
Yes. WPS Spreadsheets has the same Go To Special command: select the range, press Ctrl+G or F5, click Special, choose Blanks, and click OK.
What formula should I use if the active blank cell is in column D?
Use =D$1. The column letter in the formula must match the active cell's column so each blank cell references row 1 of its own column.
Why do I need to press Ctrl+Enter instead of Enter?
Ctrl+Enter confirms the formula in all selected blank cells at once. Pressing Enter alone would fill only the active blank cell.
How do I replace the formulas with static header values afterward?
Select the filled range, press Ctrl+C, right-click, choose Paste Special, then select Values, or use WPS paste options to paste values only.
What if my header row is not row 1?
Change the row reference in the formula to the actual header row, for example =C$2 if column C headers are in row 2.
