'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Fix Excel UNIQUE Formula Not Working in One Workbook
Question details
The UNIQUE formula functions correctly in other files but fails to calculate within one specific Excel workbook, displaying as text instead.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Using the UNIQUE function to extract distinct values from a dataset within an existing workbook.
- Observed behavior
- The formula does not execute or calculate the array; it only works if the data is copied over to a brand-new workbook.
Before modifying your formula, check if the cell containing the UNIQUE function displays the formula text itself instead of the calculated result, which is the primary indicator of a formatting issue.
Change Cell Formatting from Text to General
The most common reason a formula fails to calculate in a specific workbook is that the cells were pre-formatted as Text before the formula was entered.
When a cell is formatted as Text, Excel treats everything typed into it—including functions starting with an equal sign—as standard alphanumeric text, skipping the calculation process entirely.
Click on the cell or highlight the range of cells where the UNIQUE formula is currently typed but not calculating.
Navigate to the Home tab on the Excel ribbon, locate the Number group, and click the dropdown menu to change the format from 'Text' to 'General'.
Double-click the cell or press F2 to enter edit mode, then press Enter. This forces Excel to recognize and calculate the formula.
Disable the Show Formulas Feature
Sometimes, the entire worksheet is set to display formulas instead of their calculated results due to a specific auditing setting being enabled.
Convert Numbers Stored as Text
If your UNIQUE formula references numeric data that Excel reads as text, it may cause calculation anomalies or incorrect distinct values.
Easily Extract Unique Values with WPS Spreadsheet
WPS Spreadsheet fully supports dynamic array formulas, including the UNIQUE function, with seamless compatibility for Microsoft Excel files. It automatically handles calculations without requiring manual formatting adjustments in most standard workflows.
- 1. Open your file: Launch WPS Spreadsheet and open the workbook containing your dataset.
- 2. Select the destination: Click on an empty cell where you want the unique distinct values to start spilling.
- 3. Enter the formula: Type =UNIQUE( and highlight the range of data you want to analyze.
- 4. Generate results: Type a closing parenthesis and press Enter to instantly extract the unique entries.
Frequently Asked Questions
Why does Excel show my formula as plain text instead of calculating it?
This typically happens when the cell's number format is set to 'Text' before you type the formula. Excel treats the equal sign and the function name as a regular text string. You must change the format to 'General', press F2, and press Enter to fix it.
Why does copying data to a new workbook fix my formula issues?
New workbooks have default cell formatting, which is usually set to 'General'. When you paste unformatted data or formulas into a new workbook, it bypasses the specific 'Text' formatting rules that were preventing calculations in the original file.
How do I remove the green triangles in my Excel cells?
Green triangles indicate an error or inconsistency, such as numbers being stored as text. To resolve this, select the affected cells, click the yellow warning icon that appears beside them, and choose 'Convert to Number'.
Can the UNIQUE function spill results into multiple columns?
Yes, if your selected array spans multiple columns, the UNIQUE function will evaluate distinct rows across those columns and automatically spill the results into the adjacent cells, maintaining the original column structure.
