How to Fix Excel SUBTOTAL Function Not Working on Mac
Question details
The user is unable to use the SUBTOTAL function in Excel for Mac due to formula errors caused by system region separator mismatches.

- Product
- Microsoft Excel
- Device & OS
- macOS
- Scenario
- Entering the SUBTOTAL function to calculate data ranges in an Excel spreadsheet on a Mac.
- Observed behavior
- The formula fails to compute or returns an error because Excel requires semicolons instead of commas depending on the macOS region settings.
Verify the exact formula error message you are receiving in Excel and check if your Mac's default language is set to a region that uses commas as decimal separators.
Use Semicolons as Formula Separators
Adjust your formula syntax to match your system's region configuration by replacing commas with semicolons.
If your Mac is configured to a region that uses commas as decimal separators, Excel will automatically require semicolons to separate arguments within formulas to avoid syntax conflicts.
Click on the cell where you want to apply the SUBTOTAL function.
Type the formula using a semicolon instead of a comma. For example, enter =SUBTOTAL(1; A2:A5).
Press Return on your keyboard to execute the formula and check if the subtotal calculates correctly.

Modify macOS Language & Region Settings
Change your Mac's system preferences to standard US number separators so Excel accepts commas in formulas.
Update Microsoft Excel for Mac
Software bugs in outdated versions of Excel can cause formatting and formula delimiter issues. Ensure your software is current.
Calculate Subtotals Effortlessly with WPS Spreadsheets
You can quickly apply the SUBTOTAL function to your datasets in WPS Office without worrying about complex system region configurations. WPS Office provides excellent compatibility and intuitive formula entry for Mac users.
- 1. Open your file: Launch WPS Spreadsheets on your Mac and open your target data file.
- 2. Select the target cell: Click the specific cell where you want the subtotal result to be displayed.
- 3. Insert the function: Type =SUBTOTAL( and select the appropriate function number (e.g., 9 for SUM or 1 for AVERAGE).
- 4. Select the data range: Highlight the data range you want to calculate, add the closing parenthesis, and press Enter to view your result.

Frequently Asked Questions
Why does Excel change my formula commas to semicolons?
Excel automatically adjusts formula delimiters based on your Mac's regional settings. If your region uses a comma as a decimal separator (common in many European countries), Excel uses a semicolon for formulas to avoid confusing a formula delimiter with a decimal point.
What is the correct syntax for the SUBTOTAL function?
The SUBTOTAL function syntax is =SUBTOTAL(function_num, ref1, [ref2], ...). The 'function_num' determines the operation (like 1 for Average or 9 for Sum), and 'ref1' is the range of data to evaluate.
Will my formulas break if I send the Mac file to a Windows user?
No, Excel automatically translates the formula separators to match the regional settings of the computer opening the file. Your semicolons will appear as commas for a user whose system uses standard US comma delimiters.




