How to Return Y or N Based on Whether Cells Contain Data in Excel
Question details
The user wants to use a spreadsheet formula to check a specific range of cells and output 'Y' if any cell in that range has content, or 'N' if the entire range is completely blank.

- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Setting up data validation, status checking, or conditional reporting based on the presence or absence of data in a given cell range.
- Observed behavior
- Needs a logical formula that automatically evaluates a range and dynamically returns conditional text (Y/N) based on the presence of cell content.
Identify the exact range of cells you want to check for data, and select the destination cell where you want the final 'Y' or 'N' result to appear.
Use the IF and COUNTA Functions Together
Combine the IF function with COUNTA to evaluate if a range contains any non-blank cells and return a specific text value based on the result.
The COUNTA function is designed to count the number of cells in a range that are not empty. By nesting this function inside an IF statement, you create a logical test: if the count of non-blank cells is zero, the formula returns 'N'; if the count is one or more, it returns 'Y'.
Click on the cell (for example, A11) where you want the 'Y' or 'N' result to be displayed.
Type the formula =IF(COUNTA(A1:A10)=0,"N","Y") into the formula bar. Replace A1:A10 with the actual range of cells you need to evaluate.
Press the Enter key. The cell will now display 'Y' if there is any data in the selected range, or 'N' if all cells in the range are blank.

Effortlessly Manage Data and Formulas with WPS Spreadsheet
WPS Office provides a powerful spreadsheet application that fully supports standard formulas like IF and COUNTA. You can easily process data, build logical tests, and analyze datasets for free with an intuitive interface.
- 1. Open your spreadsheet: Launch WPS Spreadsheet and open the document where you need to check for cell data.
- 2. Select the result cell: Click on the cell where you want the conditional Y or N output to appear.
- 3. Insert the formula: Go to the Formulas tab and click Insert Function to search for IF and COUNTA, or just type =IF(COUNTA(Range)=0,"N","Y") directly into the formula bar.
- 4. Calculate instantly: Press Enter to execute the formula. WPS Spreadsheet will instantly calculate and update the result if you add or remove data from the target range.

Frequently Asked Questions
Why does the formula return 'Y' even when the cells look empty?
Cells that appear visually empty might contain hidden spaces, invisible formatting, or formulas that return an empty string (""). The COUNTA function counts these as non-blank. To fix this, select the supposedly empty cells and press the Delete key to clear hidden contents.
Can I return 'Y' or 'N' based on checking just a single cell instead of a range?
Yes. If you only need to check one cell (like A1), you do not need COUNTA. You can use the formula =IF(ISBLANK(A1),"N","Y") or =IF(A1="","N","Y").
How do I change the formula to return 'Yes' and 'No'?
You can update the text values inside the IF function. Replace the 'N' and 'Y' with your preferred words. For example: =IF(COUNTA(A1:A10)=0,"No","Yes"). Make sure to keep the quotation marks around your text.
Does the COUNTA function ignore numbers and only count text?
No, COUNTA counts any type of data, including text, numbers, dates, error values, and logical values (TRUE/FALSE). If a cell contains any content at all, it will be counted.




