Excel Power Moves

Training from Adkins Matchett & Toy

Download and go!

Expert fin

Fundamental Excel skills for modeling 1 Shortcuts 2 o!y 3 "asicFormat # Formulas & Abs'ef

Fundamental keyboard shortcuts o!ying formulas and data Sim!le formatting commands "asic formulas and S$% function Absolute and relati(e reference reference

6

PowerMoves

Powerful ways to clear and paste

) 8  10 11 12

Functions "usinessFormulas rossSheetFormulas +rrors .ntegrity harts

t

A*+'A,+ A*+'A,+- .F.F- %A/%. "asic business formulas and their a!!lication Formulas built across multi!le sheets ommon +cel errors and ho4 to fi them .ntroduction to integrity checking "asic chart creation and editing

o to ne nex xt s ee eett (CTRL Page down or click the link) et !age

5he use of this model is intended solely as a learning aid for !artici!ants in A%56s training !rograms or academic courses7 A%5 assumes no res!onsibility or liability 4hatsoe(er4hatsoe(er- to the client or to any third !arty!artyfor any other use or !ur!ose7

© 2003 by Kathleen Adkins

Page 1 of 28


ncial training For est reslts" work rogh the files files in order!

Training from AMT © AMT AMT Training Training All rights reserved !eproduction in any format without written permission prohi"ited

#ee our we"site for more info$

wwwamttrainingcom


Page 2 of 28


Lesson #$ Power %o&es

© 2003  201# Adkins %atchett 9 5o

Prere%uisites

&ontents

'ou must know how to(

This lesson covers the following skill

"uild formulas

lear formats

o!y formulas

:elete and insert cells- columns- r

$se absolute reference

$:;

$se $:;

$se the +dit Paste S!ecial comm

"efore you can complete this lesson

Selecti(ely co!y formats- formul %ani!ulate numbers 4ith o!era

Go to next sheet (CTRL Page down)

)A!*+*,$ =ou may not be able to !erform some normal +cel o!erations in this

file7 .t6s !rotected against certain actions7


Page 3 of 28


s(

o4s

nd to< as- (alues- and more ion commands


Page # of 28

4447amttraining7com


Power moves &lear Different wa's to clear :elete key< lear contents only +dit lear< lear formats- clear e(erything in the cell S?.F5 5'@  lear borders

Deleting cells" rows" or col%ns Cells, rows, columns +dit :elete +dit delete cell Fill the >hole> left by the delete by mo(ing remaining cells to the left or u!

+nsert ptions for inserting .nsert entire ro4 .nsert cell in ro4 Shift cells to right of insert right or down

Edit paste special !egular copy and paste

Copies EVERYT!"#$ %order, cell color, format, etc

&opy and paste special with Edit Paste #pecial

'astes part of the cell$ format, formula without format, the formula(s )alue, column width, and much more

Format

Edit Paste pecial For%at Paste the format- nothing else

o!y- then !aste 4ith A@5 h ( s

Formula

Edit Paste pecial For%la Paste a formula without its formatting

o!y- then !aste 4ith A@5 h ( s f


'eference !age

4447amttraining7com


Power moves .alue

Edit Paste pecial *ale Paste the value of a formula arningB 5his o!eration con(erts your formulas into hard numbersB

o!y- then !aste 4ith A@5 h ( s (

&hange num"er sign

Edit Paste pecial +ltipl' Multiply other cells "y the copied num"er

o!y a number to multi!ly by- like C1 Paste 4ith A@5 h ( f m

/ther features

Edit Paste pecial Try pasting these( olumn 4idths 5rans!ose *works on data, not formulas+ Add- subtract- multi!ly- di(ide


'eference !age


Power clear and 0*1/ Different wa's to clear cells lear< ontents only +dit lear All< Formats- e(erything S?.F5 5'@  lear borders +dit :elete< >olla!ses ells> Shifts information u! or o(er

,sing the Delete ke' to Clear$  lear this cell6s contents 55

2&

'ress the -elete key

-otice that the for%at is not cleared! .st the c ontents! ,-D with CTRL / . lear this cell6s contents 55

234


-ow t'pe a n%er into the cell0 The cell is still old and for%atted as percent0 ,-D with CTRL / / lear this cell6s contents 55

0


T'pe another n%er into the cell0 The n%er shold still e red and italici/ed0

,ndo and restore 1 $ndo 3 times  CTRL 1 2 ti%esG 0 'estore 3 times7 CTRL 3 2 ti%esG

Clear e&er'thing in the cell 2 lear e(erything out of the cell

5 lear this cell DDDE

2&

Edit Clear All A4T h e a

E)erything g

234

Edit Clear All A4T h e a -ow t'pe a n%er into the cell0 The percent for%at and old are gone!

6 Select the block of labels and numbers belo47

Press the :elete key7 otice that the borders remain7 So do the colors and other formats7


Page ) of 28


o4 clear +*+'=5?., 4ith +dit lear All7 ales growth osts H of sales ;ther income

4056 I870H 1070G

4056 I870H #70

4056 I870H 270G

4056 I870H 37I

Clear for%ats onl' Clear onl' the for%ats fro% these cells0 7 Scenario 1

Edit Clear 8ormat A4T h e f

9

786


J3#7&0




total

.


Clear orders onl' / lear the borders from these

cells7

23# I) 8

:!8T CTR4 ;

3 +o&e to next sheet

Page 8 of 28


© 2003  201# Adkins %atchett 9 5oy

oes3


Page  of 28



Page 10 of 28


+nsert and delete techni%ues Delete cells" rows" col%ns (Collapses the worksheet)  :elete 'o4 )7

1#70 I&71 1-#327I

2#7# II7# 1-&H

2)71 I870 1-&078

3870 )17I 8&07&

2#7#

2)71

3870

9tr :

9tr 2

9tr ;

10-000 1-#00 3-#00

10-000 1-I00 3-300

10-#00 1-I00 3-200

'ut cell cursor anywhere in the row to delete =se Edit Delete Row A4T h d r . :elete the bordered cell only 5 =se Edit Delete hift cells left A4T h d d l Enter

1#70

/ :elete the column 4ith the "old number =se Edit Delete Col%n A4T h d c

7nsert cells (Rips open the worksheet) 1 5he label for marketing materials 4as left out7 .t should a!!ear beside the 2-000 e!ense7 .nsert a cell bet4een :ales salaries and 'romotions and ty!e the label Marketing materials7

Sales Sales salaries Promotions ou!on costs Profit

0 5he Soft4are number

for tr 2 4as left out7 .nsert a cell for Soft4are- tr 2 and ty!e in 1&007

10-000 #-000 2-000 1-000 )00 2-300 1ivision

hi!s Soft4are Assembly

End


Page 11 of 28



I&7)

<=>

9tr >

10-&00 2-00


Page 12 of 28


Edit paste special

7t8s ti%e for 'o to learn a powerfl new wa' to paste! Edit Paste pecial lets 'o slice 8n8 dice a cell8s contents and paste 9st what 'o want!

Reglar paste$ re&iew Cop' and paste to re%ind 'orself how a nor%al paste works0  o!y this cell 1&

Paste it here 4ith +nter DDE

. o!y this cell

Paste it here 4ith 5'@ (

-otice that a reglar cop' and paste copies E*ER3T:7-G$ order" cell color" for%at" and so forth!

#H

1&H 2>

The next few pages will show 'o how to cop' and paste selecti&el'$ onl' for%ats" onl' for%las" and so on0

+o&e to next sheet

Page 13 of 28




Page 1# of 28


Paste formats Edit Paste pecial Format< co!y formats only o!y- then !aste 4ith A@5 h ( s t

Cop' for%ats withot cop'ing the for%la &opy the format from ?

this cell;

?and paste it into ?



Copy >>?

137&0H

Edit 'aste :pecial 8ormat >>>>? A4T h ) s t

.

Copy >>?

3#073#


/

Copy >>?

3#


1

Copy >>?

1#H


Cop' for%ats :2@;:@32

ost of ca!ital ashflo4s 0 .''

1#7&H #-00070G 072

:2@;:@:: :2@;:@3> @>>>>>>> Copy this cell

2-00070

1-00070

:2@;:@38

3-00070

@>>> 'aste the format here

Copy the date format from 'ro%lem 0, Row .2, and paste it to the dates in 'ro%lem 2

31:ec02

2

31:ec11

31:ec0#

31:ec0&

Copy the num%er format from the 'ro%lem 0 cash flows and paste it to the "et income nu 5 et income

#1&&73#

#23)7)

#3&07I8

##2I7I20#

+o&e to next sheet

Page 1& of 28



;this cell

07#&

1&0H

J870&

2273&H

%ers in 'ro%lem 5


Page 1I of 28


Paste formulas

© 2003  201#

Edit Paste pecial Formula< co!y formulas- not formats o!y- then !aste 4ith A@5 h ( f

Paste data into pre>?

this cell; 1#0

?and paste it into ? Edit 'aste :pecial 8ormula >>>>? A4T h ) f

.

Copy >>?

1000700H

Edit 'aste :pecial 8ormula >>>>? A4T h ) f

/

Copy >>?

=55>

Edit 'aste :pecial 8ormula >>>>? A4T h ) f

;this cell

Paste for%las into pre
Sales %fg costs 1 ,ross !rofit

.nterest e!ense +".5 +".5 to interest e!ense Sales et income 0 et margin

:ist 'ear #00 280 120 Copy

&00 #&00 

Pro yr :

Pro yr 2

Pro yr ;

##0 308

#8& 3#0

&38 3)&

Edit 'aste :pecial 8ormula A4T h ) f

@>> Copy this formula

I000 3I0 paste the formula here !f you do it right, the net margin should appear as a perce and the yellow %oB should still %e present

+o&e to next sheet

Page 1) of 28


dkins %atchett 9 5oy

t


Page 18 of 28


Paste values Edit Paste pecial *alue< o!y the (alue of a formula o!y- then !aste 4ith A@5 h ( (

?@R-7-G! This operation con&erts 'or for%las to hard n%ers!

Cop' a for%la" paste its &ale

ash urrent assets  5otal current assets

:ist 'ear #0 I&0 I0

Pro yr :

Pro yr 2

Pro yr ;

#2 I80 )22

#& I# )3

#8 )1# )I2

Copy the formula, then Edit 'aste :pecial Value into the same cell -otice that the for%la disappears and its &ale now appears in the cell

. Exa%ine the cells elow0 Con&ert each for%la to a &ale0 :ist 'ear Pro yr : Pro yr 2

Sales 'ecei(ables H of sales Accounts recei(able

Pro yr ;

120

1#&

130

13I

127&H 1&

18

1I

1)

+o&e to next sheet

Page 1 of 28




Page 20 of 28


Manipulate num"ers with operation pastes Edit Paste pecial o!y a number to di(ide by- like 1000 Paste 4ith A@5 h ( s i o!y a number to multi!ly by- like C1 Paste 4ith A@5 h ( s m

Change the scale of n%ers :i(isor

1-000

@>> Copy this cell

 .

Change the scale of these n%ers ' di&iding ' >5550 Sales #00-000-000 Edit 'aste :pecial -i)ide %fg7 costs 2&0-000-000 ,ross !rofit 1&0-000-000

/ 1

;!7 e!enses ;!erating !rofit

80-000-000 )0-000-000

0 2

5aes et income

1#-000-000 &I-000-000

You are asking num%ers %y 9

+ake sign changes Multiplier 555 5

170G

hange in o!erating 4orking ca!ital

o!y +dit Paste S!

7# Change to a negati)e num%er

6

:e!reciation

'ear :

'ear 2

'ear ;

11370G

11870G

117#G

Change to positi)e num%ers

7

a!ital e!enditures

'ear :

'ear 2

'ear ;

FG

&)070G

I1070G

Change to positi)e num%ers

Change the sign of a for%la Multiplier 555

Sales


170G 1-#0070

Page 21 of 28


ost of goods sold H of sales 9 ost of goods sold

I&70H 1070 Change the C#: formula to a negati)e "otice how the Edit 'aste :pecial -i)ide oper

Type a multiplier 555

et income :i(idends H of net income  :i(idends

32070 1070H 3270G Change the -i)idends formula to a po siti)e

+o&e to next sheet

Page 22 of 28



EBcel to di)ide these 99

cial %ulti!ly


Page 23 of 28


ation affects the formula


Page 2# of 28


#ummary @pplication$ ,se what 'o8&e learned  Make Ta"le : look like Ta"le 2

Tale >0 Car pa'%ents on A=5"555 with different %atrities and different interest rates %onths 2# 3I #8 I0 #7&0H 8)3 && #&I 3)3 #7)&H 8)& &) #&8 3)& &700H 8)) & #I1 3)) .nterest rates &72&H 880 I02 #I3 380 &7&0H 882 I0# #I& 382 &7)&H 88# I0I #I) 38# I700H 88I I08 #)0 38)

Tale =0 Present &ale of different annities o&er a range of discont rates &ash flows :333 2333 ;333 834 31&73 I307& #&78 884 31I78 I337I &07# 634 3187# I3I7) &&71 +nterest rates 684 317 I378 &78 734 3217& I#370 I#7& 784 32371 I#I71 I72 <34 32#7I I#73 )37

. &opy data from !ange : down into !ange 2 without changing the formatting in !ange 2 1o not leave any empty rows or cells &hange name and header formats as well 1ata range :

+aglen Fleming :omingo Anderssen Pa!e S4ensen allas 'amey

1ata range 2 )ilson


=ears & # # 3 & 3 & &

%inde 81 )I #& 12 2 I& ) &I

'ears

MB+ndex

1270

3#

PBto E!

3#70H

Page 2& of 28


Ci 1e*ardo Dones ,ill &i Thorton #ethi )uciak

#70 270 )70 170 370 270 &70 870

## I2 #G && 10# 22 2& 1I

1&70H 3)7&H ##73H )70H 270HG 1#7IH I&72H 878H

End of file pen neBt file


Page 2I of 28






Page 2) of 28



Page 28 of 28

Excel Power Moves

Recommend Documents