• Home
  • About
  • Outreach
  • Contact Us
  • County Offices
  • Directory
  • Employment

Formatting Text and Numbers in Excel

Formatting Text

Because it is a program used so frequently for numbers, one of the frequent frustrations with Excel is how to format text. The basic tools are on the toolbar - the font box (where the font title is displayed - e.g. Times New Roman), the font size box (where the font size is displayed - e.g. 12), the bold (B), italics (I), and underline (U) buttons, and the left, center, and right align boxes. Most of you are probably familiar with the use of these buttons - just select the cell your text is in, click the appropriate button, and the text will take on the attributes you've chosen. This article will show you more exacting methods of formatting text.

The good news: most of the tools you need for formatting text are all in the same place on the menu. First, select the cell or cells you want to format (to select more than one cell, left-click in the center of a cell and drag across until all cells are selected). Go to the Format menu, choose Cells, then click the Alignment tab. You have a number of options in this tab. The first options are the Horizontal and Vertical text alignment dropdown boxes. Click the small ? button at the right of the box to pull down your options. In the Horizontal box, you have options to align left (the default), right, center, and, if you've selected more than one cell, to center the text across the selected cells. In the Vertical box, you have options to align at the bottom (the default), the top, or the center.

In the upper right you have the Text Orientation box. This feature allows you to rotate the angle of your text. It is particularly useful in formatting columns with long column headings, allowing you to squeeze more text in than if the text were horizontal. Simply type the angle you want in the Degree box, or click and drag the line in the Orientation box to the angle you want. Be aware that some fonts don't allow you to rotate them.

Perhaps the most useful tools in the Alignment tab are the three checkboxes at the bottom: Wrap text, Shrink to fit, and Merge cells. Wrap text will automatically wrap your text within the cell, and expand the row size of the cell as you add text. It will not change the column width. Shrink to fit will shrink the size of your text to fit the cell size, and is an excellent option if your text almost fits the column width, but not quite. Merge cells will merge all the selected cells together, and allow your text to show across those cells.

A fast and easy way to merge and center your column headings is to use the Merge and Center button on the toolbar. It looks like an "a" with arrows on either side. Type in your text, select the cells you want to merge, click the button, and your text will be centered within the merged cells.

Formatting numbers

The main thing to remember while formatting numbers in Excel is that you don't type in any symbols (e.g. - $, %). If you do, Excel may interpret the number in that cell as text and not as a value. Instead, type the number in the cell and then format the cell for currency, dates, percentages, or whatever format you wish. The most common numeric formats are on your toolbar: currency ($), percentage (%), and comma (, - used to place a comma every three digits). Simply select the cell or cells, then click the appropriate button to format the cell.

To the right of these buttons are the Increase Decimal and Decrease Decimal buttons. These allow you to choose the number of decimals you wish to display. Select the cell, then click the button to increase or decrease the number of decimals. Be aware that even though Excel will round your number up or down, it uses the original value to make any computations, so you will not lose accuracy if you decrease the number of decimals.

For more formatting options, go to the Format menu, select Cells, then select the Number tab. Categories (accounting, date, time) appear on the left, types on the right. Some categories (number, currency, and accounting) also allow you to choose the number of decimals as well Select the cell or cells you wish to format, select the category and type, then click OK, and your cells will be formatted.

Fonts, borders, colors, and patterns can also be manipulated by selecting Cells in the Format menu, and going to the appropriate tab.

If you have any questions about this process, or want me to walk you through it, please don't hesitate to call me at 719-549-2045, or email me at jeffrey.wood@colostate.edu. More information about Excel (and many other programs) is available on my website at www.coopext.colostate.edu/comptrain/.

CSU Homepage | Disclaimer | Equal Opportunity | Privacy Policy | Search CSU | Webmaster | Site Map

©2007 Colorado State University Extension