INFOLINK UNIVERSITY

•
COLLEGE
C o u r s e Ti t l e : - D e v e l o p a n d u s e c om pl ex s p r e a d s h e e t s
Department:-Accounting
P r o g ra m : - T V E T
Session:-Regular

P r e p a r e d b y : - Wo n d i m a g e g n . D
Copyright – Precision Learning
 P R E S E N TAT I O N OBJEC TIVES
At the end of this presentation you will know about:

• Organise personal work environment in accordance with ergonomic

requirements

• Analyse task and determine specifications for spreadsheets

• Identify organisational and task requirements of data entry, storage, output,

reporting and presentation requirements

• Apply work organisation strategies and energy and resource conservation

techniques to plan work activities
 P R E PA R E TO D E V E L O P S P R E A D S H E E T S
• Spreadsheets help u s arrange data, sort data, with graphing or
charting data and also calculating numerical data.

• The rows (horizontal) are identified by numbers and columns

(vertical are identified by letters (e.g. A, B, C, AA, A B etc.)
Microsoft Excel is a widely used spreadsheet program. There are
others:
• Google Doc s - an online and collaborative spreadsheet.

• iWork Numbers – Apple Office Suite.

• Lotus Sy mphony – Spreadsheets.

Copyright – Precision Learning
 ORGANISE PERSONAL WORK ENVIRONMENT
WITH ERGONOMIC REQUIREMENTS
Ergonomic requirements

Avoid radiation Chair -height, Keyboard, screen

from computer seat, back Document holder Footrest and mouse
screens adjustment position

Lighting - glare Noise Posture Workstation

and reflection m inim is ation height and layout

Your own work area should be monitored by you a s part of your daily or
periodic self-check of equipment, workstation and area before work and
during work.
Organisations should encourage safe working practices.
Copyright – Precision Learning
 ORGANISE PERSONAL WORK ENVIRONMENT
W I T H E R G O N O M I C R E Q U I R E M E N T S (CONT.)
R e s p o n s i b i l it i e s include:
• hazard reporting procedures
• job procedures
• safe work instructions
• emergency procedures
• accident and near miss reporting and recording procedures
• consultation on W H S issues
• correct selection
• control of risks under direct supervision.
E x a m p l e s of w o r kp l a c e policy re q u i re me n t s :

• Maintain work areas in a safe, uncluttered and organised manner according to policy and
procedures.

• Carry out all procedures safely, effectively and efficiently with minimum
inconvenience to staff, according to policy.

• Apply policy and procedures for tidying work areas and placing items in designated areas.
Copyright – Precision Learning
 ORGANISE PERSONAL WOR K ENVIRONMENT WITH
E R G O N O M I C R E Q U I R E M E N T S (CONT.)

W o r k surf a ce C h a ir Equipment

•Place all controls and task materials •Adjust your chair so the work •Set the eye to screen distance at a
close to avoid twisting surface allows your elbows to be distance that permits you to most
•Use a document holder to minimise bent at 9 0 , forearm s parallel with the easily focus on the screen. Usually
head movement floor, wrist straight, shoulders relaxed within arm's length
•Place the keyboard in a position that •Adjust the backrest to support your •Set monitor height with top of the
allows the forearms to be close to the lower back when sitting upright screen below eye level and the
horizontal and your wrists straight - •Adjust the seat tilt so that you are bottom easily read without a marked
your hand in line with your forearm comfortable when working on the inclination of the head
•If elbows are too far out from the side keyboard •Usually the centre of the screen is
of the body re-check the work surface •Usually this will be close to horizontal, near shoulder height
or chair height or tilted slightly forwards •People with glasses need to avoid too
•S o me people prefer to have their •Your knees should be bent at a much neck flex
wrists supported on a wrist desk or the comfortable angle greater than 9 0 •A mo use should not cause undue
desk. Be careful not to have the wrist degrees. pressure on the wrist and forearm
extended or bent in an up position •If is not comfortable or if your feet do muscles
not reach the floor use a footrest •Pressure can be reduced by
•The footrest should be adjustable releasing the mouse at frequently and
by selecting a slim-line, low- profile
mouse. Keep the mo use as close as
possible to the keyboard, elbow bent
and close to the body
•Avoid cradling the phone between
your head and shoulder
•Use a headset -hands-free/speaker
Copyright – Precision Learning

phone is another option if

environment is suitable
 ORGANISE PERSONAL WO R K ENVIRONMENT WITH
E R G O N O M I C R E Q U I R E M E N T S (CONT.)
Posture and environment Li g ht i n g K e y b o a r d op e r a t i on s

•Change posture often to minimise • Place monitors to the side of the light •Typing, a physical activity that
fatigue source(s), not underneath requires skill
•Good posture is essential- natural • make sure the correcxt lighting is •Staff need to learn correct techniques
relaxed position, with opportunity for being used in your office. •Unskilled typists are at risk of an
operators to a s s u me alternate positions • Glare and reflection - to determine glare overuse injury due to:
•Avoid awkward postures at extremes of from overhead lights whilst seated, hold •Using one or two fingers which may
the joint range, especially wrists a book above your eyes at eyebrow level. overload finger tendons
•Frequent short rest breaks rather than Is the screen image clearer withour • Constantly looking from keyboard to
infrequent longer ones overhead glare screen and back, ma y strain neck
• Avoid sharp increases in work rate • If there are reflections from the desk muscles
•Changes should be gradual to ensure
surface, hold the book above the • Can adopt a tense posture, with wrists
surface and a s s e s s changes in reflected bent back and fingers tensed
work does not cause fatigue
glare •Efficiency and speed of computers
•Fixed posture for long periods is tiring and
increases likelihood of muscular aches • To reduce glare and reflection: makes it possible for skilled operators to
and pains •Tilt the screen (top forward) so type extremely quickly.This and
•Long periods of repetitive movement and reflections are directed below eye workload pressures means potential
sustained visual attention can give rise to level exists for operators to use speeds which
fatigue-related complaints •Provide LCD screens ma y cause or contribute to an overuse
•Avoid spending more than 5 hours a •Cover screen with anti-glare screen injury
day on keyboard duties and no longer •Negative contrast reduces reflections •The role of repetitive movement in injury
than 50mins/hr without a postural/ •Change text and background colours is not fully understood, but is believed to
stretching break •BEST black characters on white/ interfere with the lubrication capacity of
yellow background, or yellow on tendons, and ability of muscles to receive
black, white on black, white on blue and sufficient oxygen. 10,000 - 12,000
green on white keystrokes per hour is considered
acceptable
•Avoid red and green and yellow on
white
•Turn screen brightness down
•Look into the distance to rest your
eyes regularly
Copyright – Precision Learning
 WOR K SAFE AND SMART!
S u p e r v i s o r s s h o u l d e n s u re w o r k l o a d c ontrol s are e xe rc ised
u s i n g the fol l ow i ng s t r a t e g i e s :

Plan ahead to avoid peaks and rushed jobs. Discourage ‘endless’ drafts.
Delegate fairly to all staff. Discourage the use of typed internal
minutes.
Consider the total workload of the Smaller breaks are valuable,
individual, schedule work fairly to avoid sometimes more so than one long one.
undue pressure on an individual.
Clearly define each operator’s workload Support staff and key board operators by
ensure there are realistic expectations. providing skills training and strategies in
how to be assertive and to prioritise.
Use relief staff if required. Supporting operators when there are
unrealistic expectations imposed.
Apply strict tests to the use of ‘urgent’ Exercise breaks need to be regular
and allow people to move from static
labels. postures.
Copyright – Precision Learning
 A N A LY S E TA S K A N D D E T E R M I N E S P E C I F I C AT I O N S
W h e n analysing your task what do yo u need to calculate, what information needs to be
extracted?
Is a spreadsheet the most efficient way to continue? Is a word processor or database better?
If a spreadsheet is the most suitable software it needs to be designed and constructed to make sure
outcomes are fully realised.
A plan is essential from the start. A s k - what do I need this spreadsheet to d o ? D o I need it to:

Add a set of Sort data or create Perform complex

numbers? a graph? calculations?

K n o w i n g t h e o u t c o m e p r o v i d e s t h e b e s t p l a c e t o start.
If you understand your task our outcome - begin by thinking of structure. If you are
creating a spreadsheet for someone else make sure you know what information they
have to give you and what information they need to be able to extract at the end.
Ask questions of the eventual user what is needed from the data?

W h a t i s req u ired o f t h e t a s k ? F i n d o u t in detail.

Copyright – Precision Learning
 ANALYSE TASK A N D DETERM I N E
SPECIFICATIONS
A n a l y s e the t a s k – w h a t d o y o u n e e d y our s p r e a d s h e e t t o d o ?
Spreadsheets have many uses:

You can automatically

Most general purpose create charts from
Listing and maintaining information projects can spreadsheet data, to
groups of numerically be implemented with a print or import into
based data. presentation or other
spreadsheet or database.
applications.

A spreadsheet is
excellent for simple data Excel has presentation
entry, use the Tab key to facilities and connect
advance from cell to cell. easily to Word or
Data is laid out in rows PowerPoint.
and columns.
Copyright – Precision Learning
 DATA ENTRY, S TO R A G E , OUTPUT, R E P O R T I N G A N D P R E S E N TAT I O N

There are laws and regulations governing the collection and storage of information, especially personal
information of customers and individuals.

Organisations must not gather personal information about their customers, clients or employees, unless
necessary and relevant to their purpose. Organisations must make individuals aware of the information
they possess. Individuals must be made aware of:
• who is storing or collecting information and contact details including third parties the information
ma y be passed to
• what the information will be used for
• any laws governing the collection and storage of this information, especially medical details
• will the information will be communicated to overseas countries?
This information is usually contained within the organisation’s privacy policy, which should be available
for customers to reference. This must be in line with Privacy Laws - Privacy Act 1 9 8 8 (Cth)

Anti-Discrimination legislation also applies:

• It is against the law to discriminate against anyone on the basis of gender, sexual preference,
political opinion, trade union activities, colour, race and ethnicity, social origin, religion, nationality,
family responsibility, irrelevant medical record, irrelevant criminal history, age, marital status, carer
status, parental status, breastfeeding, disability, pregnancy.
Copyright – Precision Learning
 DATA ENTRY, S TO R A G E , OUTPUT, R E P O RT I N G A N D P R E S E N TAT I O N

S p r e a d s h e e t specifications
The structure of tables is determined by different kinds of cells and their
relationships. The specifications m a y include decimal points, headings
and format of cells.
D a t a entry – navigate through spreadsheets using different options.

• Press TAB to move your active cell to the right by one cell, and
ENTER to move your active cell down by one cell, Shift + TAB will
move you back.

• Fill down from above using CTRL + D or click and drag on the + in the
bottom right hand corner of a cell or range of cells - values are copied
down. Or use Autofill option for this purpose.
Copyright – Precision Learning
 DATA ENTRY, S T O R A G E , OUTPUT, R E P O RT I N G A N D P R E S E N TAT I O N
Output
Spreadsheets allow users to perform computations and checks on data input.
Determining required outputs is important.
• Data output is a process to study, manipulate and work with data a s
required, for u se by other parties.

• Simple statistical analysis and parameters like mean, median, mo d e and range
are available a s spreadsheet formulas.

• Spreadsheets are designed to record, sort, calculate and store data.

M a k e s u re y o ur o r g a n i s a t i o n s style g u i d e i s b e i n g followed carefully.
Style guides provide information about expected presentation standards for all
reporting and documents.
Policies and procedures exist within in the workplace to guide users in correct
methods of presentation, saving and storing.
There are times when adding graphics ma y help to communicate information more
effectively, these can include charts or graphs to illustrate results.
Copyright – Precision Learning
 W O R K O R G A N I S AT I O N , ENERGY, R E S O U R C E C O N S E R VAT I O N

W o r k o r g a n i s a t i o n s t ra t e g i e s i n v o l v e f a c t o r i n g in t h e followin g:
• Exercise breaks and rest periods maintain correct ergonomic
requirements.
• Mix of repetitive and other activities – to avoid continuous postures and
activities which could lead to overuse injury or inefficiencies in the workplace.
Stress in the workplace and recognising when a working environment is
• affecting performance.

• Observing individuals and respecting their needs. W h o will schedule the work?
Are they aware of the importance of allowing time for safe and fair work
practices in this w a y ?
W h o is monitoring physical resource u s e ? Are they aware of the importance of
minimising the businesses environmental impact?
Workplace solutions must help meet current and future needs of the
Copyright – Precision Learning

business.
 ENERGY, R ES O U RC E CONSERVATION
TECHNIQUES
Energy and resource conservation: It is our responsibility. Reduce all
resource use in the first place. Be part of a culture of change.
E.g. paper management can include the following:

Edit using print Re-use paper for rough

Electronic filing rather preview prevents Double-sided paper Recycling used and drafts (observe
than paper based printing inaccurate use shredded paper confidentiality
documents requirements)

A s part of reviewing organisation strategies work activities need to keep

pace with current and future trends on environmental management
practices. Consider these issues and high energy consumers, what can
be done at your workplace?

Air conditioning, Office

Using power-save equipm ent, Hot water
Copyright – Precision Learning

options for Managing ventilation and cooking

com puters , Lifts
equipment lighting levels heating equipm ent
printers
 ENERGY, R E S O U R C E C O N S E R VAT I O N T E C H N I Q U E S
Track re sourc e u s e

• Reduce resource use a s part of a culture change – learn the usage and
handling practices in place.

• Set targets to change levels for improved outcomes.

• Conduct audits to understand purchase, use and disposal of
resources, this will give you an indication of priorities.
• Can you use less? Take advantage of sustainable opportunities.
E.g. recycled paper or carry ba gs made from corn starch?

• Gather information on resource purchases, costs and amount of

recycled and non-recycled alternatives available or purchased.

• Communicate findings via email, internal newsletter or staff

meetings.
Copyright – Precision Learning
 D ES I G N SOFTWARE FUNCTIONS A N D
FORMULAE
A n a l y s i s of data and application -does your spreadsheet work for you in
terms of the output you need?
A p p ro p r i a t e n e s s for the audience and the task? Doe s it represent the
data accurately and in a clear manner for ease of interpretation?
Avoi d b l a n k ro w a n d c o l u m n s it is better practice to widen the row or
column to arrange spacing. Dra g on the space between rows or columns to
widen to suit your layout and format.
E m b e d d i n g cell references in for mulae a cell reference refers to the
Letter then the Number, the uppermost left cell is A1.
Create formula using cell references rather than numerical values for
calculations is much more reliable.
E x a m p l e Use cell reference ‘C1’ instead of the number. If C 1
changes the formula still works.
Copyright – Precision Learning
 D E S I G N S O F T WA R E F U N C T I O N S A N D F O R M U L A E
H e a d e r s /footers allow us to identify elements of spreadsheets and can
contain:.

Your organisation will specify contents for headers or footers. Eg. page
numbers to keep documents manageable, and file path to locate it again!
L a b e l s are the ‘names’ you give your columns or rows. They must make
sense and accurately describe your spreadsheet to others.
I m p o r t a n d e x p o r t o f d a t a – Use spreadsheets in ma n y forms, insert
a s a table, a s a chart or a s a linked file into a Word documents
M u l t i - p a g e d o c u m e n t s - create several sheets within one workbook.
Copyright – Precision Learning
 SPREADSHEET DESIGN

Linked fo rm u la e use
cells in another
worksheet/book to link to
your current spreadsheet.
Eg. This spreadsheet uses
a cell from the ‘Expenses
wages’ sheet. S o cell D 4
in ‘Expenses wages’ =
B 2 6 in the ‘Sales’
Copyright – Precision Learning
 LINKED SPREADSHEETS
Link s p r e a d s h e e t s in a c c o r d a n c e wi t h s o f t w a r e p r o c e d u r e s
Linked worksheets can be in the s a m e workbook or in different
workbooks.

• Source worksheets provide the data.

• Destination worksheets receive the data.

Wh e n cell values change in the source worksheets, Excel
automatically updates values in the destination worksheet the next
time it is opened.
W h y link w o r k s h e e t d a t a ?
The ability to link data between Excel worksheets and workbooks can
often eliminate the need to have identical data entered and updated
in multiple worksheets. This s a v e s time, reduces the chance for error
and improves overall data integrity.
Copyright – Precision Learning
 SYMBOLS AND MEANINGS
Symbol Operation Meaning

+ Plus Addition

- Minus Subtraction or negative value

* Asterisk Multiplication

/ Forward slash Division

% Percent Percent

^ Caret Exponentiation

= Equal sign Equals A formula in Excel

always

> Greater than Greater than

< Less than less than

>= Greater than or equal to Greater than or equal to

<= Less than or equal to Less than or equal to

Copyright – Precision Learning

<> Not equal to Not equal to

=SUM
Add together this range, click
Find this on the HOME tab far right on
 FUNCTIONS
There are ma n y functions. They are pre-set formula beginning with = and the
arguments:
Example:

• A d d i n g = S U M ( ) will add up the cells within the bracketed range.

• P M T funct io n will calculate a mortgage payment ($1,073.64), based on

5 % interest ( 5 % divided b y 1 2 months = the monthly interest rate) over 30-
years (360 months) for $200,000 loan:
=PMT(0.05/12,360,200000).

• = S Q RT ( A 1 ) Provides the square root of the value in A1.

• = TO D AY ( ) Returns the current date.

• = I F Specifies a logical test to perform.

• = R O U N D U P rounds the number away from zero.

•
Copyright – Precision Learning

= AV E R A G E returns the average of a range.

 FUNCTIONS
• The PI() function uses the value of pi: 3.142
• 1. References: A 2 uses the value in cell A 2
• 2. Constants: Numbers or text values entered directly into a formula, such a s

• 3. Operators: The ^ (caret) operator raises a number to a power, and the *

(asterisk) operator multiplies numbers

• M a k i n g ref erence t o a n o t h e r w o r k s h e e t - l i n ki n g

• AVERAGE worksheet function calculates the average value for range B1:B10 on
the worksheet named Marketing in the s a me workbook.

• 1. Refers to the worksheet named Marketing

• 2. Refers to the range of cells between B 1 and B10, inclusively

• 3. Separates the worksheet reference from the cell range reference

Copyright – Precision Learning
 FORMULA
Ask yourself what do you need to calculate? Are you adding numbers together? Are you
multiplying? Or are you using a more complex formula?

Remember B E D M A S ? This principle is taught in schools (which ma y use another acronym) states
that you work your brackets first, the exponents (or roots) and so on through the following order.

E x a mp l e

2×(6+7)-82 first compute the expression inside the parentheses ( 6 + 7 )

= 2×(13) -8 2 second calculate the exponent 8 squared

= 2×(13) - 64 third calculate the multiplication 2 × 1 3

= 26-64 = -38 finally the subtraction

Brackets Exponents Division Multiplication Addition Subtraction

Copyright – Precision Learning
 F O R M AT C E L L S
Cell formats allow you to change the way data appears in the spreadsheet.
Formatting only alters the presentation not the value of the data.

• Formatting allows for monetary units, scientific options, dates, times

and fractions, borders, colours, fonts and sizes.

• Formatting requirements will vary depending on the purpose of the

document, e.g. internal documents m a y not need a s ma ny
enhancements to text, page alignment or colour use, but they will need
effective formula make sure the spreadsheet works and appropriate use
of cell references.

To change the formatting of a cell or a selection, use the F o r m a t Cells

dialog which holds all of the formatting options or specific formatting
elements available a s buttons on the F o r m a t Toolbar.
Copyright – Precision Learning
 F O R M AT C E L L S
Practice your formatting
skills from the Home tab.
Copyright – Precision Learning
 TEST FO R M U L A E
A s soon a s you have used a series of formulae in Excel, you need to
make sure they work.
Check the accuracy of your calculations, manually or use these other
methods to make sure they are correct:

Check references Use formula view Order correct? Do es it look right? Break it up

• When using cell • M a n y formulas and •See B E D M A S in •Use your own •If a formula is too
references double- functions? It m a y this ebook experience and complicated, break
click a formula to be useful to switch •Excel always common sense to it up into several
highlight all the to formula view to estimate the smaller formulas
follows this
referenced cells see all of them at answer
• Double-check they
ordering, it doesn’t •Then you can
the s a me time just calculate a •If answers are check each
are correct
• Check the cell • Ctrl key + ` (grave formula from left to much larger or formula for
references, are in the accent- upper-left right. smaller, there m a y accuracy
right order eg. C 2 corner of the be a problem
minus C3, should be keyboard) •This m a y not
=C3-C2, not = C 2 - C 3 • Press Ctrl+` to always work but it
switch back to can help you
normal view quickly catch a
problem in your
formula
Copyright – Precision Learning
 ERROR TYPES

Error m e ssag e occurs when the two or more cell references are not
#NULL!
separated correctly in a formula.

This m e an s the width of the contents of the cell will not fit in the cell,
##### resize the cell and the contents will be visible.

An invalid cell reference error m e ssag e occurs when a spreadsheet

#REF!
formula contains incorrect cell references.

#DIV/0! Simply m e an s you cannot divide zero into a number. For example

=A1/A2 would result #DIV/0! IF A 2 contains nothing or zero.

Copyright – Precision Learning
 P R OT E C T I N G YO U R DATA
Advanced formatting options allow you to:

• Lock s om e of the cells so values cannot be changed

•Restrict the range of values that can be entered in selected cells

To change the formatting of a cell or a selection, you can either use
the Fo r m a t Cells dialog which holds all of the formatting options or
use specific formatting elements available a s buttons on the Fo r m a t
Toolbar.
Copyright – Precision Learning
 CELL R E F E R E N C E S
Relative cell references:
This is the most widely used type of cell reference in formulae. Relative cell
references are basic cell references that adjust and change when copied or
when using AutoFill.
Copyright – Precision Learning
 A B S O L U T E CELL R E F E R E N C E S
In an absolute cell reference, a dollar sign ($) precedes both column letter and
row number. This me an s the number you require for use in your formula must
a b s o l u t e l y be that cell – that column and that row.
W h y not just use numerical values? Because … remember the value of the cell
can change, but the formula to calculate its effect will not.
Example: If you calculate superannuation at 9 % your absolute value will be that cell
which contains the 9 a s the percentage you require. When rates change to 9.5 simply
change the value of the cell, the formula stays the same.

A mixed reference has the column a s absolute and the row is relative or vice
versa - use the dollar sign in front of just the column letter or row number. For
example:
Copyright – Precision Learning
 A U TO M AT E A N D S TA N D A R D I S E S P R E A D S H E E T S
S p r e a d s h e e t s c a n b e u s e d for the fo llo w i n g t a s k s :
Once you have determined your purpose you can decide which features will help you
with more efficiencies.

Prepare detailed budgets using spreadsheets. Compare actual versus planned performance.
Financial analysis Predict changes in values i.e. effects of a price change on costs, discounts and profit.

Accounting A fixed table can be prepared to show these changes but more significantly the figures could
be changed from time to time to reflect new situations with same formulae in place.
Budgeting
Perform bank reconciliation; calculate jobs costs, taxes, schedule payments, forecast profits
and control stocks. In all these tasks, the spreadsheet proves a very important tool in
simplifying the computation process and production of the results.

The computations m a y be presented in table on graphic form. There are buttons you can click
Graphing to make your data appear in table format or in the form of graphs and charts. These enhance
the presentation and understanding of the data.

Scientific In mathematics and the sciences, spreadsheets could be used to convert temperature figures,
applications such as Celsius to Fahrenheit, metric to imperial, pounds to kilos and currency exchange rates.

The most important features of any spreadsheet are the calculation of figures using arithmetic
Copyright – Precision Learning

Academic results signs or functions. While not completely unique to spreadsheets, this feature is particularly
more visible than word processing software.
 A U TO M AT I N G YO U R W O R K
There a r e s e v e ra l w a y s t o a u t o m a t e y o u r work:

• Templates: create your own or use any of a series from your software.
• M a c r o s : a tool that allows you to automate tasks and add functionality
to your forms, reports, and controls. Users are able to record different
tasks and apply them to another portion of the data. It allows you to
repeat the actions over and over a s a sequence of key strokes.

• N a v i g a t i o n : Speed in navigation will improve your effectiveness. TAB

to m o v e a cell to the right, use the arrows etc.

• D e f i n e d n a m e s : to define a constant amount.

• Autocorrect: to enter repeated phrases accurately.

• C o p y i n g data or a formula is simple - drag the source cell's fill handle

and Excel copies the data or formula from the source cell to cells
selected using the fill handle.
Copyright – Precision Learning
 M A C R O S TO AUTOMATE S PR EA D S H E E T
OPERATION
Create or edit a macro?
It works by recording every
If, in the future, you want to do
mouse click and keystroke you
another task in the same way,
do while performing a particular
play the recorded Macro back.
task to repeat.

Macros enable users to record different tasks and apply them over
again. If you need to repeat actions Macros can help with consistency.
To set security level temporarily to enable all macros see your eBook.
Before y o u record a m a c r o
Make sure the Developer tab is visible on the ribbon - by default this
tab is not visible, so:
1. Click File tab ⟶Options ⟶Customise Ribbon
2. Under Customise the Ribbon ⟶Main Tabs list ⟶Developer ⟶OK
Copyright – Precision Learning
 M ACRO S
Record a macro
In the Code group on the Developer tab, click
Record Macro.
Optionally, enter a name for the macro in the
Macro name box, enter a shortcut key in the
Shortcut key box, and a description in the
Description box, and then click OK to start
recording.
Perform the actions you want to automate, such
as entering text you will use over and over
again, e.g. Contract text or filling down a
column of data.
O n the Developer tab, in the Code group, click
Stop Recording.
Copyright – Precision Learning
 T E M P L AT E S
There are existing templates you can use. However make sure they suit your
organisations needs and data requirements.

Any formatting is specific to that template.

Or

You can create your own spreadsheet with formatting and details a s needed.

Setting up a complex spreadsheet takes time and considerable thought, it is

good practice to be able to use it again, easily, a s a template.

If you create a document and want to save it a s a template Save as ⟶save a s

ty pe ⟶excel Tem plate * .xltx

Templates will save as default to Custom Office template folder in M y

Documents.

They can be saved to any folder. Name templates carefully with consideration
for who will be using them and w h y ?
Copyright – Precision Learning
 T E M P L AT E S
• Creating your own template allows you to specify fonts, headers
and footers, special page formats, report layouts and form designs
for use by you or anyone in your organisation.

• It can provide a more suitable document with the right format,

specific for the needs of your business.

• Making changes to an existing template, to alter it to suit your

purpose, m a y actually take longer than starting from scratch.

• Templates can be used for the following purposes:

Invitations Invoices Newsletters Planners

Copyright – Precision Learning

Presentations Reports R esumes

 E N T E R , C H E C K A N D A M E N D DATA
We can only enter data in active cell. To activate a cell select it. One
cell can be active at a time.
A few d o s a n d d o n ' t s to consider:

D o use cell
D o plan your references and
D o save frequently D o sort your data n ame s ranges in
spreadsheet
formulas

Don’t use numbers Don’t leave blank

as column Don’t leave cells rows or columns
headings and don’t containing when entering
include units with formulas unlocked related data resize
the data instead
Copyright – Precision Learning
 E N T E R , C H E C K A N D A M E N D DATA
You have created your spreadsheet, now what?

• You m a y need to proo frea d or refo rm at it to suit the purpose

and design of the task.

• A s always make sure s p e l l i n g is accurate and data is accurate,

including formula.

• You m a y choose to double check your data manually .

Copyright – Precision Learning
 I M P O R T A N D E X P O R T DATA
The i m p o r t a n d e x p o rt o f d a t a is the automated or semi-automated
input and output of data sets between different software applications.
‘Translating’ from the format used in one application into that used by
another.
When importing and exporting data you take a data set from another
software application e.g. Word to Excel and back, and Excel to Access
database software.
Wh e n importing or exporting ensure your data is:

• still relevant

• able to be understood and applied in the document or application

you have sent it to or taken it from.
Utilising s p l i t s c r e e n s allows you to see both the source and
destination document.
Copyright – Precision Learning
 U S E M A N U A L S A N D H E L P TO O V E R C O M E P R O B L E M S
There is so much help available!
Try using these tools a s often a s you need:

• Microsoft Office tutorials.

• Excel-2013-training-courses-videos-and-tutorials.

• Help available within Excel. There is a question mark in the top

right hand corner of all Microsoft applications this is a help icon.

• F1 is the function key to access help in the application.

• You m a y have a supervisor with the skills to help you.

• Always ask if you are not able to understand.

Copyright – Precision Learning
 PRINT A N D PRINT
PREVIEW
To Prin t: File ⟶ Print, this s creen
provides an opportunity to make
your print selections and preview
your page and the layout.
Remember to preview properly
before printing – D o not print
inaccurate documents, make
changes on screen before printing.
D o you want to print :

• the whole spreadsheet

• a section

• charts.
Copyright – Precision Learning
 PRINT A N D PRINT PREVIEW
• Printing is not the only way to share information.

• Consider the benefits of using the PDF function to save work and
share it safely throughout the organisation and colleagues.

• Sa ve ⟶Create File name ⟶Sa ve a s type ⟶PDF (*.pdf)

• This will create a document which is able to be shared and is

secure.

• Email this or share it via the company intranet a s an alternative to

printing.
Copyright – Precision Learning
 N A M E A N D STOR E
How you save your work is important. Security of documents is everyone’s
responsibility. Use naming and storing protocols set by your organisation.
You m a y need to limit a c c e s s to the document you created to prevent
unauthorised changes. Access this screen via the File tab, or protect
various parts of the worksheet to prevent changes.
Are you required to save onto a network drive, your own computer, or
offsite in storage systems e.g. Dropbox?
How ever your organisation chooses to handle the data, it needs to be
secure, particularly if data is confidential.
Data stored electronically must be backed up regularly.
S a v e often and store safely.

• Keyboard shortcut for saving: C T R L + S

• M o us e command: File ⟶ S a v e
Copyright – Precision Learning
 N U M E R I C A L DATA I N G R A P H I C F O R M
• Graphs represents complex numerical data in easy to understand and
less complex form. Hence, reader can identify major trend and
behaviour in the data immediately. There are numerous types of graphs
available for your data. It depends on you whether you choose the right
one or not.

• The way we look at data determines the type of graph we will use. We
can look at data in five ways:

✓ “Comparison between/ a m o ng different items

✓ Composition of static data
✓ Looking at trends overtime
✓ Relationship or association between variables
✓ Distribution of variables” (Hafiz Umar, 2015)

Alternatively, you m a y also choose from Excel recommended charts.

Copyright – Precision Learning

•
 N U M E R I C A L DATA I N G R A P H I C F O R M
When creating a graph or
chart, select the
appropriate graph type
for your data.

Select from a number of

basic graph types, and
refinements known a s
styles.

Select the graph type you

want to work with in the
Insert tab, on the Ribbon.

Select the chart type and

style.
Copyright – Precision Learning
 G R A P H A N D M A N I P U L AT I N G
Manipulating data is the process of re-sorting, rearranging and moving
research data, without fundamentally changing it.
This can be both to prepare for another activity – or to explore the data as an
analytic tool in its own right. (adapted from Baty, 2009).

• Re-sorting data involves changing the order of the data in ascending or

descending order.

• C ommon types of sorting are chronological, numerical, and alphabetical.

This helps in identifying significant values in data i.e. highest or lowest,
most frequent or least frequent, first or last etc.

We can sort data according to requirements by following these steps below:

• Highlight/select data in the relevant worksheet e.g. in the example given
below highlight the range A1:C13. ⟶Data tab ⟶Sort button.
Copyright – Precision Learning
 DIFFERENT STYLES OF G R A PH S

Line g r a p h s Bar graphs Pie g r a p h s Scatter g raphs 3D graphs

•Used to emphasise •Used to compare •Emphasise where •Share characteristics • Add dimension to
movement or trend number, frequency your data fits in of basic line graphs your graphing
of numerical data or other measure relation to a larger •Data can be plotted presentation
over time or •Plot numerical data whole using variable scales • Dimension enables
categories by displaying •Work best when data on both axes your viewers to
•Allow viewers to rectangular blocks consists of several •Data is plotted using recognise trends
trace the evolution against a scale large sets a basic line pattern based on two or
of a point by working •The bar's length •Too ma ny variables •Use to visualise the more data sets easily
backwards corresponds to a divide the pie into density of individual
•Trend types: value or amount small, hard to see data values around
•Hig hs/lows , •Viewers can develop segments particular points or
rapid/slow mental images of •Use colour or texture to demonstrate
movement data series on individual patterns in your data
•Tendency towards comparisons by segments to create •A numeric X-axis, or
stability distinguishing visual contrast sort field, will always
•Can be plotted with heights of the bars yield a scatter graph
two or more scales •Use to display by default
to compare the sa me numerical data when •The scatter plot is
value(s), in different you want to present the only graph type
time periods distributions in Excel that treats
•Create both x-axis a s a variable
horizontal and
vertical bars
Copyright – Precision Learning
 N U M E R I C A L DATA I N G R A P H I C F O R M
C rea te g ra p h s with la bels a n d titles
Graphs are an excellent m e a n s of describing, exploring or
summarising numerical data.
The use of a visual image can simplify complex information and
highlight patterns and trends in data.
All graphs should include a title summarising what the graph shows.
The title should identify what is being described and units of
measurements (e.g. percentages, total number, frequency).
The title m a y be placed within the chart area, or above or below the
chart.
Copyright – Precision Learning

For more details, please refer to the eBook.

 CHARTS OR GRAPHS

Chart a r e a defines
the boundary of all
elements related to the
graph including the
plot, any headings and
explanatory text. It
emphasises that these
elements need to be
considered together
and are separate from
any surrounding text.
Copyright – Precision Learning
 CHARTS OR GRAPHS

• P l o t a r e a is the area containing data. It is bounded by x and y axes to the

• bottom and left.
• Th e x a x i s the horizontal line defining the base of the plot area.
Depending on the graph, different locations on the x -axis represent different
categories (i.e. years) or positions on a numerical scale (i.e. temperature or
income). Details are placed just below the x- axis and an axis label is
usually provided to clarify the units of measurement.
•
• Th e y a x i s the vertical line defining the left side of the plot area, if more
than one variable is being plotted on the graph vertical lines on both the
left and right sides of the plot area m ay be used a s y- axes. A numerical
scale used to show values i.e. counts, frequencies or percentages. Intervals
on the scale are marked by numbers and tick marks, indicating the major
divisions, to the left of the y-axis.
•
• It usually has a label providing details of the units of measurement.
Copyright – Precision Learning
 S AV E
• When you open a new workbook, the very first thing you need to
do is to save your document at your desired location by giving it a
proper name.

• Alternatively, save your spreadsheet a s a PDF. This will include

your chart, a s well a s the data in your spreadsheet and is a secure
way to present your data.

• File ⟶ Export ⟶C reate PD F/X PS D ocu m ent

For further details please see your eBook

Copyright – Precision Learning
 VIEW A N D PRINT
To print your charts, follow these steps:
•
•
•• Select your chart to show the grey
frame around it. File ⟶Print, or
•
CTRL + P.
• When you click the Print button you will get
the print preview of your document and list
of options to make
Foradjustment according
further details please to
see your eBook
Copyright – Precision Learning

your requirements.
 D E S I G N AT E D T I M E L I N E S
When creating a document make sure you are aware of expectations
of other users. Consider:

• your audience and what they are expecting

• timelines for delivery

• is any additional information required by you, or others, to create

the document

• allow time for alterations and checking for accuracy in your

planning

• what other plans should you take into account so you are able to
deliver the documents in time?
Copyright – Precision Learning
 SUMMING UP
When you work through the process of creating a document to help
solve an issue in your job consider the following:

What role do spreadsheets play in your organisation?

Make sure everyone who needs to work with spreadsheets is trained and competent

Make sure a spreadsheet is actually the best tool for your task

Who is your audience?

Focus on required outputs

Never embed in a formula anything that might need to be changed

Don’t over complicate your spreadsheet, keep it as simple a s possible

Use multiple worksheets rather than multiple documents

Use a system of back up and version control which works across all parts of the organisation

Test the workbook thoroughly

Copyright – Precision Learning

Protect spreadsheets as needed

 SUMMING UP
Remember you have a responsibility to abide by the following:
C o n c ept W h y it m a t t e r s

All people deserve to be treated fairly and decently.

It is against the law to discriminate against anyone on the basis of gender,
Anti sexual preference, political opinion, trade union activities, colour, race and
discrimination ethnicity, social origin, religion, nationality, family responsibility, irrelevant
medical record, irrelevant criminal history, age, marital status, carer status,
parental status, breastfeeding, disability, pregnancy.

You have a responsibility to behave with honour, integrity and without

Ethical
conflict of interest in your job, make sure your work is of a consistently high
standards
standard.

Codes of Are practical guides to developing systems, processes and procedures,

Practice they need to be followed to ensure standards are maintained.

Maintain high standards at all time, how information from others is handled
Privacy
is legislated.

Being safe at work is your responsibility as well as that of your manager,

WHS
follow instructions as long as it is safe to do so.
Copyright – Precision Learning