299X159 Lecture 1

1
299X-159 Lecture 1

• Introduction to Excel

2
Excel Definitions and Terminology
Title Bar
Standard Toolbar
Name Box
Formula Bar
Pulldown Menus
Row 15
Cell D15
Column D
Sheet Tab
3
Excel Definitions and Terminology
Label (text)
Constant (number)
Formula (function) Notice that the formula in B8
appears in the Formula Bar
4
To show contents of cells, use ctrl or under the
Tools Pulldown menu, choose Formula Auditing
followed by Formula Auditing Mode.
5
Example 1 Use Excel to create the following
Grade Book
6
Ideas used for Example 1

• Entering labels, constants, and formulas
• Formatting cells (font, alignment, number)
• Adjusting column width and row height
• Fill down and fill right
• Built-in functions AVERAGE and SUM

7
Example 2 Use Excel to make a Semester Schedule
8
Ideas used for Example 2

• Fill Handle
• Merge and Center
• Fill Color
• Borders
• Hyperlink

9
Example 3 Use Excel to create a plot of Toad
Growth

• The following table shows the land area in
  Australia colonized by the American marine toad
  (Bufo marinis).
• Plot Year vs. Area on a Cartesian coordinate
  system (Year is on the x axis )
• See Handout!

10
Importing data from a text file

• Often the data we need is given as ASCII
  characters in a text file.
• We can use Excel to open the file and help put
  the data into into more usable form.

11
Importing data from a text file (cont.)

• From Excel, open up the text file that contains
  the data, with commas or spaces between each
  piece of data.
• The Text Import Wizard will appear!

12
The Text Wizard

• Choose Delimited and click Next.
• Choose Comma in the Check-box and click Next.
• Set the column formats and choose Finish.
• The data should be in Excel!

13
Ideas used for Example 3

• Making a chart with the Chart Wizard
• Using F11 to create a chart automatically
• Importing data from a text file

14
Example 4 Create the following Function Table!
15
Example 4 (cont.)

• Creating the first three columns in the table for
  Example 4 with user-defined functions is
  straightforward.
• For the last column, notice that if we let
  x(n) 12 n, then we have
• x(1) 1
• x(2) 3 1 2 x(1) 2
• x(3) 6 1 2 3 x(2) 3
• In general, x(n) x(n-1) n for n 2.
• Functions like this are called recurrence
  relations and can be implemented with Excel!

16
Example 4 (cont.)
17
Example 4 (cont.)

• To print out a table that appears on more than
  one page, choose Page Setup.
• Click on the Sheet tab and choose the rows to
  repeat at the top of each page!

18
Ideas used for Example 4

• Fill handle
• Fill down
• Creating a formula
• Page setup for printing
• Recurrence relation

19
Example 5 Sorting Data

• Excel is excellent for sorting data in lists or
  tables!
• For example, suppose we wish to sort a list of
  famous mathematicians by given Birth Year, Name,
  and Birth Year followed by Name!
• First put the data into an Excel worksheet with
  Birth Year in column A and Name in column B.

20
Example 5 (cont.)

• To sort by Birth Year, click on any cell in the
  Birth Year column (column A).
• Then click on the Sort Ascending button.
• Repeat with column B to sort by Name!

21
Example 5 (cont.)

• Another option is to first highlight the data you
  wish to sort.
• Then click on the Data drop-down menu and choose
  Sort.
• The Sort menu allows recursive sorting in either
  ascending or descending order!

22
Example 5 (cont.)

• When working with data, a useful tool for
  choosing portions of the data is the AutoFilter.
• Highlight the data you wish to study, click on
  the Data drop-down menu, choose Filter, followed
  by AutoFilter.

23
Example 5 (cont.)

• With the AutoFilter, you can look at subsets of
  the data, for example the mathematicians born
  between 1600 and 1899.
• To do so, choose the Conditional filter in the
  first column and fill in the Custom AutoFilter
  accordingly.

24
Ideas used for Example 5

• Sort ascending button
• Sort menu
• AutoFilter

25
Homework 1 (due Monday, 8/27/07)

• Read the Excel Tutorial Handout, take the online
  quiz, and turn in a printed copy of your quiz and
  quiz score.