Excel Training Manual for Financial Professionals

188 Pages·2016·10.54 MB·English

by  Anton du Plessis

Excel Training Manual for Financial
Professionals
First Edition
Copyright © 2016 Anton Du Plessis
All rights reserved. No part of this publication may be reproduced, distributed or
transmitted in any form or by any means, including photocopying, recording or
other electronic or mechanical methods, without the prior written permission of
the publisher.
First Edition, 2016
Russell Templar Ltd.
Registered in England and Wales Company Number: 10187229
14 Meadowview, Winnersh, RG41 5PD, UK

www.russelltemplar.com
ISBN-13: 978-1539533283
ISBN-10: 153953328X About the Author
I have worked in the IT and financial fields since 1997. I have many years’
experience as a software developer and I am also a chartered accountant. In my
experience in both the IT and financial fields Excel has always been one of the
main software packages I have used over the years. I started using spreadsheets
before my computer came with a mouse. When I started using spreadsheets the
software was called Lotus 1-2-3 and together with WordPerfect you could “run”
an office. Over the years my skills grew with the various versions of Excel that
an office. Over the years my skills grew with the various versions of Excel that
was released. In 2010 I qualified as a chartered accountant at one of the big four
audits firms where I was also introduced to the world of training. I quickly
realised that I seemed to work quicker and more efficient in Excel than most of
my colleagues. This was not because my colleagues did not have IT skills or
where new to Excel. They just needed to be shown certain skills and techniques
to sharpen the skill they already had. From that need came this book, that I
originally used as a training manual for a one day Excel course.
I moved to the UK in 2013 and have been working in the financial field here as
well and saw the same need for the training here. Although it is currently not
practical for me to run full time training courses I have decided to publish my
training manual aimed specifically at financial professionals.
Anton, 2016 First Edition For Sue, Fiona, Paul, Siobhan, Hiwot, Mark and Irina.
These have been my colleagues in recent times. All brilliant people that put up
with me in the last 3 years. I sometimes trained them against their will but they
all inspired me to write and publish my book. Funny enough not this book. That
book, also on Excel will see the light soon.
And always my wife Janine whose love and support push me further than I ever
thought I could go.
Contents
Background............................................................................................................................................
1
Downloads..........................................................................................................................................
1
Before we
begin…..................................................................................................................................
2
The
Ribbon.........................................................................................................................................
2
Shortcut keys
......................................................................................................................................
3
Cell selection
notation.........................................................................................................................
3
Course content
...................................................................................................................................
3
Course Material
..................................................................................................................................
3
Excel
parts..........................................................................................................................................
3
End of Exercises
.................................................................................................................................
5
Working with cells and
worksheets......................................................................................................... 7
Selecting adjacent multiple
cells......................................................................................................... 7
Selecting nonadjacent multiple
cells.................................................................................................. 7
Selecting all cells in a worksheet
........................................................................................................ 7
Merge adjacent
cells...........................................................................................................................
8
Split a merged
cell..............................................................................................................................
8
Find merged cells
............................................................................................................................... 9
Inserting a
Worksheet.......................................................................................................................10
Deleting a Worksheet
.......................................................................................................................11
Copy a
Worksheet............................................................................................................................12
Move a Worksheet
............................................................................................................................13
Renaming a Worksheet
....................................................................................................................14
Navigating
Worksheets.....................................................................................................................15
Select Worksheets in a
Range..........................................................................................................16
Select NonAdjacent Worksheets
.....................................................................................................16
Select all Worksheets
.......................................................................................................................16
Exercise: Working with cells and worksheets
.......................................................................................17
Worksheet
Protection...........................................................................................................................18
Unlock
Cells......................................................................................................................................18
Protect the Worksheet
......................................................................................................................21
Unprotect the Worksheet
..................................................................................................................21
Exercise: Worksheet
protection............................................................................................................ 22
Paste Special
.......................................................................................................................................
23 Exercise: Paste Special
........................................................................................................................26
Nesting Functions
................................................................................................................................
28 Insert a Function within a Function
................................................................................................... 28 Absolute
Referencing...........................................................................................................................30
Exercise: Absolute
Referencing............................................................................................................
31 Logical Functions
.................................................................................................................................
32 IF
Function........................................................................................................................................
32 AND
Function...................................................................................................................................
35 OR Function
.....................................................................................................................................
37 IFERROR
Function...........................................................................................................................
39 Exercise: Logical
Functions..................................................................................................................
40 VLOOKUP
Function.............................................................................................................................
44 Exercise: VLOOKUP
Function.............................................................................................................. 48
Named
Ranges....................................................................................................................................
49 Naming a Cell
...................................................................................................................................
49 Using Column Headings to Name
Columns...................................................................................... 49 Manually
create a Name from a Selected
Range.............................................................................. 50 Insert a Named Range
in Formulas .................................................................................................. 50
Editing a Named
Range....................................................................................................................51
Navigating Named
Ranges...............................................................................................................52
Exercise: Named Ranges
..................................................................................................................... 53
Mathematical Functions
....................................................................................................................... 55
SUMIF
Function................................................................................................................................
55 AVERAGEIF Function
...................................................................................................................... 57
Exercise: SUMIF Function
.................................................................................................................... 59
Conditional
Formatting.........................................................................................................................60
Add Conditional Formatting
..............................................................................................................60 Add
Conditional Formatting across Rows
......................................................................................... 61 Copy Conditional
Formatting ............................................................................................................
62 Change Conditional Formatting
........................................................................................................63 Remove
Conditional
Formatting........................................................................................................ 63
Highlight
Duplicates..........................................................................................................................
63 Exercise: Conditional
Formatting.......................................................................................................... 64
Auditing a
Formula...............................................................................................................................66
Trace Precedent/Dependent Arrows
.................................................................................................66
Remove Arrows
................................................................................................................................67
Exercise: Auditing a
Formula................................................................................................................68
PivotTables
..........................................................................................................................................70
Concept and
Layout..........................................................................................................................72
PivotTable Field List
.........................................................................................................................73
Turn the Field List On/Off
..................................................................................................................73
PivotTable Ribbon
............................................................................................................................74
PivotTable
Layout.............................................................................................................................74
Create a
PivotTable..........................................................................................................................76
Format a PivotTable
.........................................................................................................................78
Remove/Add and Move
Fields..........................................................................................................79
Remove a Field
................................................................................................................................79
Add a Field
.......................................................................................................................................80
Move Fields within the
Table.............................................................................................................80
Changes to Source
Data...................................................................................................................81
Filter a PivotTable
Report.................................................................................................................81
Field Settings in Row/Column
Labels................................................................................................82
Create a Custom
Name....................................................................................................................83
Value Field Settings in Values
..........................................................................................................85
Change Number
Format...................................................................................................................85
Data Drill
Down.................................................................................................................................86
Calculated
Fields..............................................................................................................................87
Edit/Delete calculated
field................................................................................................................89
Changes to the Location/Area of the Source
Data............................................................................89
Exercise:
PivotTables...........................................................................................................................91
Hyperlinks
............................................................................................................................................94
Exercise:
Hyperlinks.............................................................................................................................95
Text Functions
......................................................................................................................................96
LEFT
Function..................................................................................................................................96
MID
Function....................................................................................................................................98
FIND Function
..................................................................................................................................99
CONCATENATE
Function..............................................................................................................
102
TRIM
Function................................................................................................................................
104
LEN
Function..................................................................................................................................
105 TEXT
Function................................................................................................................................
106 Exercise: Text Functions
.................................................................................................................... 107
Working with Data
Lists......................................................................................................................
111 Sorting
Data.......................................................................................................................................
112 Single Level
Sorting........................................................................................................................
112 Multi Level
Sorting..........................................................................................................................
113 Set a Custom Sort Order
................................................................................................................ 113
Sorting data by Rows
...................................................................................................................... 117
Exercise: Sorting
Data........................................................................................................................
119 Exercise: Custom Sort order
.............................................................................................................. 122
Subtotalling
........................................................................................................................................
125 Create
Subtotals.............................................................................................................................
125 Remove all
Subtotals......................................................................................................................
126 Copying
Subtotals...........................................................................................................................
126 Exercise:
Subtotalling.........................................................................................................................
128 SUBTOTAL
Function......................................................................................................................
131 Filtering
Data......................................................................................................................................
134 Filter
Options..................................................................................................................................
135 Apply AutoFilter
..............................................................................................................................
137 Clear
Filter......................................................................................................................................
139 Remove AutoFilter
.......................................................................................................................... 139
Advanced Filter
Features................................................................................................................
140 Exercise: Filtering
Data......................................................................................................................
141 Exercise: Advanced
Filtering..............................................................................................................
143 Group and
Outline..............................................................................................................................
145 Group rows and Columns
............................................................................................................... 145
Ungroup Parts of the Outline
.......................................................................................................... 145 Remove
an