Number formats are used to control the display of cell values that contain numeric data. This numeric data can include things like dates, times, costs, percentages, and anything else expressed as a number. The most important thing to understand about number formats is that they only affect how a number looks—they have no effect on the actual value stored by Excel. Let’s take a look. The most common number formats are available on the home tab of the ribbon, in the Number format area. Here you’ll find formats for currency, numbers, date, time, and more. To apply a number format, just select one or more cells and choose a format. Number formats only affect numbers; they have no effect on text. Let’s format the rest of the values in our table. After we have applied the formats, notice that Excel will display the name of the format when a cell is selected. When you have multiple cells selected, you’ll see the format of the active cell displayed. Be aware that the active cell in a selection can vary, depending on how you select cells. Most number formats have one or more options. To set number format options, and to see all number formats, visit the Format Cells dialog box. You can access this dialog by clicking the small arrow in the Number group on the ribbon or using the keyboard shortcut Ctrl-1. On the Number tab, you’ll find all the options available for each number format. Let’s use the Format Cells dialog box to adjust the Fraction format. To get our fraction to display correctly, let’s format it to use hundredths. Finally, remember that number formats only affect the display of a cell value, not the value itself. If we check the cells in our table, watching the formula bar, we see that the values are unchanged. The value for Percentage looks different, but if we switch the format back to General, we see that the value being stored is, indeed, .05.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
