Bps Number Format in Excel: Display Basis Points

How-to basics · Updated November 19, 2026

Excel has no built-in “bps” number format. To show values already stored as basis-point numbers, select the cells, open Format Cells, and apply the custom format 0" bps"; if your cells contain decimal rates instead, convert them to basis points first, for example by multiplying by 10,000.

What “bps” means in a spreadsheet

Bps is an abbreviation for basis points, a unit used to describe small changes in interest rates, yields, fees, and other percentage-based measures. One basis point is one hundredth of a percentage point: 1 bps equals 0.01 percentage point, and 100 bps equals 1 percentage point. A change from 4.00% to 4.25%, for example, is 25 basis points.

Excel can store the same rate in different numeric forms. A rate of 1.25% is commonly stored as the decimal 0.0125. A change of 125 basis points can instead be stored as the integer 125. The right number format depends on which representation your cells contain. A suffix format adds a label to the displayed number; it does not calculate a conversion between these representations.

This distinction matters because applying a bps suffix to 0.0125 would display “0.0125 bps,” not “125 bps.” That result is technically consistent with the stored number but usually not what a reader intends. Decide whether a column holds decimal rates, percentage values, or basis-point counts before formatting it.

Apply a custom bps format in Excel for Windows

In Excel for Microsoft 365 on Windows, use a custom number format to append “bps” while keeping the cell numeric. This is preferable to typing the letters into each cell, because the values remain available for calculations, sorting, charts, and numeric filters.

  1. Select the cells that already contain basis-point numbers, such as 25 or 125.
  2. Press Ctrl+1 to open the Format Cells dialog. You can also use the dialog launcher in the Home tab’s Number group.
  3. Choose the Number tab, then select Custom in the Category list.
  4. Enter 0" bps" in the Type box and select OK.

The value 125 will display as “125 bps,” while the formula bar still shows the numeric value 125. To show a decimal place, use 0.0" bps"; 12.5 will display as “12.5 bps.” For thousands separators, use #,##0" bps", which displays 12500 as “12,500 bps” in a typical English-language regional setting.

On Excel for Mac, select the cells and press Command+1 to open Format Cells, then choose Number > Custom and enter the format code. The dialog’s layout can differ slightly by Excel release, but the custom format works the same way. In Excel for the web, existing custom formats generally display, but if you cannot create or edit the format in your version, open the workbook in the desktop app.

Choose the right format code

Excel custom number formats can control the number of displayed decimal places and include a literal unit label. The characters inside quotation marks are shown as text, while the number pattern controls how the numeric value appears. These are useful starting points for basis-point columns:

  • 0" bps" displays whole-number values with a suffix, such as 25 bps.
  • 0.0" bps" displays one decimal place, such as 25.4 bps.
  • 0.00" bps" displays two decimal places, such as 25.40 bps.
  • #,##0" bps" adds a thousands separator to whole-number values.

Choose precision based on the source data and how the numbers will be interpreted. A format that displays no decimals can make 25.4 appear as 25, hiding a meaningful tenth of a basis point. Conversely, showing several decimals does not make the underlying data more accurate; it may only make estimated or rounded figures look more precise than they are. A number format changes what is visible, not the stored value.

For different display rules for positive, negative, and zero values, Excel custom formats can have up to four semicolon-separated sections. Such codes can be useful in polished reports, but simpler formats are easier to audit. Test any multi-section format with a positive number, a negative number, and zero before using it in a shared workbook.

Convert decimal rates to basis points

If the source values are decimal rates, use a formula to create a separate basis-point result, then apply the suffix format to that result. For example, a decimal rate of 0.0125 represents 1.25%, or 125 bps. Multiplying a decimal rate by 10,000 converts it to basis points because 1.0000 as a decimal rate equals 10,000 bps.

Assume decimal rates are in A2:A10 and you want the basis-point result for the first rate in B2. Enter =A2*10000 in B2 and fill the formula down; if A2 contains 0.0125, B2 returns 125. Apply 0" bps" to B2:B10 to show the unit. Keep the original rate column if you need to preserve the source values or use them in other calculations.

The reverse conversion is also straightforward. Assume basis-point values are in A2:A10 and you want the equivalent decimal rate in B2. Enter =A2/10000 in B2 and fill down; if A2 contains 125, B2 returns 0.0125. Format B2:B10 as Percentage to display 1.25%. In Excel, percentage formatting displays a decimal value multiplied by 100, so applying Percentage to 125 would display 12,500%, not 1.25%.

For a change between two decimal rates, subtract the earlier rate from the later rate, then multiply the difference by 10,000. Assume the earlier decimal rate is in A2 and the later decimal rate is in B2. Enter =(B2-A2)*10000 in C2; if A2 is 0.04 and B2 is 0.0425, C2 returns 25, meaning an increase of 25 bps. A negative result indicates a decrease. This calculation expresses the difference in basis points, not the percentage change relative to the earlier rate.

How to choose between formatting and formulas

Use a custom number format when the value is already stored as a basis-point count and you only want a readable label. For instance, a dataset with values 5, 25, and 100 can remain numeric and display as 5 bps, 25 bps, and 100 bps. This approach keeps a single source of truth and avoids converting values unnecessarily.

Use a formula when the underlying numbers use a different scale, such as decimal rates or percentage changes. A separate formula column makes the conversion visible and can be checked against the original data. It also avoids a common reporting error: labeling 0.0025 as 25 bps without actually multiplying it by 10,000.

For a one-off report, it may be tempting to type “bps” into each cell, but that turns the entries into text. Text values can behave differently in arithmetic, sorting, and charting; some functions may ignore text that looks like a number. If you need a heading, put “Change (bps)” in the column header and keep the cells numeric, or use a custom suffix when the unit should appear on every displayed value.

Common mistakes and troubleshooting

  • The display says 0.0125 bps. The cell probably contains a decimal rate. Multiply it by 10,000 in a formula, or store the converted result in a separate column.
  • The display says 12,500%. Percentage formatting was applied to a value such as 125 that is already a basis-point count. Convert it to a decimal rate by dividing by 10,000 before applying Percentage.
  • The value has a bps suffix but calculations seem wrong. A custom suffix does not change the stored number. Check the formula bar or use the value in a test calculation to confirm whether it is 0.0125 or 125.
  • Decimals seem to disappear. The selected format may show zero decimal places. Choose a pattern such as 0.0" bps" if tenths are meaningful.
  • The unit appears twice. Check whether the cell contains text such as “25 bps” and also has a custom suffix. Keep the cell numeric and use only one method to show the unit.

Also verify whether a source column represents a rate level or a change. A rate of 4.25% is 425 bps when expressed as a level; the change from 4.00% to 4.25% is 25 bps. Confusing the level with the difference is a frequent source of misleading labels, even when the Excel formatting itself is correct.

Using bps in charts and shared workbooks

Charts usually inherit number formatting from their source cells, but the result can vary with chart type and axis settings. If a chart axis should display basis-point values, make sure the plotted values are already in basis points, then format the source cells or the chart axis with an appropriate custom format. If the chart plots decimal rates, labeling the axis with a bps suffix alone does not convert the scale.

When sharing a workbook, include the unit in a clear heading, such as “Spread (bps),” and explain any conversion if readers may encounter both decimal rates and basis-point counts. The suffix improves readability, while the heading and source notes make the measurement unambiguous. For externally supplied data, check whether values are stored as whole basis points, decimals, or percentage points before building formulas or charts.

Custom number formats are generally stored with the workbook, so recipients using desktop Excel can see the intended display. Regional settings may affect the appearance of decimal and thousands separators, but not the underlying numeric value. If a recipient sees an unexpected result, have them inspect the cell’s number format and actual stored value rather than assuming the displayed suffix performed a conversion.

Frequently Asked Questions

Does Excel have a built-in bps format?
No. Excel does not provide a dedicated basis-points category in its standard number formats. You can create a custom format such as 0" bps" to append the label to numeric values that are already stored as basis points.

How do I display 0.01 as 100 bps?
If 0.01 is a decimal rate, multiply it by 10,000: 0.01 × 10,000 = 100. Put the calculation in a formula result cell, then apply a custom format such as 0" bps". Adding the suffix to 0.01 alone will not make Excel display 100.

How many basis points are in 1 percent?
One percentage point equals 100 basis points. As a decimal rate, 1% is 0.01, which equals 100 bps. Be careful to distinguish a 1% rate from a 1% relative increase in some other value; the latter is not automatically a one-percentage-point change.

Can I use a bps-formatted cell in a formula?
Yes. If the cell contains a numeric value and “bps” is only a custom-format suffix, Excel calculations use the underlying number. If you typed the letters into the cell itself, the entry is text and may not behave as a number in formulas or charts.

How do I show negative basis points?
Use the same basic format, such as 0" bps"; Excel will show a minus sign for a negative numeric value by default. If your report needs a special style for negatives, create and test a custom format with separate positive and negative sections, taking care not to hide a meaningful sign.

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.