In part cases, number in a worksheet space actually formatted and also stored in cells together text, i beg your pardon can reason problems with calculations or develop confusing sort orders. This problem sometimes wake up after you import or copy data from a database or other exterior data source.

You are watching: You cannot format an entire column at once.

Numbers that room formatted as text are left-aligned rather of right-aligned in the cell, and also are often significant with one error indicator.

What perform you desire to do?

Technique 1: convert text-formatted number by using Error Checking

If you income data into Excel from one more source, or if you type numbers right into cells that were formerly formatted together text, you may see a tiny green triangle in the upper-left corner of the cell. This error indicator tells you that the number is stored as text, as displayed in this example.

*

If this isn"t what friend want, you can follow these steps to convert the number the is stored together text earlier to a continuous number.

On the worksheet, select any solitary cell or selection of cells that has an error indicator in the upper-left corner.

How to select cells, ranges, rows, or columns

To select

Do this

A solitary cell

Click the cell, or push the arrow keys to relocate to the cell.

A selection of cells

Click the first cell in the range, and also then drag to the critical cell, or organize down change while you press the arrowhead keys to prolong the selection.

You can likewise select the first cell in the range, and then press F8 to expand the an option by using the arrow keys. Come stop expanding the selection, push F8 again.

A big range the cells

Click the an initial cell in the range, and then organize down transition while you click the last cell in the range. You deserve to scroll to do the critical cell visible.

All cell on a worksheet

Click the Select All button.

To choose the whole worksheet, you can additionally press Ctrl+A.

If the worksheet contains data, Ctrl+A selects the present region. Pushing Ctrl+A a 2nd time selects the whole worksheet.

Nonadjacent cell or cabinet ranges

Select the an initial cell or selection of cells, and also then hold down Ctrl if you select the various other cells or ranges.

You can also select the very first cell or range of cells, and then press Shift+F8 to add another nonadjacent cabinet or range to the selection. To stop adding cells or varieties to the selection, push Shift+F8 again.

You can not cancel the selection of a cabinet or variety of cells in a nonadjacent an option without canceling the whole selection.

An whole row or column

Click the row or pillar heading.

1. Row heading

2. Pillar heading

You can likewise select cell in a heat or obelisk by choosing the an initial cell and also then pushing Ctrl+Shift+Arrow an essential (Right arrow or Left arrowhead for rows, Up arrowhead or Down arrowhead for columns).

If the heat or column includes data, Ctrl+Shift+Arrow crucial selects the row or tower to the last provided cell. Pushing Ctrl+Shift+Arrow vital a second time selects the entire row or column.

Adjacent rows or columns

Drag across the row or shaft headings. Or pick the first row or column; then host down transition while you select the last heat or column.

Nonadjacent rows or columns

Click the column or heat heading the the very first row or column in her selection; then host down Ctrl while girlfriend click the obelisk or heat headings of various other rows or columns that you want to add to the selection.

The an initial or last cell in a heat or column

Select a cell in the heat or column, and then press Ctrl+Arrow an essential (Right arrow or Left arrow for rows, Up arrow or Down arrow for columns).

The first or last cell top top a worksheet or in a jajalger2018.org Office Excel table

Press Ctrl+Home to select the very first cell ~ above the worksheet or in an Excel list.

Press Ctrl+End to pick the critical cell on the worksheet or in an Excel list that has data or formatting.

Cells come the last supplied cell ~ above the worksheet (lower-right corner)

Select the first cell, and also then push Ctrl+Shift+End to prolong the choice of cells to the last provided cell top top the worksheet (lower-right corner).

Cells come the beginning of the worksheet

Select the very first cell, and then push Ctrl+Shift+Home to expand the selection of cells to the beginning of the worksheet.

More or fewer cells than the active selection

Hold down transition while you click the last cell the you want to include in the brand-new selection. The rectangular variety between the active cell and the cell that you click i do not care the new selection.

To publication a selection of cells, click any type of cell on the worksheet.

Next to the selected cell or variety of cells, click the error button that appears.

*

peak of web page

Technique 2: transform text-formatted number by using Paste Special

In this technique, friend multiply every selected cell by 1 in bespeak to force the conversion native a text-formatted number come a continual number. Since you"re multiplying the components of the cabinet by 1, the result in the cabinet looks identical. However, Excel actually replaces the text-based components of the cell with a numerical equivalent.

Select a empty cell and also verify the its number format is General.

How to verify the number format

On the Home tab, in the Number group, click the arrow next to the Number Format box, and also then click General.

*

In the cell, type 1, and also then press ENTER.

Select the cell, and also then push Ctrl+C come copy the worth to the Clipboard.

Select the cells or varieties of cells the contain the numbers stored as text that you want to convert.

How to select cells, ranges, rows, or columns

To select

Do this

A solitary cell

Click the cell, or push the arrowhead keys to move to the cell.

A variety of cells

Click the very first cell in the range, and also then traction to the last cell, or host down transition while you push the arrow keys to extend the selection.

You can likewise select the an initial cell in the range, and also then press F8 to expand the an option by making use of the arrowhead keys. To stop prolonging the selection, push F8 again.

A big range of cells

Click the very first cell in the range, and also then hold down shift while girlfriend click the critical cell in the range. You deserve to scroll to do the critical cell visible.

All cell on a worksheet

Click the Select All button.

To pick the whole worksheet, friend can also press Ctrl+A.

If the worksheet has data, Ctrl+A selects the current region. Pushing Ctrl+A a second time selects the entire worksheet.

Nonadjacent cell or cell ranges

Select the very first cell or range of cells, and also then organize down Ctrl if you pick the other cells or ranges.

You can likewise select the first cell or selection of cells, and then push Shift+F8 to add another nonadjacent cell or variety to the selection. Come stop including cells or ranges to the selection, press Shift+F8 again.

You can not cancel the selection of a cell or variety of cell in a nonadjacent an option without canceling the entire selection.

An whole row or column

Click the heat or tower heading.

1. Heat heading

2. Column heading

You can likewise select cells in a heat or pillar by picking the first cell and then pushing Ctrl+Shift+Arrow key (Right arrow or Left arrowhead for rows, Up arrow or Down arrow for columns).

If the row or column consists of data, Ctrl+Shift+Arrow key selects the row or tower to the last used cell. Pressing Ctrl+Shift+Arrow an essential a second time selects the entire row or column.

Adjacent rows or columns

Drag throughout the heat or column headings. Or choose the an initial row or column; then hold down change while you select the last row or column.

Nonadjacent rows or columns

Click the obelisk or heat heading that the an initial row or tower in your selection; then organize down Ctrl while girlfriend click the shaft or heat headings of various other rows or columns that you desire to include to the selection.

The first or critical cell in a heat or column

Select a cabinet in the row or column, and also then press Ctrl+Arrow key (Right arrow or Left arrow for rows, Up arrowhead or Down arrowhead for columns).

The an initial or last cell on a worksheet or in a jajalger2018.org Office Excel table

Press Ctrl+Home to choose the very first cell top top the worksheet or in one Excel list.

Press Ctrl+End to pick the last cell on the worksheet or in one Excel perform that consists of data or formatting.

Cells come the last used cell ~ above the worksheet (lower-right corner)

Select the an initial cell, and then press Ctrl+Shift+End to prolong the selection of cells to the last used cell ~ above the worksheet (lower-right corner).

Cells come the beginning of the worksheet

Select the first cell, and also then press Ctrl+Shift+Home to prolong the selection of cell to the beginning of the worksheet.

More or under cells than the energetic selection

Hold down shift while girlfriend click the critical cell that you want to encompass in the brand-new selection. The rectangular range between the energetic cell and the cell that you click becomes the new selection.

To cancel a an option of cells, click any type of cell ~ above the worksheet.

On the Home tab, in the Clipboard group, click the arrowhead below Paste, and then click Paste Special.

Under Operation, pick Multiply, and then click OK.

To delete the content of the cell the you typed in step 2 after all numbers have actually been converted successfully, choose that cell, and also then press DELETE.

Some accountancy programs display an adverse values together text, v the an unfavorable sign () come the best of the value. To convert the message string come a value, you need to use a formula to return every the personalities of the text string except the rightmost character (the negation sign), and then multiply the result by –1.

For example, if the worth in cabinet A2 is "156–" the following formula counter the message to the value –156.

Data

Formula

156-

=Left(A2,LEN(A2)-1)*-1

top of web page

Technique 3: use a number layout to text-formatted numbers

In some scenarios, girlfriend don"t have actually to transform numbers stored as text earlier to numbers, as described earlier in this article. Instead, you can just use a number style to attain the same result. Because that example, if you enter numbers in a workbook, and then style those numbers together text, girlfriend won"t view a environment-friendly error indicator appear in the upper-left edge of the cell. In this case, girlfriend can use number formatting.

Select the cells the contain the number that are stored as text.

How to pick cells, ranges, rows, or columns

To select

Do this

A solitary cell

Click the cell, or press the arrow keys to relocate to the cell.

A range of cells

Click the first cell in the range, and also then drag to the last cell, or hold down shift while you push the arrowhead keys to extend the selection.

You can also select the first cell in the range, and also then press F8 to expand the an option by utilizing the arrowhead keys. To stop prolonging the selection, push F8 again.

A huge range of cells

Click the very first cell in the range, and then hold down shift while you click the critical cell in the range. You deserve to scroll to make the critical cell visible.

All cells on a worksheet

Click the Select All button.

To choose the entire worksheet, friend can likewise press Ctrl+A.

If the worksheet contains data, Ctrl+A selects the current region. Pushing Ctrl+A a second time selects the entire worksheet.

Nonadjacent cell or cabinet ranges

Select the first cell or range of cells, and also then hold down Ctrl when you choose the other cells or ranges.

You can also select the an initial cell or selection of cells, and also then push Shift+F8 to add another nonadjacent cell or selection to the selection. To stop including cells or arrays to the selection, push Shift+F8 again.

You can not cancel the choice of a cabinet or variety of cells in a nonadjacent choice without canceling the entire selection.

An entire row or column

Click the row or obelisk heading.

1. Row heading

2. Obelisk heading

You can also select cells in a row or shaft by choosing the an initial cell and also then pushing Ctrl+Shift+Arrow an essential (Right arrowhead or Left arrowhead for rows, Up arrow or Down arrow for columns).

If the heat or column consists of data, Ctrl+Shift+Arrow crucial selects the heat or shaft to the last provided cell. Pressing Ctrl+Shift+Arrow crucial a second time selects the whole row or column.

Adjacent rows or columns

Drag throughout the row or obelisk headings. Or choose the first row or column; then hold down shift while you choose the last heat or column.

Nonadjacent rows or columns

Click the obelisk or heat heading of the very first row or column in your selection; then host down Ctrl while girlfriend click the pillar or heat headings of other rows or columns the you desire to add to the selection.

The an initial or last cell in a heat or column

Select a cabinet in the row or column, and then press Ctrl+Arrow an essential (Right arrow or Left arrowhead for rows, Up arrow or Down arrow for columns).

The very first or critical cell on a worksheet or in a jajalger2018.org Office Excel table

Press Ctrl+Home to select the first cell on the worksheet or in an Excel list.

Press Ctrl+End to pick the critical cell on the worksheet or in one Excel perform that contains data or formatting.

Cells to the last offered cell on the worksheet (lower-right corner)

Select the first cell, and then press Ctrl+Shift+End to expand the an option of cells to the last used cell on the worksheet (lower-right corner).

Cells to the start of the worksheet

Select the an initial cell, and also then push Ctrl+Shift+Home to expand the choice of cells to the start of the worksheet.

More or fewer cells 보다 the active selection

Hold down transition while you click the critical cell the you desire to incorporate in the brand-new selection. The rectangular variety between the active cell and also the cell that you click becomes the new selection.

To release a selection of cells, click any type of cell on the worksheet.

On the Home tab, in the Number group, click the Dialog box Launcher beside Number.

*

optimal of page

Turn off Error Checking

With error check turned on in Excel, you view a little green triangle if you get in a number into a cabinet that has actually text formatting applied to it. If friend don’t want to watch these error indicators, you can turn castle off.

Click the File tab.

Under Help, click Options.

In the Excel options dialog box, click the Formulas category.

See more: Cylinder Layout 2000 Toyota Tundra 4.7 Firing Order, Solved: 2000 4

Under Error checking rules, clean the Numbers formatted as message or preceded by one apostrophe inspect box.