Microsoft Excel: Basic Worksheet Formatting

Users often want to customize the look of Excel spreadsheets. There are many basic formatting options available at the cell and worksheet level. Most are available on the Format Menu located on the Standard Excel toolbar.

Format Cells

Choose Format on the toolbar and select Cells to open the Format Cells dialog box. This box has six tabs, each for a different type of formatting. (You can also access this dialog box by rightclicking in any cell and selecting Format Cells from the pop-up menu.)

The Number tab offers shortcuts so that Excel will automatically format data as you type. When you select one of the 12 categories on the left, a sample appears on the right. The Special category allows zero to be the first digit in a cell, which is useful for entering social security numbers or ZIP codes. Some other key selections include Currency, which adds a dollar sign and allows you to display decimal points, and Date, which allows you to enter dates in simple numerical form and format them in various ways.

On the Alignment tab, you can align text horizontally and vertically within a cell, change the orientation of text within a cell, and change the text direction. If you have more data than will fit in a cell, you have some neat options in the Text control section. Check the box in front of Wrap Text to continue your text onto the next line within a cell and then increase the height of the cell to view all the text. Check Shrink To Fit to decrease the size of your text until it fits into its current cell. Or select several contiguous cells and then check Merge Cells to create one larger cell that shows more text. Merge cells is also an easy way to center a header across a spreadsheet with multiple columns.

The Font tab offers options familiar from Microsoft Word, including Font, Font Style, Size, Underline, Color, and Effects. Similar to Microsoft Word, there is a Preview screen to view your choices before you click OK to apply the formatting.

The Border tab offers multiple ways to add borders to cells. (There is also a toolbar button with multiple border options.) First, select the line style and color in the line section. You can click the preset buttons at the top (None, Outline, Inside) or the more detailed buttons around the preview diagram to make borders appear on the diagram. You can also click directly on the preview diagram to make lines appear. Click on lines again to delete.

The Patterns tab allows you to choose background color and patterns for each cell. Click your color choice on the color palette and choose your pattern from the drop-down menu.

In order to lock a cell so that its data cannot be changed, check the box in front of Locked on the Protection tab. In order to hide a formula so it is not visible on a spreadsheet, check Hidden. (These features are only available on protected worksheets.)

Format Rows

Select Row on the Format menu and the formatting choices will appear. The Height option allows you to choose a numeric value for the currently selected row height. If you have entered data in a cell that is not high enough to view all the data, select that cell and choose AutoFit from the menu. The row which contains the selected cell will automatically resize to view all the data within. (Shortcut: Hold your cursor to the far left side of the screen over the bottom border of a row. When it turns to a line with arrows pointing up and down, double click the mouse, and the row will automatically resize to show all data.)

To hide a row, select any cell in the appropriate row. Then select Hide from the menu to conceal the entire row. Data will not be lost. To make a hidden row reappear, select the two cells surrounding it. Select Unhide from the Row menu, and the row will reappear.

Format Columns

Select Column on the Format menu, and the formatting choices will appear. The choices are similar to those available for formatting rows. The Width option allows you to specify a numeric width for selected cells. The AutoFit is the same for rows and columns. (The shortcut is also the same: Double-click to the right of the column you want to resize.) The Hide and Unhide features are also the same for rows and columns. Standard Width changes the default cell width. Highlight one or more sheets and choose Standard Width on the Column menu. Type a measurement in the pop-up dialog box. For estimation purposes, this number corresponds to the number of digits that will fit in a cell. (This applies if you are using the default text font for your worksheet.)

Format Sheets

Of course, if you select all cells on a worksheet before you make a formatting change, you will change the entire sheet. But there are two places in your workbook that are specifically designed to apply formatting at the worksheet level.

Select Sheet from the Format menu;there, you can select Rename to rename your sheet (names appear on the tabs at bottom left) and select Hide or Unhide (corresponding tab will be concealed).

Select Background to open the Sheet Background dialog box. From there, you can insert a graphic file as background for your sheet. Selecting Tab Color opens the Format Tab Color dialog box, so you can choose a tab color from the color palette. Worksheet tabs. Sheet formatting changes can also be made through your sheet tabs. Right click any tab, and a menu will appear. Some of the options (Rename and Tab Color) are also available on the Sheet menu and have already been described. The remaining options will help you further format and manipulate your worksheets.

Select Insert to open the Insert dialog box. From there, you can insert available templates into your worksheet. Select Delete to delete the current worksheet. Choose Select All Sheets to highlight all the sheets in a workbook. This can be useful if you’re making a sweeping formatting change, like the standard cell width.

Select Move or Copy to open the Move Or Copy dialog box. You can move your sheet within the current workbook, to any open workbook, or to a new workbook. (If you want to move a sheet within the same workbook, you can also simply click and drag that tab to change its position.) To copy the sheet, check the Create A Copy box. The Copy feature can be convenient if you are making numerous changes to a worksheet. With your copied sheet in the same workbook, you can simply look back at the o d tab or recopy it if you decide against the changes you’ve made. Copying a worksheet is also an easy way to create several tabs with the same formatting in a single workbook (for example, monthly calendars).

These basic formatting options can save you time at the keyboard and make your spreadsheets look professional.

_____________________
by Liz Stefaniak