Comp1900 Australian National University, Semester 1 1999
Introduction to Information Technology Applications
Module 2
Spreadsheets
Section 1 – Basics
What
What is a spreadsheet?
• grid of "cells", arranged in rows and columns
• cells may contain data:
labels (typically in first row and column)
numbers
text
money amounts
dates
(images)
formulas
and usually (though not necessarily) some calculations.
Simplest example: a grocery list!
Even without any calculations, an ss can be a useful database (eg record collection)
When
Their place in the history of the personal computer
• Visicalc the first "killer app"
Why
Why use a computer rather than pen & paper?
• large data sets
• sorting and searching capability
• automatic and accurate calculation
• complicated or very long calculations
• re-calculation on correction or change of data
• "what if" analysis
• templates for re-use
• graphics and visual representation of data
How would use a s/s in your work?
How
Using Excel
• the Excel interface - Look and Feel
Rows and Columns
Sheet tabs, status bar, scroll bars, pane control
Active cell, Formula (value/entry) bar
Menus
Tool bar(s)
An Excel document - "Workbook"
Filing : Save, Name, Close, Open.
Entering data
Cell address; navigating; cursor types
Editing
Modifying entries
Undo/Redo
Inserting rows and columns
Deleting cells
Selecting a range cells; entering data within a range; AutoFill
Moving and Copying data
Importing data
Formulas
Constructing and editing formulas
Inserting functions
Specifying a range
Copying a formula
Relative and absolute addresses
Paste Special
Database capabilities
Search
Sort
Section 2 – Graphics
Creating charts
Chart toolbar – types of charts
Section 3 – Enhancements
Formatting the spreadsheet
Cell sizes
Inserting/Deleting cells
Hiding rows and columns
Alignment, styles
Adding borders, colours, …
Adornments for charts
Adding Legends, titles, axes, grids, backgrounds, images, drawings
Section 4 – The finished product
Save as …
Printing
Print Area
Print Preview
Exporting spreadsheets and charts to a word processor
Section 5 – Advanced Topics
Further features of Excel include
Goal seeking
Macros
Templates
Database: filtering of lists, look-up tables, data queries
AutoCorrection, tracing, auditing; protection of data
Analysis tools
Depending on time, we may take up one or two of these topics
References
Excel's On-line Help facility
BluePond Excel Training manuals available in Chifley Short loan
Excel manuals produced by Microsoft or third parties are available commercially
Recommended reading
Edward Tufte: Envisioning Information, Graphics Press