Excel

1
Excel

• Jianhua Yang
• Department of Math and Computer Science
• Bennett College

2
Goals

• How to create, edit, format a spreadsheet
• Integrate excel and word
• Create a chart, graph
• Function operation

3
1. Get Started with Microsoft Office Excel

• What are spreadsheets?
• Excel and word
• The excel window
• Select a cell and enter data
• Enter a simple addition formula
• Enter values and labels
• Copy a formula
• Print with or without gridlines

4
What are Spreadsheets?

• Are tables
• Are used in business to plan and track budgets
  and projects and to perform financial analysis

5
Cell

• Can be filled with
• Data ( Text label, or number value)
• Formulas

Which tell the spreadsheet program which data to
use and how to calculate results.
6
Workbook

• Excel organizes its spreadsheets into worksheets,
  which combine to make a workbook
• The workbook is saved as a file.

7
Reference

• Within workbook
• Between workbook

Cells in one worksheet can reference and use data
from another worksheet in the same workbook.
Cells in one workbook can reference and use data
from the other workbooks.
8
Representing spreadsheet pictorially

• Line graph
• Pie chart
• Bar chart

9
Excel and word

• Excel object can be inserted into a word file
  seamlessly
• Word objects can also be loaded into a excel file.

10
The Excel Window (show the students)

• Title bar
• Menu bar
• Standard toolbar
• Formatting toolbar
• Name box
• Column headings
• Row headings
• Formula bar
• Worksheet
• Active cell
• Worksheet names
• Status bar
• Task pane
• Control buttons

11
Name box

• Which tells you which cell is selected

12
Formula bar
Insert Function
Name box
Enter
Cancel
Formula area
13
Select a cell and enter data

• Show the students

14
Enter a simple addition formula

• Show the students

15
Enter values and labels

• Values
• Labels

• Values are entries in a cell that can be used in
  formulas
• Values include numbers, dates, and results of
  formulas
• When you type in a cell with one of the digits
  0-9, a period, or a , Excel treats the entry as
  a value.

• Labels are text that is not used in a formula.
• When you type in a cell with any alphabetic
  character or the single quotation mark, Excel
  treats the entry as a label.

16
Try Values and Labels

• Show the students

17
Copy a formula

• We can copy a formula from one cell to another.
  (page 220)
• Show the students how to do it.

18
Printing With or Without Gridlines

• Page setup

19
Exercise

• Handout to the students (Page 222)
• And direct the students to do the exercise.

20
2. Work with Ranges and the Sum Function

• Select rows and columns
• Select a range of cells
• Use auto fill to complete a series
• The sum function
• Use AutoSum
• Copy a formula to a range
• Copy a formula using auto fill
• Ask a question to resolve errors

21
Selecting

• Rows
• Show the students how to do.
• Columns
• Selecting multiply rows, columns, and ranges.
• A Range of cells

22
Use auto fill to complete a series

• The auto fill features is used to complete a
  series of numbers, days, weeks, quarters, or
  years.
• You need two values that Excel can use to
  determine constructing the series.
• After creating it, you can change it.

23
The Sum Function

• If you sum a lot of cells, do not type them one
  by one
• Using sum function
• Using AutoSum.

24
Copy a formula to a Range

• Select a cell, and copy the formula
• Select a range, and enter.

25
Copy a formula using auto fill

• Select a cell
• Click and drag the Auto Fill Handle to the right
  cell.

26
Exercise 2

• Handout for the students.
• Do the exercise with the students.

27
3. Use the Toolbar and the AutoSum Drop-Down Menu

• Format numbers
• Format Labels
• Align Labels
• Use Functions
• Use the AutoSum Drop-Down Menu

28
Format Numbers

• Excel uses a number format called general as its
  default format.
• Change the way numbers display, we use
• Number formatting buttons on the formatting
  toolbar
• The Excel formatting toolbar.

29
Formatting a Range

• Using common style Button
• Select a range first
• And then use Comma Style Button ,.
• Using Currency Style button
• Using percent style button

30
Format Labels, Align Labels

• Show the students how to do them.

31
Working with other functions

• Average
• MAX
• MIN
• COUNT
• COUNTA

32
4. Manage Columns and Rows and Sort Data

• Adjust column widths
• Insert rows or columns
• Add a title to a worksheet
• Sort data

33
5. Use Absolute References and the Print Dialog
Box

• Relative and absolute references
• Change print orientation
• Print to fit on one page

34
Relative and Absolute Reference

• We have used relative reference in formula
  copying
• Absolute reference (E12)

35
Change Print Orientation

• From Portrait to Landscape

36
Print to Fit on One Page

• Rather than change orientation, you can specify
  that Excel fit the worksheet on a single portrait
  page.

37
6. Use Chart Wizard

• Create pie chart
• Create a bar chart
• Create a line graph
• Edit a line graph

38
Create Pie Charts

• A pie chart is a circle divided into segments.
• It can increase the impact of your message by
  including pictorial representations of your
  spreadsheet data.

39
Create a Pie Chart

• Step1 Select the data, and the chart type
• Step2 Specify the Chart Source Data Range
• Step3 Specify Chart Options
• Step4 Specify Chart Location.

40
Edit the Chart Title

• Show the students how to do it.

41
Create a Bar Chart

• Bar and column charts are good tools for
  illustrating comparisons of data
• Excel works with two data series one series is
  shown along the x-axis, another series is shown
  along the y-axis.

42
Create a Line Graph

• A line graph is a good tool to illustrate a trend
  or the changes in values through time.

43
7.Use Insert Function Use the IF Function

• Use insert function feature
• About the IF function
• Understand Operators
• Insert the IF function

44
Use Insert Function Feature

• Two ways to insert a function
• Using fx
• Using
• Show the students how to use them.

45
About IF Function

• IF function lets you ask a question of data and
  make the result conditional on that data
• Format
• IF( Condition, , )

Action to take if condition is NOT met
then
Action to take if condition is met
else
46
Examples about IF Function

• Suppose A16
• IF(A16, YES, NO)
• Suppose A1Tom
• IF(A1Tom, Math, Physics)
• IF(A1gtB1, A1B1, A1-B1)

47
Operators

• , -, , /, ,
• , gt, lt, gt, lt, ltgt

48
Exercise on the book P296
49
8. Integrate Excel and Word

• Insert an Excel object into a Word file
• Insert a Word table into an Excel Worksheet

50
Inset a graph to a word file

• Show the students with example on Page302

51
Insert a table from word to Excel

• Show the students with example on Page 302-303

52
9. Challenge Exercises

• Structure of a Checkbook Register
• Create a Check Register
• Insert an Excel Spreadsheet into Word

53
Summary

• How to use Excel to create a spreadsheet.
• Computational, Graphic and statistical function.