Excel Chapter 9

About This Presentation

Title:

Excel Chapter 9

Description:

When Excel displays the Import Text File dialog box, select UDISK 2.0 (E:) in the Address bar to display the text files on drive E. – PowerPoint PPT presentation

Number of Views:129

Avg rating:3.0/5.0

Slides: 81

Provided by: csusmEdu

Learn more at: https://public.csusm.edu

Category:

more less

Transcript and Presenter's Notes

Title: Excel Chapter 9

1
Excel Chapter 9

Importing Data, Working with Pivot Charts,
PivotTables, and Trendlines

2
Objectives

Import data from a text file, an
Access database, a Web page, and a Word Document
Transpose data while pasting it
Convert text to columns
Replicate formulas
Explain sharing and collaboration techniques
Insert, edit, and delete a comment

3
Objectives

Track changes and share a workbook
Accept and reject tracked changes made to a
workbook
Analyze worksheet data using a PivotTable and a
PivotChart
Compare and merge shared workbooks
Analyze worksheet data using a trendline
Save a custom view of a worksheet and format a
worksheet background

4
Planning Ahead

Analyze the existing workbook and the formats of
the data to be imported
Evaluate the various options Excel provides for
collaboration
Visualize your workbook in various PivotTable and
PivotChart layouts
Identify the trend or trends to analyze with a
trendline
Gather workbooks to be merged and then assess any
differences in the workbooks that are in
disagreement

5
Starting Excel and Opening a Workbook

Start Excel
Open the file FasToast Ovens Analysis from the
Data Files for Students and then save the
workbook using the file name, FasToast Ovens
Analysis1. h
If the Excel window is not maximized,
double-click its title bar to maximize it
If the worksheet window in Excel is not
maximized, click the Maximize button next to the
Close button on its title bar to maximize the
worksheet within Excel

6
Starting Excel and Opening a Workbook
7
Importing data from a Text File into a Worksheet

With the Repair Revenue Analysis worksheet
active, if necessary, select cell A3
Click the Data tab on the Ribbon
Click the From Text button on the Ribbon to
display the Import Text File dialog box. If you
have more than one Excel add-in installed, such
as Solver, the From Text button may appear on a
submenu that is displayed when you click the Get
External Data button. The Get External Data group
on the Ribbon may be collapsed to a single Get
External Data button
When Excel displays the Import Text File dialog
box, select UDISK 2.0 (E) in the Address bar to
display the text files on drive E

8
Importing data from a Text File into a Worksheet

Double-click the file name, FasTo Ovens North
Dakota Revenue 2008, to display the Text Import
Wizard - Step 1 of 3 dialog box
Click the Next button to display the Text Import
Wizard - Step 2 of 3 dialog box
When Excel displays the Text ImportWizard - Step
2 of 3 dialog box, click Comma in the Delimiters
area to display the data fields correctly in the
Data preview area
Click Tab in the Delimiters area to clear the
check box
Click the Next button to display the TextImport
Wizard -Step 3 of 3 dialog box

9
Importing data from a Text File into a Worksheet

Click the Finish button to display the Import
Data dialog box
Click the Properties button
When Excel displays the External DataRange
Properties dialog box, click Adjust column
width in the Data formatting and layout area
to clear the check box
Click the OK button
When Excel displays the Import Data dialog box,
click the OK button to import the data from the
text file into the worksheet beginning at cell A3

10
Importing data from a Text File into a Worksheet
11
Importing Data from an Access Table into a
Worksheet

Select cell A8
With the Data tab active, click the From Access
button on the Ribbon to display the Select Data
Source dialog box
When Excel displays the Select Data Source dialog
box, select UDISK 2.0 (E) in the Address bar to
display the Access database files on drive E
Double-click the file name, FasToast Ovens South
Dakota Revenue 2008 in the Name list to display
the Import Data dialog box
Click the OK button to import the data in the
database to a table in the range A8G13

12
Importing Data from an Access Table into a
Worksheet

Right-click cell A8 and then point to the Table
on the shortcut menu
Click Convert to Range
When Excel displays the Microsoft Office Excel
dialog box, click the OK button to convert the
table to a range
Right-click the row heading for row 8 to display
the shortcut menu
Click Delete on the shortcut menu to delete row 8

13
Importing Data from an Access Table into a
Worksheet

Drag through the column headings of column A
through G in the column heading area to select
the columns
Double-click the column separator in the column
headings area between columns A and B to change
the widths of each column to best fit
If necessary, click the Home tab on the Ribbon
and then select the range A7G7
Click the Format Painter button on the Ribbon and
then drag though the range A8G12 to copy the
formats of the selected range to the range A8G12
Select cell A13

14
Importing Data from an Access Table into a
Worksheet
15
Importing Data from a Web Page into a Worksheet

With cell A13 selected, click the Data tab on the
Ribbon
Click the From Web button on the Ribbon to
display the New Web Query dialog box
When Excel displays the New Web Query dialog box,
type e\FasToastOvensNebr askaRevenue2008.htm in
the Address bar and then click the Go button to
display the Web page in the preview area
Click the Click to select this table arrow to
select the HTML table containing the Nebraska
repair revenue report
Click the Import button to display the Import
Data dialog box and display a marquee around cell
A13

16
Importing Data from a Web Page into a Worksheet

Click the Properties button to display the
External Data Range Properties dialog box
When Excel displays the External Data Range
Properties dialog box, click Adjust column
width in the Data formatting and layout area
to clear the check box
Click the OK button
When Excel displays the Import Data dialog box,
click the OK button to import the data from the
Web page into the worksheet beginning at cell A13
Drag through the row 13 and 14 row headings and
then right-click to display the shortcut menu
Click Delete on the shortcut menu to delete rows
13 and 14, which contained the column headings
from the Web Page

17
Importing Data from a Web Page into a Worksheet
18
Copying and Transposing Data from a Word Document
to a Worksheet

Click the Home tab on the Ribbon and then select
cell A24
Start Word and then open the Word document named,
FasToast Ovens Kansas Revenue 2008, from the Data
Files for Students
In Word, if necessary, click the Home tab on the
Ribbon
Drag through all of the cells in the second
through last columns in the table in the Word
document and then click the Copy button on the
Ribbon to copy the contents of the table to the
Office Clipboard
Quit Word and, if necessary, click the FasToast
Ovens Analysis1 workbook taskbar button to make
it active
Click the Paste button arrow on the Ribbon to
display the Paste menu

19
Copying and Transposing Data from a Word Document
to a Worksheet

Click Paste Special on the Paste menu to display
the Paste Special dialog box
When the Paste Special dialog box appears, click
Text in the As list
Click the OK button in the Paste Special dialog
box to paste the contents of the Office Clipboard
to the range A24E29
Select the range A24E29 and then click the Copy
button on the Ribbon
Select cell A18
Click the Paste button arrow on the Ribbon and
then point to Transpose on the Paste menu
Click Transpose on the Paste menu to transpose
and paste the copied cells to a range beginning
with cell A18

20
Copying and Transposing Data from a Word Document
to a Worksheet
21
Converting Text to Columns

If necessary, click the Home tab on the Ribbon
Select the range A24E29 and press the DELETE key
to delete the range
Select the range B18F22 and then click the Cut
button on the Ribbon to copy the range to the
Office Clipboard
Select cell C18 and then click the Paste button
on the Ribbon
Select the range C17G17 and then click the
Format Painter button on the Ribbon
Select the range C18G22 to copy the formats in
range C17G17 down to the corresponding columns
of range C18G22

22
Converting Text to Columns

Select the range A18A22 and then click the Data
tab on the Ribbon
Click the Text to Columns button on the Ribbon to
display the Convert Text to Columns Wizard - Step
1 of 3 dialog box
Click the Next button to display the Convert Text
to Columns Wizard - Step 2 of 3 dialog box
When Excel displays the Convert Text to Columns
Wizard - Step 2 of 3 dialog box, click Comma in
the Delimiters area to display the data fields
correctly in the Data preview area
Click Tab in the Delimiters area to clear the
check box
Click the Finish button to close the Convert Text
to Columns Wizard - Step 2 of 3 dialog box and
separate the data in column A to two columns

23
Converting Text to Columns
24
Replicating Formulas

Select the range H3I3
Drag the fill handle down through row 22 to copy
the two formulas to the range H4I22 and display
the new values for the Revenue per Repair Person
2007 and Revenue per Repair Person 2008 columns
Click the Save button on the Quick Access Toolbar
to save the workbook using the file name,
FasToast Ovens Analysis1.

25
Replicating Formulas
26
Sharing a Workbook and Collaborate on a Workbook

Click the Review tab on the Ribbon
Click the Share Workbook button on the Ribbon to
display the Share Workbook dialog box
When Excel displays the Share Workbook dialog
box, click Allow changes by more than one user
at the same time
Click the OK button
When Excel displays the Microsoft Office Excel
dialog box, click the OK button to place the
workbook in share mode

27
Sharing a Workbook and Collaborate on a Workbook

If possible, have a classmate open a second copy
of the workbook
With a second copy of the workbook open, click
the Share Workbook button on the Review tab on
the Ribbon to display the Share Workbook dialog
box, which lists all users who currently have the
workbook open
Click the OK button
Ask the second workbook user to select G13, enter
183,540 as the new value, and then save the
workbook
Click the Save button on the Quick Access Toolbar
to display the Microsoft Office Excel dialog box
indicating that the workbook has been updated
with changes saved by another user
Click the OK button

28
Sharing a Workbook and Collaborate on a Workbook

Point to the blue triangle in cell G13 to display
the comment indicating the other users changes
Ask the second user of the workbook to close the
workbook
Click the Review tab on the Ribbon and then click
the Share Workbook button on the Ribbon to
display the Share Workbook dialog box
When Excel displays the Share Workbook dialog
box, click Allow changes by more than one user
at the same time to clear the check box
Click the OK button to stop sharing the workbook
If Excel displays the Microsoft Office Excel
dialog box, click the Yes button

29
Sharing a Workbook and Collaborate on a Workbook
30
Inserting a Comment

Right-click cell E13 to display the shortcut menu
Click Insert Comment on the shortcut menu to open
a comment box next to the selected cell and
display a comment indicator in the cell
When Excel displays the comment box, enter the
comment
Click anywhere outside the comment box to close
the comment box and display a red comment
indicator in cell E13
Click the Save button on the Quick Access Toolbar

31
Inserting a Comment
32
Turning on Track Changes

If necessary, click the Review tab on the Ribbon
and then click the Track Changes button on the
Ribbon to display the Track Changes menu
Click Highlight Changes on the Track Changes menu
to display the Highlight Changes dialog box
When Excel displays the Highlight Changes dialog
box, click Track changes while editing
If necessary, click all of the check boxes in the
Highlight which changes area to clear them
Click the OK button
When Excel displays the Microsoft Office Excel
dialog box, click the OK button to save the
workbook
Close the workbook

33
Turning on Track Changes
34
Opening a Workbook and Reviewing Tracked Changes

With Excel active, open the file, FasToast Ovens
Analysis2, from the Data Files for Students
Click the Review Tab on the Ribbon and then click
the Track Changes button on the Ribbon to display
the Track Changes menu
Click Highlight Changes on the Track Changes menu
When Excel displays the Highlight Changes dialog
box, click When to clear the check box
Click the OK button
Click the Show All Comments button on the Ribbon
to display all comments
Point to the blue triangle in cell G5 to display
the comment box with information about the change
made to the cell G5

35
Opening a Workbook and Reviewing Tracked Changes

Click the Track Changes button on the Ribbon to
display the Track Changes menu
Click Accept/Reject Changes on the Track Changes
menu
If necessary, when Excel displays the Select
Changes to Accept or Reject dialog box, click all
check boxes to clear them, indicating that all
changes in the change history file should be
reviewed
Click the OK button to display the first tracked
change
Click the Accept button
As Excel displays each change in the Accept or
Reject Changes dialog box, click the Accept
button.
Right-click cell A18 and then click Delete
Comment on the shortcut menu
Right-click cell E13 and then click Delete
Comment on the shortcut menu

36
Opening a Workbook and Reviewing Tracked Changes
37
Saving the Workbook with a New File Name

Click the Office Button then click Save As on the
Office Button menu
When Excel displays the Save As dialog box, type
FasToast Ovens Analysis3 in the File name text
box
Click the Save button in the Save As dialog box
to save the workbook with the new file name

38
Turning Off Track Changes

Click the Track Changes button on the Ribbon, and
then click Highlight Changes on the Track Changes
menu
When Excel displays the Highlight Changes dialog
box, click Track Changes while editing to clear
the check box
Click the OK button
When Excel displays the Microsoft Office Excel
dialog box asking if the workbook should be made
exclusive, click the Yes button

39
Creating a PivotTable

Select cell A3 and then click the Insert tab on
the Ribbon
Click the PivotTable button arrow on the Ribbon
to display the PivotTable menu
Click PivotTable to display the Create PivotTable
dialog box
Click the OK button to create the PivotTable on a
new worksheet and display the PivotTable Field
List pane

40
Creating a PivotTable
41
Adding Data to the PivotTable

Drag the Shop Type field from the Choose fields
to add to report list to the Row Labels area to
add the Shop Type field to the PivotTable
Drag the State field from the Choose fields to
add to report list below the Shop Type button in
the Row Labels area to add the State field to the
PivotTable
Drag the Revenue per Repair Person 2007 field to
the Values area to add the field to column B of
the PivotTable
Drag the Revenue per Repair Person 2008 field to
the Values area to add the field to column C of
the PivotTable

42
Adding Data to the PivotTable
43
Changing the View of a PivotTable

Drag the Shop Type button in the Row Labels area
below the State button to group revenue by shop
type, rather than by state
Click the Sum of Revenue per Repair Person 2007
button arrow in the Values area to display the
shortcut menu
Click Remove Field on the shortcut menu to remove
Sum of Revenue per Repair Person 2007 from the
PivotTable
Click the Undo button on the Quick Access Toolbar
to undo the removal of Sum of Revenue per Repair
Person 2007 column from the PivotTable
Click the Row Labels AutoFilter button in cell A4
to display the Row Labels AutoFilter menu

44
Changing the View of a PivotTable

When Excel displays the Row Labels AutoFilter
menu, click the check boxes for Kansas and North
Dakota to clear them
Click the OK button to display only the Nebraska
and South Dakota data in the PivotTable
Click the Undo button on the Quick Access Toolbar
to display the data for all states

45
Changing the View of a PivotTable
46
Filtering a PivotTable Using a Page Field

Drag the State button in the Row Labels area to
the Report Filter area in the PivotTable Field
List pane to create a new page field in the
PivotTable
Click the State AutoFilter button in cell B1,
click South Dakota on the AutoFilter menu, and
then click the OK button to display totals for
South Dakota

47
Filtering a PivotTable Using a Page Field
48
Formatting a PivotTable

Double-click the Sheet1 tab
Type PivotTable and then press the ENTER key.
Select cell A4, right-click the PivotTable tab,
and then point to Tab Color on the shortcut menu.
Click Olive Green, Accent 3 (column 7, row 1) in
the Theme Color area
If necessary, select a cell in the PivotTable and
then click the Design tab on the Ribbon

49
Formatting a PivotTable

Click the PivotTable Styles More button to
display the PivotTable Styles gallery
Point to the Pivot Style Medium 11 PivotTable
style in the PivotTable Styles gallery to view a
preview of the style in the PivotTable
Click the Pivot Style Medium 11 PivotTable style
to apply the style to the PivotTable
Right-click anywhere in cell B4 to display the
shortcut menu
Click Number Format on the shortcut menu to
display the Format Cells dialog box

50
Formatting a PivotTable

When Excel displays the Format Cells dialog box,
if necessary, click Currency in the Category
list, type 0 in the Decimal places box, and then
click the OK button
Right-click anywhere in cell C4 to display the
shortcut menu
Click Number Format on the shortcut menu to
display the Format Cells dialog box
When Excel displays the Format Cells dialog box,
click Currency in the Category list, type 0 in
the Decimal places box, and then click the OK
button to format the numeric values in the
PivotTable with the Currency style
Select cell A9 to deselect the worksheet
Click the Save button on the Quick Access toolbar
to save the workbook

51
Formatting a PivotTable
52
Switching Summary Functions in a PivotTable

Select cell B4 and then drag the State button in
the Report Filter area above the Shop Type button
in the Row Labels area
Right-click cell B4 to display the shortcut menu
Point to Summarize Data By on the shortcut menu
Click Average on the Summarize Data By submenu to
change the numbers in column B from sums to
averages
Right-click cell C4 to display the shortcut menu

53
Switching Summary Functions in a PivotTable

Point to Summarize Data By on the shortcut menu
Click Average on the Summarize Data By submenu to
change the numbers in column C from sums to
averages
Click cell A19 to deselect the PivotTable and
close the PivotTable Field List

54
Switching Summary Functions in a PivotTable
55
Creating a PivotChart, Changing the PivotChart
Type, and Formatting the Chart

Select cell A4
Click the Options tab on the Ribbon and then
click the PivotChart button on the Ribbon to
display the Insert Chart dialog box
Click Clustered Cylinder (column 1, row 2) in the
Column chart type gallery
Click the OK button to add the chart to the
PivotTable worksheet
With the chart selected, click the Move Chart
button on the Ribbon to display the Move Chart
dialog box
When Excel displays the Move chart dialog box,
click New sheet to select it and then type
PivotChart in the New sheet text box

56
Creating a PivotChart, Changing the PivotChart
Type, and Formatting the Chart

Click the OK button
Right-click the PivotChart tab and then point to
Tab Color on the shortcut menu
Click Purple, Accent 4 (column 8, row1) to change
the color of the PivotChart sheet tab
Click the Layout tab on the Ribbon and then click
the Chart Title button on the Ribbon to display
the Chart Title menu
Point to Above Chart on the Chart Title menu
Click Above Chart on the Chart Title menu
When the title is placed above the chart, select
the title text. Type FasToast Ovens Analysis and
then click anywhere on the chart to add the title
to the chart

57
Creating a PivotChart, Changing the PivotChart
Type, and Formatting the Chart
58
Changing the View of a PivotChart

Drag the Shop Type button in the Row Labels above
the State button to group the data by state
within shop type
Click the PivotTable sheet tab

59
Changing the View of a PivotC hart
60
Closing the Workbook

Click the workbook Close button
When Excel displays the Microsoft Office Excel
dialog box, click the Yes button

61
Comparing Workbooks

Open the file, FasToast Ovens Travel Expenses,
from the Data Files for Students
Open the file, FasToast Ovens Travel Expenses
Maura, from the Data Files for Students.
Click the View tab on the Ribbon Click the View
Side by Side button on the Ribbon to display the
workbooks side by side. Depending on how previous
Excel windows were arranged on your computer, the
workbooks may appear next to each other
left-to-right
Use the scroll bar in the top window to scroll
the FasToast Ovens Travel Expenses Maura
worksheet

62
Comparing Workbooks

Click the View Side by Side button on the Ribbon
again
Click the workbook Close Window button to close
the FasToast Ovens Travel Expenses Maura workbook
If Excel displays the Microsoft Office Excel
dialog box, click the No button
If necessary, click the Maximize button in the
FasToast Ovens Travel Expenses window

63
Comparing Workbooks
64
Merging Workbooks

Click the Customize Quick Access Toolbar button
arrow next to the Quick Access Toolbar and then
click More Commands on the Customize Quick Access
Toolbar menu
When the Excel Options dialog box appears, select
All Commands in the Choose commands from list
Scroll to the Compare and Merge Workbooks
command in the list on the left and then select
it
Click the Add button to add the Compare and Merge
Workbooks button to the list on the right side of
the dialog box
Click the OK button to add the Compare and Merge
Workbooks button to the Quick Access Toolbar

65
Merging Workbooks

Click the Compare and Merge Workbooks button on
the Quick Access Toolbar to display the Select
Files to Merge Into Current Workbook dialog box
Select UDISK 2.0 (E) in the Address bar
Click FasToast Ovens Travel Expenses Stanley,
hold down the SHIFT key, and then click FasToast
Ovens Travel Expenses Maura to select both files
Click the OK button to merge the workbooks

66
Merging Workbooks
67
Turning Off Workbook Sharing and Saving the
Workbook

Click the Review tab on the Ribbon and then click
the Share Workbook button on the Ribbon
When Excel displays the Share Workbook dialog
box, click Allow changes by more than one user
at the same time to clear the check box
Click the OK button
If Excel displays the Microsoft Office Excel
dialog box, click the Yes button
Click the Save button on the Quick Access Toolbar

68
Adding a Trendline to a Chart

Select the chart by clicking the shaded area
within the chart
Click the Design tab on the Ribbon
Click the Change Chart Type button on the Ribbon
to display the Change Chart Type dialog box
Click the Line with Markers chart type in the
Change Chart Type dialog box

69
Adding a Trendline to a Chart

Click the OK button to change the Travel Expenses
chart to a line with markers chart
With the chart selected, click the Layout tab on
the Ribbon
Click the Trendline button on the Ribbon to
display the Trendline menu
Click Linear Forecast Trendline to add a
trendline to the chart with a 2-period forecast

70
Adding a Trendline to a Chart
71
Saving a Custom View of a Workbook

Resize the FasToast Ovens Travel Expenses
workbook window as shown in Figure 991
Click the View tab on the Ribbon
Click the Custom Views button on the Ribbon to
display the Custom Views dialog box
Click the Add button to display the Add View
dialog box
Type Expenses in the Name text box

72
Saving a Custom View of a Workbook

Click the OK button
Click the Maximize button on the FasToast Ovens
Travel Expenses workbook title bar
Click the Custom Views button on the Ribbon to
display the Custom Views dialog box
Click Expenses in the Views list and then click
the Show button to resize the workbook to the
size saved in the Expenses view

73
Saving a Custom View of a Workbook
74
Formatting a Worksheet Background

C lick the Maximize button on the FasToast Ovens
Travel Expenses workbook title bar
C lick the Page Layout tab on the Ribbon
C lick the Background button on the Ribbon to
display the Sheet Background dialog box
If necessary, select UDISK 2.0 (E) in the
Address bar
Click the file name, FasToast Travel

75
Formatting a Worksheet Background

C lick the Background button on the Ribbon to
display the Sheet Background dialog box
If necessary, select UDISK 2.0 (E) in the
Address bar
C lick the file name, FasToast Travel

76
Formatting a Worksheet Background
77
Quitting Excel