Switch Excel Cell Format: Change Numbers, Dates, and Text

To switch an Excel cell’s format, select the cell or range, then choose a format from Home > Number Format in the Number group. For more choices, press Ctrl+1 on Windows (Command+1 on Mac) to open Format Cells and select a category such as Number, Date, Text, or Custom. This changes how Excel displays values; it does not always change the values themselves.

Change a cell format from the ribbon

The ribbon is the fastest way to apply a common format. Select one cell, a group of cells, a whole row or column, or a range, and use the Number Format drop-down on the Home tab. Common choices include General, Number, Currency, Accounting, Short Date, Long Date, Time, Percentage, Fraction, Scientific, and Text. The options visible can vary with the Excel version and the width of the ribbon.

For example, select a column of prices and choose Currency to display currency symbols and decimal places. Select a column of ratios and choose Percentage. The selected format applies to every selected cell, including blank cells, so formatting a whole column before entering data can help keep future entries consistent.

Choose General when you want Excel to use its usual display for the value.
Choose Number for values that should show a selected number of decimal places, with optional thousands separators.
Choose Short Date or Long Date to display date values in a date layout.
Choose Text when you need entries such as product codes to be treated as text.

Clicking a format applies it immediately. To adjust details such as decimal places, negative-number display, or a custom date layout, open Format Cells instead of relying on the quick choices.

What a cell format changes—and what it does not

A cell’s format controls presentation: things such as decimal places, date layout, currency symbols, alignment, font, borders, and fill color. In most cases Excel keeps the underlying value unchanged. If a cell contains the number 0.25, the Percentage format displays 25%, while the stored value remains 0.25. A Number format with two decimal places may display 0.25, and a custom format such as 0.000 may display 0.250.

This distinction matters because the displayed result is not always the value used in calculations. If a value is 12.345 and Number is set to show one decimal place, the cell displays 12.3, but calculations generally still use 12.345. Use number formatting when you want to change the display. If you need to round the underlying result, use a rounding formula or change the calculation that produces it.

Formatting also does not reliably turn text into a number or a date. If the value was stored as text, choosing Number or Date may leave it unchanged. Excel’s automatic interpretation when data is typed or imported depends on the entry and regional settings, so inspect the stored data when a format choice seems to have no effect.

Use Format Cells for detailed control

Format Cells provides more options than the ribbon’s quick format list. Select the cells and press Ctrl+1 in Excel for Microsoft 365 on Windows. The dialog includes Number, Alignment, Font, Border, Fill, and Protection tabs. Choose Number to select a category and set the available details, then click OK. On a Mac, Command+1 opens the formatting dialog; you can also use the Home tab’s formatting controls.

Within the Number category, you can set decimal places, choose whether to show a thousands separator, and choose how negative numbers appear. Currency and Accounting both show currency symbols, but their layouts differ: Accounting aligns symbols and decimal points down a column, which can make financial statements easier to scan. The symbol shown is a display choice, not a conversion of the amount into another currency.

The Custom category lets you create a display pattern from format codes. For example, #,##0.00 displays a number with a thousands separator and two decimal places; 0.0% displays a percentage with one decimal place; and yyyy-mm-dd displays a date in year-month-day order. A custom pattern such as 00000 can show the number 42 as 00042, but the underlying value is still 42. Custom formatting is useful when a standard option is close but not quite right.

Choose the right format for numbers, dates, and codes

Choose a format based on what the value means and how readers need to interpret it. For measured quantities, Number is often suitable. For a proportion stored as a decimal, Percentage is clear, provided the underlying value is correct: 0.15 displays as 15%, whereas 15 displays as 1,500%. That is a common surprise when a user applies Percentage to whole-number data.

Excel stores recognized dates as serial numbers and uses the cell format to show them as calendar dates. A date may therefore appear as a number after switching to General; applying a date format can make the same stored date readable again. The exact displayed order, such as month/day/year or day/month/year, may depend on the chosen format and regional settings. If a date was imported as text, a date format alone may not convert it.

For identifiers, ask whether arithmetic is meaningful. Postal codes, account codes, and some product IDs are labels, not quantities, so Text is often the safer format. This is especially important for long digit strings: Excel preserves only 15 significant digits for numeric values. If an identifier is longer, store it as text rather than as a number. Applying a custom zero-padding format can preserve a visual pattern for shorter numeric codes, but it does not make the underlying value textual.

When changing the format does not work

If selecting Number leaves a cell looking unchanged, check whether the entry is text. Imported values may contain leading apostrophes, spaces, or other characters, and cells formatted as Text before entry may keep later entries as text. A green error indicator may offer a Convert to Number option. For a column of consistently structured values, Data > Text to Columns can also help: review the steps and choose General for the column where appropriate, then finish the conversion. Make a copy first if the source data is important.

A formula can convert text that contains a number into a numeric result. With the text value 1250 in A2, enter =VALUE(A2) in another cell; it returns the number 1250. The new result can then be formatted as Number or Currency. This method only works when the text can be interpreted as a number under the workbook’s regional conventions; text containing extra words or an incompatible decimal separator may need cleaning first.

If a date format produces an unexpected serial number, the cell may already contain a recognized date, and the display may simply be set to General. Apply a date format to confirm. If nothing changes, the entry may be text or may not match the expected date convention. Do not assume that a date-looking string has become a true Excel date just because it resembles one on screen.

Also check for conditional formatting. A rule can change a cell’s appearance when its condition is met, making the visible result differ from the ordinary formatting you set. Review Home > Conditional Formatting > Manage Rules if colors or other formatting appear to return or behave inconsistently. The rule may need to be edited or removed rather than the cell’s basic number format.

Copy, remove, or reset formatting

To copy formatting without replacing the destination’s value, select the source cell and click Format Painter on the Home tab, then select the destination. Format Painter copies visual formatting, including number format and such properties as font and fill; it is not a way to copy only the number format. If you want to copy a cell’s contents as well, ordinary copy and paste does that, so check the paste options before proceeding.

To remove formatting while keeping cell contents, select the cells and choose Home > Editing > Clear > Clear Formats in Excel for Windows. This clears formatting such as number formats, fonts, fills, borders, and conditional formatting applied directly to the selected cells, but it does not delete the values or formulas. The Clear menu also contains options such as Clear Contents, which does delete entries, so choose carefully.

To reset only the number format, open Format Cells, choose Number, and select General. General restores the usual default display behavior for many values, but it does not repair text stored as text, remove every other style property, or undo a prior conversion. If you need to restore an entire cell’s appearance, use Clear Formats and then apply the desired formatting again.

Excel for Mac and Excel for the web

The main ribbon method is similar in Excel for Microsoft 365 on Windows and Mac: select cells, open Home, and use the number-format controls. On Mac, Command+1 is the common shortcut for Format Cells, whereas Windows uses Ctrl+1. If a shortcut is unavailable on your keyboard layout or is intercepted by the operating system, use the ribbon command instead.

Excel for the web also provides number-format choices on the Home tab. Its interface and the set of detailed options can differ from the desktop app, and a browser may reserve some keyboard shortcuts. If you cannot find a detailed custom-format control in the web interface, open the workbook in the desktop app to use its full Format Cells dialog. Formatting changes made in a workbook should still be checked in the view where the file will be used, especially when collaborators use different regional settings.

Frequently Asked Questions

What is the shortcut to change cell format in Excel?
On Windows, select the cells and press Ctrl+1 to open Format Cells. On Mac, use Command+1. To apply a common format quickly, use the Number Format drop-down on the Home tab.

How do I change a cell from Text to Number?
Set the format to Number, then check whether the entry is actually numeric. If it remains text, use Excel’s Convert to Number option when available, or use Data > Text to Columns for a suitable column. Changing the format alone does not convert every text value.

Why does Excel show a number instead of a date?
A recognized Excel date is stored as a serial number. If the cell is set to General, that serial may be visible; apply a date format to display it as a calendar date. If the entry is text, formatting it as a date may not be enough.

How can I show leading zeros in Excel?
For a numeric value that only needs a particular display, apply a custom format such as 00000 to show five digits. For identifiers that must remain text, set the cells to Text before entering or importing them; a display format does not change the underlying number.

Does changing a cell format change the value?
Usually it changes only the display. Percentage, date, and decimal formats can make the same stored value look very different. To alter the value itself, use an appropriate calculation or conversion rather than relying on formatting.

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.