ICT 162
Computing Skills Fundamentals II
Lecture 1: Introduction to Spreadsheets
Overview
What is a spreadsheet?
Benefits of an electronic spreadsheets
Spreadsheets applications
Microsoft Excel
Features
Window
Navigation
Entering Data and Formulas
Working with ranges
What is a Spreadsheet?
A grid that organizes data into columns and rows
It can be used to store, sort & manipulate information…
… and to perform calculations
Historically mainly used for handling financial information
e.g. staff salaries
Original spreadsheets
Made modifications difficult
Required more storage
Difficult to analyze
e.t.c
• Image Source: www.ghmchs.org
Electronic Spreadsheet
Automated version of a paper based spreadsheet
Easy to move, copy, edit, and delete information
Easy to format design and appearance of information
Ability to open and use multiple spreadsheets at one time
Easy to arrange and analyze numbers and perform
calculations
Example uses of an electronic spreadsheet
budgets
inventory management
financial planning
cash flow analysis
Electronic Spreadsheet
Spreadsheet programs
Microsoft Office Excel
Lotus 1,2,3
LibreOffice Calc
ZK Spreadsheet
Google Sheets
Microsoft Excel
Most popular spreadsheet program in use today
Part of the MS Office suite
Excel stores spreadsheets in documents called workbooks.
Each workbook is made up of individual worksheets, or
sheets.
When you open Excel, by default it will open a blank
workbook with one or three blank worksheets.-depends on
version
Excel files ends with .xls or .xlsx
Microsoft Excel
Popular Features
Ability to perform a variety of data manipulations and
calculations e.g. can do formula based calculations
Can display data in charts/graphs
Auto-update of related numbers when data changes
When you set up calculations in a worksheet, if an entry is
changed in a cell, the spreadsheet will automatically update
any calculated values that were based on that entry
Ability to open and use multiple workbooks at one time
Microsoft Excel
Set of 3 open workbooks
Microsoft Excel Window
Basic components
Name box
Formula bar
Column headings
Row headings
Cell
Active cell
Mouse pointer
Sheet tabs
Task Pane
Tab scrolling buttons and Toolbars.
You will be introduced to these during your labs
Microsoft Excel Window
Basic components
Microsoft Excel Window
Descriptions of Components
NB: In Excel 2010 worksheet size is 1,048,576 rows by 16,384 columns
Worksheet Navigation
To navigate within a worksheet, you use the
arrow keys, Page Up, Page Down, or the Ctrl key
in combination with the arrow keys to make
larger movements.
The most direct means of navigation is with
your mouse.
Scroll bars are provided and work as they do in
all Windows applications.
Worksheet Navigation
To move to other Worksheets, you can:
Click their tab with the mouse
Use the Ctrl key with the Page Up and Page Down
keys to move sequentially up or down through
the worksheets
Navigation Keystrokes
Undo and Redo Features
The Undo feature allows you to sequentially back
up to a certain action, such as a delete, a move, an
entry, etc. and allows you to reverse those actions.
Redo allows you to reapply actions one step at a
time that you have previously undone.
Insert, Move, and Rename Worksheets
Worksheets are much like pages within a book;
you peruse through them like you flip the pages of
a book.
There are several ways to move, copy and work
with worksheets.
Dragging to new location
Using Move\Copy Option
Developing A Worksheet
Determine the worksheet’s purpose.
Manage grades
Develop holiday checklist
Generate grocery list
Enter the data and formulas.
Test the worksheet and make any necessary edits /
corrections.
Improve worksheet appearance.
Save and print the complete worksheet.
Entering Data into a Worksheet
To enter data, first make the cell in which you
want to enter the data active by clicking it.
Entering Data into a Worksheet
Data Types: Text, Constant, Formula
Labels/Text - text entries that describe the contents of other
cells. Labels may be alphanumeric e.g. ICT
Numeric values/constants - numbers used in mathematical
operations such as addition, subtraction etc. e.g. 1002
Formula – expression that calculates a value : 12*(23+C5)
Label
Numeric
Formula
Entering Formulas
A formula is a mathematical expression that calculates a
value.
In Excel, formulas always begin with an equal sign
(=).
A formula can consist of one or more arithmetic
operators.
To calculate a value- Excel follows order of precedence
rules
A set of predefined rules defining correct order of
mathematical operations - similar to BODMAS
Arithmetic Operators
Order of Precedence Rules
Identify Cell Ranges
A group of worksheet cells is known as a cell range, or range.
Working with ranges in a worksheet makes working with the
data easier.
Ranges can be adjacent or nonadjacent.
An adjacent range is a single, rectangular block of cells
Select an adjacent range by clicking on a cell and dragging to an
opposite corner of a rectangle of cells
A nonadjacent range is comprised of two or more adjacent
ranges that are not contiguous to each other i.e. borders do not
touch
To select a nonadjacent range, begin by selecting an adjacent range,
then press and hold down the Ctrl key as you select other adjacent
ranges
Adjacent and Nonadjacent Ranges
Range Selection Techniques
Select and Move Worksheet Cells
To select a large area of cells, select the first cell in the
range, press and hold the Shift key, and then click the last
cell in the range.
Once you have selected a range of cells, you may move the
cells within the worksheet by clicking and dragging the
selection from its current location to its new one.
By pressing and holding the Ctrl key as you drag, Excel will
leave the original selection in its place and paste a copy of
the selection in the new location.
To move between workbooks, use the Alt key while
dragging the selection.
Moving Selected Cell Ranges
Appearance: Resize Worksheet Rows and Columns
There are a number of methods for altering row height and
column width using the mouse or menus:
Click the dividing line on the column or row, and drag the
dividing line to change the width of the column or height of
the row
Double-click the border of a column heading, and the
column will increase in width to match the length of the
longest entry in the column
Widths are expressed either in terms of the number of
characters or the number of screen pixels.
Resize a Column
Insert and/or Delete Worksheet Rows and Columns
You can insert one or many additional rows or
columns within a worksheet with just a few steps
using the mouse or menu options.
You can insert individual cells within a row or
column and then choose how to displace the existing
cells.
To delete and clear cells, rows, or columns, you can
use the Edit menu, or right click on a heading or a
selection of cells and choose Delete from the
shortcut menu.
Print a workbook
To Print a worksheet, you can use:
A menu
The Print button on the standard toolbar
The Ctrl-P keystroke to initiate a printout of the
worksheet
Excel uses the same basic methods for printing as other
Windows and Microsoft Office applications.
The Print Dialog Box
Can you Identify Major Components?
Summary
A spreadsheet package is a software application designed to support data manipulation
and mathematical calculations
Cells can contain words (labels), numbers or formulas
A workbook is an Excel file that can contain one or more worksheets
A worksheets is organized into rows and columns
Columns are generally identified by letters and rows by numbers
A cell name refers to one single cell (for example, A1)
A group of cells is referred to as a range-can be adjacent or non-adjacent
There are three basic types of data -labels, values and formulas
Labels are text entries that describe the content of other cells, values are numbers that are
available for manipulation and formulas are the means of mathematically manipulating
values in cells.
Exercise
How many blank workbooks are created by default when you
create an excel workbook?
Give three example uses of an electronic spreadsheet.
What is the difference between an adjacent and non-adjacent
range?
Create a checklist of items (include other descriptors e.g. serial
numbers where necessary) in your room with quantities and
estimated value per item. Figure out a way to add up the values
and find the total for all items. How could this list be useful to
you in the future?
FYI: Excel Limits and Specifications
https://
support.office.com/en-us/article/Excel-specifications-
and-limits-1672b34d-7043-467e-8e27-269d656771c3
Next : Working with Formulas and Functions
