Software

How do I... Perform basic formatting in Excel 2003?

Sometimes, we take for granted what users know. While <b>Microsoft Excel</b> may be familiar to you, if you are on a <b>help desk</b>, you will have to explain how the application works to users who fall within a vast range of skill levels and knowledge. This series of Excel tutorials can make that training much simpler. Properly formatting a spreadsheet can bring a project home.

This article was orginally published on September 9, 2005. A downloadable PDF version is also available.

By this point, you've probably learned about the basics in Microsoft Excel, and you've learned how to create some rudimentary formulas. Now, perhaps you've created a useful spreadsheet, complete with formulas that calculate sales of your inventory. Before you submit it to your boss, wouldn't it be nice to make some things stand out? Maybe you'd like to easily turn some values into dollar figures or have Excel automatically highlight an inventory line for which you've run out of stock. In this How do I..., and in future articles, series, you'll learn to do these things, and more.

Before you start

Before formatting your spreadsheet, it probably looks like a bland table of numbers. By the end of the next three articles in this How do I... series, you'll be able to do the following:

Part 1

  • Boldface, italicize and underline cell content
  • Change the size and font of your text
  • Apply a default Excel style (i.e. dollar, percent, etc) to cells
  • Use date and time formatting in your spreadsheet
  • Apply shading

Part 2

  • Apply borders
  • Automatically format cells based on their contents
  • Resize rows and columns
  • Word wrap text
  • Justify cell contents
  • Change the direction of the text in your spreadsheet

Part 3

  • Change the margins for your printed page
  • Add a header and footer to your printer spreadsheet
  • Use all of the features of the Format Cells dialog box

The Formatting toolbar

If you've used other Microsoft programs, you're probably familiar with the use of the Formatting toolbar, shown in Figure A. In Figure B, see examples of what the various buttons on the Formatting toolbar accomplish. To use one of the buttons, select the cell, or range of cells, that you wish to format and then click the desired button on the toolbar. Table A below outlines the Excel default styles that are available from the Formatting toolbar.

Figure A

The Excel Formatting toolbar.

Figure B

An example of the results of some of the buttons on the Formatting toolbar.

Table A

Task

Keyboard Shortcut

Description

Change the font

Ctrl + Shift + F

Click the drop-down arrow next to the font name to see a list of the fonts available for you to use in the selected cell or cell range. The keyboard shortcut just takes you to the font name box. It's easier to use the mouse.

Change the size of the font

Ctrl + Shift + P

Change the size of the text in the selected cell or range. The keyboard shortcut just takes you to the font size box. It's easier to use the mouse.

Boldface text

Ctrl + B

Boldface the text in the selected cell or range.

Italicized text

Ctrl + I

Italicize the text in the selected cell or range.

Underlined text

Ctrl + U

Underline the text in the selected cell or range.

Left-justified text

Line the text up with the left-hand side of the cell.

Centered text

Center the text in the cell.

Right-justified text

Line the text up with the right-hand side of the cell.

Currency

Ctrl + Shift + 4

Automatically right-justifies the number in the cell and adds a dollar sign and two decimal places. Numbers are automatically right-justified when you put them into a cell.

Percentage

Ctrl + Shift + 5

Right-justifies the number in the cell and adds a percent sign to the end of the number.

Comma

Ctrl + Shift + 1

Adds a comma and two decimal places to the number.

Add a decimal place

Adds a decimal place to a number. For example, 10,000.05 becomes 10,000.050.

Remove a decimal place

Removes a decimal place from a number. For example, 10,000.050 becomes 10,000.05.

Common formatting toolbar options

There are more options on the Formatting toolbar, some of which will be discussed later in this article.

Font face, style, and size

If you've ever used a word processor, you probably know all about fonts. When used appropriately, fonts are a really easy way to make your work look good and stand out.

There are two ways to change the font in Excel: (1) use the Formatting toolbar or (2) use the Format Cells dialog box. We'll go over both methods here.

Use the formatting toolbar to change the font

The first two options on the formatting toolbar control the font face and size in your spreadsheet. By selecting a cell or range of cells and then using these formatting toolbar options, you can select a different font and/or make the font bigger or smaller, depending on your needs. (See Figure C)

Figure C

These two options control the font face and size.

To change the font, select the cells upon which you'd like to foist the new font and click the down arrow next to the current font name. A complete list of fonts available on your computer comes up, with each font conveniently displayed in its respective style, as shown in Figure D.

Figure D

Scroll up and down the list to find the font that suits your needs.

To apply a new font, just select it from the list. That typestyle is then instantly applied to your selection. If you don't like it, press the Undo button on the Standard toolbar (it's the button with the rounded arrow pointing to the left) or press Ctrl + Z on your keyboard. Both accomplish the same goal: undoing your last action.

The font size is managed in the same way. Click the down arrow next to the font size and choose the size you want for your text. Unfortunately, the font size box options are all the same size, so you'll need to experiment a bit to find the size that meets your needs.

In addition to the font face and size, you can also change the font color using the Formatting toolbar. At the right-hand side of the toolbar, find the button with the "A" on it. (See Figure E)

Figure E

This button lets you quickly change the color of the font in your spreadsheet.

Use this button by first selecting the cell or cells for whose contents you'd like to change the color and then click the down arrow immediately to the right of the button, as shown in Figure E. The result will be similar to the example shown below in Figure F.

Figure F

Select a color you like. It's immediately applied to your selection.

Likewise, if you want to apply boldface, italics, or underlining to your text, you can use the corresponding buttons on the Formatting toolbar.

It's as easy as that! If you want more control over your formatting options, you can opt to use the Format Cells dialog box to make changes to your text.

Change the font with the Format Cells dialog box

The Format Cells dialog box is your one-stop-shop for text formatting. Accessible from Format | Cells (or right-click your cell selection and choose Format Cells from the shortcut menu), click on the Font tab at the top of the dialog box to make changes to the font in your selection. See Figure G below for an example.

Figure G

From this window, you can completely control the look of your text.

The main drawback to the Format Cells dialog box is that it doesn't list each font in that font's style -- meaning that you need to individually select each font to see what it looks like. As a shortcut, click your mouse on any font in the list and then use your keyboard's up and down arrow keys to move through the list. You'll save your wrist that way!

In addition to the font face and size, you can change the font style -- bold, italics, regular—underline style, color -- and apply some effects, including strikethrough, superscript, and subscript, to your text. The Format Cells dialog box includes a preview window so you can see the final result without having to constantly go back to your spreadsheet.

Take note of the Normal Font check box. Suppose you've made a bunch of changes to your spreadsheet and you want to undo them all and just put things back to the normal style -- Arial font, 10 point, etc. Just select this check box and click OK. Everything in the cell or range of cells you've selected will return to this default style.

Numeric, date, and time formats

You've already learned a little about how to apply basic numeric formatting to your spreadsheet. To Excel, a date and time are also just numbers -- the style you assign to a cell determines its final look and feel. Why is this? Simply put, Excel considers a date just another number. In fact, if you look deep, you'll find that a date, such as August 21, 2005, looks to Excel to be the number 38,595. This date -- August 31, 2005 -- happens to be 38,595 days since January 0, 1900.

No, "January 0" is not a typo--it's Excel's "day zero." Some documentation also indicates that Excel's day zero is December 30, 1899. Excel also treats the year 1900 as a leap year, even though it wasn't. Like "January 0," this is by design. Excel acts this way to maintain compatibility with Lotus 123, which did have this bug. Excel was introduced when Lotus 123 had a huge share of the market and to make it easy for people to switch to Excel, Microsoft maintained the bug.

Since Excel treats dates as numbers, you can easily do math on dates. Try this: The formula "=now()" (those are parentheses) places the current date and time into the cell. For example, entering "=now()" into a cell while I write this article returns the following:

8/23/2005 23:49

Now, if I enter "=now()-1" into a cell, I get the following:

8/22/2005 23:49

Look closely and note that this is just one day earlier.

This might seem like a digression, but the discussion leads us to numeric formatting. Enter a date into a cell, such as "8/31/2005" and then apply the comma numeric format to the cell. See the example below in Figure H.

Figure H

Into both cells, I entered 8/31/2005. For cell A2, I applied the comma numeric format.

To format a cell as a date, you can just enter a date into the cell, such as "8/31/2005" or "August 31, 2005". Excel knows what a date is supposed to look like and automatically changes the format of the cell to a date. However, if for some reason, you need to change a cell or range of cells to date format, do so by going to Format | Cells... (the keyboard shortcut for this is Ctrl + 1). The Format Cells dialog box opens, which provides you with a ton of options for changing the format of your cells. The Format Cells dialog box is shown in Figure I.

Figure I

The Format Cells dialog box provides a way for you to assign any format you want to a cell range.

You probably get the idea about by now about how this kind of formatting works in Excel. Let's move on to another kind of formatting.

Shading

Use of shading can create a spreadsheet that draws your reader to a specific section -- perhaps the section with the most important information, totals, and so on. Consider the sample sales spreadsheet shown in Figure J.

Figure J

In this example, the "total" line is bold, but it still doesn't stand out very well.

You'll notice that the total line in Figure J is boldface, but it still lacks something. Take a look at the same information, but with shading added to make the total line stand out a bit more Figure K.

Figure K

The totals line now stands out from the rest of the sheet.

There are a couple ways you can add shading to your sheet. First, you can use the Fill Color button on the formatting toolbar. This is the quickest way to apply shading to cells in your spreadsheet. Or you can use the Format Cells dialog box, which requires a couple of additional steps but provides many more options.

Shading from the Formatting toolbar

Figure L

The Fill Color button on the formatting toolbar.

To use the Fill Color button, make sure that the cell to which you'd like to apply shading is the active cell in your spreadsheet. Then, click the down arrow next to the picture of the can of pouring paint. This opens up a color selection window from which you can select the color you'd like to apply to your spreadsheet. The color selection window is shown in Figure M. As soon as you choose a color, it's immediately applied to the cell or range of cells you've selected in your worksheet. Keep in mind that just the background color of the cell is changed. You'll learn how to change the text color in the next section.

Figure M

In this figure, the gray color is selected since that is the color currently in use to shade the cell.

Shading from the Format Cells dialog box

The Format Cells dialog box provides you with a place from which you can control just about any formatting aspect of your spreadsheet. You saw the Format Cells dialog box earlier in this tutorial when you were formatting cells for dates and times. To shade cells using Format Cells, select the cell or cells you'd like to shade and go to Format | Cells. Alternatively, you can right-click your cell or selection and choose Format Cells from the shortcut menu. Both options bring you to the same place -- the Format Cells window. Once this window opens, choose the Patterns tab (Figure N), which provides cell shading options.

Figure N

The Patterns tab of the Format Cells dialog box provides more options than the Fill Color button on the Formatting toolbar.

Notice first that there is a color section, plus a pattern selection drop-down box, along with a sample box in the right-hand part of the window. Like you can from the Fill Color button on the Formatting toolbar, you can just select a color and click OK. However, you can also apply a pattern, such as diagonal lines, to your spreadsheet. Even more, you can apply patterns of a specific color that lie on top of whatever color you select as the background color for the cell. See Figure O for an example.

Figure O

Note the three selections in this window that, when combined, form what you see in the Sample box.

In Figure O, the background color for the cell is yellow, and the pattern is a series of red diagonal stripes, which result in what you see in the Sample box. Using the various combinations, you can achieve a practically unlimited palette for your spreadsheet.

Once you've made your selections, click the OK button. Your selections are immediately applied to your spreadsheet.

More formatting

In this part of the Excel How do I... series, you've explored some of the formatting options available to you in Excel. In part two, you'll learn more advanced formatting features. In part three, you'll learn about overall page formatting.

3 comments
kessel
kessel

OI think its carp, ALL I want to know is how to stop EXCEL from adding dates to numbers that have a line format. 5/11 will result in " 5 November" etc. What idiot decided that in MS ???

dr
dr

Hi kessel, Put an apostrophe in the cell. This defeats Excel's built in date formatting "feature". ie enter: ' 5/11 and it will be formatted as text. Cheers, dr http://www.members.shaw.ca/excelVBA