HCS081 S3. Excel

MS excel

ex of “spreadsheet applic software”

spreadsheet

grid of columns & rows

exs of spreadsheet applics

google sheets, apple numbers

quick access toolbar

Contain commands user uses often

ribbon

Consist of Tabs, Groups & Commands

name box

Displays address of active cell

(Located at left of formula bar)

formula bar

toolbar which displays content of active cell

insert function

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

select all button

Used to highlight all cells in worksheet

 (above row 1 & column A)

(click on rectangle box on upper left corner where column & row headings meet)

column headers

Labelled using letters of alphabet to distinguish betw columns

row header

Labelled using nos to distinguish betw rows

cell

Where column & row intersects

new sheet button

Adds new sheet to existing workbook

tab/arrow keys

used to navigate throughout spreadsheet

ctrl + home

returns to 1^st cell (A1)

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 calcs

• left-aligned

(Letters, comb of letters, numbers, special characters)

numbers (value)

• can be used in mathematical calcs

• right-aligned

(characters 0-9, numeric characters $ % * + / )

formulae (value)

o  represents mathematical calcs

o  usually right-aligned

(always starts w = sign)

function vs formula 

“Function” – use name of operators

“Formula” - use mathematical operators

7 number formats

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

incr decimal vs decr decimal

2 tools to add diff effects to text

wrap text & orientation tool

wrap text

creates multiple lines within cell

orientation tool

change rotation of text

3 cell refs

relative, absolute & mixed

relative cell ref

changes when copied to another cell of spreadsheet

(ex. A1)

absolute cell ref

doesn't change when copied to another cell of spreadsheet

(permanent ref to cell, Indicated by $ sign)

(ex. $A$1)

mixed cell ref

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

(ex. $A1)

formula

mathematical expression that calcs values of cells in spreadsheet

·        always start w = sign

function

special type of formula that manipulates numerical data

·        built-in shortcuts

function name vs function argument

1.   “Function NAME” = type of function being used

(ex. MIN, MAX, AVERAGE)

 

2.   “Function ARGUMENT” = Consist of cell addresses & ref operators

ref operator

descrs set of cells that function is to act upon

2 types of reference operators

range/colon & union/comma

range operator / colon (:)

-        Adjacent cells, no spaces

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

Ex. =SUM(A1:A5)

union operator / comma (,)

-        Non- adjacent cells, spaces betw

UNION operator referencing range of cells, from union of single/multiple cells

Ex. =SUM(A3,A5,A7)

SUM & PRODUCT function

SUM  = total of all numeric values in group

PRODUCT = result of multiplying all values in range of cell locations

AVERAGE & MEDIAN function

AVERAGE  = avg/arithmetic mean for group of nos

MEDIAN = middle no in group of nos

COUNT & COUNTA function

COUNT  = no of cell locations in range that contain numeric character

 

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

MAX & MIN function

MAX = highest numeric value in group of nos

 

MIN = lowest numeric value in group of nos

STDEV function

standard dev for group of nos based on sample

autosum tool

Automatically enters SUM function in active cell

autofill tool

Automatically copies content of a cell to adjacent cells

“####” meaning + soltuion

column too narrow to show entire fig entered

        = adjust column width

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

row 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 content in middle

3 cursor shapes + uses

2 types of sorting + define

ascending—data can be arranged from lowest -highest value

descending—data can be arranged from highest -lowest value

3 ways data can be sorted

alphabetical, numerical, chronological

alphabetical, numerical & chronological ascending & descending

purposes of charts

• Communicate data graphically

• Illustr trends, compare & relate

bar graph vs column graph

horizontal graph vs vertical, goes up

worksheet display (3)

print preview vs page break preview

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

importing vs exporting data (data integration)

IMPORTING DATA = placing data from 1 applic into another

EXPORTING DATA = taking data from 1 applic & placing in another

5 steps to import data from excel into word

1.   Open excel

2.   Select data you want to X

3.   Right click & select copy

4.   Opes MS word & go to where you want to insert

5.   On clipboard group select paste special

5steps to link excel files in word

1.   Open word doc where worksheet will display

2.   Open excel worksheet that contains data you want to link

3.   Select & copy range of cells

4.   Open word doc

5.   Right click & select link & keep source formatting in paste options

importance of linking

*LINKING is important bc changes made to excel will automatically update to word