How to Display a Column Header in WPS Office Spreadsheets When a Cell Equals 1
Question details
The user needs a formula that displays the header from the first row of a column when the corresponding data cell contains 1, and returns a blank when it contains 0, without changing the source data.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has data in a range such as A2:N100 with column headers in A1:N1 and wants an output range showing only the headers for cells equal to 1.
- Observed behavior
- The desired result is an output grid that shows the corresponding column header where each source cell equals 1 and an empty string where each source cell equals 0.
Confirm that your data range and header row are in the same columns and that the output range has the same number of rows and columns as the source data.
Use the IF formula for fixed data ranges
Best for most WPS Office Spreadsheets and Excel workbooks when you need a simple, editable result that works without dynamic array functions.
Select the cell in your output range that corresponds to the first data cell, for example the cell next to A2.
Type =IF(A2=1,A$1,"") and press Enter. A$1 locks the header row while the column reference adjusts when you copy the formula.
Drag the fill handle across the remaining columns and then down through all rows to match the source data range, such as A2:N100.
Use MAKEARRAY with LAMBDA for a single dynamic formula
Use this method in Microsoft 365 or Excel for the web when you want one formula to spill the complete result across the output range.
Display Column Headers When a Cell Equals 1 in WPS Office Spreadsheets
WPS Office Spreadsheets can complete this task directly with a simple IF formula. Enter the formula in an output area to read the source data and return the correct header without restructuring your workbook.
- 1. Open the workbook: Launch WPS Office Spreadsheets and open the workbook containing the headers in A1:N1 and data in A2:N100.
- 2. Select the output cell: Click the cell in the output range that corresponds to the first data cell in the source range.
- 3. Enter the formula: Type =IF(A2=1,A$1,"") and press Enter. The cell displays the header if A2 equals 1, otherwise it stays blank.
- 4. Fill the output range: Copy the formula across all columns and down all rows that match the source data.
- 5. Verify the result: Confirm that each 1 in the source data now shows the correct column header and each 0 shows a blank cell.
Frequently Asked Questions
Why does the formula use A$1 instead of $A$1?
A$1 locks row 1 so the header row stays fixed when you copy the formula down. The column part remains relative, so copying across columns changes A, B, C and so on.
Can I return a header when a cell contains text instead of 1?
Yes. Replace the condition with a text comparison, for example =IF(A2="Yes",A$1,""), adjusting the value and ranges to your data.
Will this formula change my source data?
No. The formula only reads the source cells. Place the formula in a separate output area to keep the original data unchanged.
Does this work in both WPS Office and Microsoft Excel?
Yes. The basic IF formula works in WPS Office Spreadsheets and all modern versions of Microsoft Excel, and the workbook can be saved as .xlsx.
Why does my blank result show as 0?
Use "" as the false value in the IF formula so the result is an empty string. If you still see zeros, check the worksheet option for displaying zero values or confirm the formula was entered exactly.
