Top of the Page| 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: What-If Analysis,
Charting,
and Working with Large Worksheets
Text has no angle upon entering it into a cell. However, Excel allows you to rotate text within in a cell. Text can be rotated between 1 and 90 degrees. To rotate text, access the Alignment dialog box from the Format Cells menu as shown in Figure 3-03. Click the degree of orientation you desire in the Orientation box.
2. Create a series of month names
You can fill in several types of data series by selecting cells and dragging the fill handle. You can also use the Series command from the Edit Fill menu. AutoFill allows you to copy text, values, formulas, as well as fill in a series. A series is a pattern or combination of numbers, text, dates, or time periods based on a pattern you establish. For example, you have entered January in cell B3. You can use the Fill Handle to complete the remaining months. The Fill Handle also copies the format of the original cell to the selected cells.
3. Use the Format Painter button of format cells
The Format Painter copies the format of a cell and then allows you to apply the format to other cells in the worksheet. The Format Painter copies only the formatting characteristics of a cell or group of cells. However, the cell content is not copied. The Format Painter is located on the Standard toolbar. To use the Format Painter, select the cells or group of cells you wish to copy the format of and click the Format Painter button. Then brush the Format Painter tool over the cells you wish to apply the format.
The Copy allows you to copy the content of a selected cell or group of cells. The Copy command copies not only the contents of the cell but also how the cell is formatted. The Copy button on the Standard toolbar is the most convenient way to copy cells. You can also access the Copy command from the Edit menu and the shortcut menu. The Paste command allows you to insert the information you have copied to a group of cells. The cells may be adjacent or nonadjacent. Though the Fill Handle can be used to copy data, it is not very effective for copying data to nonadjacent worksheet cells. The Paste button on the Standard toolbar is the most convenient way to copy cells. You can also access the Paste command from the Edit menu and the shortcut menu.
Rows, Columns, and Cells can be inserted into a worksheet by using the Insert command on the menu bar. The Shortcut menu also contains an Insert command. The Insert Row command inserts a new row in between existing worksheet rows. Excel inserts a row at the location of the cell pointer. You can insert a single row or multiple rows into a worksheet. To insert multiple rows, select the number of rows you wish to insert. Then click the Insert Row command on the menu bar. Excel will insert the number of rows you have selected. The Insert columns command inserts a new column at the location of the cell pointer. The Insert cell command allows you to insert blanks cells to the right or below the location of the cell pointer. Cell references used in formulas are automatically adjusted.
6. Format numbers using format symbols
When entering in numbers into a worksheet, you can enter format symbols with the number. For example, you can enter the percent symbol with a whole number. Excel will display the number and percent symbol without any decimal places. You can also enter commas, and dollars signs when entering in money.
You can freeze a column on the screen with the Freeze Pane command on the Window menu. Once frozen, the column remains on the screen when you scroll to other parts of the worksheet. The Freeze Pane command freezes columns directly to the left of the cell pointer. Rows can also be frozen the screen. The Freeze Pane command freezes the rows directly above the cell pointer. The Remove Panes command on the Window menu removes frozen rows and columns.
8. Display and Format the System Date
The NOW function returns the number of the current date and time in the active cell. The result is formatted as a date and is displayed using the date format: MM/DD/YY. There are date formats for displaying the date in a cell. A different date formats can be selected via the Format Cells menu and the Numeric Tab as shown in Figure 3-22.
9. Use absolute cell references in a formula
Excel automatically adjusts the cell references in a formula when the formula is copied to a new cell location. You can prevent Excel from changing a cell reference when it copies a formula. This is called by making a cell reference absolute. An absolute cell reference allows you can copy a formula without the cell reference changing. The dollar symbol ($) is used to indicate an absolute cell reference absolute. The dollar symbol can be typed or the F4 key can be used to automatically enter the dollar symbol into the cell reference. The position of the dollar symbol effects which part of the cell reference is to remain absolute. You can absolute the entire cell by using two dollars signs, such as $B$5. You can also absolute just a column or just row of the cell reference such as $B5 to absolute the column and B$5 to absolute just the row. To enter a cell using an absolute cell reference, start the formula as you would any formula by typing the equal (=) sign. Then enter the appropriate cell addresses and arithmetic operators. Upon entering a cell reference that requires an Absolute reference press the F4 key as shown in Figure 3-24.
F4 will position the dollar ($) sign in the cell reference. You can position placement of the dollar ($) signs by pressing F4 multiple times. Press the Enter key upon completing the formula.
10. Use the IF function to perform a logical test
The IF function is used to test values and formulas. It returns one value if a condition tested is TRUE and another value if the condition tested is FALSE. The syntax of the IF function is IF(logical_test, value_if_true, value_if_false). Page 3.27 gives an example of the IF function. When the value_if_true and value_if_false arguments are evaluated, IF returns the value returned by those statements. Up to seven IF functions can be to construct to create elaborate tests.
11. Copy absolute cell references
You can use the Fill Handle to copy a formula to adjacent cells. You can also use the Copy and Past buttons to copy a formula to adjacent or non-adjacent cells. A formula that contains absolute cell references the cells references remain constant. Cell references that do not contain an absolute reference automatically change when copied.
Excel has over a 100 toolbars including 19 built-in toolbars. The Standard and Formatting toolbars are two of the built-in toolbars. The Drawing toolbar is another built-in Excel toolbar. The Drawing button on the Standard toolbar is used to display the Drawing toolbar. The Drawing toolbar usually displays near bottom of the Excel window. To display Excel's other toolbars; select the Toolbar command from the shortcut menu or the View Toolbar command from the Excel menu. Toolbars can be either docked or floating. A floating toolbar is displayed any where in the Excel menu while docked toolbars are aligned along the border of the Windows. A Docked toolbar is displayed at the top, bottom, or sides of the Excel window. You can use the title bar to moved a toolbar to a new location.
13. Add a drop shadow to a range of cells
The Drawing toolbar contains several tools for add graphics to the worksheet. For example, the Shadow Style button allows you to apply a shadow box to a range of cells. The Shadow Style button displays several shadow styles to choose from. Upon selecting a drop shadow, the shadow appears around the selected cells as shown in Figure 3-34.
14. Create 3-D Pie chart on a separate chart sheet
Pie charts are one of the many charts available in Excel. A pie chart depicts each piece of the pie as a percentage of the whole pie. To build a pie chart, you must select the cells to be charted first. Upon selecting the cells to be charted, use the Chart button to create a pie chart. Excel offers several types of pie charts to choose from as shown in Figure 3-52.
Once completed, the chart appears on the screen. You can use the Chart Toolbar and Formatting Toolbar to modify the charts appearance. The Delete key can be used to remove selected objects from the Chart. You can also explode a pie by clicking and dragging a piece of the pie. By dragging a piece of the pie, the entire pie explodes.
The worksheet tabs of a worksheet usually have a white background. You can add color to the tabs using the Tab Color command. The Tab Color command allows you to select a new color from a color palette. By clicking on a new color, the worksheet tab is filled with color. An active worksheet tab will display with a white background and an underlined worksheet name.
16. Rearrange sheets in a workbook
Worksheet tabs can be arranged any order. The easiest way to relocate a worksheet is to click and drag the worksheet tab to a new position. The shortcut menu also provides a command for moving worksheet tabs.
17. Preview and print multiple sheets
You can print all the worksheets in a workbook using the File Print command. Prior to printing a worksheet, the worksheets you wish to print must be selected. To select a worksheet for printing, ctrl-click on the worksheet tab. Once the worksheets have been selected, you should use the Print Preview button to preview the selected worksheets. The Next button on the Print Preview window displays the other selected worksheets for printing. You can use the Print button on the Print Preview window to print the active sheets.
18. Use the Zoom box to change the worksheet view
Excel allows you to shrink or magnify a worksheet with the Zoom feature. Shrinking a worksheet allows more of the worksheet to display in the Excel window. Magnifying a worksheet causes it to be enlarged within the Excel window. You can choose how much to shrink or enlarge a worksheet by entering a number between 10 and 400 in the Zoom box. The Zoom box is located on the Standard Toolbar shown in Figure 3-74.
19. View different parts of the worksheet through window panes
The Window panes command works similarly to the Freezing Panes command. Window panes allow you to split your window into two or four different viewing areas. This allows you to display two or four different sections of your worksheet. The Split command is located on the Window menu. Excel splits you worksheet into four different panes at the location of your cell pointer. To change the split, click and drag the split border to the desired location. You can double-click on the window pane to remove the split. You can also use the Remove Split command on the Window menu to remove the split.
20. Use Excel to answer what-if questions
When numbers are manipulated the worksheet automatically recalculates. The automatic recalculation feature makes Excel a powerful tool for analyzing worksheet data. Charts will automatically reflect any changes that impact the chart.
Goal Seek is a powerful that automatically manipulates data on a worksheet to meet a specified objective. If you have a goal in mind you can use the Goal Seek tool to determine what value a cell needs to be to achieve your goal. The Goal Seek command is located on the Tools Menu bar. The Goal Seek dialog box displays upon activating the command as shown in Figure 3-82.
The Goal Seek dialog box allows you to set your goal by entering in information about the value you wish to achieve and the cells required to achieve it.
| Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 |
| Discovering Computers | Windows XP | Word 2002 | PowerPoint 2002 | Excel 2002 | Site Map |