Formula Errors

Fix an Excel Name Manager Formula Error Caused by Regional Settings

Kushani NimanthikaKushani Nimanthika Nov 20, 2026 869 views

Question details

The user needs to enter an OFFSET formula in Excel Name Manager without a formula error.

Fix an Excel Name Manager Formula Error Caused by Regional Settings
Product
Microsoft Excel
Device & OS
not provided
Scenario
Creating a named range that counts entries on the Lists worksheet.
Observed behavior
Excel reports a formula error when the formula is pasted or retyped in Name Manager.
Before you start

Confirm that the workbook has a worksheet named Lists and that cells K7:K300 contain the intended entries. Check whether formulas in your Excel installation use commas or semicolons between arguments.

Solution 1Recommended

Use the regional list separator in the Name Manager formula

Use this method if Excel expects semicolons instead of the commas in the supplied formula.

1
Open Name Manager

In Excel, select Formulas > Name Manager. Select the existing name and click Edit, or click New to create one.

2
Enter the adjusted formula

In the Refers to box, enter =OFFSET(Lists!$K$6;1;;COUNTA(Lists!$K$7:$K$300)) if your installation uses semicolons. Keep both consecutive semicolons: they represent the omitted column-offset argument.

3
Save and test the name

Click OK and close Name Manager. Select the name from the Name Box beside the formula bar to check the referenced range.

Use the regional list separator in the Name Manager formula
Check the source range: COUNTA must return a positive count for this OFFSET range. The formula assumes populated cells are contiguous from K7; gaps can cause the resulting range to include blanks and omit later entries.
Free Microsoft Office alternative

Manage spreadsheet formulas with WPS Office

WPS Office is a free, lightweight alternative for working with spreadsheets in a familiar interface. Open compatible Excel files and continue editing formulas as you move between office suites; WPS does not change Excel's regional settings.

Edit spreadsheet formulas in a familiar worksheet interfaceOpen and save compatible Microsoft Excel XLSX and XLS filesWork with a free, lightweight office suiteMove existing spreadsheet files into WPS Office without starting over
microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel reject commas in my Name Manager formula?

Excel may expect a semicolon as the formula argument separator under your regional settings. In Formulas > Name Manager, replace the commas with semicolons and try the formula again.

Why are there two semicolons together in the corrected formula?

In =OFFSET(Lists!$K$6;1;;COUNTA(Lists!$K$7:$K$300)), the empty argument between the two semicolons omits the column offset, so the reference stays in column K.

What happens if K7:K300 contains no data?

COUNTA returns zero, which gives OFFSET a zero height and can produce an error. Add an entry to the range or revise the formula to handle an empty list.

Can gaps in the list affect the named range?

Yes. COUNTA counts nonempty cells, but OFFSET returns a continuous range starting at K7. If the list has gaps, the range can include blank cells and exclude later entries.