Function Problems

How to Fix Excel SUBTOTAL Function Not Working on Mac

Steven ParkerSteven Parker Nov 2, 2026 869 views

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.

Fix Excel SUBTOTAL Function Not Working on Mac
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the cell

Click on the cell where you want to apply the SUBTOTAL function.

2
Enter the formula

Type the formula using a semicolon instead of a comma. For example, enter =SUBTOTAL(1; A2:A5).

3
Calculate the result

Press Return on your keyboard to execute the formula and check if the subtotal calculates correctly.

Use Semicolons as Formula Separators
Formula Syntax Auto-Translation: When you send this file to a user whose region settings use commas as formula separators, Excel will automatically convert your semicolons to commas for them.
WPS Spreadsheets Function Guide

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. 1. Open your file: Launch WPS Spreadsheets on your Mac and open your target data file.
  2. 2. Select the target cell: Click the specific cell where you want the subtotal result to be displayed.
  3. 3. Insert the function: Type =SUBTOTAL( and select the appropriate function number (e.g., 9 for SUM or 1 for AVERAGE).
  4. 4. Select the data range: Highlight the data range you want to calculate, add the closing parenthesis, and press Enter to view your result.
Seamlessly handles Microsoft Excel formulas and .xlsx file formats.Built-in function wizard makes calculating totals, averages, and counts easy without syntax memorization.Free and lightweight, optimized for maximum performance on macOS.
microsoft office alternative - wps office

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.