How to Show Complete When All Excel Cells Are Filled in WPS Office
Question details
The user wants a formula to display Complete only when all cells in a monitored range contain a text or numeric entry.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user needs a status indicator that confirms all required cells in a range are filled.
- Observed behavior
- The sheet currently lacks a formula, so there is no automatic Complete indicator when the range is fully filled.
Open the workbook in WPS Office Spreadsheets and identify the exact range to check, such as A1:A5. Keep the result cell outside that range so the formula does not include its own output.
Use COUNTBLANK to show Complete when all cells are filled
Use this formula when you need a simple Excel-compatible way to treat any empty cell in the monitored range as incomplete.
COUNTBLANK counts only truly empty cells in the selected range. It works for mixed text and numeric entries because it does not depend on cell content type.
Click the cell where you want the Complete or Not complete status to appear, such as B1.
Type =IF(COUNTBLANK(A1:A5)>0,"Not complete","Complete") and adjust A1:A5 to your monitored range.
Press Enter to calculate the result. The cell will display Complete only when every cell in the range is filled.
Use COUNTA and ROWS for a count-based Complete check
Use this if you prefer to confirm that the number of non-empty cells matches the total number of cells in the range.
Display a Complete Status Formula in WPS Office Spreadsheets
Use WPS Office Spreadsheets to create a live status formula that shows Complete when every required cell is filled. It works with the same COUNTBLANK logic as Excel and saves in Microsoft Excel-compatible file formats.
- 1. Launch WPS Office Spreadsheets: Open WPS Office and create or open the workbook that contains the cells to monitor.
- 2. Identify the monitored range: Select or note the cells to check, such as A1:A5, and make sure they contain the required text or numbers.
- 3. Click the result cell: Click an empty cell outside the monitored range where the Complete status should appear.
- 4. Enter the status formula: Type =IF(COUNTBLANK(A1:A5)>0,"Not complete","Complete") and press Enter.
Frequently Asked Questions
Why does the formula show Not complete even though the cell looks filled?
If a cell contains a space, COUNTBLANK does not treat the space as empty. Delete the cell content or clean entries before checking.
Can I use this formula when the range contains only numbers?
Yes. COUNTBLANK ignores content type, so it works for numbers, text, and mixed data. It only counts cells that are truly empty.
How can I change the messages from Complete and Not complete?
Edit the text inside the quotation marks in the formula. For example, use "Done" or "Pending" as the status labels.
Does this formula work in Microsoft Excel and WPS Office?
Yes. The COUNTBLANK, IF, COUNTA, and ROWS functions are supported in both WPS Office Spreadsheets and Microsoft Excel, including .xlsx files.
