Transcript Excel Project 3 - York Technical College

Microsoft
Office 2003
Introductory Concepts
and Techniques
Excel Project 3
What-If Analysis, Charting,
and Working with
Large Worksheets
Objectives
•
•
•
•
•
Rotate text in a cell
Create a series of month names
Use the Format Painter button to format cells
Copy, paste, insert, and delete cells
Format numbers using format symbols
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
2
Objectives
•
•
•
•
Freeze and unfreeze titles
Show and format the system date
Use absolute cell references in a formula
Use the IF function to perform a logical
test
• Show and dock toolbars
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
3
Objectives
• Create a 3-D Pie chart on a separate chart
sheet
• Color and rearrange worksheet tabs
• Change the worksheet view
• Goal seek to answer what-if questions
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
4
Starting and Customizing Excel
• Click the Start button on the Windows taskbar, point to
All Programs on the Start menu, point to Microsoft Office
on the All Programs submenu, and then click Microsoft
Office Excel 2003 on the Microsoft Office submenu
• If the Excel window is not maximized, double-click its title
bar to maximize it
• If the Language bar appears, right-click it and then click
Close the Language bar on the shortcut menu
• If the Getting Started task pane appears in the Excel
window, click its Close button in the upper-right corner
• If the Standard and Formatting toolbars are positioned
on the same row, click the Toolbar Options button and
then click Show Button on Two Rows
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
5
Bolding the Font
of the Entire Worksheet
• Click the Select All button immediately
above row heading 1 and to the left of
column heading A
• Click the Bold button on the Formatting
toolbar
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
6
Entering the Worksheet Titles and
Saving the Workbook
• Select cell A1 and then enter Aquatics Wear as the
worksheet title
• Select cell A2 and then enter Six-Month Financial
Projections as the worksheet subtitle
• With a floppy disk in drive A, click the Save button on the
Standard toolbar
• When Excel displays the Save As dialog box, type
Aquatics Wear Six-Month Financial
Projection in the File name text box
• If necessary, click 3½ Floppy (A:) in the Save in list.
Click the Save button in the Save As dialog box
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
7
Rotating Text and Using the Fill Handle
to Create a Series of Month Names
• Select cell B3
• Type July as the cell entry and then click the
Enter box
• Click the Font Size box arrow on the Formatting
toolbar and then click 11 in the Font Size list
• Click the Borders button arrow on the Formatting
toolbar and then click the Bottom Border button
(column 2, row 1) on the Borders palette
• Right-click cell B3
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
8
Rotating Text and Using the Fill Handle
to Create a Series of Month Names
• Click Format Cells on the shortcut menu
• When the Format Cells dialog box is
displayed, click the Alignment tab
• Click the 45° point in the Orientation area
• Click the OK button
• Point to the fill handle on the lower-right
corner of cell B3
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
9
Rotating Text and Using the Fill Handle
to Create a Series of Month Names
• Drag the fill handle to the right to select
the range C3:G3
• Release the mouse button
• Click the Auto Fill Options button below
the lower-right corner of the fill area
• Click the Auto Fill Options button to hide
the Auto Fill Options menu
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
10
Rotating Text and Using the Fill Handle
to Create a Series of Month Names
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
11
Copying a Cell’s Format
Using the Format Painter Button
• Click cell H3
• Type Total and then press the LEFT
ARROW key
• With cell G3 selected, click the Format
Painter button on the Standard toolbar
• Point to cell H3
• Click cell H3 to assign the format of cell
G3 to cell H3. Click cell A4
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
12
Copying a Cell’s Format
Using the Format Painter Button
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
13
Increasing Column Widths
and Entering Row Titles
• Move the mouse pointer to the boundary between
column heading A and column heading B so that the
mouse pointer changes to a split double arrow
• Drag the mouse pointer to the right until the ScreenTip
displays, Width: 35.00 (250 pixels)
• Release the mouse button
• Click column heading B and drag through column
heading G to select columns B through G
• Move the mouse pointer to the boundary between
column headings B and C and then drag the mouse to
the right until the ScreenTip displays, Width: 14.00 (103
pixels)
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
14
Increasing Column Widths
and Entering Row Titles
• Release the mouse button
• Use the technique described in Step 1 to
increase the width of column H to 15.00
• Enter the row titles in the range A4:A18 as
shown on the next slide, but without the indents
• Click cell A5 and then click the Increase Indent
button on the Formatting toolbar
• Select the range A9:A13 and then click the
Increase Indent button on the Formatting toolbar
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
15
Increasing Column Widths
and Entering Row Titles
• Click cell A19
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
16
Copying a Range of Cells to a
Nonadjacent Destination Area
• Select the range A9:A13 and then click the
Copy button on the Standard toolbar
• Click cell A19, the top cell in the
destination area
• Click the Paste button on the Standard
toolbar
• Scroll down so row 5 appears at the top of
the window
• Press the ESC key
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
17
Copying a Range of Cells to a
Nonadjacent Destination Area
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
18
Inserting a Row
• Right-click row heading 21, the row below where
you want to insert a row
• Click Insert on the shortcut menu
• Click cell A21 in the new row and then enter
Margin as the row title
• Right-click row heading 24 and then click Insert
on the shortcut menu
• Click cell A24 in the new row and then enter
Revenue for Bonus as the row title
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
19
Inserting a Row
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
20
Entering a Number
with Format Symbols
• Enter 250,000.00 in cell B19, 5.00% in
cell B20, 62.00% in cell B21, 14.00% in
cell B22, 6.75% in cell B23,
15,000,000.00 in cell B24, and 30.00%
in cell B25
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
21
Entering a Number
with a Format Symbol
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
22
Freezing Column and Row Titles
• Press CTRL+HOME to select cell A1 and
ensure that Excel displays row 1 and
column 1 on the screen
• Select cell B4
• Click Window on the menu bar
• Click Freeze Panes on the Window menu
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
23
Freezing Column and Row Titles
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
24
Entering the Projected Monthly
Total Net Revenue
• Enter 23538000 in cell B4, 10781000 in
cell C4, 18875345 in cell D4, 11451990
in cell E4, 15109656 in cell F4, and
25235860 in cell G4
• Click cell H4 and then click the AutoSum
button on the Standard toolbar twice
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
25
Entering the Projected Monthly
Total Net Revenue
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
26
Entering and Formatting
the System Date
• Click cell H2 and then click the Insert Function
box on the formula bar
• When Excel displays the Insert Function dialog
box, click the Or select a category box arrow,
and select Date & Time in the list
• Scroll down in the Select a function list and then
click NOW
• Click the OK button
• When Excel displays the Function Arguments
dialog box, click the OK button
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
27
Entering and Formatting
the System Date
• Right-click cell H2
• Click Format Cells on the shortcut menu
• When Excel displays the Format Cells
dialog box, if necessary, click the Number
tab
• Click Date in the Category list. Scroll
down in the Type list and then click
3/14/2001
• Click the OK button
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
28
Entering and Formatting
the System Date
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
29
Entering a Formula Containing
Absolute Cell References
• Press CTRL+HOME and then click cell B5
• Type = (equal sign), click cell B4, type
*(1-b21, and then press F4 to change
b21 from a relative cell reference to an
absolute cell reference
• Type ) to complete the formula
• Click the Enter box in the formula bar
• Click cell B6, type = (equal sign), click cell
B4, type -, and then click cell B5
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
30
Entering a Formula Containing
Absolute Cell References
• Click the Enter box in the formula bar
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
31
Entering an IF Function
• Click cell B9. Type
=if(b4>=$b$24,
$b$19,0 in the cell
• Click the Enter box in
the formula bar
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
32
Entering the Remaining July Formulas
• Enter the remaining formulas, as
instructed on page EX 173
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
33
Copying Formulas with Absolute Cell
References Using the Fill Handle
• Select the range
B5:B16 and then
point to the fill handle
in the lower-right
corner of cell B16
• Drag the fill handle to
the right to select the
destination area
C5:G16
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
34
Determining Row Totals
in Nonadjacent Cells
• Select the range
H5:H16. Hold down
the CTRL key and
select the range
H9:H14 and cell H16
• Click the AutoSum
button on the
Standard toolbar
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
35
Unfreezing the Worksheet Titles
and Saving the Workbook
• Press CTRL+HOME
to select cell B4 and
view the upper-left
corner of the screen
• Click Window on the
menu bar and then
click Unfreeze Panes
• Click the Save button
on the Standard
toolbar
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
36
Assigning Formats
to Nonadjacent Ranges
• Select the range B4:H4
• While holding down the CTRL key, select the
nonadjacent ranges B6:H6, B9:H9, B14:H14, and
B16:H16 and then release the CTRL key
• Right-click the selected range
• Click Format Cells on the shortcut menu
• When Excel displays the Format Cells dialog box, click
the Number tab, click Currency in the Category list,
select 2 in the Decimal places box, click $ in the Symbol
list to ensure a dollar sign shows, and click ($1,234.10)
in the Negative numbers list
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
37
Assigning Formats
to Nonadjacent Ranges
• Click the OK button
• Select the range B5:H5
• While holding down the CTRL key, select
the range B10:H13, and then release the
CTRL key
• Right-click the selected range
• Click Format Cells on the shortcut menu
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
38
Assigning Formats
to Nonadjacent Ranges
• When Excel displays the Format Cells
dialog box, click Currency in the Category
list, select 2 in the Decimal places box,
click None in the Symbol list so a dollar
sign does not show, click (1,234.10) in the
Negative numbers list
• Click the OK button
• Press CTRL+HOME to select cell A1
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
39
Assigning Formats
to Nonadjacent Ranges
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
40
Formatting the Worksheet Titles
• Select cell A1 and then click the Font box arrow
on the Formatting toolbar
• Scroll down and point to Franklin Gothic Medium
(or a similar font) in the Font list
• Click Franklin Gothic Medium
• Click the Font Size box arrow on the Formatting
toolbar and then click 36 in the Font Size list
• Click cell A2 and then click the Font box arrow
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
41
Formatting the Worksheet Titles
• Click Franklin Gothic Medium (or a similar font)
in the Font list
• Click the Font Size box arrow and then click 16
in the Font Size list
• Select the range A1:H2 and then click the Fill
Color button arrow on the Formatting toolbar
• Click Green (column 4, row 2) on the Fill Color
palette and then click the Font Color button
arrow on the Formatting toolbar
• Click White (column 8, row 5) on the Font Color
palette
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
42
Formatting the Worksheet Titles
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
43
Displaying the Drawing Toolbar
• Click the Drawing button on the Standard
toolbar
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
44
Moving and Docking a Toolbar
• Point to the Drawing
toolbar title bar or to a
blank area in the
Drawing toolbar
• Drag the Drawing
toolbar over the
status bar at the
bottom of the screen
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
45
Adding a Drop Shadow
• With the range A1:H2
selected, click the
Shadow Style button on
the Drawing toolbar
• Click Shadow Style 14
(column 2m row 4) on the
Shadow Style palette
• Click cell A4 to deselect
the range A1:H2
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
46
Changing Font Size, Adding Underlines, Adding
Background Colors, and Adding Drop Shadows to
Nonadjacent Cells
• With Cell A4 selected, hold down the CTRL key, click
cells A6, A8, A14, and A16
• Click the Font box arrow on the Formatting toolbar, scroll
down and click Franklin Gothic Medium (or a similar font)
in the Font list
• Click the Font Size box arrow on the Formatting toolbar
and then click 12 in the Font Size list
• Use the CTRL key to select the nonadjacent ranges
B5:H5 and B13:H13 and then click the Borders button on
the Formatting toolbar
• Click cell A4 and then while holding down the CTRL key,
click cells A6, A8, A14, and select the range A16:H16
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
47
Changing Font Size, Adding Underlines, Adding
Background Colors, and Adding Drop Shadows to
Nonadjacent Cells
• Click the Fill Color button arrow on the
Formatting toolbar and then click Light
Yellow (column 3, row 5)
• Click the Shadow Style button on the
Drawing toolbar
• Click Shadow Style 14 (column 2m row 4)
on the Shadow palette
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
48
Changing Font Size, Adding Underlines, Adding
Background Colors, and Adding Drop Shadows to
Nonadjacent Cells
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
49
Formatting the Assumptions Table
• Scroll down to view rows 18 through 25 and then click
cell A18
• Click the Font Size box arrow on the Formatting toolbar
and then click 16 in the Font Size list. Click the Italic
button and then click the Underline button on the
Formatting toolbar
• Select the range A18:B25, click the Fill Color button
arrow on the Formatting toolbar, and then click Green
(column 4, row 2) on the Fill Color palette
• Click the Font Color button on the Formatting toolbar to
change the font in the selected range to white
• Click the Shadow Style button on the Drawing toolbar
and then click Shadow Style 14 on the Shadow Style
palette
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
50
Formatting the Assumptions Table
• Click cell D25 to deselect the range
A18:B25
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
51
Hiding the Drawing Toolbar
and Saving the Workbook
• Click the Drawing
button on the
Standard toolbar
• Click the Save button
on the Standard
toolbar
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
52
Drawing a 3-D Pie Chart
on a Separate Chart Sheet
• Select the range B3:G3
• While holding down the CTRL key, select the
range B16:G16
• Click the Chart Wizard button on the Standard
toolbar
• When Excel displays the Chart Wizard – Step 1
of 4 – Chart Type dialog box, click Pie in the
Chart type list and then click the 3-D Pie chart
(column 2, row 1) in the Chart sub-type box
• Click the Next button
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
53
Drawing a 3-D Pie Chart
on a Separate Chart Sheet
• Click the Next button
• When Excel displays the Chart Wizard – Step 3
of 4 – Chart Options dialog box, type SixMonth Projected Operating Income in
the Chart title text box
• Click the Legend tab and then click Show legend
to remove the check mark
• Click the Data Labels tab
• In the Label Contains area, click Category name
and click Percentage to select them
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
54
Drawing a 3-D Pie Chart
on a Separate Chart Sheet
• If necessary, click Show leader lines to
select it
• Click the Next button
• When Excel displays the Chart Wizard –
Step 4 of 4 – Chart Location dialog box,
click As new sheet
• Click the Finish button
• If the Chart toolbar appears, click its Close
button
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
55
Drawing a 3-D Pie Chart
on a Separate Chart Sheet
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
56
Formatting the Chart Title
and Data Labels
• Click the chart title. On the Formatting toolbar,
click the Font Size box arrow, click 28 in the Font
Size list, click the Underline button, click the
Font Color button arrow, and then click Red
(column 1, row 3) on the Font Color palette
• Click one of the five data labels that identify the
slices. On the Formatting toolbar, click the Font
Size box arrow, click 12 in the Font Size list,
click the Bold button, and then click the Font
Color button to change the font to the color red
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
57
Formatting the Chart Title
and Data Labels
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
58
Changing the Colors
of the Pie Slices
• Click the July slice twice (do not double-click).
Click the Fill Color button arrow on the
Formatting toolbar
• Click Orange (column 2, row 2). One at a time,
click the remaining slices and then use the Fill
Color button arrow on the Formatting toolbar to
change each slice to the following colors: August
– Yellow; September – Green; October – Plum;
November – Red; and December – Blue. Click
outside the Chart Area
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
59
Changing the Colors
of the Pie Slices
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
60
Exploding a 3-D Pie Chart
• Click the slice labeled
December twice (do
not double-click)
• Drag the slice to the
desired position
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
61
Rotating and Tilting
the 3-D Pie Chart
• With the December slice selected, click Chart on
the menu bar
• Click 3-D View
• When Excel displays the 3-D View dialog box,
click the up arrow button until 25 shows in the
Elevation box
• Click the Left Rotation button until the Rotation
box displays 270
• Click the OK button. Click outside the chart area
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
62
Rotating and Tilting
the 3-D Pie Chart
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
63
Showing Leader Lines
with the Data Labels
• Click the December data label
twice (do not double-click)
• Point to the upper-left sizing
handle on the box border and
drag the December data label
away from the December slice
• Select and drag the remaining
data labels away from their
corresponding slices as shown
• Click outside the chart area
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
64
Renaming and Reordering the
Sheets, and Coloring Their Tabs
• Double-click the tab labeled Chart1 at the
bottom of the screen
• Type 3-D Pie Chart and then press the
ENTER key
• Right-click the tab
• Click Tab Color on the Shortcut menu
• When Excel displays the Format Tab Color
dialog box, click Red (column 1, row 3) in
the Tab Color area
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
65
Renaming and Reordering the
Sheets, and Coloring Their Tabs
• Click the OK button
• Follow the first two steps, naming Sheet1
Six Month Financial Projection,
and use Light Yellow as the Tab Color
• Drag the Six-Month Financial Projection
tab to the left in front of the 3-D Pie Chart
tab and then click cell E18
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
66
Renaming and Reordering the
Sheets, and Coloring Their Tabs
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
67
Checking Spelling
in Multiple Sheets
• With the Six-Month Financial Projection
sheet active, hold down the CTRL key and
then click the 3-D Pie Chart tab
• Click the Spelling button on the Standard
toolbar
• Correct any errors and then click the OK
button when the spell check is complete
• Click the Save button on the Standard
toolbar
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
68
Previewing and Printing the Workbook
• Ready the printer. If both sheets are not
selected, hold down the CTRL key and then click
the tab of the inactive sheet
• Click File on the menu bar and then click Page
Setup. Click the Page tab and then click
Landscape. Click Fit to in the Scaling area
• Click the Print Preview button in the Page Setup
dialog box. When the preview of the first of the
selected sheets appears, click the Next button at
the top of the Print Preview window to view the
next sheet. Click the Previous button to
redisplay the first sheet
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
69
Previewing and Printing the Workbook
• Click the Print button at the top of the Print
Preview window. When Excel displays the
Print dialog box, click the OK button
• Right-click the Six-Month Financial
Projection tab. Click Ungroup Sheets on
the shortcut menu to deselect the 3-D Pie
Chart tab
• Click the Save button on the Standard
toolbar
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
70
Previewing and Printing the Workbook
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
71
Shrinking and Magnifying the View
of a Worksheet or Chart
• If cell A1 is not active, press CTRL+HOME
• Click the Zoom box arrow on the Standard
toolbar.
• Click 75%
• Click the Zoom box arrow on the Standard
toolbar and then click 100%
• Click the 3-D Pie Chart tab at the bottom of the
screen. Click the Zoom box arrow on the
Standard toolbar and then click 100%
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
72
Shrinking and Magnifying the View
of a Worksheet or Chart
• Enter 66 in the Zoom box to return the
chart to its original magnification
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
73
Splitting a Window into Panes
• Click the Six-Month Financial Projections
tab at the bottom of the screen
• Click cell C7, the intersection of the four
proposed panes
• Click Window on the menu bar
• Click Split on the Window menu
• Use the scroll arrows to show the four
corners of the worksheet at the same time
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
74
Splitting a Window into Panes
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
75
Removing the Panes
from the Window
• Position the mouse pointer at the
intersection of the horizontal and vertical
split bars
• When the mouse pointer changes to a
four-headed arrow, double-click
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
76
Analyzing Data in a Worksheet
by Changing Values
• Use the vertical scroll bar to move the window so cell A6
is in the upper-left corner of the screen
• Drag the vertical split box from the lower-right corner of
the screen to the left so that the vertical split bar is
positioned as shown on the next slide
• Use the right scroll arrow to view the totals in column H
in the right pane
• Click cell B19 in the left pane
• Enter 100000 in cell B19, 10 in cell B22, and 32 in cell
B25
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
77
Analyzing Data in a Worksheet
by Changing Values
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
78
Goal Seeking
• Close the workbook without saving
changes and then reopen it
• Drag the vertical split box to the middle of
column F
• Scroll down so row 6 is at the top of the
screen
• Show column H in the right pane
• Click cell H16, the cell that contains the
six-month total operating income
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
79
Goal Seeking
• Click Tools on the menu bar
• Click Goal Seek
• When Excel displays the Goal Seek dialog
box, click the To value text box, type
7,000,000 and then click the By
changing cell box
• Click cell B25 on the worksheet
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
80
Goal Seeking
• Click the OK button
• Click the Cancel
button in the Goal
Seek Status dialog
box
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
81
Quitting Excel
• Click the Close button on the title bar
• If the Microsoft Excel dialog box is
displayed, click the No button
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
82
Summary
• Rotate text in a cell
• Create a series of month names
• Use the Format Painter button to format
cells
• Copy, paste, insert, and delete cells
• Format numbers using format symbols
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
83
Summary
•
•
•
•
Freeze and unfreeze titles
Show and format the system date
Use absolute cell references in a formula
Use the IF function to perform a logical
test
• Show and dock toolbars
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
84
Summary
• Create a 3-D Pie chart on a separate chart
sheet
• Color and rearrange worksheet tabs
• Change the worksheet view
• Goal seek to answer what-if questions
Excel Project 3: What-If Analysis, Charting, and Working with Large Worksheets
85
Microsoft
Office 2003
Introductory Concepts
and Techniques
Excel Project 3
Complete