Excel Basic ( Guide 2)

Entering and Editing Data

---

Entering Data

You can enter text, numbers and dates in an Excel worksheet. In Excel¹s terminology, numbers and dates are called values and text is referred to as a label. To enter data of any type, click on the cell you want to contain that data, and then type the information you want in the cell. When you begin typing, your data also appears in the formula bar. When you have finished typing the data for the active cell, press the Return or Enter key.

Editing Data

The easiest way to edit the contents of a cell is to select the cell and then retype the entry. The new entry replaces the old contents. For example, to change the number in cell B6 to 199, select cell B6, type 199 and press Return. This method works well with numbers, but is more difficult when editing long text labels or formulas.

The formula bar gives you more flexibility while editing. When the mouse pointer moves into the formula bar, it changes shape to an I-beam, signifying that you can enter or edit text. Text in the active cell appears in the formula bar and you can edit it there. Use the mouse to select the text you want to change in the formula bar and then type the new text. Excel automatically replaces what is selected. Don't forget to press the Return key when you finish editing a cell.

You can also edit labels and values directly in cells. Double-click on the cell you want to edit. This puts an insertion point in the cell. Edit the contents of that cell the same way you would using the formula bar.

Moving the Active Cell

Cell selection and movement around the worksheet are similar operations in Excel. To select a given cell or make it active, simply click on that cell. Use the mouse or the arrow keys to move around the worksheet. For example, if you press the right arrow key twice you move two cells to the right.

Refer to the table below for additional information on using the keyboard to navigate a worksheet.

To move	Press this key
One cell left	Left Arrow
One cell right	Right Arrow
One cell up	Up Arrow
One cell down	Down Arrow
To top of worksheet (cell A1)	Control Home
To last cell containing data	Control End
To end of data in a column	Control Down Arrow
To beginning of data in a column	Control Up Arrow
To end of data in a row	Control Right Arrow
To beginning of data in a row	Control Left Arrow

Go To Command

A quick way to move a large distance on a worksheet is with the Go To command on the Edit menu. When you select the Go To command, a dialog box prompts you to identify the cell. Enter the cell reference and click OK or press Return.

Clearing Cell Contents

To clear the contents of a cell choose Clear from the Edit menu. Then, select what you want to clear from the cell: All, Formats, Contents, or Notes. Most frequently you will want to clear the Contents of a cell. Pressing theDelete key also clears the contents of cells.

Undoing Mistakes

If you make a terrible mistake and you accidentally delete important data. Use the Undo command on the Editmenu or the Undo tool to correct the mistake.

Copying and Moving Data

Copy selected data from one cell to another with the Copy and Paste commands, or with the Drag and Drop procedure. If you want to move data instead, use the Cut and Paste commands, or the Drag and Drop procedure.

These commands and procedures are described below.

	Cut
	The Cut command extracts the selected data and puts it on the Clipboard, a temporary storage area. The contents of the Clipboard are overwritten with each copy or cut.
	Copy
	The Copy command puts a copy of the selected data on the Clipboard.
	Paste
	The Paste command inserts the Clipboard¹s contents into the active cell. Selected data is replaced by pasted text.
	Drag and Drop
	This procedure does not involve the Clipboard and works best when moving data a short distance. To move cells, position the mouse on the cell borders. Wait until the mouse changes to a left-pointing arrow. When the mouse is this shape, press and drag the data to the new location.

Formatting Data

---

Changing formats using the toolbar

In Excel, you can change text fonts and styles in the worksheet. Excel has a Formatting toolbar to simplify basic formatting tasks. The Formatting toolbar appears in the figure below.

The formatting toolbar has several tools you can use to change formats. The B button makes cells bold, the I button italicizes cells and the U button underlines. Use these buttons to turn formats off as well as on. For example, if cells are bold and you want to turn off that format, select the cells and click the B button. The toolbar also has buttons to change font and size.

Font tab in Format Cells

The formatting toolbar offers quick access to varying text formats. For a more complete selection of formatting options, use the Font tab in the Format Cells dialog box.

In the Font section you can select Font, Size, Style, Color and Effects. Notice the Preview area in the dialog box. This shows a preview of the font and style you select before you click the OK button.

Alignment

By default, Excel left-aligns labels and right-aligns values in a worksheet. You can change cell alignment using the toolbar or the Format Cells command.

The toolbar has text alignment icons next to the bold and italic icons. You can left-align, right-align, or center text within a cell using these buttons. Select the cell(s) you want to align and click the appropriate alignment button on the toolbar.

The toolbar also has a button that will center a label over a range of cells, for example centering a title over a report. To center data over a range of cells, select the cell you want to center and the columns you want to center it over and click the Center over Cells button (shown at right).

Format Alignment command

You can also change the alignment of data within cells using the Alignment section of the Format Cells dialog. This dialog box also has options to change the orientation of text (i.e. sideways or vertical) and a box to wrap text within a cell.

Changing Numeric Formatting

To change the format of a number, choose the Cells command from the Format menu. In the Format Cells dialog box, Excel displays different tabs for various formatting types. To change numeric formats, click the Number tab. Select the category you want and then the actual format. For example, to display numbers as currency with two decimal places, select the Currency category, enter 2 for the number of decimal places, and select the appropriate currency symbol. Or to display a number as a percentage, choose the Percentage category and select the number of decimals you want to display.

Placing borders around cells

The toolbar has a button for placing borders around cells. You can also use the Border section of the Format Cells dialog box, which provides more options. Select the cells you want a border on and choose the desired format from either the toolbar or the Format Cells dialog box.

Shading cells

To apply a specific pattern or color to a range of cells, use the Patterns section of the Format Cells dialog box. The sample area in the Patterns dialog box displays what the selected colors and patterns will look like. You can also apply a solid color using the Fill Color tool on the Formatting toolbar.

Changing column width with the Format menu

To change column width using the Format menu, select the column or columns you wish to change and choose Column then Width. In the Column Width dialog box, type a number for the width of the column. The number represents the number of characters that can fit in the cell. The default column width is 10, which means a column is wide enough for ten, Helvetica 10-point characters. Choose Best Fit to automatically widen a column to accommodate the widest label, or click the Hide button to hide the column completely. Hiding columns is useful when you have data that you do not want to print.

* Changing row height is similar to changing column width. Select the row(s) you wish to change and choose Row then Height from the Format menu.

Changing column widths by dragging column borders

You can change column widths by dragging column borders with the mouse. Move the mouse pointer to the right hand border of the column you wish to change. The mouse pointer will change shape to a left and right pointing arrow as seen below.

Click and drag the mouse to adjust the column width. Note that when you are adjusting the width in this way, a numeric width indicator appears in the upper left part of the formula bar.

Formulas and Functions

---

Formulas and functions that perform calculations are the true power of spreadsheets.

Formulas

To build a formula, first select the cell in which you want the results to appear. In Excel, all formulas start with the = sign. After the = sign, type the cells you want to add or subtract along with the mathematical operation you wish to perform. For example, to add the January sales in the worksheet below, the formula would be =B3+B4+B5. If you want to subtract February Widget Sales from January Widget sales, the formula would be =B3-C3.

Building many formulas can quickly become tedious. Fortunately, the Copy command described earlier also works with formulas.

Copying Formulas

The easiest way to copy a formula is with the Fill Handle in the lower right corner of the cell. Create your initial formula and then position the mouse on the Fill Handle. When the mouse changes shape to a cross-hair, press and drag over the adjacent cells you want to copy the formula to.

SUM( ) function

The SUM( ) function is probably the most common function in Excel. It adds a range of numbers. To build a SUM( ) function, begin by typing the = sign; all functions begin with the = sign. Next type the word SUM followed by an open parenthesis. You must now tell Excel which cells to sum. Using the mouse, click and drag over the range of cells you wish to add. A dotted outline will appear around the cells and the cell range will be displayed in the formula bar. When you have the correct cells selected, release the mouse button, type a closing parenthesis and press the <Enter> key.

If you do not want to use the mouse, type in the references of the cells you want to sum. For example, to add cells B3 through B5, type =SUM(B3:B5). Excel interprets B3:B5 as the range of cells from B3 to B5.

AutoSum button

In Excel, the standard toolbar has a button that simplifies adding a column or row of numbers. The AutoSum button, which resembles the Greek letter Sigma (shown at right), automatically creates a SUM( ) function. When you click the AutoSum button Excel creates a sum function for the column of numbers directly above or the row of numbers to the left. Excel pastes the SUM( ) function and the range to sum into the formula bar. If the range is not correct, simply select the proper range with your mouse on the worksheet. When you have the correct range entered, press the <Enter> key to complete the function.

Using the Insert Function Button

The Insert Function Button is located by the Formula Bar. Click the Insert funtion button in to activate the Insert Function dialog window.

First, choose the Function Category you are interested in from the select a category drop down menu.

Then select the function you want in that category. When you have selected the proper function click OK.

In the Function Arguements dialog box you specify the cells the function will operate on, which are called its arguments. Select the cells with the mouse and click OK. Notice the creation of the function in the formula bar.

Adjusting Page Setup settings

---

Before you actually print a worksheet, you should provide Excel information about margins, headers, footers, and page orientation. You change these settings using the Page Setup option on the File menu.

The Page Setup dialog box, shown below, has controls for margins, page orientation, headers and footers and whether gridlines and row and column heading should be printed.

To change page margins

To change your margins, click the Margins tab, and enter the measurement for your margin, in inches, in the appropriate text box.

To change page orientation

If you want to change the orientation of the page from portrait (vertical) to landscape (horizontal) click the appropriate button in the Page Orientation section of the Page tab.

To change the header or footer

A header is text that prints at the top of each page. A footer is text that prints at the bottom of each page. Headers and footers often include the name of the file being printed or an alternative title, the date, page number, or additional descriptive information. To change the header or footer, click on the Header /Footer tab in the Page Setup dialog box. The current header and footer will appear at the top and bottom of the dialog box respectively. Click the Custom Header or Custom Footer button to change the existing header or footer.

The header and footer are divided into three sections (left, center and right). Press <Tab> or <Shift Tab> to move from section of the header or footer to another.

Printing

---

Print preview

Before you actually print, it¹s a good idea to preview your output on the screen.Choose Print Preview on the File menu to preview your output on screen. Excel¹s Print Preview screen displays how the document will print on the page, but it is difficult to actually read the text. Notice that the mouse pointer takes the shape of a magnifying glass. You can enlarge the printed image by clicking the Zoom button or by using the magnifying glass. Simply click the magnifying glass on a part of the page you want to enlarge.

The Print Preview screen also has several buttons at the top of the screen for making adjustments. For example, the Setup button opens the Page Setup dialog box and the Margins button lets you change page margin and column widths to fit more information on one page.

If you are satisfied with how the output appears in the Print Preview screen, the Print button lets you send your output directly to the printer.

Sending your workbook to the printer

Choosing the Print option on the File menu or clicking the Print button on the toolbar opens a dialog box that lets you change print settings and specify the number of copies to print. The Print dialog box appears below.

Within the Print dialog box, you can select what you want to print, how many copies and which pages. For example, if you only want to print the selected cells, or the entire worksheet you can choose those options.

Getting Help

---

In the Help System, any topic that has additional information is underlined. To read the information about an underlined topic, click on the topic. The Help System automatically switches to information about the selected topic.

To expand to the full Help window, click the Show icon.

The Contents Tab

The Contents Tab is a listing of ³books² containing one or more Help topics and even some subbooks. You can open any of these books by double-clicking on it to get information about a topic.

The Answer Wizard Tab

Using the Answer Wizard is similar to using the Office Assistant. You can type in your question or the term you¹d like help on, click Search and a list of topics will be displayed. Information on the topic will appear in the right-hand window.

The Index Tab

The Index is an alphabetical listing of key words found in the Help topics. You can either type a word in the first text box or select a topic from the scrolling list displayed below. When you have selected a topic click on the Search button. A list of related topics is displayed in the third text box. Select the topic of your choice and information about that topic will appear in the right-hand window.