How Change Date Format & Number Format in Excel

Applying number formatting—such as currency, accounting, or percentage—changes how values are displayed. Excel is often smart enough to apply number formatting automatically, but you can also add it manually.

Format a Number or Date

  1. Select the cell(s) that contain values you want to format.
  2. Click the Number Format list arrow from the Home tab.
  3. Select the number or date format you want to apply.
    Format a Number or Date
Number Format Description
General General format cells have no specific number format applied.
Number Used to display general numbers that aren’t monetary values.
Currency Used to display general monetary values. Aligns the decimal points in a column.
Accounting Lines up the currency symbols and decimal points in a column.
Date & Time Display date and time serial numbers as date values. Formats that begin with an asterisk (*) respond to changes in the date/time settings specified for the operating system.
Percentage Multiplies the cell value by 100 and displays the result with a percent symbol.
Fraction Displays numbers as actual fractions instead of decimal values.
Scientific Displays a number in exponential notation (e.g. 2.31E+8).

Advanced Number Formats

If the standard number formats don’t meet your needs, there are additional format options to select from.

  1. Select the cell(s) that contain values you want to format.
  2. Click the Number Format list arrow from the Home tab.
  3. Select More Number Formats.
    Advanced Number Formats 01

    Right-click the cell(s) to be formatted and select Format Cells from the contextual menu.

    A dialog box appears with number formatting options.

  4. Select a Category.
  5. Specify the formatting options.
  6. Click OK.
    Advanced Number Formats

Create a Custom Number Format

You can create your own custom number format if you have a specific format you are going to use frequently.

  1. Select the cell(s) that contain the value you want to format.
  2. Click the Number Format list arrow from the Home tab.
  3. Select More Number Formats.
    Create a Custom Number Format
  4. Select Custom.
  5. Select an existing format as a starting point.
  6. Edit the number code in the Type field to meet your needs.

    As you modify the number code in the Type field, the sample above it updates so you can ensure the number format looks just right.

  7. Click OK.
    Create a Custom Number Format

The cells are updated to the new custom number format, and the format is saved for future use.

Excel Cheat Sheet

FREE Quick Reference

Click to Download

Free to distribute with our compliments; we hope you will consider our paid training.