Home > Notes > Excel > Lesson 11

| Discovering Computers | Windows XP | Word 2002 | PowerPoint 2002 | Excel 2002 | Site Map |


| Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 |

| Project Reinforcement | Flash Cards | Practice Test |

Excel 2002: Formulas, Functions, Formatting,
and Web Queries

  1. Enter multiple lines of text in the same cell
  2. Enter a formula using the keyboard
  3. Enter formulas using the point method
  4. Identify the arithmetic operators
  5. Recognize Smart Tags
  6. Applying the AVERAGE, MAX, and MIN functions
  7. Verify a formula using Range Finder
  8. Change the font of a cell
  9. Color the characters and background of a cell
  10. Add borders to a range
  11. Format numbers using the Format Cell dialog box
  12. Add conditional formatting to a range of cells
  1. Align text in cells
  2. Change the width of a column and height of a row
  3. Check the Spelling of a worksheet
  4. Preview how a printed copy of the worksheet will look
  5. Distinguish between portrait and landscape orientation
  6. Print a partial or complete worksheet
  7. Display and print the formulas version of a worksheet
  8. Print to Fit
  9. Use a Web query to get real-time data from a Web site
  10. Rename sheets
  11. E-mail the active workbook from within Excel

1. Enter Multiple Lines of Text in the Same Cell

At times you may want to enter a long cell entry in a cell. To display a lengthy entry you can widen the cell or choose to wrap the long text within the cell. To wrap a cell entry, select the Format command on the menu bar and then select the Cell option. The Format Cells dialog box appears on the screen. Click on the Alignment tab and than click on the wrap text check box. Excel automatically wraps the text entered in a cell formatted for wrapped text. You can indicate where you would like a wrapped line to end by typing the ALT+Enter key at the desired text location. The row height of cell that contains wrapped text will automatically adjusts to the accommodate the wrapped text entry.

ani_back Top of the Page

2. Enter a Formula Using the Keyboard

Formulas are used frequently to calculate data on the worksheet. You should start a formula by clicking on the cell you wish to display the results. A formula begins with an equal (=) sign. Upon typing the equal (=) sign, the remaining formula is entered. You can use numbers or cell references in the formula. On page E2.09 the formula =d3*e3 is entered in to cell F3 to multiply the contents of cell D3 and E3. Cell references are not case sensitive. Hence, you can enter either uppercase or lowercase letters. In addition, you can spaces before and after the arithmetic operator. Adding spaces often makes the formula easier to read. Complete formula entries by pressing the Enter key or the arrow key. Upon completion, Excel displays the results in the formula bar and in the cell.

ani_back Top of the Page

3. Enter Formulas Using the Point Method

Formulas can also be entered using the point method. The point method utilizes the mouse to help build a formula. To use the point method, click on the cell you wish to enter your formula and type the equal (=) sign. Then point and click on the cell you want in your formula. The pointing method automatically enters the cell reference in to the formula. Hence, you do not have to type the cell reference. You can use the Enter box on the formula bar as shown in figure 2-7 to enter the formula in to the cell or you can press the Enter key.

ani_back Top of the Page

4. Identify the Arithmetic Operators

There are several arithmetic operators that can be used in an Excel formula. A list of the valid arithmetic operators and their meaning can be found in Table 2-2.

When multiple arithmetic operators are used in a formula, the operator with the highest order is calculated first. Excel calculates formulas with multiple operators in the following order: negation, percentages, exponentiations, multiplication and division, addition and subtraction. At times you may want to calculate a part of the formula first that is not normally calculated first. Parentheses can be used to override the order of operations. When Excel encounters parentheses in a formula, it calculates that portion of the formula first.

ani_back Top of the Page

5. Recognize Smart Tags

A smart tags automatically appears on the screen and provides additional options. Excel notifies the user of a Smart Tag by displaying a small triangle in the corner of a cell. Upon clicking on the Smart Tag indicator, the Smart Tag button appears. If the Smart Tag button is clicked, a list of options is provided. A list of the Smart Tags available in Excel can be found in Table 2-4.

ani_back Top of the Page

6. Applying the AVERAGE, MAX, and MIN Functions

Functions are pre-written formulas that automatically compute statistical equations. Excel contains several functions. The most commonly used functions include the Average, Max, and Min functions. Because functions are formulas, they begin with an equal (=) sign. Then the function name and then function arguments (or cells) to be calculated are entered. As an example, to average a range of cells you would enter the function: =Average (D3:D10). This averages the values in cells D3 to D10. You can type a function in a cell or you can use the Function box on the formula bar as shown in Figure 2-16.

The Function box displays the Insert Function dialog box on the screen. You can select the function you wish to enter from the list of functions provided in the Insert Function window. You can also enter the most commonly used function by clicking the List Arrow on the AutoSum button as shown in Figure 2-19.

Upon clicking on the AutoSum button's list arrow, the AutoSum menu displays the most commonly used functions. You can select the function you wish to enter from the list.

ani_back Top of the Page

7. Verify a Formula Using Range Finder

A common mistake when entering in formulas is to enter the wrong cell address. Doing so causes the formula to calculate incorrectly. The Range Finder feature of Excel helps to verify which cells are being in used in a formula. When you double-click on a cell containing a formula, Excel's Range Finder highlights the cells used in the formula with colorful borders. The colored borders make it easy to visually identify which cell entries are being used to calculate a formula.

ani_back Top of the Page

8. Change the Font of a Cell

A worksheet's appearance can be drastically improved by applying various formatting attributes. One such attribute is the font attribute. The font is the style of text. Excel can display text and values in lots of different font sizes, colors, or styles. The easiest way to change the font style is to click on the Font box arrow on the Formatting toolbar as shown in Figure 2-28.

This provides a list of the available Font Style. You can select the desired Font Style by clicking on it. The Font Style is a applied to the cell or range of cells you have selected. You can also change the Font Style using the Format Cells menu by clicking on the Font tab and selecting a font.

ani_back Top of the Page

9. Color the Characters and Background of a Cell

In addition to changing the font style, you can also change the font color and the background color of a cell. The Font Color button on the formatting toolbar changes the text color. The Font Color list arrow displays a color palette for selecting desired text color as shown in Figure 2-34.

The Font color palette contains an Automatic selection. The Automatic selection sets the font color back to the default font color. The background color of a cell or a range of cells can be changed by using the Fill Color button as shown in Figure 2-33.

The Fill Color button list arrow displays a color palette for selecting the desired fill color. The Fill color palette contains a No Fill selection. The No Fill selection removes fill color from a cell or group of cells.

ani_back Top of the Page

10. Add Borders to a Range

Borders can also be applied to a cell or range of cell. The Border button applies borders to selected cells. The Border list arrow displays a palette with several options for applying borders as shown in Figure 2-35.

The border palette contains a No Borders selection. The No Borders selection removes borders from a cell.

ani_back Top of the Page

11. Format Numbers Using the Format Cell Dialog Box

Numbers can be formatted to display dollar signs, commas, percentages and other formatting symbols. The easiest way to format a number is to use the formatting buttons on the Formatting toolbar. For instance, the Currency button automatically formats numbers in a cell for Currency and two decimal places as shown in Figure 2-41.

The Percent button formats a cell to display a percent sign and zero decimal places. The Increase and Decrease buttons allow you to customize the number of decimal places you wish to display as shown in Figure 2-41.

You can also format numbers using the Format Cell command. The Number tab displays a list of numeric formats. The General format is the default format for numbers.

ani_back Top of the Page

12. Add Conditional Formatting to a Range of Cells

Conditional formatting applies formatting to a cell if the cell entry meets a specified condition. The Conditional Formatting command on the Format menu is used to set up the condition and the format. The Conditional Formatting dialog box appears upon selecting the Conditional Formatting command as shown in Figure 2-49.

You must enter the condition, a relational operator, and the desired formatting in the dialog box. Excel will check the provided condition and format the selected cells that fulfill the condition. In the case a cell entry changes and no longer matches the condition, Excel will suppress the format.

ani_back Top of the Page

13. Align Text in Cells

Cell entries can be aligned within a cell many different ways. The most common alignment for text is left, center, right, or justified alignment. The easiest way to apply an alignment format to a cell or range of cells is with the Formatting toolbar. The formatting toolbar contains a button for each of the left, center, right, and justified alignment. You can also use the Format Cells menu to align text in a cell. The Alignment tab in the Format Cells dialog menu offers additional alignment options.

ani_back Top of the Page

14. Change the Width of a Column and Height of a Row

The column-width of any column or a group of columns can be increased to accommodate large cell entries or decreased to accommodate small cell entries. You can adjust the width of column or a selected of columns by clicking and dragging the column border. You can also adjust the column-width to automatically fit the widest column-entry by double-clicking the column border. In addition, the Format Column command can to manually increase or decrease the column size. Double-clicking on the row border automatically adjusts the row height to fit the largest column entry. The row height can be adjusted manually by clicking and dragging the row border. In addition, the Format Row command can to manually adjust the row height.

ani_back Top of the Page

15. Check the Spelling of a Worksheet

The Excel program has a spell checker that allows you to check for spelling errors. The Spell Checking button on the Standard toolbar invokes the spell checker. The spell checker checks the entire spreadsheet for misspelled words. Upon encountering a word not found in the dictionary, spell check displays a dialog box as shown in Figure 2-61.

The Spelling dialog box gives you the opportunity to select the correct word from a suggested list or edit the word on the worksheet. Words that you commonly use but are not in the dictionary may be added to the dictionary by clicking on the Add To Dictionary button.

ani_back Top of the Page

16. Preview How a Printed Copy of the Worksheet Will Look

Print Preview provides you with an opportunity to see how your worksheet will look printed out without actually printing it. Print Preview button on the Standard toolbar is the easiest way to preview your worksheet. Print preview also allows you to modify the layout of your printout.

ani_back Top of the Page

17. Distinguish Between Portrait and Landscape Orientation

You can select whether to print your worksheet in Portrait or Landscape orientation. Portrait orientation prints across the width of the paper. Landscape orientation prints across the length of the paper.

ani_back Top of the Page

18. Print a Partial or Complete Worksheet

You can print the entire worksheet by clicking the Print button. However, you may wish to print just a section of the worksheet rather than the entire worksheet. To do so, you must first select the section of the worksheet you wish to print. Upon selecting the section to print, you should select the File Print command from the Excel menu. The Print dialog box appears on the menu. Click on the Selection option in the Print what box as shown in Figure 2-69 and then click the OK button.

ani_back Top of the Page

19. Display and Print the Formulas Version of a Worksheet

The results of a formula is automatically displayed in a cell. Excel also allows you to also display the formula in a cell. By displaying the formula in the cell, you can see what formulas are being use to calculate values on the worksheet. You can toggle between displaying the formulas and the values by typing Ctrl and ` (accent mark). Formulas can also be displayed by checking the Formulas box on the View tab of the Tools Option dialog box. Excel automatically adjusts the worksheet's column width to display the entire formula.

ani_back Top of the Page

20. Print to Fit

In order to print a wide worksheet on a single page, it may be necessary to compress the print size. Excel provides a Fit to option that automatically reduces the print size to accommodate printing a large worksheet on a single sheet of paper. The Fit To option can be found on the Page Setup dialog box. The Page Setup dialog box is displayed by selecting the File Page Setup command or Setup from the Print Preview window. Upon selecting the Fit to option, Excel will automatically compress the print size so that it will print on one sheet of paper. The scaling percentage can be adjusted manually by entering a percentage in the Scaling box. To set print size back to its normal size, the scaling should be set to 100%.

ani_back Top of the Page

21. Use a Web Query to Get Real-Time Data From a Web Site

The Web Query command allows users to import data directly into an Excel worksheet from the World Wide Web. By selecting the Data and Import External Data commands, you can build a web query. A Web query allows you to import data from a particular web page directly into the worksheet.

ani_back Top of the Page

22. Rename Sheets

An Excel workbook initially displays three worksheets inan Excel workbook. The worksheets are named Sheet1, Sheet2, and Sheet3. You can change the name of the sheet tabs by double-clicking on the sheet name. The tab color can also be changed and the order of the sheet tabs can be rearranged. In addition, new worksheet tabs can be inserted via the Insert command on the menu bar.

ani_back Top of the Page

23. E-mail the Active Workbook From Within Excel

E-mail is a quick and convenient way to communicate with other people. If you have access to the Internet and an E-mail account, you can send worksheets to other users on the Internet. The E-mail button on the Standard toolbar is the quickest way to activate Excel's E-mail feature. The E-mail message window appears on the screen as shown in Figure 2-83.

You must complete the mail recipient's E-mail address in the TO field and enter a subject for your message. When you are ready to send the message click the Send button in the E-mail message window. When you use the E-mail button to send a worksheet it displays the worksheet in the body of the E-mail message. The File Send To command also allows you to send a worksheet via E-mail. The Send To option provides an option for sending an E-mail as an attachment or in the body of the E-mail. When the worksheet is sent as an attachment the recipient can open the worksheet using Excel and modify its contents.

ani_back Top of the Page

 

| Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 |


| Discovering Computers | Windows XP | Word 2002 | PowerPoint 2002 | Excel 2002 | Site Map |