Lesson 3: Enhancing a Simple Worksheet

by Teri Odegard, Business Information Technology Department, Edmonds Community College

Changing the Widths of Columns and Heights of Rows

As you modify a worksheet, you might find it necessary to insert or delete rows and columns to keep your worksheet current. For example, you might need to insert rows to accommodate new inventory products or remove a column of yearly totals that are no longer current.

 

Inserting or deleting rows or columns that are specifically referenced in formulas can cause problems. Be sure to check formulas after inserting or deleting rows or columns.

 

You can delete a row, column, or cell by pressing Ctrl and selecting the item you want to delete from the dialog box. If you click the row or column header first, it deletes without a dialog box.

 

If a row is deleted accidentally, it can be returned using the Undo feature.

 

Insert a row, column, or cell by pressing Ctrl + Shift + and selecting the item you want to insert. If you click the row or column header first, it inserts without a dialog box.

 

To be sure you place the new column or row in the correct location; select a cell in the exact location you want the new column or row.

 

        Default column width is 8.43 characters

        Default row height is 12.75 points. The row height can vary between zero and 409 points

        Best Fit means that the width of the column will be increased or decreased so the widest entry will fit in the column

        Hiding columns is used to hide sensitive data on the screen that you do not want others to see when printing, hidden columns do not print

 

By default, Excel provides three worksheets; however, a workbook is not limited to this number. You are limited only by the capacity of your computer.

 

Rename sheets to make it easier to recognize their content, especially when using three or more worksheets.

 

You can also right-click the sheet tab and choose Rename.

 

Q & A Which cell must be selected to insert a new column?

Answer: Any cell in that column.

 

What happens when data does not fit in the width of a cell? What might you do to correct this problem?

 

If the data in a cell is longer than the cell width, the data appears as long as the cell to the right is empty. If there is data in the cell to the right, the column displays pound signs (or cut off the text) and needs to be widened.

Using the Undo and Redo Commands

If you find an error but you have made many actions since then, it may be easier to correct it manually because the Undo feature works in order.

 

You can undo the Merge and Center feature by choosing Format, Cells, and selecting the Alignment tab. Then uncheck the Merge cells box.

Moving Data to a New Location

Using the Cut, Copy, and Paste buttons or the Excel drag-and-drop feature, you can copy or move information from one cell or range in your worksheet to another. You can also cut, copy, and paste data from one worksheet to another to make corrections and add information using the Office Clipboard, which can store up to 12 items.

 

The Office Clipboard lets you copy and paste multiple items such as text, images, tables, or Excel ranges within or between the Microsoft Office applications. The Office Clipboard can hold up to 12 items copied or cut from any Office program. The clipboard toolbar displays the items stored on the Office Clipboard. You choose whether to delete the first item from the Clipboard when you copy the thirteenth item. The collected items remain in the Office Clipboard and are available to you until you close all open Office applications.

Formatting Numbers: The Basics

Formatting determines how labels and values appear in cells; it does not alter the data in any way. To format a cell, first select it, then apply the formatting. Cells and ranges can be formatted before or after data is entered. If you enter a value in a cell and the cell appears to display the data incorrectly, adjust the cells format to display the value correctly.

 

Number formats affect how numbers look onscreen and when printed. They do not affect the way Excel stores or uses the values in calculations. The default number format setting in a worksheet is General. In most cases when you enter numbers, the numbers appear just as you enter them, unformatted.

 

Unformatted numbers are displayed without a thousands separator such as a comma, with negative values preceded by a (minus sign), and with as many decimal place settings as cell space allows.

        Currency style appends a dollar sign to the left of the number, inserts a comma every three positions to the left of the decimal point, and displays numbers to the nearest cent.

        A fixed dollar sign displays to the far left in the cell, often with spaces between it and the first digit.

        A floating dollar sign displays immediately to the left of the number. You need to use the Cells command on the Format menu or the Format Cells command on the shortcut menu

        Comma style format displays monetary amounts without dollar signs and inserts a comma every three positions to the left of the decimal point

        When a decimal place is needed, it must be entered. When entering numbers with zeros following the decimal place, Excel drops those zeros. They appear after the appropriate format has been

        Pound signs appear when a number is too long for the width of the cell. Do not panic or re-enter the data.

        Numbers align to the right of a cell and text to the left.

        The Decrease Decimal button rounds off the number to the nearest decimal place. It does not truncate the number.

        Sometimes numbers reformat themselves. Use the Format; Cells command to choose the correct format. The General format is used if no particular format is needed.

Applying Formats to the Worksheet

A font is the name given to a collection of characters with specific design. The font size is the physical size of the text, measured in units call points. The default font in Excel is 10 point Arial. You can change the font, the size, or both of any entry or section in a worksheet by using the Format command on the menu bar or by using the Formatting toolbar.

        Worksheets need to be formatted to improve their appearance and readability

        Different fonts often are used in the same worksheet.

        You can change the font any time while the worksheet is active

        Select nonadjacent ranges by selecting the first range and then, while holding down the CTRL key, selecting the second range to format

Using the Format Painter

A cells format can be painted into other cells using the Format Painter button on the Standard toolbar. This is similar to using drag and drop to copy information, but instead of copying cell contents, you copy only the cell format.

Adding Borders

You can use colors, patterns, and borders to enhance the overall appearance of a worksheet and to improve its readability. You can add these enhancements using the Patterns tab in the Format Cells dialog box or by using the Borders and Color buttons on the Formatting toolbar. You can apply color or patterns to the background of a cell or range or to cell contents. Adding borders may be difficult to grasp. Take time to think about where the lines are needed and what types are needed prior to applying borders.

 

For more border options, choose Format, Cells, Borders tab. Customizing can also be performed in this dialog box. Try adding an outline around the data and changing the line attributes for various cells.

 

Be selective when using color or pattern fills. Too many patterns can be overwhelming and distracting.

 

If darker colors are chosen for fill, it may be best to change text to a light color and/or bold to make it more readable.

 

Gridlines do not print unless that particular print option is selected (File, Page Setup, Sheet Tab).

 

You must add borders to place lines exactly where you want and in the style you need. When creating borders, it is often helpful to turn off the gridlines, since gridlines and borders tend to obscure one another on the screen. You may want to turn off gridlines when borders are used in the future.

 

Q & A

What should you do if a number reformats itself after you enter it?

Answer: Choose the correct format.

Applying Text Attributes and Cell Borders

All font attributes can be changed at the same time by using Format, Cells, and selecting the Font tab. In the Format Cells dialog box, a sample of the text previews the font. Have students use this method to change the utility names.

 

Use of the fonts makes them more familiar. At first, it takes time to find the right ones. To make it easier to find one, choose Tools, Customize, Options tab, and check List font names in their font. These names appear on the Formatting toolbar.

 

Text should be 10-12 points. Titles and emphasized data should be 14-16 points. The larger the font size, the bigger the characters.

 

Q & A When you change the font, font size, or emphasize text, which cells are affected?

Answer: The selected cell(s).