SAMPLE - Amazon S3

225 Pages·2013·6.14 MB·English

E
x
c
e
l
Excel 2013
2
0
1
Intermediate
3

I
n
t
e
r

m

e
d
i
a
t
e

SAMPLE
Excel 2013 Intermediate Page 2
© 2013 Cheltenham Courseware Pty. Ltd.

All trademarks acknowledged. E&OE.

No part of this document may be copied without written permission from Cheltenham Courseware unless produced
under the terms of a courseware site license agreement with Cheltenham Courseware.

All reasonable precautions have been taken in the preparation of this document, including both technical and non-
technical proofing. Cheltenham Courseware and all staff assume no responsibility for any errors or omissions. No
warranties are made, expressed or implied with regard to these notes. Cheltenham Courseware shall not be
responsible for any direct, incidental or consequential damages arising from the use of any material contained in this
document. If you find any errors in these training modules, please inform Cheltenham Courseware. Whilst every effort
is made to eradicate typing or technical mistakes, we apologize for any errors you may detect. All courses are
updated on a regular basis, so your feedback is both valued by us and will help us to maintain the highest possible
standards.

Sample versions of courseware from Cheltenham Courseware:
(Normally supplied in Adobe Acrobat format): If the version of courseware that you are viewing is marked as NOT
FOR TRAINING, SAMPLE, or similar, then it cannot be used as part of a training course, and is made available
purely for content and style review. This is to give you the opportunity to preview our courseware, prior to making a
purchasing decision. Sample versions may not be re-sold to a third party.

For current license information:
This document may only be used under the terms of the license agreement from Cheltenham Courseware.
Cheltenham Courseware reserves the right to alter the licensing conditions at any time, without prior notice. Please
see the site license agreement available at: www.cheltenhamcourseware.com.au/agreement

Contact Information

Australia / Asia Pacific / Europe (ex. UK / Ireland) / Rest of the World
Email: [email protected]
Web: www.cheltenhamcourseware.com.au

USA / Canada
Email: [email protected]
Web: www.cheltenhamcourseware.com

UK / Ireland
Email: [email protected]
Web: www.cctglobal.com
SAMPLE

FOR USE AT THE LICENSED SITE(S) ONLY
 2013 Cheltenham Courseware Pty. Ltd. www.cheltenhamcourseware.com.au
Excel 2013 Intermediate Page 3

TUTOR SETUP INFORMATION.....................................................................................................................6 
SAMPLE FILES FOR THIS COURSE............................................................................................................7 
EXCEL 2013 SETUP AND VIEWING OPTIONS...........................................................................................8 
WORKSHEET MARGINS.............................................................................................................................................8 
WORKSHEET ORIENTATION......................................................................................................................................9 
WORKSHEET PAGE SIZE.........................................................................................................................................10 
HEADERS AND FOOTERS........................................................................................................................................12 
HEADER AND FOOTER FIELDS.................................................................................................................................13 
VIEWING WORKBOOKS SIDE BY SIDE......................................................................................................................15 
ZOOMING THE VIEW................................................................................................................................................16 
EXCEL 2013 FUNCTIONS AND FORMULAS..............................................................................................18 
GETTING HELP WITH FUNCTIONS...........................................................................................................................18 
NESTED FUNCTIONS...............................................................................................................................................21 
CONSOLIDATING DATA USING A 3-D REFERENCE SUM FUNCTION.........................................................................24 
MIXED REFERENCES WITHIN FORMULAS................................................................................................................26 
EXCEL 2013 TIME & DATE FUNCTIONS....................................................................................................29 
INSERTING THE CURRENT TIME AND DATE..............................................................................................................29 
TODAY FUNCTION.................................................................................................................................................29 
NOW FUNCTION.....................................................................................................................................................31 
DAY FUNCTION......................................................................................................................................................33 
MONTH FUNCTION................................................................................................................................................35 
YEAR FUNCTION....................................................................................................................................................38 
EXCEL 2013 MATHEMATICAL FUNCTIONS..............................................................................................42 
ROUND FUNCTION................................................................................................................................................42 
ROUNDDOWN FUNCTION...................................................................................................................................44 
ROUNDUP FUNCTION..........................................................................................................................................47 
EXCEL 2013 LOGICAL FUNCTIONS...........................................................................................................50 
IF FUNCTION...........................................................................................................................................................50 
AND FUNCTION......................................................................................................................................................53 
SOR FUNCTIONA...............................M........................................P...............................L...........................E.......................54 
EXCEL 2013 MATHEMATICAL FUNCTIONS..............................................................................................56 
SUMIF FUNCTION..................................................................................................................................................56 
EXCEL 2013 STATISTICAL FUNCTIONS....................................................................................................59 
COUNT FUNCTION................................................................................................................................................59 
COUNTA FUNCTION..............................................................................................................................................62 
COUNTIF FUNCTION.............................................................................................................................................66 
COUNTBLANK FUNCTION...................................................................................................................................69 
RANK FUNCTION...................................................................................................................................................72 
EXCEL 2013 TEXT FUNCTIONS..................................................................................................................81 
LEFT FUNCTION.....................................................................................................................................................81 
RIGHT FUNCTION..................................................................................................................................................84 
MID FUNCTION.......................................................................................................................................................88 
TRIM FUNCTION.....................................................................................................................................................92 
FOR USE AT THE LICENSED SITE(S) ONLY
 2013 Cheltenham Courseware Pty. Ltd. www.cheltenhamcourseware.com.au
Excel 2013 Intermediate Page 4
CONCATENATE FUNCTION................................................................................................................................96 
EXCEL 2013 FINANCIAL FUNCTIONS........................................................................................................99 
FV FUNCTION.........................................................................................................................................................99 
PV FUNCTION.......................................................................................................................................................102 
NPV FUNCTION....................................................................................................................................................105 
RATE FUNCTION..................................................................................................................................................109 
PMT FUNCTION....................................................................................................................................................113 
EXCEL 2013 LOOKUP FUNCTIONS..........................................................................................................116 
VLOOKUP FUNCTION.........................................................................................................................................116 
HLOOKUP FUNCTION.........................................................................................................................................120 
EXCEL 2013 DATABASE FUNCTIONS.....................................................................................................124 
DSUM FUNCTION.................................................................................................................................................124 
DMIN FUNCTION..................................................................................................................................................127 
DMAX FUNCTION.................................................................................................................................................130 
DCOUNT FUNCTION...........................................................................................................................................133 
DAVERAGE FUNCTION......................................................................................................................................136 
EXCEL 2013 NAMED RANGES..................................................................................................................141 
NAMING CELL RANGES..........................................................................................................................................141 
REMOVING A NAMED RANGE.................................................................................................................................144 
NAMED CELL RANGES AND FUNCTIONS................................................................................................................145 
EXCEL 2013 CELL AND RANGE FORMATTING.....................................................................................149 
APPLYING STYLES TO A RANGE............................................................................................................................149 
CONDITIONAL FORMATTING..................................................................................................................................151 
CUSTOM NUMBER FORMATS.................................................................................................................................154 
ADVANCED WORKSHEET MANIPULATION WITHIN EXCEL 2013.......................................................159 
COPYING OR MOVING WORKSHEETS BETWEEN WORKBOOKS.............................................................................159 
SPLITTING THE WINDOW VIEW..............................................................................................................................162 
HIDING ROWS........................................................................................................................................................164 
HIDING COLUMNS..................................................................................................................................................166 
HIDING WORKSHEETS...........................................................................................................................................169 
SUN-HIDING ROAWS...........................M........................................P...............................L...........................E.....................169 
UN-HIDING COLUMNS............................................................................................................................................171 
UN-HIDING WORKSHEETS.....................................................................................................................................173 
EXCEL 2013 TEMPLATES..........................................................................................................................175 
USING TEMPLATES................................................................................................................................................175 
CREATING AN EXCEL 2013 PERSONAL TEMPLATE..............................................................................................177 
EDITING AN EXCEL 2013 PERSONAL TEMPLATE..................................................................................................184 
DELETING AN EXCEL 2013 PERSONAL TEMPLATE...............................................................................................185 
PASTE SPECIAL OPTIONS WITHIN EXCEL 2013...................................................................................187 
USING PASTE SPECIAL TO ADD RANGES..............................................................................................................187 
USING PASTE SPECIAL TO SUBTRACT RANGES...................................................................................................189 
USING PASTE SPECIAL TO MULTIPLY RANGES.....................................................................................................191 
USING PASTE SPECIAL TO DIVIDE RANGES..........................................................................................................193 
USING PASTE SPECIAL ‘VALUES’..........................................................................................................................195 
USING PASTE SPECIAL TRANSPOSE OPTION.......................................................................................................199 
FOR USE AT THE LICENSED SITE(S) ONLY
 2013 Cheltenham Courseware Pty. Ltd. www.cheltenhamcourseware.com.au
Excel 2013 Intermediate Page 5
PROOFING AND PRINTING.......................................................................................................................202 
SPELL CHECKING..................................................................................................................................................202 
PREVIEWING A WORKSHEET.................................................................................................................................202 
VISUALLY CHECKING YOUR CALCULATIONS..........................................................................................................203 
SCALING YOUR WORKSHEET TO FIT A PAGE(S)....................................................................................................203 
DISPLAYING GRIDLINES WHEN PRINTING..............................................................................................................206 
PRINTING TITLES ON EVERY PAGE WHEN PRINTING.............................................................................................208 
PRINTING THE EXCEL ROW AND COLUMN HEADINGS...........................................................................................213 
TO VIEW PRINTING OPTIONS.................................................................................................................................213 
SETTING THE NUMBER OF COPIES TO PRINT........................................................................................................214 
SELECTING A PRINTER..........................................................................................................................................215 
SELECTING INDIVIDUAL WORKSHEETS OR THE ENTIRE WORKBOOK....................................................................216 
SELECTING WHICH PAGES TO PRINT....................................................................................................................217 
SINGLE OR DOUBLE SIDED PRINTING....................................................................................................................217 
COLLATION OPTIONS............................................................................................................................................218 
PAGE ORIENTATION..............................................................................................................................................219 
PAPER SIZE...........................................................................................................................................................220 
MARGINS...............................................................................................................................................................221 
SCALING................................................................................................................................................................223 
PRINTING..............................................................................................................................................................225 

SAMPLE
FOR USE AT THE LICENSED SITE(S) ONLY
 2013 Cheltenham Courseware Pty. Ltd. www.cheltenhamcourseware.com.au
Excel 2013 Intermediate Page 6
Tutor Setup Information

 Copy the sample files folder, Excel 2013 Intermediate to the Documents folder on
the PC.
 At the end of the course, remove all files modified or created during the course, prior
to re-running the course.
 At the end of the course, reset all program and operating system defaults that may
have been modified during the course, prior to re-running the course.

SAMPLE
FOR USE AT THE LICENSED SITE(S) ONLY
 2013 Cheltenham Courseware Pty. Ltd. www.cheltenhamcourseware.com.au
Excel 2013 Intermediate Page 7
Sample files for this Course

 During this course you will need to open samples files.

These are stored under the Documents folder in a sub-folder called:

Excel 2013 Intermediate.

If you create any new files, unless otherwise instructed, you should also save the
files in this folder.

SAMPLE
FOR USE AT THE LICENSED SITE(S) ONLY
 2013 Cheltenham Courseware Pty. Ltd. www.cheltenhamcourseware.com.au
Excel 2013 Intermediate Page 8
Excel 2013 Setup and Viewing Options

Worksheet margins
 Open a workbook called Print setup.

NOTE: The sample files for this course are stored in a folder called Excel 2013
Intermediate under the Documents folder.

 Click on the Page Layout tab, and from within the Page Setup group, click on the
Margins icon.

 This will display a drop down from which you can select Normal, Wide or Narrow.

SAMPLE

 Clicking on the Custom Margins command displays the Margins tab within the
Page Setup dialog box. You can use this dialog box to set custom top, bottom, left
and right margins.

FOR USE AT THE LICENSED SITE(S) ONLY
 2013 Cheltenham Courseware Pty. Ltd. www.cheltenhamcourseware.com.au
Excel 2013 Intermediate Page 9

TIP: You can also use this dialog box to set Header and Footer values, as well as
options to center the table on the page vertically and/or horizontally.

Experiment with setting margins.

TIP: Be sure not to make the margin size too small or you may have problems
printing the worksheet.
SAMPLE

Worksheet orientation
 Click on the Page Layout tab, and from within the Page Setup group, click on the
Orientation icon.

FOR USE AT THE LICENSED SITE(S) ONLY
 2013 Cheltenham Courseware Pty. Ltd. www.cheltenhamcourseware.com.au
Excel 2013 Intermediate Page 10
 You can select either Portrait or Landscape orientation, as illustrated.

 Try setting the orientation to Landscape. To see the effect in Print Preview mode,
press the Ctrl+F2 keyboard shortcut.

SAMPLE

 To return to the normal view, press the Esc key.

 Before continuing reset the orientation back to Portrait.

Worksheet page size
 Click on the Page Layout tab, and from within the Page Setup group, click on the
Size icon.

FOR USE AT THE LICENSED SITE(S) ONLY
 2013 Cheltenham Courseware Pty. Ltd. www.cheltenhamcourseware.com.au