Fix MAXIFS and MINIFS #NAME Error When Importing CSV in Excel
Question details
Users need a way to prevent Excel from automatically adding the '@' symbol to MAXIFS and MINIFS formulas when opening CSV files, which breaks the formulas.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Opening or importing a CSV file that contains MAXIFS or MINIFS formulas into Microsoft Excel.
- Observed behavior
- Excel automatically adds the implicit intersection operator (@) to the formulas (e.g., =@MAXIFS(A:A,A:A,2)), which causes the cells to display a #NAME? error.
Because CSV is a plain-text format that does not natively store formula structures, ensure your system's regional settings use the correct list separator (comma or semicolon) before opening the file.
Convert CSV to XLSX Using an Alternative Spreadsheet Application
Opening the CSV in a different program bypasses Excel's dynamic array auto-formatting, allowing you to save the formulas intact.
Microsoft 365 automatically applies the implicit intersection operator (@) to certain functions when reading plain text files. Using a lightweight alternative like LibreOffice or WPS Office prevents this behavior during the initial file load.
Launch an alternative spreadsheet program (such as LibreOffice Calc or WPS Spreadsheet) and open your original CSV file.
Check the cells containing the MAXIFS and MINIFS functions to ensure the '@' symbol has not been added and no #NAME? error is displayed.
Navigate to 'File' > 'Save As' and choose 'Office Open XML Spreadsheet (*.xlsx)' as the file format.
Launch Microsoft Excel and open the newly saved .xlsx file. The formulas will now load correctly as native Excel functions.
Import Raw CSV Data into an Existing Workbook
Instead of storing formulas inside a plain-text CSV, keep your formulas in an Excel workbook and import only the raw data.
Rewrite MAXIFS as an Array Formula
If you must use older formats or environments, you can replace MAXIFS with a combination of MAX and IF.
Open CSV Formulas Perfectly with WPS Office
WPS Spreadsheet seamlessly opens CSV files without misinterpreting newer functions or forcefully adding implicit intersection operators. You can natively load your MAXIFS and MINIFS formulas, work on them, and directly save them to standard Excel formats.
- 1. Launch WPS Spreadsheet: Open WPS Office and start a new Spreadsheet session.
- 2. Open the CSV File: Click 'Menu' > 'Open' and locate your CSV file containing the formulas.
- 3. Save as Excel Workbook: Once opened and verified, click 'Menu' > 'Save As' and select the standard '.xlsx' format.
Frequently Asked Questions
Why does Excel add the '@' symbol to formulas in my CSV?
The '@' symbol is the implicit intersection operator used in modern Excel versions with dynamic arrays. When opening unsupported formats like CSV, Excel's parser attempts to enforce compatibility for formulas it perceives as returning multiple values, forcefully adding the '@' to functions like MAXIFS and causing the #NAME? error.
Can CSV files safely store formulas?
No. CSV (Comma Separated Values) is strictly a plain text format designed for storing raw text and numbers. While you can save text that looks like a formula (e.g., '=A1+B1'), it is entirely up to the spreadsheet software's internal parser to interpret it correctly when reopened.
Does Power Query import formulas from CSVs?
Power Query imports the literal text or numeric values from the CSV. It is not designed to read and map functional formulas from plain text. Best practice is to import the raw CSV data into a table and apply formulas natively within the Excel workbook.
