The Basics of Using Spreadsheets Flashcards
CHAPTER 9
Spreadsheet
Electronic piece of paper divided into rows (h) and columns (v)
What do you call the intersection of a row and a column ?
A ‘cell’
A cell address consists of..
Its row & column reference
Why do we use spreadsheet ?
► A tool for calculating, analysing & manipulating numerical data
► Make the calculation and manipulation of data easier & quicker
What function can we use to make some changes to an existing file to which you want to save but also leave the original document intact ?
’ Save as ‘ - then give the amended file a new name
–> original file remains in original form
–> file with new name contains changes
Press Enter =
Move to cell below
Shift + Enter =
Move to cell above
- F5
- Fn + F5
- Ctrl + G
Go To –> specify cell address would like to move to
Press Tab =
Move to the right
Shift + Tab =
Move to the left
Press what to select a group of cells adjacent to each other
1) Select the 1st cell
2) Press and hold Shift
3) Using arrow keys, press further cells
Press what to select a group of cells not adjacent to each other
1) Select the 1st cell
2) Press and hold Ctrl
3) Using mouse pointer, press further cells
Select an entire row of cells at one time
Shift + Spacebar
Select an entire worksheet
Ctrl + A
Worksheet
A single page/sheet in excel
Workbook can contain more than one…?
Worksheet
What is the content of a cell ?
- Text
- Values
- Formulae
How to make Excel treat number as a text ?
Add apostrophe (‘) before it
–> then it will be Left aligned
What is the default alignment for Text & Values
Text
- left aligned
- overflow to adjacent blank cells
Number
- right aligned
- NO overflow feature
What do you call a small square in the corner of a cell ?
’ Fill handle ‘
What appears when your mouse points to fill handle ?
’ Black cross ‘
What does it mean when there is red triangle at the top right corner of the cell?
Someone add comment to the cell
Cell protection/locking :
Condition : All cells are locked except those specifically unlocked
1) Select the range to be unlocked
2) Right click, choose Format Cells
3) Click Protection tab
4) Untick ‘Locked’ & ‘Hidden’ boxes
5) Go to ‘Review’ tab
6) Click ‘Protect Sheet’
** Only cells highlighted can be changed
Cell protection/locking :
Condition : All cells are unlocked except those specifically locked
1) Select the whole worksheet
2) Untick ‘Locked’ & ‘Hidden’ boxes
3) Select the range to be locked
4) Right click, Format Cell
3) Click Protection tab
4) Tick the ‘Locked’ & ‘Hidden’ boxes
5) Click on ‘Protect Sheet’
Step 5 is important !! - cells can still be changed if you skip this
** Cells highlighted CANNOT be changed
What does the formula bar shows ?
- Cell address of the active cell
- Formula
=C4 + C5
Add value in C4 to C5
=C4 - 5
Subtract 5 from value in C4
= C4 - C5
Subtract value in C5 from value in C4
= C4*5
Multiplies value in C4 by 5
= C4* 120%
Adds 20% to the value in C4
= 2^2
2 to the power of 2
OR
2²
= 4 ^ (1/2)
Square root of 4
Ctrl + 1
Format cells
Ctrl + ;
Enter today’s date as a numerical value
= TODAY()
Enter a date which will be updated each time the spreadsheet is opened
explain :
ROUND (value, places)
Value –> value to be rounded
Places -> number of places to be rounded
Relative cell reference
Change when you copy formulae to other locations
Absolute cell reference ($)
Maintain the original cell reference
Shortcut to absolute a cell
F4
If you use Paste special and paste, does the pasted numbers change when you change the numbers in the original source
NO - They have become pure numbers and do not link back to their sources
Circular reference
A formula that includes a reference to its own cell address
Precedent
A cell that is referred to by another cell
–> to trace cells that provide data to the formula
–> tracer arrow appears
Dependent
Opposite of precedent
–> A cell that contains a formula that refers to another cell
DIV/0
Attempt to divide a number by zero/blank cell
VALUE!
A mathematical formula refers to a cell containing text OR ada space dalam blank cell
** Function that operates on ranges (SUM,AVERAGE) will ignore text, so this error won’t occur
NULL!
Cell references within formula are not separated correctly
Etc : separate ranges by space rather than colon
= (B4:B6) ✅
= (B4 B6) ❌ - #NULL!
REF!
Formula has invalid cell reference (deleted/removed)
#######
A column not being wide enough to display its contents
OR
A negative number is formatted as a date/time
NAME?
A formula can’t find the referenced data it needs to complete the calculation
Etc: cell reference not exist / misspelt function name
NUM!
A formula contains numeric values that aren’t valid
N/A
No value is available
