HCS081 Excel Theory test

MS excel

ex of a “spreadsheet application software”

spreadsheet

A grid of columns & rows

examples of spreadsheet applications

google sheets, apple numbers

uses of spreadsheet

Used to enter & manage numeric info

Keep records, create budgets, charts, store & sort data

quick access toolbar

Contain commands that user uses often

ribbon

Consist of Tabs, Groups & Commands

name box

Displays address of the active cell

(Located at left of the formula bar)

formula bar

A toolbar which displays the content of the active cell

insert function

A tool which houses built in functions that can be used for mathematical calculations

select all button

Used to highlight all cells in the worksheet

 (above row 1 & column A)

column headers

Labelled using letters of the alphabet to distinguish betw diff columns

row header

Labelled using nos to distinguish betw lines of a worksheet

cell

Where a column & row intersects

new sheet button

Adds new sheet to existing workbook/file

tab/arrow keys

used to navigate throughout the spreadsheet

ctrl + home

returns to the 1^st cell (A1)

enter

Press to confirm the entry in the active cell

esc

cancels an entry into a cell

adjacent cells 

no space betw cells 

non-adjacent cells

space betw them

3 types of data

text (labels), numbers (value), formulae (value)

text (labels)

• can't be used in mathematical calculations

• left-aligned

(Letters & combination of letters, numbers & special characters)

numbers (value)

• can be used in mathematical calculations

• right-aligned

(contain characters 0-9 & numeric characters such as $ % * + / etc)

formulae (value)

o  represents mathematical calculations/expressions

o  usually right-aligned

(always starts with an = sign)

function vs formula 

“Function” – use name of operators

“Formula” - use mathematical operators

comma style number format

separates groups of 3 digits in nos >999 (ex. 1,500)

incr decimal (number format)

show more dps for more precise value

decr decimal (number format)

show fewer dps

2 tools to add diff effects to text

wrap text & orientation tool

wrap text

creates multiple lines within a cell (fit sentence in cell)

orientation tool

change direction/rotation of text

(ex. angle clockwise, vertical text)

3 cell refs

relative, absolute & mixed

relative cell ref

changes when copied/dragged to another cell of spreadsheet

(Most commonly used type of cell ref when using formulae)

(ex. A1, B55, ZZ140)

absolute cell ref

doesn't change when copied/dragged to another cell of spreadsheet

(Used to signal a permanent reference to a cell)

 (Indicated by $ sign in front of column letter & row no of cell address) 

(ex. $A$1, $B$55, $ZZ$140)

mixed cell ref

either absolute column & relative row/ relative column & absolute row

(ex. $A1, B$55, ZZ$140)

(ex. cell ref $B3 has an absolute column B & a relative row 3)

formula + ex

A mathematical expression that calcs values of cells in a spreadsheet

·        must always start with = sign

·        Uses arithmetic operators to carry out calc

(ex. =A1+B5*C4)

function + ex

A special type of formula that manipulates numerical data

·        another way to conduct mathematical calcs

·        built-in shortcuts

(ex. SUM (A2:A10)

function name

Refs to the type of function being used

(ex. MIN, MAX, AVERAGE, etc)

function argument

Consist of cell addresses & ref operators 

ref operator

descrs the set of cells that the function is to act upon

2 types of reference operators

range/colon & union/comma

range operator / colon (:)

-        Adjacent cells, no spaces (next to each other)

-        A RANGE operator referencing a range of cells, betw & incl 2 cell refs

Ex. =SUM(A1:A5)

union operator / comma (,)

-        Non- adjacent cells, have spaces betw

-        A UNION operator referencing a range of cells,

 from the union of single/multiple cells

Ex. =SUM(A3,A5,A7)

SUM function

total of all numeric values in a group

AVERAGE function

avg/arithmetic mean for group of nos

COUNT function

no of cell locations in a range that contain a numeric character

COUNTA function

no of cell locations in a range that contain a text/numeric character

MAX function

highest numeric value in group of nos

MEDIAN function

middle no in group of nos

MIN function

lowest numeric value in group of nos

PRODUCT function

result of multiplying all values in range of cell locations

STDEV function

standard dev for group of nos based on a sample

autosum tool

Automatically enters SUM function in active cell

(Excel usually makes educated guess of cell addresses to be incl in the formula)

autofill tool

Automatically copies content of a cell to adjacent cells

(Autofill options & contents - Formulae, Functions, Number, Months, Days/ dates, etc.)

“####” meaning + soltuion

column is too narrow to show entire fig entered

        = adjust column width

“What happens if apply wrap text & won’t show multiple lines?” 

row is too narrow to show entire fig entered

    = adjust row height

inserting row betw row 3&4

right click row 4 & insert (will always insert above)

inserting column betw C&D

right click column D & insert (will always insert behind)

merge & center

Allows you to combine 2/more cells & puts the content in the middle

3 cursor shapes + uses

white cross = for selecting cells

arrow cross = for moving cells

black cross = for copying cells

2 types of sorting + define

ascending—data can be arranged from lowest to highest value

descending—data can be arranged from highest to lowest value

3 ways data can be sorted

alphabetical, numerical, chronological

alphabetical ascending & descending

A-Z, Z-A

numerical ascending & descending

lowest-highest (1-9), highest-lowest (9-1)

chronological ascending & descending

earliest-latest (2000-2019), latest-earliest (2019-2000)

purposes of charts

• Communicate data graphically

• Illustr trends, compare & relate

bar graph vs column graph

horizontal graph vs vertical, goes up

new window

opens up 2nd window of same spreadsheet file

freeze pane

restricts portion of sheet to keep it visible

split pane (worksheet display)

divides window into 4 diff panes to be scrolled separately

print preview

allows you to view entire pages of your worksheet before printing

page break preview

shows which pages columns & rows will be printed on

(View tab > Page Break Preview)