Excel Script: Set Number Format in a Range

How-to basics · Updated November 19, 2026

To set a number format with an Excel Office Script, get the target range and call its setNumberFormat() method. For example, range.setNumberFormat('#,##0.00'); displays values with two decimal places and a thousands separator without changing the values stored in the cells.

What an Excel script does when it sets a number format

Excel’s scripting feature is called Office Scripts. It uses TypeScript to automate workbook tasks, including applying number formats to ranges. The format is a string written in Excel’s number-format syntax; it is not a JavaScript date pattern or a formula. A script can select cells by address, then apply one format to the whole range or assign different formats to different cells.

For a fixed range, a basic script looks like this:

function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet();
  const amountRange = sheet.getRange('B2:B20');
  amountRange.setNumberFormat('#,##0.00');
}

This script applies the format to B2:B20 on the active worksheet when it runs. If the active sheet can vary, make the script target a named worksheet instead, so it does not format the wrong tab by accident. Office Scripts change workbook contents, so check the selected sheet and address before running a script on important data.

How to create and run the script

In supported versions of Excel for Microsoft 365, open the workbook and select Automate > New Script. The script editor opens with a starter script. Replace or edit the code, then save it with a recognizable name. To run a saved script, open it from the Automate tab and choose Run. The precise availability of Office Scripts depends on the account, Microsoft 365 plan, platform, and organization settings; an administrator may disable the feature.

  1. Open the workbook and confirm which worksheet should be formatted.
  2. Choose Automate > New Script, then enter or paste the script.
  3. Change the worksheet reference, cell address, and format code to fit the workbook.
  4. Save the script, review the target range, and run it.
  5. Inspect a few cells to confirm that the display matches what you intended.

Office Scripts are available in Excel for the web and in supported Microsoft 365 desktop versions, including Windows and Mac. If the Automate tab is missing, check whether the account and organization allow Office Scripts rather than assuming the script itself is at fault. Menu labels and availability can vary with product updates and account configuration.

Choosing a format code

The format code controls how Excel displays a number. It does not calculate a new value. Common examples include:

  • 0 displays a whole number, including a zero when the value is less than one.
  • #,##0 displays whole numbers with a thousands separator where appropriate.
  • #,##0.00 displays two decimal places, adding zeros when needed.
  • 0.0% displays a proportion as a percentage with one decimal place. A stored value of 0.125 appears as 12.5%.
  • yyyy-mm-dd displays a recognized date in a year-month-day layout.
  • $#,##0.00 displays a dollar sign and two decimal places, but the symbol does not convert a value into a different currency.

In many custom formats, 0 is a required digit placeholder: Excel shows a zero when there is no digit in that position. The # placeholder shows a digit only when needed. The decimal point and comma determine the displayed precision and grouping in the format pattern, subject to regional settings. A format can also include sections separated by semicolons for positive numbers, negative numbers, zero, and text. For example, a pattern can give negative values a red display or show zero as a dash. Test custom patterns on representative values before applying them broadly.

Formatting dates, percentages, and currency

Dates and times are stored as numeric values in Excel, so a date format changes how a serial value is displayed rather than turning text into a date. If a cell contains a valid Excel date, applying yyyy-mm-dd can make its display easier to sort and read. If it contains text such as March 5, setting a date number format will not necessarily convert that text into a usable date. Convert or clean the source data separately when required.

Percentage formatting is a frequent source of surprises. Excel displays 0.2 as 20% under a percentage format because the display scales the underlying value by 100. If a cell already stores 20 and the script applies 0%, it will display as 2000%, not 20%. The appropriate fix depends on how the source values were entered; changing the format alone should not be used to repair a scaling mistake.

Likewise, a currency symbol is a display choice, not proof that the data uses that currency. If a workbook contains several currencies, use an appropriate symbol or code for each group and document what the values mean. A format such as $#,##0.00 does not perform a currency conversion or identify whether the dollars are US, Canadian, Australian, or another currency.

Applying different formats to different cells

Use setNumberFormat() when every cell in the range should share one format. To assign a different format to each cell, use setNumberFormats(), which takes a two-dimensional array of format strings. Its row and column dimensions must match the range.

function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet();
  const range = sheet.getRange('A2:C3');
  range.setNumberFormats([
    ['@', '#,##0.00', '0%'],
    ['@', '#,##0.00', '0%']
  ]);
}

Here, A2:A3 receive the text format, B2:B3 receive two-decimal number formatting, and C2:C3 receive percentage formatting. The format does not convert existing values. In particular, applying @ to cells after numbers or dates are already stored does not necessarily restore an earlier text value; it sets how Excel treats or displays entries going forward. For a large or irregular selection, it may be clearer to divide the cells into logical ranges and call setNumberFormat() once for each range.

Number format versus changing the underlying value

A number format is different from editing a value, writing a formula, or converting text. If a cell stores 4.567, applying 0.00 makes it display as 4.57, but the stored number remains 4.567. Calculations that refer to the cell continue to use the underlying value, not the rounded display. If the requirement is to change the stored value, use an appropriate calculation or data-conversion step rather than relying on formatting.

This distinction also explains why formatting cannot fix every problem. If imported values are text, arithmetic may fail even after a numeric-looking format is applied. If a number is displayed in scientific notation because of a narrow column, adjusting the column width may be needed in addition to setting a format. And if a custom format hides digits, the data may still contain them. Check the formula bar or use a wider format when you need to verify what is actually stored.

Locale, regional settings, and script alternatives

Office Scripts provides both setNumberFormat() and setNumberFormatLocal(). The first accepts a number-format code; the Local version uses the format syntax associated with the user’s locale. This matters in workbooks used across languages or regions, where date conventions, separators, and localized format names can differ. For shared scripts, test the chosen code in the environments where it will run. If a script is specifically meant for one locale, the Local method can make that intent explicit.

For a one-time change, using Excel’s ribbon may be quicker: select the cells and choose a format from Home > Number, or open the Format Cells dialog for more options. Use an Office Script when you need to repeat the same operation, apply a consistent format to a known range, or automate formatting as part of a larger workflow. VBA macros are another automation option in desktop Excel, but they are a separate technology and have different security and platform considerations. An Office Script should not be confused with a formula typed into a worksheet cell.

Common problems and how to diagnose them

  • The script runs but nothing seems to change: Verify the active worksheet and range address, then check whether the values already look the same under the new format.
  • Percentages are 100 times too large: Inspect the stored values. Percentage formatting scales the display; it does not divide the stored number by 100.
  • A date remains unchanged: Check whether the cell contains a real Excel date value or text. A number format does not reliably parse arbitrary text into a date.
  • Only some cells show the expected format: Look for merged cells, different target ranges, or conditional formatting rules that apply their own visual number format.
  • The displayed decimals differ from the calculation: Remember that formatting can round the display while preserving more precision in the stored number.
  • A script errors on a format array: Ensure the two-dimensional array passed to setNumberFormats() has exactly the same number of rows and columns as the selected range.

Conditional formatting is worth checking when a cell appears to ignore the base format. A conditional formatting rule can apply a number format when its condition is met, so the appearance may depend on the cell’s value. Review the rules for the affected range in Home > Conditional Formatting > Manage Rules before repeatedly changing the script.

Frequently Asked Questions

What is the Office Script method to set a number format?
Use setNumberFormat() on a range for one shared format, such as range.setNumberFormat('0.00%');. Use setNumberFormats() when the cells need different format codes.

Does setting a number format change the cell value?
No. It changes the display format, not the stored value. For example, showing two decimal places does not round the underlying number used in calculations.

Can an Excel script format a whole column?
Yes. A range can refer to a column, but formatting an entire column may be unnecessary for a task that affects only a few rows. Targeting the relevant cells can make the script’s purpose clearer and avoid changing unrelated workbook formatting.

Why does 0.15 display as 15%?
That is the expected result when percentage formatting is applied. Excel displays the stored proportion multiplied by 100, so 0.15 appears as 15%.

Can I set the format without using a script?
Yes. Select the cells and use the Number controls on the Home tab, or open Format Cells to choose a built-in or custom format. A script is useful when the same formatting needs to be repeated or automated.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.