Occasionally, numbers might be formatted and stored in cells as text, which later can cause problems with calculations or produce confusing sort orders. For example, you might have typed a number in a cell that was formatted as text, or the data may have been imported or copied as text from an external data source.
Numbers that are formatted as text are left-aligned instead of right-aligned in the cell.
If numbers are entered in cells that are formatted as text, you can use error checking to convert the numbers to text. If numbers are imported as text or if they are formatted as text after they were entered in cells, you cannot use error checking to convert the text to numbers. However, you can apply a number format instead.
You can also quickly convert numbers that are formatted as text in multiple nonadjacent cells or ranges to numbers.
Click the cell, or press the arrow keys to move to the cell.
A range of cells
Click the first cell in the range, and then drag to the last cell, or hold down SHIFT while you press the arrow keys to extend the selection.
You can also select the first cell in the range, and then press F8 to extend the selection by using the arrow keys. To stop extending the selection, press F8 again.
A large range of cells
Click the first cell in the range, and then hold down SHIFT while you click the last cell in the range. You can scroll to make the last cell visible.
All cells on a worksheet
Click the Select All button.
To select the entire worksheet, you can also press CTRL+A.
Note If the worksheet contains data, CTRL+A selects the current region. Pressing CTRL+A a second time selects the entire worksheet.
Nonadjacent cells or cell ranges
Select the first cell or range of cells, and then hold down CTRL while you select the other cells or ranges.
You can also select the first cell or range of cells, and then press SHIFT+F8 to add another nonadjacent cell or range to the selection. To stop adding cells or ranges to the selection, press SHIFT+F8 again.
Note You cannot cancel the selection of a cell or range of cells in a nonadjacent selection without canceling the entire selection.
An entire row or column
Click the row or column heading.
Row heading
Column heading
You can also select cells in a row or column by selecting the first cell and then pressing CTRL+SHIFT+ARROW key (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN ARROW for columns).
Note If the row or column contains data, CTRL+SHIFT+ARROW key selects the row or column to the last used cell. Pressing CTRL+SHIFT+ARROW key a second time selects the entire row or column.
Adjacent rows or columns
Drag across the row or column headings. Or select the first row or column; then hold down SHIFT while you select the last row or column.
Nonadjacent rows or columns
Click the column or row heading of the first row or column in your selection; then hold down CTRL while you click the column or row headings of other rows or columns that you want to add to the selection.
The first or last cell in a row or column
Select a cell in the row or column, and then press CTRL+ARROW key (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN ARROW for columns).
The first or last cell on a worksheet or in a Microsoft Office Excel table
Press CTRL+HOME to select the first cell on the worksheet or in an Excel list.
Press CTRL+END to select the last cell on the worksheet or in an Excel list that contains data or formatting.
Cells to the last used cell on the worksheet (lower-right corner)
Select the first cell, and then press CTRL+SHIFT+END to extend the selection of cells to the last used cell on the worksheet (lower-right corner).
Cells to the beginning of the worksheet
Select the first cell, and then press CTRL+SHIFT+HOME to extend the selection of cells to the beginning of the worksheet.
Click the cell, or press the arrow keys to move to the cell.
A range of cells
Click the first cell in the range, and then drag to the last cell, or hold down SHIFT while you press the arrow keys to extend the selection.
You can also select the first cell in the range, and then press F8 to extend the selection by using the arrow keys. To stop extending the selection, press F8 again.
A large range of cells
Click the first cell in the range, and then hold down SHIFT while you click the last cell in the range. You can scroll to make the last cell visible.
All cells on a worksheet
Click the Select All button.
To select the entire worksheet, you can also press CTRL+A.
Note If the worksheet contains data, CTRL+A selects the current region. Pressing CTRL+A a second time selects the entire worksheet.
Nonadjacent cells or cell ranges
Select the first cell or range of cells, and then hold down CTRL while you select the other cells or ranges.
You can also select the first cell or range of cells, and then press SHIFT+F8 to add another nonadjacent cell or range to the selection. To stop adding cells or ranges to the selection, press SHIFT+F8 again.
Note You cannot cancel the selection of a cell or range of cells in a nonadjacent selection without canceling the entire selection.
An entire row or column
Click the row or column heading.
Row heading
Column heading
You can also select cells in a row or column by selecting the first cell and then pressing CTRL+SHIFT+ARROW key (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN ARROW for columns).
Note If the row or column contains data, CTRL+SHIFT+ARROW key selects the row or column to the last used cell. Pressing CTRL+SHIFT+ARROW key a second time selects the entire row or column.
Adjacent rows or columns
Drag across the row or column headings. Or select the first row or column; then hold down SHIFT while you select the last row or column.
Nonadjacent rows or columns
Click the column or row heading of the first row or column in your selection; then hold down CTRL while you click the column or row headings of other rows or columns that you want to add to the selection.
The first or last cell in a row or column
Select a cell in the row or column, and then press CTRL+ARROW key (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN ARROW for columns).
The first or last cell on a worksheet or in a Microsoft Office Excel table
Press CTRL+HOME to select the first cell on the worksheet or in an Excel list.
Press CTRL+END to select the last cell on the worksheet or in an Excel list that contains data or formatting.
Cells to the last used cell on the worksheet (lower-right corner)
Select the first cell, and then press CTRL+SHIFT+END to extend the selection of cells to the last used cell on the worksheet (lower-right corner).
Cells to the beginning of the worksheet
Select the first cell, and then press CTRL+SHIFT+HOME to extend the selection of cells to the beginning of the worksheet.
Click the cell, or press the arrow keys to move to the cell.
A range of cells
Click the first cell in the range, and then drag to the last cell, or hold down SHIFT while you press the arrow keys to extend the selection.
You can also select the first cell in the range, and then press F8 to extend the selection by using the arrow keys. To stop extending the selection, press F8 again.
A large range of cells
Click the first cell in the range, and then hold down SHIFT while you click the last cell in the range. You can scroll to make the last cell visible.
All cells on a worksheet
Click the Select All button.
To select the entire worksheet, you can also press CTRL+A.
Note If the worksheet contains data, CTRL+A selects the current region. Pressing CTRL+A a second time selects the entire worksheet.
Nonadjacent cells or cell ranges
Select the first cell or range of cells, and then hold down CTRL while you select the other cells or ranges.
You can also select the first cell or range of cells, and then press SHIFT+F8 to add another nonadjacent cell or range to the selection. To stop adding cells or ranges to the selection, press SHIFT+F8 again.
Note You cannot cancel the selection of a cell or range of cells in a nonadjacent selection without canceling the entire selection.
An entire row or column
Click the row or column heading.
Row heading
Column heading
You can also select cells in a row or column by selecting the first cell and then pressing CTRL+SHIFT+ARROW key (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN ARROW for columns).
Note If the row or column contains data, CTRL+SHIFT+ARROW key selects the row or column to the last used cell. Pressing CTRL+SHIFT+ARROW key a second time selects the entire row or column.
Adjacent rows or columns
Drag across the row or column headings. Or select the first row or column; then hold down SHIFT while you select the last row or column.
Nonadjacent rows or columns
Click the column or row heading of the first row or column in your selection; then hold down CTRL while you click the column or row headings of other rows or columns that you want to add to the selection.
The first or last cell in a row or column
Select a cell in the row or column, and then press CTRL+ARROW key (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN ARROW for columns).
The first or last cell on a worksheet or in a Microsoft Office Excel table
Press CTRL+HOME to select the first cell on the worksheet or in an Excel list.
Press CTRL+END to select the last cell on the worksheet or in an Excel list that contains data or formatting.
Cells to the last used cell on the worksheet (lower-right corner)
Select the first cell, and then press CTRL+SHIFT+END to extend the selection of cells to the last used cell on the worksheet (lower-right corner).
Cells to the beginning of the worksheet
Select the first cell, and then press CTRL+SHIFT+HOME to extend the selection of cells to the beginning of the worksheet.
On the Home tab, in the Clipboard group, click the arrow below Paste, and then click Paste Special.
Under Operation, select Multiply, and then click OK.
To delete the content of the cell that you typed in step 2 after all numbers have been converted successfully, select that cell, and then press DELETE.
Note Some accounting programs display negative values as text, with the negative sign (–) to the right of the value. To convert the text string to a value, you must use a formula to return all the characters of the text string except the rightmost character (the negation sign), and then multiply the result by –1.
For example, if the value in cell A2 is "156–" the following formula converts the text to the value –156.
Click the cell, or press the arrow keys to move to the cell.
A range of cells
Click the first cell in the range, and then drag to the last cell, or hold down SHIFT while you press the arrow keys to extend the selection.
You can also select the first cell in the range, and then press F8 to extend the selection by using the arrow keys. To stop extending the selection, press F8 again.
A large range of cells
Click the first cell in the range, and then hold down SHIFT while you click the last cell in the range. You can scroll to make the last cell visible.
All cells on a worksheet
Click the Select All button.
To select the entire worksheet, you can also press CTRL+A.
Note If the worksheet contains data, CTRL+A selects the current region. Pressing CTRL+A a second time selects the entire worksheet.
Nonadjacent cells or cell ranges
Select the first cell or range of cells, and then hold down CTRL while you select the other cells or ranges.
You can also select the first cell or range of cells, and then press SHIFT+F8 to add another nonadjacent cell or range to the selection. To stop adding cells or ranges to the selection, press SHIFT+F8 again.
Note You cannot cancel the selection of a cell or range of cells in a nonadjacent selection without canceling the entire selection.
An entire row or column
Click the row or column heading.
Row heading
Column heading
You can also select cells in a row or column by selecting the first cell and then pressing CTRL+SHIFT+ARROW key (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN ARROW for columns).
Note If the row or column contains data, CTRL+SHIFT+ARROW key selects the row or column to the last used cell. Pressing CTRL+SHIFT+ARROW key a second time selects the entire row or column.
Adjacent rows or columns
Drag across the row or column headings. Or select the first row or column; then hold down SHIFT while you select the last row or column.
Nonadjacent rows or columns
Click the column or row heading of the first row or column in your selection; then hold down CTRL while you click the column or row headings of other rows or columns that you want to add to the selection.
The first or last cell in a row or column
Select a cell in the row or column, and then press CTRL+ARROW key (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN ARROW for columns).
The first or last cell on a worksheet or in a Microsoft Office Excel table
Press CTRL+HOME to select the first cell on the worksheet or in an Excel list.
Press CTRL+END to select the last cell on the worksheet or in an Excel list that contains data or formatting.
Cells to the last used cell on the worksheet (lower-right corner)
Select the first cell, and then press CTRL+SHIFT+END to extend the selection of cells to the last used cell on the worksheet (lower-right corner).
Cells to the beginning of the worksheet
Select the first cell, and then press CTRL+SHIFT+HOME to extend the selection of cells to the beginning of the worksheet.
Click the cell, or press the arrow keys to move to the cell.
A range of cells
Click the first cell in the range, and then drag to the last cell, or hold down SHIFT while you press the arrow keys to extend the selection.
You can also select the first cell in the range, and then press F8 to extend the selection by using the arrow keys. To stop extending the selection, press F8 again.
A large range of cells
Click the first cell in the range, and then hold down SHIFT while you click the last cell in the range. You can scroll to make the last cell visible.
All cells on a worksheet
Click the Select All button.
To select the entire worksheet, you can also press CTRL+A.
Note If the worksheet contains data, CTRL+A selects the current region. Pressing CTRL+A a second time selects the entire worksheet.
Nonadjacent cells or cell ranges
Select the first cell or range of cells, and then hold down CTRL while you select the other cells or ranges.
You can also select the first cell or range of cells, and then press SHIFT+F8 to add another nonadjacent cell or range to the selection. To stop adding cells or ranges to the selection, press SHIFT+F8 again.
Note You cannot cancel the selection of a cell or range of cells in a nonadjacent selection without canceling the entire selection.
An entire row or column
Click the row or column heading.
Row heading
Column heading
You can also select cells in a row or column by selecting the first cell and then pressing CTRL+SHIFT+ARROW key (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN ARROW for columns).
Note If the row or column contains data, CTRL+SHIFT+ARROW key selects the row or column to the last used cell. Pressing CTRL+SHIFT+ARROW key a second time selects the entire row or column.
Adjacent rows or columns
Drag across the row or column headings. Or select the first row or column; then hold down SHIFT while you select the last row or column.
Nonadjacent rows or columns
Click the column or row heading of the first row or column in your selection; then hold down CTRL while you click the column or row headings of other rows or columns that you want to add to the selection.
The first or last cell in a row or column
Select a cell in the row or column, and then press CTRL+ARROW key (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN ARROW for columns).
The first or last cell on a worksheet or in a Microsoft Office Excel table
Press CTRL+HOME to select the first cell on the worksheet or in an Excel list.
Press CTRL+END to select the last cell on the worksheet or in an Excel list that contains data or formatting.
Cells to the last used cell on the worksheet (lower-right corner)
Select the first cell, and then press CTRL+SHIFT+END to extend the selection of cells to the last used cell on the worksheet (lower-right corner).
Cells to the beginning of the worksheet
Select the first cell, and then press CTRL+SHIFT+HOME to extend the selection of cells to the beginning of the worksheet.
The General format is the default number format that Excel applies when you type a number. For the most part, numbers that are formatted with the General format are displayed just the way that you type them. However, if the cell is not wide enough to show the entire number, the General format rounds numbers that have decimals. The General number format also uses scientific (exponential) notation for large numbers (12 or more digits).
Click the cell, or press the arrow keys to move to the cell.
A range of cells
Click the first cell in the range, and then drag to the last cell, or hold down SHIFT while you press the arrow keys to extend the selection.
You can also select the first cell in the range, and then press F8 to extend the selection by using the arrow keys. To stop extending the selection, press F8 again.
A large range of cells
Click the first cell in the range, and then hold down SHIFT while you click the last cell in the range. You can scroll to make the last cell visible.
All cells on a worksheet
Click the Select All button.
To select the entire worksheet, you can also press CTRL+A.
Note If the worksheet contains data, CTRL+A selects the current region. Pressing CTRL+A a second time selects the entire worksheet.
Nonadjacent cells or cell ranges
Select the first cell or range of cells, and then hold down CTRL while you select the other cells or ranges.
You can also select the first cell or range of cells, and then press SHIFT+F8 to add another nonadjacent cell or range to the selection. To stop adding cells or ranges to the selection, press SHIFT+F8 again.
Note You cannot cancel the selection of a cell or range of cells in a nonadjacent selection without canceling the entire selection.
An entire row or column
Click the row or column heading.
Row heading
Column heading
You can also select cells in a row or column by selecting the first cell and then pressing CTRL+SHIFT+ARROW key (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN ARROW for columns).
Note If the row or column contains data, CTRL+SHIFT+ARROW key selects the row or column to the last used cell. Pressing CTRL+SHIFT+ARROW key a second time selects the entire row or column.
Adjacent rows or columns
Drag across the row or column headings. Or select the first row or column; then hold down SHIFT while you select the last row or column.
Nonadjacent rows or columns
Click the column or row heading of the first row or column in your selection; then hold down CTRL while you click the column or row headings of other rows or columns that you want to add to the selection.
The first or last cell in a row or column
Select a cell in the row or column, and then press CTRL+ARROW key (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN ARROW for columns).
The first or last cell on a worksheet or in a Microsoft Office Excel table
Press CTRL+HOME to select the first cell on the worksheet or in an Excel list.
Press CTRL+END to select the last cell on the worksheet or in an Excel list that contains data or formatting.
Cells to the last used cell on the worksheet (lower-right corner)
Select the first cell, and then press CTRL+SHIFT+END to extend the selection of cells to the last used cell on the worksheet (lower-right corner).
Cells to the beginning of the worksheet
Select the first cell, and then press CTRL+SHIFT+HOME to extend the selection of cells to the beginning of the worksheet.
Microsoft Office Excel provides many built-in number formats, but if these do not meet your needs, you can customize a built-in number format to create your own. To learn more about how to change number format codes, you may want to review the guidelines for customizing a number format before you get started.
You cannot delete a built-in number format, but you can delete a custom number format when you no longer need it.
To create a custom number format, you start by selecting 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.
A number format can have up to four sections of code, separated by semicolons. These code sections define the format for positive numbers, negative numbers, zero values, and text, in that order.
<POSITIVE>;<NEGATIVE>;<ZERO>;<TEXT>
For example, you can use these code sections to create the following custom format:
[Blue]#,##0.00_);[Red](#,##0.00);0.00;"sales "@
You do not have to include all code sections in your custom number format. If you specify only two code sections for your custom number format, the first section is used for positive numbers and zeros, and the second section is used for negative numbers. If you specify only one code section, it is used for all numbers. If you want to skip a code section and include a code section that follows it, you must include the ending semicolon for the section that you skip.
The following guidelines should be helpful for customizing any of these number format code sections.
Guidelines for including text and adding spacing
Display both text and numbers To display both text and numbers in a cell, enclose the text characters in double quotation marks (" ") or precede a single character with a backslash (\). Include the characters in the appropriate section of the format codes. For example, type the format $0.00" Surplus";$-0.00" Shortage" to display a positive amount as "$125.74 Surplus" and a negative amount as "$-125.74 Shortage." Note that there is one space character before both "Surplus" and "Shortage" in each code section.
The following characters are displayed without the use of quotation marks.
$
Dollar sign
+
Plus sign
(
Left parenthesis
:
Colon
^
Circumflex accent (caret)
'
Apostrophe
{
Left curly bracket
<
Less-than sign
=
Equal sign
-
Minus sign
/
Slash mark
)
Right parenthesis
!
Exclamation point
&
Ampersand
~
Tilde
}
Right curly bracket
>
Greater-than sign
Space character
Include a section for text entry If included, a text section is always the last section in the number format. Include an "at" character (@) in the section where you want to display any text that you type in the cell. If the @ character is omitted from the text section, text that you type will not be displayed. If you want to always display specific text characters with the typed text, enclose the additional text in double quotation marks (" "). For example, "gross receipts for "@
If the format does not include a text section, any nonnumeric value that you type in a cell with that format applied is not affected by the format. In addition, the entire cell is converted to text.
Add spaces To create a space that is the width of a character in a number format, include an underscore character (_), followed by the character that you want to use. For example, when you follow an underscore with a right parenthesis, such as _), positive numbers line up correctly with negative numbers that are enclosed in parentheses.
Repeat characters To repeat the next character in the format to fill the column width, include an asterisk (*) in the number format. For example, type 0*- to include enough dashes after a number to fill the cell, or type *0 before any format to include leading zeros.
Guidelines for using decimal places, spaces, colors, and conditions
Include decimal places and significant digits To format fractions or numbers that contain decimal points, include the following digit placeholders, decimal points, and thousand separators in a section.
0 (zero)
This digit placeholder displays insignificant zeros if a number has fewer digits than there are zeros in the format. For example, if you type 8.9, and you want it to be displayed as 8.90, use the format #.00.
#
This digit placeholder follows the same rules as the 0 (zero). However, Excel does not display extra zeros when the number that you type has fewer digits on either side of the decimal than there are # symbols in the format. For example, if the custom format is #.##, and you type 8.9 in the cell, the number 8.9 is displayed.
?
This digit placeholder follows the same rules as the 0 (zero). However, Excel adds a space for insignificant zeros on either side of the decimal point so that decimal points are aligned in the column. For example, the custom format 0.0? aligns the decimal points for the numbers 8.9 and 88.99 in a column.
. (period)
This digit placeholder displays the decimal point in a number.
If a number has more digits to the right of the decimal point than there are placeholders in the format, the number rounds to as many decimal places as there are placeholders. If there are more digits to the left of the decimal point than there are placeholders, the extra digits are displayed. If the format contains only number signs (#) to the left of the decimal point, numbers less than 1 begin with a decimal point; for example, .47.
To display
As
Use this code
1234.59
1234.6
####.#
8.9
8.900
#.000
.631
0.6
0.#
12 1234.568
12.0 1234.57
#.0#
44.398 102.65 2.8
44.398 102.65 2.8 (with aligned decimals)
???.???
5.25 5.3
5 1/4 5 3/10 (with aligned fractions)
# ???/???
Display a thousands separator To display a comma as a thousands separator or to scale a number by a multiple of 1,000, include the following separator in the number format.
, (comma)
Displays the thousands separator in a number. Excel separates thousands by commas if the format contains a comma that is enclosed by number signs (#) or by zeros. A comma that follows a digit placeholder scales the number by 1,000. For example, if the format is #.0,, and you type 12,200,000 in the cell, the number 12.200.0 is displayed.
To display
As
Use this code
12000
12,000
#,###
12000
12
#,
12200000
12.2
0.0,,
Specify colors To specify the color for a section of the format, type the name of one of the following eight colors enclosed in square brackets in the section. The color code must be the first item in the section.
Guidelines for currency, percentages, and scientific notation format
Include currency symbols To type one of the following currency symbols in a number format, press NUM LOCK and use the numeric keypad to type the ANSI code for the symbol.
To enter
Press this code
¢
ALT+0162
£
ALT+0163
¥
ALT+0165
ALT+0128
Note Custom formats are saved with the workbook. To have Excel always use a specific currency symbol, you must change the currency symbol that is selected in the Regional Options in Control Panel before you start Excel.
Display percentages To display numbers as a percentage of 100 — for example, to display .08 as 8% or 2.8 as 280% — include the percent sign (%) in the number format.
Display scientific notations To display numbers in scientific (exponential) format, use the following exponent codes in a section.
E (E-, E+, e-, e+)
Displays a number in scientific (exponential) format. Excel displays a number to the right of the "E" or "e" that corresponds to the number of places that the decimal point was moved. For example, if the format is 0.00E+00, and you type 12,200,000 in the cell, the number 1.22E+07 is displayed. If you change the number format to #0.0E+0, the number 12.2E+6 is displayed.
Guidelines for date and time formats
Display days, months, and years To display numbers as date formats (such as days, months, and years), use the following codes in a section.
m
Displays the month as a number without a leading zero.
mm
Displays the month as a number with a leading zero when appropriate.
mmm
Displays the month as an abbreviation (Jan to Dec).
mmmm
Displays the month as a full name (January to December).
mmmmm
Displays the month as a single letter (J to D).
d
Displays the day as a number without a leading zero.
dd
Displays the day as a number with a leading zero when appropriate.
ddd
Displays the day as an abbreviation (Sun to Sat).
dddd
Displays the day as a full name (Sunday to Saturday).
yy
Displays the year as a two-digit number.
yyyy
Displays the year as a four-digit number.
To display
As
Use this code
Months
1–12
m
Months
01–12
mm
Months
Jan–Dec
mmm
Months
January–December
mmmm
Months
J–D
mmmmm
Days
1–31
d
Days
01–31
dd
Days
Sun–Sat
ddd
Days
Sunday–Saturday
dddd
Years
00–99
yy
Years
1900–9999
yyyy
Display hours, minutes, and seconds To display time formats (such as hours, minutes, and seconds), use the following codes in a section.
h
Displays the hour as a number without a leading zero.
[h]
Displays elapsed time in hours. If you are working with a formula that returns a time in which the number of hours exceeds 24, use a number format that resembles [h]:mm:ss.
hh
Displays the hour as a number with a leading zero when appropriate. If the format contains AM or PM, the hour is based on the 12-hour clock. Otherwise, the hour is based on the 24-hour clock.
m
Displays the minute as a number without a leading zero.
Note The m or mm code must appear immediately after the h or hh code or immediately before the ss code; otherwise, Excel displays the month instead of minutes.
[m]
Displays elapsed time in minutes. If you are working with a formula that returns a time in which the number of minutes exceeds 60, use a number format that resembles [mm]:ss.
mm
Displays the minute as a number with a leading zero when appropriate.
Note The m or mm code must appear immediately after the h or hh code or immediately before the ss code; otherwise, Excel displays the month instead of minutes.
s
Displays the second as a number without a leading zero.
[s]
Displays elapsed time in seconds. If you are working with a formula that returns a time in which the number of seconds exceeds 60, use a number format that resembles [ss].
ss
Displays the second as a number with a leading zero when appropriate. If you want to display fractions of a second, use a number format that resembles h:mm:ss.00.
AM/PM, am/pm, A/P, a/p
Displays the hour using a 12-hour clock. Excel displays AM, am, A, or a for times from midnight until noon and PM, pm, P, or p for times from noon until midnight.
Open the workbook in which you want to create and store a custom number format.
On the Home tab, click the Dialog Box Launchernext to Number.
In the Category box, click Custom.
In the Type list, select the number format that you want to customize.
The number format that you select appears in the Type box above the Type list.
Note When you select a built-in number format in the Type list, Excel creates a copy of that number format that you can then customize. The original number format in the Type list cannot be changed or deleted.
In the Type box, make the necessary changes to the selected number format.
Note A custom number format is stored in the workbook in which it was created and will not be available in any other workbooks. To use a custom format in a new workbook, you can save the current workbook as an Excel template that you can use as the basis for the new workbook.
Occasionally, dates may become formatted and stored in cells as text. For example, you may have entered a date in a cell that was formatted as text, or the data might have been imported or pasted from an external data source as text.
Dates that are formatted as text are left-aligned instead of right-aligned in a cell. With Error Checking turned on, text dates with two-digit years might also be marked with an error indicator .
Because Error Checking in Microsoft Office Excel can identify text-formatted dates with two-digit years, you can use the automatic correction options to convert them to date-formatted dates. You can use the DATEVALUE function to convert most other types of text dates to dates.
Click the cell, or press the arrow keys to move to the cell.
A range of cells
Click the first cell in the range, and then drag to the last cell, or hold down SHIFT while you press the arrow keys to extend the selection.
You can also select the first cell in the range, and then press F8 to extend the selection by using the arrow keys. To stop extending the selection, press F8 again.
A large range of cells
Click the first cell in the range, and then hold down SHIFT while you click the last cell in the range. You can scroll to make the last cell visible.
All cells on a worksheet
Click the Select All button.
To select the entire worksheet, you can also press CTRL+A.
Note If the worksheet contains data, CTRL+A selects the current region. Pressing CTRL+A a second time selects the entire worksheet.
Nonadjacent cells or cell ranges
Select the first cell or range of cells, and then hold down CTRL while you select the other cells or ranges.
You can also select the first cell or range of cells, and then press SHIFT+F8 to add another nonadjacent cell or range to the selection. To stop adding cells or ranges to the selection, press SHIFT+F8 again.
Note You cannot cancel the selection of a cell or range of cells in a nonadjacent selection without canceling the entire selection.
An entire row or column
Click the row or column heading.
Row heading
Column heading
You can also select cells in a row or column by selecting the first cell and then pressing CTRL+SHIFT+ARROW key (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN ARROW for columns).
Note If the row or column contains data, CTRL+SHIFT+ARROW key selects the row or column to the last used cell. Pressing CTRL+SHIFT+ARROW key a second time selects the entire row or column.
Adjacent rows or columns
Drag across the row or column headings. Or select the first row or column; then hold down SHIFT while you select the last row or column.
Nonadjacent rows or columns
Click the column or row heading of the first row or column in your selection; then hold down CTRL while you click the column or row headings of other rows or columns that you want to add to the selection.
The first or last cell in a row or column
Select a cell in the row or column, and then press CTRL+ARROW key (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN ARROW for columns).
The first or last cell on a worksheet or in a Microsoft Office Excel table
Press CTRL+HOME to select the first cell on the worksheet or in an Excel list.
Press CTRL+END to select the last cell on the worksheet or in an Excel list that contains data or formatting.
Cells to the last used cell on the worksheet (lower-right corner)
Select the first cell, and then press CTRL+SHIFT+END to extend the selection of cells to the last used cell on the worksheet (lower-right corner).
Cells to the beginning of the worksheet
Select the first cell, and then press CTRL+SHIFT+HOME to extend the selection of cells to the beginning of the worksheet.
Convert text dates by using the DATEVALUE function
To convert a text date in a cell to a serial number, you use the DATEVALUE function. Then you copy the formula, select the cells that contain the text dates, and use Paste Special to apply a date format to them.
Select a blank cell and verify that its number format is General.
To copy the conversion formula into a range of contiguous cells, select the cell in which you typed the formula, and then drag the fill handle across a range of empty cells that matches in size the range of cells that contain text dates.
After you drag the fill handle, you should have a range of cells with serial numbers that corresponds to the range of cells that contain text dates.
Select the cell or range of cells that contains the serial numbers, and then on the Home tab, in the Clipboard group, click Copy.
Keyboard shortcut You can also press CTRL+C.
Select the cell or range of cells that contains the text dates, and then on the Home tab, in the Clipboard group, click the arrow below Paste, and then click Paste Special.
In the Paste Special dialog box, under Paste, select Values, and then click OK.
On the Home tab, in the Number group, click the arrow next to the Number Format box, and then click Short Date or Long Date.
Tip For other date formats, click More. In the Format Cells dialog box, on the Number tab, click Date in the Category box, and then click the date format that you want in the Type box.
To delete the serial numbers after all of the dates are converted successfully, select the cells that contain them, and then press DELETE.
Change the character used to separate thousands or decimals
By default, Microsoft Excel uses the system separators that are defined in the regional settings in Control Panel. If you sometimes need to display numbers with different separators for thousands or decimals, you can temporarily replace the system separators with custom separators.
Click the Microsoft Office Button, and then click Excel Options.
On the Advanced tab, under Editing options, clear the Use system separators check box.
Type new separators in the Decimal separator and Thousands separator boxes.
Tip When you want to use the system separators again, select the Use system separators check box.
Note For a permanent solution, you must change the regional settings in Control Panel.
Click the cell, or press the arrow keys to move to the cell.
A range of cells
Click the first cell in the range, and then drag to the last cell, or hold down SHIFT while you press the arrow keys to extend the selection.
You can also select the first cell in the range, and then press F8 to extend the selection by using the arrow keys. To stop extending the selection, press F8 again.
A large range of cells
Click the first cell in the range, and then hold down SHIFT while you click the last cell in the range. You can scroll to make the last cell visible.
All cells on a worksheet
Click the Select All button.
To select the entire worksheet, you can also press CTRL+A.
Note If the worksheet contains data, CTRL+A selects the current region. Pressing CTRL+A a second time selects the entire worksheet.
Nonadjacent cells or cell ranges
Select the first cell or range of cells, and then hold down CTRL while you select the other cells or ranges.
You can also select the first cell or range of cells, and then press SHIFT+F8 to add another nonadjacent cell or range to the selection. To stop adding cells or ranges to the selection, press SHIFT+F8 again.
Note You cannot cancel the selection of a cell or range of cells in a nonadjacent selection without canceling the entire selection.
An entire row or column
Click the row or column heading.
Row heading
Column heading
You can also select cells in a row or column by selecting the first cell and then pressing CTRL+SHIFT+ARROW key (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN ARROW for columns).
Note If the row or column contains data, CTRL+SHIFT+ARROW key selects the row or column to the last used cell. Pressing CTRL+SHIFT+ARROW key a second time selects the entire row or column.
Adjacent rows or columns
Drag across the row or column headings. Or select the first row or column; then hold down SHIFT while you select the last row or column.
Nonadjacent rows or columns
Click the column or row heading of the first row or column in your selection; then hold down CTRL while you click the column or row headings of other rows or columns that you want to add to the selection.
The first or last cell in a row or column
Select a cell in the row or column, and then press CTRL+ARROW key (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN ARROW for columns).
The first or last cell on a worksheet or in a Microsoft Office Excel table
Press CTRL+HOME to select the first cell on the worksheet or in an Excel list.
Press CTRL+END to select the last cell on the worksheet or in an Excel list that contains data or formatting.
Cells to the last used cell on the worksheet (lower-right corner)
Select the first cell, and then press CTRL+SHIFT+END to extend the selection of cells to the last used cell on the worksheet (lower-right corner).
Cells to the beginning of the worksheet
Select the first cell, and then press CTRL+SHIFT+HOME to extend the selection of cells to the beginning of the worksheet.
Click the cell, or press the arrow keys to move to the cell.
A range of cells
Click the first cell in the range, and then drag to the last cell, or hold down SHIFT while you press the arrow keys to extend the selection.
You can also select the first cell in the range, and then press F8 to extend the selection by using the arrow keys. To stop extending the selection, press F8 again.
A large range of cells
Click the first cell in the range, and then hold down SHIFT while you click the last cell in the range. You can scroll to make the last cell visible.
All cells on a worksheet
Click the Select All button.
To select the entire worksheet, you can also press CTRL+A.
Note If the worksheet contains data, CTRL+A selects the current region. Pressing CTRL+A a second time selects the entire worksheet.
Nonadjacent cells or cell ranges
Select the first cell or range of cells, and then hold down CTRL while you select the other cells or ranges.
You can also select the first cell or range of cells, and then press SHIFT+F8 to add another nonadjacent cell or range to the selection. To stop adding cells or ranges to the selection, press SHIFT+F8 again.
Note You cannot cancel the selection of a cell or range of cells in a nonadjacent selection without canceling the entire selection.
An entire row or column
Click the row or column heading.
Row heading
Column heading
You can also select cells in a row or column by selecting the first cell and then pressing CTRL+SHIFT+ARROW key (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN ARROW for columns).
Note If the row or column contains data, CTRL+SHIFT+ARROW key selects the row or column to the last used cell. Pressing CTRL+SHIFT+ARROW key a second time selects the entire row or column.
Adjacent rows or columns
Drag across the row or column headings. Or select the first row or column; then hold down SHIFT while you select the last row or column.
Nonadjacent rows or columns
Click the column or row heading of the first row or column in your selection; then hold down CTRL while you click the column or row headings of other rows or columns that you want to add to the selection.
The first or last cell in a row or column
Select a cell in the row or column, and then press CTRL+ARROW key (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN ARROW for columns).
The first or last cell on a worksheet or in a Microsoft Office Excel table
Press CTRL+HOME to select the first cell on the worksheet or in an Excel list.
Press CTRL+END to select the last cell on the worksheet or in an Excel list that contains data or formatting.
Cells to the last used cell on the worksheet (lower-right corner)
Select the first cell, and then press CTRL+SHIFT+END to extend the selection of cells to the last used cell on the worksheet (lower-right corner).
Cells to the beginning of the worksheet
Select the first cell, and then press CTRL+SHIFT+HOME to extend the selection of cells to the beginning of the worksheet.
Specify the decimal places for a built-in number format
On the Home tab, click the Dialog Box Launchernext to Number.
In the Category box, click Number, Currency, Accounting, Percentage, or Scientific.
In the Decimal places box, enter the number of decimal places that you want to display.
Note You cannot change the default setting of 2 decimal places. To use more or less than 2 decimal places, you need to change the number of decimal places every time you apply a Number, Currency, Accounting, Percentage, or Scientific format. However, you can create a custom number format that displays the number of decimals that you want. For more information, see Create or delete a custom number format.
Click the Microsoft Office Button, and then click Excel Options.
In the Advanced category, under Editing options, select the Automatically insert a decimal point check box.
In the Places box, enter a positive number for digits to the right of the decimal point or a negative number for digits to the left of the decimal point.
For example, if you enter 3 in the Places box and then type 2834 in a cell, the value will be 2.834. If you enter -3 in the Places box and then type 283 in a cell, the value will be 283000.
Click OK.
The Fixed decimal indicator appears in the status bar.
On the worksheet, click a cell, and then type the number that you want.
Note The data that you typed before you selected the Fixed decimal check box is not affected.
Tips
To temporarily override the fixed decimal option, type a decimal point when you type the number.
To remove decimal points from numbers that you already entered with fixed decimals, do the following:
Click the Microsoft Office Button, and then click Excel Options.
In the Advanced category, under Editing options, clear the Automatically insert a decimal point check box.
In an empty cell, type a number such as 10, 100, or 1,000, depending on the number of decimal places that you want to remove.
TipFor example, type 100 in the cell if the numbers contain two decimal places and you want to convert them to whole numbers.
On the Home tab, in the Clipboard group, click Copy.
Keyboard shortcutYou can also press CTRL+C.
On the worksheet, select the cells that contain the numbers with decimal places that you want to change.
On the Home tab, in the Clipboard group, click the arrow below Paste, and then click Paste Special.
In the Paste Special dialog box, under Operation, click Multiply.
Click the cell, or press the arrow keys to move to the cell.
A range of cells
Click the first cell in the range, and then drag to the last cell, or hold down SHIFT while you press the arrow keys to extend the selection.
You can also select the first cell in the range, and then press F8 to extend the selection by using the arrow keys. To stop extending the selection, press F8 again.
A large range of cells
Click the first cell in the range, and then hold down SHIFT while you click the last cell in the range. You can scroll to make the last cell visible.
All cells on a worksheet
Click the Select All button.
To select the entire worksheet, you can also press CTRL+A.
Note If the worksheet contains data, CTRL+A selects the current region. Pressing CTRL+A a second time selects the entire worksheet.
Nonadjacent cells or cell ranges
Select the first cell or range of cells, and then hold down CTRL while you select the other cells or ranges.
You can also select the first cell or range of cells, and then press SHIFT+F8 to add another nonadjacent cell or range to the selection. To stop adding cells or ranges to the selection, press SHIFT+F8 again.
Note You cannot cancel the selection of a cell or range of cells in a nonadjacent selection without canceling the entire selection.
An entire row or column
Click the row or column heading.
Row heading
Column heading
You can also select cells in a row or column by selecting the first cell and then pressing CTRL+SHIFT+ARROW key (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN ARROW for columns).
Note If the row or column contains data, CTRL+SHIFT+ARROW key selects the row or column to the last used cell. Pressing CTRL+SHIFT+ARROW key a second time selects the entire row or column.
Adjacent rows or columns
Drag across the row or column headings. Or select the first row or column; then hold down SHIFT while you select the last row or column.
Nonadjacent rows or columns
Click the column or row heading of the first row or column in your selection; then hold down CTRL while you click the column or row headings of other rows or columns that you want to add to the selection.
The first or last cell in a row or column
Select a cell in the row or column, and then press CTRL+ARROW key (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN ARROW for columns).
The first or last cell on a worksheet or in a Microsoft Office Excel table
Press CTRL+HOME to select the first cell on the worksheet or in an Excel list.
Press CTRL+END to select the last cell on the worksheet or in an Excel list that contains data or formatting.
Cells to the last used cell on the worksheet (lower-right corner)
Select the first cell, and then press CTRL+SHIFT+END to extend the selection of cells to the last used cell on the worksheet (lower-right corner).
Cells to the beginning of the worksheet
Select the first cell, and then press CTRL+SHIFT+HOME to extend the selection of cells to the beginning of the worksheet.
Click the cell, or press the arrow keys to move to the cell.
A range of cells
Click the first cell in the range, and then drag to the last cell, or hold down SHIFT while you press the arrow keys to extend the selection.
You can also select the first cell in the range, and then press F8 to extend the selection by using the arrow keys. To stop extending the selection, press F8 again.
A large range of cells
Click the first cell in the range, and then hold down SHIFT while you click the last cell in the range. You can scroll to make the last cell visible.
All cells on a worksheet
Click the Select All button.
To select the entire worksheet, you can also press CTRL+A.
Note If the worksheet contains data, CTRL+A selects the current region. Pressing CTRL+A a second time selects the entire worksheet.
Nonadjacent cells or cell ranges
Select the first cell or range of cells, and then hold down CTRL while you select the other cells or ranges.
You can also select the first cell or range of cells, and then press SHIFT+F8 to add another nonadjacent cell or range to the selection. To stop adding cells or ranges to the selection, press SHIFT+F8 again.
Note You cannot cancel the selection of a cell or range of cells in a nonadjacent selection without canceling the entire selection.
An entire row or column
Click the row or column heading.
Row heading
Column heading
You can also select cells in a row or column by selecting the first cell and then pressing CTRL+SHIFT+ARROW key (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN ARROW for columns).
Note If the row or column contains data, CTRL+SHIFT+ARROW key selects the row or column to the last used cell. Pressing CTRL+SHIFT+ARROW key a second time selects the entire row or column.
Adjacent rows or columns
Drag across the row or column headings. Or select the first row or column; then hold down SHIFT while you select the last row or column.
Nonadjacent rows or columns
Click the column or row heading of the first row or column in your selection; then hold down CTRL while you click the column or row headings of other rows or columns that you want to add to the selection.
The first or last cell in a row or column
Select a cell in the row or column, and then press CTRL+ARROW key (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN ARROW for columns).
The first or last cell on a worksheet or in a Microsoft Office Excel table
Press CTRL+HOME to select the first cell on the worksheet or in an Excel list.
Press CTRL+END to select the last cell on the worksheet or in an Excel list that contains data or formatting.
Cells to the last used cell on the worksheet (lower-right corner)
Select the first cell, and then press CTRL+SHIFT+END to extend the selection of cells to the last used cell on the worksheet (lower-right corner).
Cells to the beginning of the worksheet
Select the first cell, and then press CTRL+SHIFT+HOME to extend the selection of cells to the beginning of the worksheet.
123 1/2, rounding to the nearest single-digit fraction value
Double-digit fraction
123 26/57, rounding to the nearest double-digit fraction value
Triple-digit fraction
123 57/125, rounding to the nearest triple-digit fraction value
Fraction as halves
123 1/2
Fraction as quarters
123 2/4
Fraction as eighths
123 4/8
Fraction as sixteenths
123 7/16
Fraction as tenths
123 5/10
Fraction as hundredths
123 46/100
Tip The number in the active cell of the selection on the worksheet appears in the Sample box, so that you can preview the number formatting options that you select.
Notes
After you apply a fraction format to a cell, decimal numbers as well as actual fractions that you type in that cell will be displayed as a fraction. For example, typing .5 or 1/2 results in 1/2 when the cell has been formatted with a fraction type of Up to one digit.
If no fraction format is applied to a cell, and you type a fraction such as 1/2, it will be formatted as a date. To display it as a fraction, apply a Fraction format, and then retype the fraction.
If you don't need to perform calculations on fractions, you can format a cell as text before you type a fraction into it by clicking Text in the Category list. This way, the fractions that you type will not be reduced or converted to decimals. However, you cannot perform mathematical calculations on fractions that are displayed as text.
To reset the number format, click General in the Category box (Format Cells dialog box) or in the Number Format box (Home tab, Number group). Cells that are formatted with the General format do not have a specific number format.
Click the cell, or press the arrow keys to move to the cell.
A range of cells
Click the first cell in the range, and then drag to the last cell, or hold down SHIFT while you press the arrow keys to extend the selection.
You can also select the first cell in the range, and then press F8 to extend the selection by using the arrow keys. To stop extending the selection, press F8 again.
A large range of cells
Click the first cell in the range, and then hold down SHIFT while you click the last cell in the range. You can scroll to make the last cell visible.
All cells on a worksheet
Click the Select All button.
To select the entire worksheet, you can also press CTRL+A.
Note If the worksheet contains data, CTRL+A selects the current region. Pressing CTRL+A a second time selects the entire worksheet.
Nonadjacent cells or cell ranges
Select the first cell or range of cells, and then hold down CTRL while you select the other cells or ranges.
You can also select the first cell or range of cells, and then press SHIFT+F8 to add another nonadjacent cell or range to the selection. To stop adding cells or ranges to the selection, press SHIFT+F8 again.
Note You cannot cancel the selection of a cell or range of cells in a nonadjacent selection without canceling the entire selection.
An entire row or column
Click the row or column heading.
Row heading
Column heading
You can also select cells in a row or column by selecting the first cell and then pressing CTRL+SHIFT+ARROW key (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN ARROW for columns).
Note If the row or column contains data, CTRL+SHIFT+ARROW key selects the row or column to the last used cell. Pressing CTRL+SHIFT+ARROW key a second time selects the entire row or column.
Adjacent rows or columns
Drag across the row or column headings. Or select the first row or column; then hold down SHIFT while you select the last row or column.
Nonadjacent rows or columns
Click the column or row heading of the first row or column in your selection; then hold down CTRL while you click the column or row headings of other rows or columns that you want to add to the selection.
The first or last cell in a row or column
Select a cell in the row or column, and then press CTRL+ARROW key (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN ARROW for columns).
The first or last cell on a worksheet or in a Microsoft Office Excel table
Press CTRL+HOME to select the first cell on the worksheet or in an Excel list.
Press CTRL+END to select the last cell on the worksheet or in an Excel list that contains data or formatting.
Cells to the last used cell on the worksheet (lower-right corner)
Select the first cell, and then press CTRL+SHIFT+END to extend the selection of cells to the last used cell on the worksheet (lower-right corner).
Cells to the beginning of the worksheet
Select the first cell, and then press CTRL+SHIFT+HOME to extend the selection of cells to the beginning of the worksheet.
Display only the last few digits of Social Security numbers
For common security measures, you may want to display only the last few digits of a Social Security number and replace the rest of the digits with zeros or other characters. You can do this by using a formula that includes the CONCATENATE, and RIGHT functions.
The following procedure uses example data to show how you can display only the last four numbers of a Social Security number. After you copy the formula to your worksheet, you can adjust it to display your own Social Security numbers in a similar manner.
Create a blank workbook or worksheet.
In this Help article, select the following example data without the row and column headers.
1
2
3
4
A
B
Type
Data
Social Security Number
555-50-1234
Formula
Description (Result)
=CONCATENATE("000-00-", RIGHT(B2,4))
Displays the "000-00-" text string instead of the first 5 digits of the Social Security number and combines it with the last four digits of the Social Security number (000-00-1234)
To switch between viewing the result and viewing the formula that returns the result, on the Formulas tab, in the Formula Auditing group, click Show Formulas.
Keyboard shortcut You can also press CTRL+` (grave accent).
Notes
To prevent other people from viewing the entire Social Security number, you can first hide the column that contains that number (column B in the example data), and then protect the worksheet so that unauthorized users cannot unhide the data.
Important As a best practice, you may want to avoid storing complete Social Security numbers in your workbooks. Instead, store the full Social Security numbers in a location that meets stringent security standards (for example, a database program, such as Microsoft Office Access), and then use only the last four digits of the numbers in your workbooks.
Microsoft Office Excel provides two special number formats for postal codes, but you can also create a custom postal code format that displays the postal code correctly regardless of whether it has five or nine digits. In addition, you can create a custom format that displays the postal code preceded by leading characters to fill a cell's width.
Click the cell, or press the arrow keys to move to the cell.
A range of cells
Click the first cell in the range, and then drag to the last cell, or hold down SHIFT while you press the arrow keys to extend the selection.
You can also select the first cell in the range, and then press F8 to extend the selection by using the arrow keys. To stop extending the selection, press F8 again.
A large range of cells
Click the first cell in the range, and then hold down SHIFT while you click the last cell in the range. You can scroll to make the last cell visible.
All cells on a worksheet
Click the Select All button.
To select the entire worksheet, you can also press CTRL+A.
Note If the worksheet contains data, CTRL+A selects the current region. Pressing CTRL+A a second time selects the entire worksheet.
Nonadjacent cells or cell ranges
Select the first cell or range of cells, and then hold down CTRL while you select the other cells or ranges.
You can also select the first cell or range of cells, and then press SHIFT+F8 to add another nonadjacent cell or range to the selection. To stop adding cells or ranges to the selection, press SHIFT+F8 again.
Note You cannot cancel the selection of a cell or range of cells in a nonadjacent selection without canceling the entire selection.
An entire row or column
Click the row or column heading.
Row heading
Column heading
You can also select cells in a row or column by selecting the first cell and then pressing CTRL+SHIFT+ARROW key (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN ARROW for columns).
Note If the row or column contains data, CTRL+SHIFT+ARROW key selects the row or column to the last used cell. Pressing CTRL+SHIFT+ARROW key a second time selects the entire row or column.
Adjacent rows or columns
Drag across the row or column headings. Or select the first row or column; then hold down SHIFT while you select the last row or column.
Nonadjacent rows or columns
Click the column or row heading of the first row or column in your selection; then hold down CTRL while you click the column or row headings of other rows or columns that you want to add to the selection.
The first or last cell in a row or column
Select a cell in the row or column, and then press CTRL+ARROW key (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN ARROW for columns).
The first or last cell on a worksheet or in a Microsoft Office Excel table
Press CTRL+HOME to select the first cell on the worksheet or in an Excel list.
Press CTRL+END to select the last cell on the worksheet or in an Excel list that contains data or formatting.
Cells to the last used cell on the worksheet (lower-right corner)
Select the first cell, and then press CTRL+SHIFT+END to extend the selection of cells to the last used cell on the worksheet (lower-right corner).
Cells to the beginning of the worksheet
Select the first cell, and then press CTRL+SHIFT+HOME to extend the selection of cells to the beginning of the worksheet.
On the Home tab, click the Dialog Box Launchernext to Number.
In the Category box, click Special.
In the Type list, click Zip Code or Zip Code + 4.
Note These codes are available in the Type list only if the Locale (location) is set to English (United States). Different locales provide different or no special codes in the Type list.
Click the cell, or press the arrow keys to move to the cell.
A range of cells
Click the first cell in the range, and then drag to the last cell, or hold down SHIFT while you press the arrow keys to extend the selection.
You can also select the first cell in the range, and then press F8 to extend the selection by using the arrow keys. To stop extending the selection, press F8 again.
A large range of cells
Click the first cell in the range, and then hold down SHIFT while you click the last cell in the range. You can scroll to make the last cell visible.
All cells on a worksheet
Click the Select All button.
To select the entire worksheet, you can also press CTRL+A.
Note If the worksheet contains data, CTRL+A selects the current region. Pressing CTRL+A a second time selects the entire worksheet.
Nonadjacent cells or cell ranges
Select the first cell or range of cells, and then hold down CTRL while you select the other cells or ranges.
You can also select the first cell or range of cells, and then press SHIFT+F8 to add another nonadjacent cell or range to the selection. To stop adding cells or ranges to the selection, press SHIFT+F8 again.
Note You cannot cancel the selection of a cell or range of cells in a nonadjacent selection without canceling the entire selection.
An entire row or column
Click the row or column heading.
Row heading
Column heading
You can also select cells in a row or column by selecting the first cell and then pressing CTRL+SHIFT+ARROW key (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN ARROW for columns).
Note If the row or column contains data, CTRL+SHIFT+ARROW key selects the row or column to the last used cell. Pressing CTRL+SHIFT+ARROW key a second time selects the entire row or column.
Adjacent rows or columns
Drag across the row or column headings. Or select the first row or column; then hold down SHIFT while you select the last row or column.
Nonadjacent rows or columns
Click the column or row heading of the first row or column in your selection; then hold down CTRL while you click the column or row headings of other rows or columns that you want to add to the selection.
The first or last cell in a row or column
Select a cell in the row or column, and then press CTRL+ARROW key (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN ARROW for columns).
The first or last cell on a worksheet or in a Microsoft Office Excel table
Press CTRL+HOME to select the first cell on the worksheet or in an Excel list.
Press CTRL+END to select the last cell on the worksheet or in an Excel list that contains data or formatting.
Cells to the last used cell on the worksheet (lower-right corner)
Select the first cell, and then press CTRL+SHIFT+END to extend the selection of cells to the last used cell on the worksheet (lower-right corner).
Cells to the beginning of the worksheet
Select the first cell, and then press CTRL+SHIFT+HOME to extend the selection of cells to the beginning of the worksheet.
On the Home tab, click the Dialog Box Launchernext to Number.
In the Category box, click Custom.
In the Type list, select the number format that you want to customize.
The number format that you select appears in the Type box above the Type list.
Note When you select a built-in number format in the Type list, Excel creates a copy of that number format that you can customize. The original number format in the Type list cannot be changed or deleted.
In the Type box, make the necessary changes to the selected number format.
Note If a range of cells contains both five-digit and nine-digit postal codes (ZIP Codes), you can apply a custom format that displays both types of ZIP Codes correctly. In the Type box, type [<=99999]00000;00000-0000
You can format a cell or range of cells to display leading characters so that the postal code is preceded by enough characters to fill the cell's width. For example, you can use zeros or dashes to display a postal code as follows: 0000000 98052 or ------- 98052.
Click the cell, or press the arrow keys to move to the cell.
A range of cells
Click the first cell in the range, and then drag to the last cell, or hold down SHIFT while you press the arrow keys to extend the selection.
You can also select the first cell in the range, and then press F8 to extend the selection by using the arrow keys. To stop extending the selection, press F8 again.
A large range of cells
Click the first cell in the range, and then hold down SHIFT while you click the last cell in the range. You can scroll to make the last cell visible.
All cells on a worksheet
Click the Select All button.
To select the entire worksheet, you can also press CTRL+A.
Note If the worksheet contains data, CTRL+A selects the current region. Pressing CTRL+A a second time selects the entire worksheet.
Nonadjacent cells or cell ranges
Select the first cell or range of cells, and then hold down CTRL while you select the other cells or ranges.
You can also select the first cell or range of cells, and then press SHIFT+F8 to add another nonadjacent cell or range to the selection. To stop adding cells or ranges to the selection, press SHIFT+F8 again.
Note You cannot cancel the selection of a cell or range of cells in a nonadjacent selection without canceling the entire selection.
An entire row or column
Click the row or column heading.
Row heading
Column heading
You can also select cells in a row or column by selecting the first cell and then pressing CTRL+SHIFT+ARROW key (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN ARROW for columns).
Note If the row or column contains data, CTRL+SHIFT+ARROW key selects the row or column to the last used cell. Pressing CTRL+SHIFT+ARROW key a second time selects the entire row or column.
Adjacent rows or columns
Drag across the row or column headings. Or select the first row or column; then hold down SHIFT while you select the last row or column.
Nonadjacent rows or columns
Click the column or row heading of the first row or column in your selection; then hold down CTRL while you click the column or row headings of other rows or columns that you want to add to the selection.
The first or last cell in a row or column
Select a cell in the row or column, and then press CTRL+ARROW key (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN ARROW for columns).
The first or last cell on a worksheet or in a Microsoft Office Excel table
Press CTRL+HOME to select the first cell on the worksheet or in an Excel list.
Press CTRL+END to select the last cell on the worksheet or in an Excel list that contains data or formatting.
Cells to the last used cell on the worksheet (lower-right corner)
Select the first cell, and then press CTRL+SHIFT+END to extend the selection of cells to the last used cell on the worksheet (lower-right corner).
Cells to the beginning of the worksheet
Select the first cell, and then press CTRL+SHIFT+HOME to extend the selection of cells to the beginning of the worksheet.
When you type a number that contains more than 15 digits in a worksheet cell, Microsoft Office Excel changes any digits past the fifteenth place to zeros. In addition, Excel displays the number in exponential notation, replacing part of the number with E+n, where E (which signifies exponent) multiplies the preceding number by 10 to the nth power.
If you create a custom number format for a 16-digit credit card number (such as ################ or ####-####-####-####), Excel still changes the last digit to a zero. To successfully display a 16-digit credit card number in full, you must format the number as text.
For security purposes, you can obscure all except the last few digits of a credit card number by using a formula that includes the CONCATENATE, RIGHT, and REPT functions.
Click the cell, or press the arrow keys to move to the cell.
A range of cells
Click the first cell in the range, and then drag to the last cell, or hold down SHIFT while you press the arrow keys to extend the selection.
You can also select the first cell in the range, and then press F8 to extend the selection by using the arrow keys. To stop extending the selection, press F8 again.
A large range of cells
Click the first cell in the range, and then hold down SHIFT while you click the last cell in the range. You can scroll to make the last cell visible.
All cells on a worksheet
Click the Select All button.
To select the entire worksheet, you can also press CTRL+A.
Note If the worksheet contains data, CTRL+A selects the current region. Pressing CTRL+A a second time selects the entire worksheet.
Nonadjacent cells or cell ranges
Select the first cell or range of cells, and then hold down CTRL while you select the other cells or ranges.
You can also select the first cell or range of cells, and then press SHIFT+F8 to add another nonadjacent cell or range to the selection. To stop adding cells or ranges to the selection, press SHIFT+F8 again.
Note You cannot cancel the selection of a cell or range of cells in a nonadjacent selection without canceling the entire selection.
An entire row or column
Click the row or column heading.
Row heading
Column heading
You can also select cells in a row or column by selecting the first cell and then pressing CTRL+SHIFT+ARROW key (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN ARROW for columns).
Note If the row or column contains data, CTRL+SHIFT+ARROW key selects the row or column to the last used cell. Pressing CTRL+SHIFT+ARROW key a second time selects the entire row or column.
Adjacent rows or columns
Drag across the row or column headings. Or select the first row or column; then hold down SHIFT while you select the last row or column.
Nonadjacent rows or columns
Click the column or row heading of the first row or column in your selection; then hold down CTRL while you click the column or row headings of other rows or columns that you want to add to the selection.
The first or last cell in a row or column
Select a cell in the row or column, and then press CTRL+ARROW key (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN ARROW for columns).
The first or last cell on a worksheet or in a Microsoft Office Excel table
Press CTRL+HOME to select the first cell on the worksheet or in an Excel list.
Press CTRL+END to select the last cell on the worksheet or in an Excel list that contains data or formatting.
Cells to the last used cell on the worksheet (lower-right corner)
Select the first cell, and then press CTRL+SHIFT+END to extend the selection of cells to the last used cell on the worksheet (lower-right corner).
Cells to the beginning of the worksheet
Select the first cell, and then press CTRL+SHIFT+HOME to extend the selection of cells to the beginning of the worksheet.
Display only the last few digits of credit card numbers
For common security measures, you may want to display only the last few digits of a credit card number and replace the rest of the digits with asterisks or other characters. You can do this by using a formula that includes the CONCATENATE, REPT, and RIGHT functions.
The following procedure uses example data to show how you can display only the last four numbers of a credit card number. After you copy the formula to your worksheet, you can adjust it to display your own credit card numbers in a similar manner.
Create a blank workbook or worksheet.
In this Help article, select the following example data without the row and column headers.
1
2
3
4
A
B
Type
Data
Credit Card Number
5555-5555-5555-5555
Formula
Description (Result)
=CONCATENATE(REPT("****-",3), RIGHT(B2,4))
Repeats the "****-" text string three times and combines the result with the last four digits of the credit card number (****-****-****-5555)
To switch between viewing the result and viewing the formula that returns the result, on the Formulas tab, in the Formula Auditing group, click Show Formulas.
Keyboard shortcut You can also press CTRL+` (grave accent).
Notes
To prevent other people from viewing the entire credit card number, you can first hide the column that contains that number (column B in the example data), and then protect the worksheet so that unauthorized users cannot unhide the data. For more information, see Hide or display rows and columns and Protect worksheet or workbook elements.
Click the cell, or press the arrow keys to move to the cell.
A range of cells
Click the first cell in the range, and then drag to the last cell, or hold down SHIFT while you press the arrow keys to extend the selection.
You can also select the first cell in the range, and then press F8 to extend the selection by using the arrow keys. To stop extending the selection, press F8 again.
A large range of cells
Click the first cell in the range, and then hold down SHIFT while you click the last cell in the range. You can scroll to make the last cell visible.
All cells on a worksheet
Click the Select All button.
To select the entire worksheet, you can also press CTRL+A.
Note If the worksheet contains data, CTRL+A selects the current region. Pressing CTRL+A a second time selects the entire worksheet.
Nonadjacent cells or cell ranges
Select the first cell or range of cells, and then hold down CTRL while you select the other cells or ranges.
You can also select the first cell or range of cells, and then press SHIFT+F8 to add another nonadjacent cell or range to the selection. To stop adding cells or ranges to the selection, press SHIFT+F8 again.
Note You cannot cancel the selection of a cell or range of cells in a nonadjacent selection without canceling the entire selection.
An entire row or column
Click the row or column heading.
Row heading
Column heading
You can also select cells in a row or column by selecting the first cell and then pressing CTRL+SHIFT+ARROW key (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN ARROW for columns).
Note If the row or column contains data, CTRL+SHIFT+ARROW key selects the row or column to the last used cell. Pressing CTRL+SHIFT+ARROW key a second time selects the entire row or column.
Adjacent rows or columns
Drag across the row or column headings. Or select the first row or column; then hold down SHIFT while you select the last row or column.
Nonadjacent rows or columns
Click the column or row heading of the first row or column in your selection; then hold down CTRL while you click the column or row headings of other rows or columns that you want to add to the selection.
The first or last cell in a row or column
Select a cell in the row or column, and then press CTRL+ARROW key (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN ARROW for columns).
The first or last cell on a worksheet or in a Microsoft Office Excel table
Press CTRL+HOME to select the first cell on the worksheet or in an Excel list.
Press CTRL+END to select the last cell on the worksheet or in an Excel list that contains data or formatting.
Cells to the last used cell on the worksheet (lower-right corner)
Select the first cell, and then press CTRL+SHIFT+END to extend the selection of cells to the last used cell on the worksheet (lower-right corner).
Cells to the beginning of the worksheet
Select the first cell, and then press CTRL+SHIFT+HOME to extend the selection of cells to the beginning of the worksheet.
On the Home tab, click the Dialog Box Launchernext to Number.
In the Category box, click Currency or Accounting.
In the Symbol box, click the currency symbol that you want.
Note If you want to display a monetary value without a currency symbol, you can click None.
In the Decimal places box, enter the number of decimal places that you want to display.
In the Negative numbers box, select the display style for negative numbers.
Note The Negative numbers box is not available for the Accounting number format.
Tips
The number in the active cell of the selection on the worksheet appears in the Sample box, so that you can preview the number formatting options that you select.
To quickly display a number with the default currency symbol, select the cell or range of cells, and then click Accounting Number Formatin the Number group on the Home tab. To use another currency, click the arrow next to Accounting Number Format, and then click the currency that you want.
To change the default currency symbol for Microsoft Office Excel and other Microsoft Office programs, you can change the default regional currency settings in Control Panel. Note that although the Accounting Number Format button image does not change, the currency symbol that you choose will be applied when you click this button.
To reset the number format, click General in the Category box (Format Cells dialog box) or in the Number Format box (Home tab, Number group). Cells that are formatted with the General format do not have a specific number format.
When a date or time is typed in a cell, it appears in a default date and time format. The default date and time format is based on the regional date and time settings that are specified in Control Panel, and changes when changes are made to those settings. You can display numbers in several other date and time formats, most of which are not affected by Control Panel settings.
Click the cell, or press the arrow keys to move to the cell.
A range of cells
Click the first cell in the range, and then drag to the last cell, or hold down SHIFT while you press the arrow keys to extend the selection.
You can also select the first cell in the range, and then press F8 to extend the selection by using the arrow keys. To stop extending the selection, press F8 again.
A large range of cells
Click the first cell in the range, and then hold down SHIFT while you click the last cell in the range. You can scroll to make the last cell visible.
All cells on a worksheet
Click the Select All button.
To select the entire worksheet, you can also press CTRL+A.
Note If the worksheet contains data, CTRL+A selects the current region. Pressing CTRL+A a second time selects the entire worksheet.
Nonadjacent cells or cell ranges
Select the first cell or range of cells, and then hold down CTRL while you select the other cells or ranges.
You can also select the first cell or range of cells, and then press SHIFT+F8 to add another nonadjacent cell or range to the selection. To stop adding cells or ranges to the selection, press SHIFT+F8 again.
Note You cannot cancel the selection of a cell or range of cells in a nonadjacent selection without canceling the entire selection.
An entire row or column
Click the row or column heading.
Row heading
Column heading
You can also select cells in a row or column by selecting the first cell and then pressing CTRL+SHIFT+ARROW key (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN ARROW for columns).
Note If the row or column contains data, CTRL+SHIFT+ARROW key selects the row or column to the last used cell. Pressing CTRL+SHIFT+ARROW key a second time selects the entire row or column.
Adjacent rows or columns
Drag across the row or column headings. Or select the first row or column; then hold down SHIFT while you select the last row or column.
Nonadjacent rows or columns
Click the column or row heading of the first row or column in your selection; then hold down CTRL while you click the column or row headings of other rows or columns that you want to add to the selection.
The first or last cell in a row or column
Select a cell in the row or column, and then press CTRL+ARROW key (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN ARROW for columns).
The first or last cell on a worksheet or in a Microsoft Office Excel table
Press CTRL+HOME to select the first cell on the worksheet or in an Excel list.
Press CTRL+END to select the last cell on the worksheet or in an Excel list that contains data or formatting.
Cells to the last used cell on the worksheet (lower-right corner)
Select the first cell, and then press CTRL+SHIFT+END to extend the selection of cells to the last used cell on the worksheet (lower-right corner).
Cells to the beginning of the worksheet
Select the first cell, and then press CTRL+SHIFT+HOME to extend the selection of cells to the beginning of the worksheet.
On the Home tab, in the Number group, click the Dialog Box Launchernext to Number.
In the Category list, click Date or Time.
In the Type list, click the date or time format that you want to use.
Note Date and time formats that begin with an asterisk (*) respond to changes in regional date and time settings that are specified in Control Panel. Formats without an asterisk are not affected by Control Panel settings.
TipIf you do not find the format that you are looking for in the Type list, you can create a custom number format by clicking Custom in the Category list, and then by using format codes for dates and times.
To display dates and times in the format of other languages, click the language setting that you want in the Locale (location) box.
Dates or times that you enter in formatted cells will be displayed in the format that you selected.
Tips
The number in the active cell of the selection on the worksheet appears in the Sample box, so that you can preview the number formatting options that you select.
To quickly format a date or time, click the date or time format that you want in the Number Format box in the Number group on the Home tab.
If you want to use the default date or time format, click the cell that contains the date or time, and then press CTRL+SHIFT+# or CTRL+SHIFT+@.
A cell might display ##### when it contains data that has a number format that is wider than the column width. To see all text, you must increase the width of the column.
Click the cell for which you want to change the column width.
On the Home tab, in the Cells group, click Format.
Under Cell Size, do one of the following:
To fit all text in the cell, click AutoFit Column Width.
To specify a larger column width, click Column Width, and then type the width that you want in the Column width box.
When you try to undo a date or time format by selecting General in the Category list, Excel displays a number code. When you enter a date or time again, Excel displays the default date or time format. To enter a specific date or time format, such as January 2005, you can format it as text by selecting Text in the Category list.
Click the cell, or press the arrow keys to move to the cell.
A range of cells
Click the first cell in the range, and then drag to the last cell, or hold down SHIFT while you press the arrow keys to extend the selection.
You can also select the first cell in the range, and then press F8 to extend the selection by using the arrow keys. To stop extending the selection, press F8 again.
A large range of cells
Click the first cell in the range, and then hold down SHIFT while you click the last cell in the range. You can scroll to make the last cell visible.
All cells on a worksheet
Click the Select All button.
To select the entire worksheet, you can also press CTRL+A.
Note If the worksheet contains data, CTRL+A selects the current region. Pressing CTRL+A a second time selects the entire worksheet.
Nonadjacent cells or cell ranges
Select the first cell or range of cells, and then hold down CTRL while you select the other cells or ranges.
You can also select the first cell or range of cells, and then press SHIFT+F8 to add another nonadjacent cell or range to the selection. To stop adding cells or ranges to the selection, press SHIFT+F8 again.
Note You cannot cancel the selection of a cell or range of cells in a nonadjacent selection without canceling the entire selection.
An entire row or column
Click the row or column heading.
Row heading
Column heading
You can also select cells in a row or column by selecting the first cell and then pressing CTRL+SHIFT+ARROW key (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN ARROW for columns).
Note If the row or column contains data, CTRL+SHIFT+ARROW key selects the row or column to the last used cell. Pressing CTRL+SHIFT+ARROW key a second time selects the entire row or column.
Adjacent rows or columns
Drag across the row or column headings. Or select the first row or column; then hold down SHIFT while you select the last row or column.
Nonadjacent rows or columns
Click the column or row heading of the first row or column in your selection; then hold down CTRL while you click the column or row headings of other rows or columns that you want to add to the selection.
The first or last cell in a row or column
Select a cell in the row or column, and then press CTRL+ARROW key (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN ARROW for columns).
The first or last cell on a worksheet or in a Microsoft Office Excel table
Press CTRL+HOME to select the first cell on the worksheet or in an Excel list.
Press CTRL+END to select the last cell on the worksheet or in an Excel list that contains data or formatting.
Cells to the last used cell on the worksheet (lower-right corner)
Select the first cell, and then press CTRL+SHIFT+END to extend the selection of cells to the last used cell on the worksheet (lower-right corner).
Cells to the beginning of the worksheet
Select the first cell, and then press CTRL+SHIFT+HOME to extend the selection of cells to the beginning of the worksheet.
On the Home tab, click the Dialog Box Launchernext to Number.
On the Number tab, in the Category list, click Number.
To display or hide the thousands separator, select or clear the Use 1000 Separator (,) check box.
Tip To quickly display the thousands separator, you can click Comma Stylein the Number group on the Home tab.
Note By default, Microsoft Office Excel displays the system separator for thousands. You can specify a different system separator by changing the regional settings in Control Panel.
You can frequently prevent floating point rounding errors from affecting your work by setting the Precision as displayed option before you apply a number format to your data. This option forces the value of each number in the worksheet to be at the precision that is displayed on the worksheet.
Note Using the Precision as displayed option can have cumulative calculation effects that can make your data increasingly inaccurate over time. Use this option only if you are certain that the displayed precision will maintain the accuracy of your data.
Click the Microsoft Office Button , and then click Excel Options.
Click Advanced, and then under When calculating this workbook, select the Set precision as displayed check box, and then click OK.
Click OK.
In the worksheet, select the cells that you want to format.
On the Home tab, click the Dialog Box Launchernext to Number.
In the Category box, click Number.
In the Decimal places box, enter the number of decimal places that you want to display.
Tip To minimize any effects of floating point arithmetic storage inaccuracy, you can also use the ROUND function to round numbers to the number of decimal places that is required by your calculation.
