Excel Project 3 - PowerPoint PPT Presentation

About This Presentation

Title:

Excel Project 3

Description:

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 ... – PowerPoint PPT presentation

Number of Views:51

Avg rating:3.0/5.0

Slides: 87

Provided by: Steven673

Learn more at: http://www.sci.brooklyn.cuny.edu

Category:

more less

Transcript and Presenter's Notes

Title: Excel Project 3

1
Excel Project 3

What-If Analysis, Charting, and Working with
Large Worksheets

2
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

3
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

4
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

5
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

6
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

7
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 USB flash drive connected, 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 USB flash drive in the Save
in list. Click the Save button in the Save As
dialog box

8
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

9
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

10
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 C3G3
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

11
Rotating Text and Using the Fill Handle to Create
a Series of Month Names
12
Copying a Cells 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

13
Copying a Cells Format Using the Format Painter
Button
14
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)

15
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 A4A18 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 A9A13 and then click the
Increase Indent button on the Formatting toolbar

16
Increasing Column Widths and Entering Row Titles

Click cell A19

17
Copying a Range of Cells to a Nonadjacent
Destination Area

Select the range A9A13 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

18
Copying a Range of Cells to a Nonadjacent
Destination Area
19
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

20
Inserting a Row
21
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

22
Entering a Number with a Format Symbol
23
Freezing Column and Row Titles

Press CTRLHOME 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

24
Freezing Column and Row Titles
25
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

26
Entering the Projected Monthly Total Net Revenue
27
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

28
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

29
Entering and Formatting the System Date
30
Entering a Formula Containing Absolute Cell
References

Press CTRLHOME 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

31
Entering a Formula Containing Absolute Cell
References

Click the Enter box in the formula bar

32
Entering an IF Function

Click cell B9. Type if(b4gtb24, b19,0 in
the cell
Click the Enter box in the formula bar

33
Entering the Remaining July Formulas

Enter the remaining formulas, as instructed on
page EX 173

34
Copying Formulas with Absolute Cell References
Using the Fill Handle

Select the range B5B16 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 C5G16

35
Determining Row Totals in Nonadjacent Cells

Select the range H5H16. Hold down the CTRL key
and select the range H9H14 and cell H16
Click the AutoSum button on the Standard toolbar

36
Unfreezing the Worksheet Titles and Saving the
Workbook

Press CTRLHOME 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

37
Assigning Formats to Nonadjacent Ranges

Select the range B4H4
While holding down the CTRL key, select the
nonadjacent ranges B6H6, B9H9, B14H14, and
B16H16 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

38
Assigning Formats to Nonadjacent Ranges

Click the OK button
Select the range B5H5
While holding down the CTRL key, select the range
B10H13, and then release the CTRL key
Right-click the selected range
Click Format Cells on the shortcut menu

39
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 CTRLHOME to select cell A1

40
Assigning Formats to Nonadjacent Ranges
41
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

42
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 A1H2 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

43
Formatting the Worksheet Titles
44
Displaying the Drawing Toolbar

Click the Drawing button on the Standard toolbar

45
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

46
Adding a Drop Shadow

With the range A1H2 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 A1H2

47
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
B5H5 and B13H13 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 A16H16

48
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

49
Changing Font Size, Adding Underlines, Adding
Background Colors, and Adding Drop Shadows to
Nonadjacent Cells
50
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 A18B25, 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

51
Formatting the Assumptions Table

Click cell D25 to deselect the range A18B25

52
Hiding the Drawing Toolbar and Saving the
Workbook

Click the Drawing button on the Standard toolbar
Click the Save button on the Standard toolbar

53
Drawing a 3-D Pie Chart on a Separate Chart Sheet

Select the range B3G3
While holding down the CTRL key, select the range
B16G16
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

54
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 Six-Month
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

55
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

56
Drawing a 3-D Pie Chart on a Separate Chart Sheet
57
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

58
Formatting the Chart Title and Data Labels
59
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

60
Changing the Colors of the Pie Slices
61
Exploding a 3-D Pie Chart

Click the slice labeled December twice (do not
double-click)
Drag the slice to the desired position

62
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

63
Rotating and Tilting the 3-D Pie Chart
64
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

65
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

66
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

67
Renaming and Reordering the Sheets, and Coloring
Their Tabs
68
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

69
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

70
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

71
Previewing and Printing the Workbook
72
Shrinking and Magnifying the View of a Worksheet
or Chart

If cell A1 is not active, press CTRLHOME
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

73
Shrinking and Magnifying the View of a Worksheet
or Chart

Enter 66 in the Zoom box to return the chart to
its original magnification

74
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

75
Splitting a Window into Panes
76
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

77
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

78
Analyzing Data in a Worksheet by Changing Values
79
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

80
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

81
Goal Seeking