Saturday, November 17, 2018

Quick Start: Format numbers in a worksheet

Quick Start: Format numbers in a worksheet

By applying different number formats, you can display numbers as percentages, dates, currency, and so on. For example, if you're working on your quarterly budget, you can use the Currency number format to show monetary values.

Numbers formatted as currency

Watch the video

How?

Icon image

Select the cells that you want to format.

Selected cells

Icon image

On the Home tab, in the Number group, click the dialog box launcher next to Number (or just press CTRL+1).

Dialog Box Launcher in Number group

Icon image

In the Category list, click the format that you want to use, and then adjust settings, if necessary. For example, if you're using the Currency format, you can select a different currency symbol, show more or fewer decimal places, or change the way negative numbers are displayed.

Format Cells dialog box

For more information about number formats, see Available number formats.

Next steps

  • If Microsoft Excel displays ##### in a cell after you apply number formatting to it, the cell probably isn't wide enough to display the data. To expand the column width, double-click the right boundary of the column that contains the cells with the ##### error. This automatically resizes the column to fit the number. You can also drag the right boundary until the columns are the size that you want.

  • Most of the time, numbers display the way you intend them to, whether you enter them in the worksheet or import them from a database or other external source. However, Excel occasionally applies an unexpected number format, and you may have to make some adjustments as a result. For example, if you type a number that contains a slash mark (/) or hyphen (-), Excel may interpret it as a date and convert it to date format. If you want to type non-calculated values such as 10e5, 1 p, or 1-2, and you do not want Excel to convert the value to a built-in number format, you can apply the Text format to the cells, and then enter the number.

  • If the built-in number format doesn't meet your needs, you can create your own custom number format. Since the code used to create the number formats can be tricky to understand at first, it's best to use one of the built-in number formats as a starting point. You can then change any one of the code sections of that format to create your own custom number format. To see the number format code for a built-in number format, click the Custom category, and look in the Type box. For example, the code [<=9999999]###-####;(###) ###-#### is used to display the phone number (555) 555-1234. For more information, see Create or delete a custom number format.

Top of Page

No comments:

Post a Comment