IT

1
IT som værktøj

• Bent Thomsen
• Institut for Datalogi
• Aalborg Universitet

2
Introduction to Spreadsheets

• Bent Thomsen

3
What is an electronic spreadsheet?

• It is the electronic equivalent of an accounting
  worksheet, comprised of rows and columns to allow
  you to do many tasks in the organization of
  numbers in a clear, easy to understand format

4
What is an electronic spreadsheet?

• It is a tool to help you calculate budgets, do
  economic analysis, statistics, planning,
  engineering calculations,
• Replaces pen, paper and pocket calculator
• Can show diagrams and graphs
• Can input data from other programs
• Can output data to other programs

5
Some Advantages of Spreadsheets

• Spreadsheets are capable of exploring
  what-ifscenarios (e.g. budgets, submitting
  bids)
• Once it is set up properly, the user can save
  time by never having to set up the spreadsheet
  again
• Blank spreadsheets are called templates.
• Monthly salaries,grade sheets

6
Popular Spreadsheets

• Quattro Pro
• Lotus 1-2-3
• StarOffice Calc
• Microsoft Excel

7
Spreadsheet terminology

• Row - horizontal axis (designated by numbers)
• Column - vertical axis (designated by letters)
• Cell - intersection of row and column (designated
  by an address comprised of the column letter and
  row number e.g. A1)
• Block//Range - a rectangular group of one or more
  cells (identified by block coordinates (e.g.
  A1G4)

8
Spreadsheet terminology (cont.)

• Label - alphanumeric
• Value - a number or formula result
• Formula - creates relationships among other cells
• Template - a notebook that has labels, formulas,
  and all of the formatting but no actual data
  (e.g. actual figures and numbers)

9
How big is a spreadsheet?

• Normally you see 9 columns and 18 rows
• 162 cells
• One sheet has 256 columns and 65536 rows
• 1677216 cells
• That is more than 103000 screens
• Would take 34000 A4 pages to print
• Take 194 days to fill at one cell pr second

10
Starting Excel

• Menu bar
• Blank worksheet in document window

11
Exploring the Excel Screen
Title bar
Menu toolbar
Standard toolbar
Formatting toolbar
Screen Tip
Active worksheet in workbook window
Task Pane organizes related commands
12
Activating Toolbars
Click on View and Toolbars
Toolbars sub-menu appears
Click on desired toolbar
Check indicates active item click to deactivate
13
Moving Around the Worksheet

• Working in an active cell(intersection of a row
  and column)

Insertion point where text will be entered
I-beam to place insertion point
Cell pointer
14
Moving Around the Worksheet

• Move cell pointer
• arrow keys
• scroll bars
• Change pages
• click on tabs
• tab scroll buttons

15
Moving Around the Worksheet

• Consider cell B4 active
• Note
• thick cross mouse pointer
• row, column buttons highlighted
• After scrolling to right, note
• row button still highlighted
• name box still shows B4 as active cell

16
Moving Around the Worksheet

• Using the Go To dialog box
• Named cell references showhere
• specify name or cell reference
• Special buttonto go to other kinds of objects

17
Moving Around the Worksheet

• To select a column
• Click on the column heading button
• Whole column is highlighted

18
Entering Labels

• Click desired cell to make it active
• Label is displayed both in cell and in formula
  bar as you type
• Label displays out of its column
• as long as other columns are empty

19
Worksheet with Labels

• Note
• Documentationsection
• Label cut off, next celloccupied
• Labels aligned left

20
Editing a Cell's Information

• Click on desired cell
• Cell pointer moves there
• Contents displayed in formula bar
• Click mouse pointer (I-beam) to location within
  text
• type, delete, copy, paste as needed

I
21
Entering Values

• When entering numbers
• do not use commas
• numbers are right justified by default
• To proceed to next cell right use Tab or right
  arrow key
• To proceed down, use Enter key

22
Entering Formulas

• Formulas are mathematical equations
• perform calculations
• always start with an equal sign ()
• Formula shows informula bar
• Note color referencesin formula

. . .
23
Entering Formulas

• After formula entered and cell pointer moved
• Formula does not show in formula bar
• Result of calculations shows in cell where
  formula entered

24
Operators

• - exponents
• - addition
• - multiplication
• / - division
• - - subtraction
• - function

25
Example

• 513
• 8

26
Order Calculations are Performed

• First exponents
• Then any multiplication and division in the order
  they occur
• Then any addition and subtraction in the order
  they occur

27
Parentheses

• Operations within parentheses are performed
  before those outside.
• Within the parentheses the basic rules are
  followed.
• Multiple sets of parentheses, the innermost are
  executed first followed by the next set.

28
Built-in functions

• Functions are pre-written formulas
• Functions must start with an equal sign
• Functions takes value(s), perform an operation,
  and returns a value(s)
• Values you use with a function are arguments
• AVERAGE(D3D7)
• AVERAGE is the function
• D3D7 is the argument

29
Using Functions

• Advantages of predefined functions
• save time
• more accurate
• Using AutoSum
• Click cell atbottom of column
• Click AutoSumbutton
• Excel assumesit should totalthe column
• SUM functioninserted

30
Using Functions

• AutoSum can also be used to right of a row of
  numbers

31
Using Functions

• Note end results of using AutoSum
• Note
• Click AutoSum button once to display
  formula,again to apply
• SUM formuladisplays in Formula bar

32
Using the Function Insert Feature

• Click on Insert, and Function
• Insert Function dialog box appears

Select function category
Choose specific function desired
33
Using the Function Insert Feature
34
Using the Function Insert Feature
35
Creating a Chart

• Select series of numbers from worksheet
• Click Chart Wizard button
• Dialog box opens
• Choose charttype, sub-type
• Note previewbutton

36
Creating a Chart

• Step 2
• Review and change series range asneeded
• Click CategoryLabelsbutton to specifysource of
  labelsfor chart

37
Creating a Chart

• Labels now show inlegend
• Range for labelsnow displayed

38
Creating a Chart

• Step 3
• Enter titles (whichwill show on preview)
• Specify legend detailson legend tab
• Specify Data Label details as shown

39
Creating a Chart

• Step 4
• Specify where chart will appear
• Click Finish

40
Creating a Chart

• Chart is displayed as object in worksheet

Note Chart toolbar displayed while chart is
selected
41
Moving, Resizing a Chart

• With chart selected, mouse cursor changes to
  when mouse key pressed
• This is the "movement pointer"

42
Formatting a Chart

• Chart features may be altered after the fact
• Colors, patterns, fill effects, fonts, borders
• Area to be formatted must be selected
• Be careful to click directly on the chart
  element
• Double Click on Piewedge, Data Pointdialog box
  appears

43
Formatting a Chart

• Double click on title of chart and Format Chart
  Title dialog box appears
• Note options
• Fonts
• Patterns
• Alignment

44
Formatting a Chart

• Formatted chart shows changes
• Box around title
• Change in fonts
• Pie slice pulled out
• Chart toolbar
• Select items
• Pull updialog boxes

45
Changing a Chart Type

• Possible to use the same data and change to a
  different chart
• Column chart to bar chart
• Bar chart to line chart
• Can also switch between sub-types
• Flat pie chart to 3D pie chart
• Important to use a chart type which best
  represents what the data portrays

46
Changing a Chart Type

• To change chart type
• Select the chart
• Click on Chart, then Chart Type
• Opens Chart Type dialog box

47
Changing a Chart Type

• Chart Type dialog box

Choosing 3-D sub-type
Specify a type as the default type
48
Changing a Chart Type

• 3-D view can also be changed
• Click Chart, then 3-D view
• Opens 3-D View dialog box

49
Changing a Chart Type

• Resulting chart
• 3-D view
• View has been elevated to 30 degrees

50
Previewing and Printing a Worksheet

• Click the preview button on the standard toolbar
• Worksheet preview displayed
• note mouse cursoris magnifier
• click on area tozoom in

51
Previewing and Printing a Worksheet

• Click the Print button
• Print dialog boxappears
• Note options

52
Previewing and Printing a Worksheet

• Alternative ways to print
• Click File and the Print
• Note Print Preview option herealso
• Click Print icon on menu bar
• (Print dialog box will not appear)

53
Using the Office Assistant

• Click Help on the menu bar
• choose Show Office Assistant
• Enter question in dialog balloon
• Select a help topic

54
Using the Office Assistant

• Read the chosen help topic
• Note possible multiplepanes of information
• Office Assistant can bedisabled
• uncheck option boxin dialog box

55
Saving and Closing a Workbook

• Click File and Save fromthe menu bar
• Use the Save As dialogbox
• Specify new folderas needed withNew Folder
  dialogbox
• With name of file enteredclick Save button

56
Steps in Developing a Spreadsheet

• 1. Determining the purpose - what inputs, what
  outputs, what printed reports
• 2. Planning - plan it on paper first
• 3. Building and testing - make sure it
  manipulates the data correctly
• 4. Documenting - should include something within
  the worksheet itself (directions, name and date)