Fix an Excel Name Manager Formula Error Caused by Regional Settings
Question details
The user needs to enter an OFFSET formula in Excel Name Manager without a formula error.

- 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.
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.
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.
In Excel, select Formulas > Name Manager. Select the existing name and click Edit, or click New to create one.
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.
Click OK and close Name Manager. Select the name from the Name Box beside the formula bar to check the referenced range.

Check the Windows regional list separator
Use this method on Windows to verify the separator Excel is expected to use.
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.

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.




