excel education - intermediate

8/9/2019 Excel Education - Intermediate

1/10

Excel Education – Intermediate

Table of Contents

1 ………. Introduction

2 ………. Pivot Tables3 ………. Ranks

4 ………. IF Statements

5 ………. Conditional Formattin! ………. "ata Filters

1 ………. Introduction

#$cel is ca%able o& muc' more t'an sim%l( addin t)o cells or returnin a sim%le result*

it can be an incredibl( d(namic %iece o& so&t)are. It is ver( unlikel( t'at in our roles)it'in I+, )e )ould be asked to build a s%reads'eet s'o)in in&ormation t'at doesn-t

c'ane* &or t'is reason it is desirable to automate (our s%reads'eet to a deree* so itc'anes alon )it' t'e in&ormation. o%e&ull( some o& t'ese &unctions )ill allo) (ou to

do t'at.

2 ………. Pivot Tables

Pivot tables are e$tremel( use&ul &or condensin and summarisin data &rom lare &ieldsinto a more manaeable &orm. /nd as t'e name suest* (ou can %ivot t'e table to ive

t'e data &rom anot'er %ers%ective. For e$am%le in t'e data I 'ave %rovided* t'ere is a listo& iveback numbers &or eac' I+, location* and it is s%lit do)n b( (ear and se$. I& )e)anted )e could see )'ic' location 'ad t'e most males overall* or )'ic' location 'ad

t'e most iveback 'ours in a iven (ear.

Step 1

Click to %ae +

Step 2

Click an()'ere in t'e data &ield

Step 3

Click

"ate Pivot Table and C'art 0iard*

t'e &ollo)in dialoue bo$ )ill a%%ear


2/10

Select t'e tick bo$es as above* click ne$t

Click ne$t aain

Click a(out


3/10

"ra t'e 'eadins to t'e a%%ro%riate areas* &or e$am%le i& (ou-d like to oranise b(

ocation and t'en Start ear* dra ocation and t'en Start ear to t'e R0 sections.T'en dra umber to t'e "/T/ section* t'e data )e are sortin al)a(s oes t'ere.

T#6 "rain t'e 'eadins to eit'er C7, or R0 )ill sim%l( c'ane t'e )a(t'e table is laid out* t'is is %rett( muc' do)n to %ersonal %re&erence.

T'en click ok t'en &inis'. T'e out%ut s'ould look like t'is


4/10

Step

ou can dra 'eadins t'at (ou did not oriinall( include in t'e initial setu% o& t'e %ivottable*


5/10

3 ………. !an"s

Ranks* as t'e name suests* is a tool )'ic' allo)s us to rank t'e data &rom eit'er

ascendin or descendin scores.

#$%TE#

In #$cel* t'e eneral &ormat is

=rank( number , ref , order )

$umber

T'e s%eci&ic number )e )ould like to kno) t'e rank o&

!ef

T'e list o& all numbers )e )ant to rank t'e s%eci&ic number aainst

%rder

T'is is askin )'et'er t'e 'i'est score ets ranked number 1 or t'e lo)est sore. For

e$am%le t'e 'i'est e$am score )ould et ranked 1* or in anot'er case t'e lo)est number

o& errors in a drivin test )ould et ranked 1. In #$cel t'is is done b( usin 89: or 81: in

t'e Re& %lace.

89: ranks t'e 'i'est score 1 and 81: ranks t'e lo)est score 1.

………. I& Statements

IF statements in t'eir basic &orm can be used as a ver( ood indicator iven a condition.

For e$am%le i& a student s'ould %ass or &ail. T'e &ollo)in is a list o& averae marks ineac' sub;ect at a secondar( sc'ool.


6/10

/n IF statement comes in t'e &orm

=if( some condition , result if condition is true , result if condition is false )

It is use&ul to identi&( )'at )e are tr(in to test* and )rite it out in )ords* T# %ut it

into t'e IF statement.

T'e outcome )e )ould like to arrive at is' if a student (ets less t)an *+' t)en t)e,fail' if t)e, (et *+ or )i()er t)e, pass. T'e )a( (ou )rite t'is %rett( muc' %uts it int'e correct &orm. So i& )e %ut t'is in t'e i& statement &orm )e et6

=if( D4


7/10

Sa( )e )ant t'e cell to o reen i& someone %asses and red )'en t'e( &ail* 'i'li't t'e

entire &ield (ou )ould like to a%%l( t'e conditionin to

>o to6

Format Conditional Formattin

T'e &ollo)in dialoue bo$ )ill a%%ear

ou can a%%l( more t'an one condition to a &ield* so a %ass could be reen and a &ailcould be red. T'e conditions can be set u% like t'is6


8/10

T'en t'e outcome looks like t'is

ata &ilters

"ata &ilters are one o& t'e =uickest and best )a(s o& =uickl( sortin and arranin data.Sa( )e 'ave a lare amount o& data like t'is6

/s (ou can see t'ere is a lot o& data and in no %articular order* but a data &ilter can make

it a lot more manaeable.

Step 1

Click on t'e ro) number )'ic' all o& t'e 'eadins are on.


9/10

/s (ou can see t'e )'ole ro) )ill become 'i'li'ted.

Step 2

Click on

8"ata Filter /uto&ilter:

ou )ill et t'e &ollo)in result.

otice all o& t'e little dro% do)n bo$es.

Step 3

I& I )ant to s'o) onl( t'ose students )'o did mat's* I click on t'e 8Course: dro% do)n

and select mat's.

T'en as (ou can see* it s'o)s onl( t'e mat's students.


10/10

/nd (ou can sort more t'an one section at a time.

For e$am%le I )ant to see all o& t'e (ear 2* 'istor( students )'o %assed. I sort 8ear: on

2* 8Course: on istor( and 8Pass

excel education - intermediate

Documents