MTC Training Solutions Ltd. MS Office 2010 Excel Advanced Manual

236 Pages·7.148 MB·English

MS Office 2010

Excel Advanced
Manual

Table of Contents
INTRODUCTION ....................................................................................................... 1
HOW TO USE THIS GUIDE ......................................................................................... 1
INSTRUCTIONS ........................................................................................................ 1
KEYBOARD ............................................................................................................. 1
COMMANDS ........................................................................................................... 1
NOTES .................................................................................................................. 2
GROUPING WORKSHEETS .............................................................................................. 1
ACTIVATE GROUP MODE .......................................................................................... 1
GROUP ADJACENT SHEETS ........................................................................................ 1
GROUP NON-ADJACENT SHEETS ................................................................................ 1
DEACTIVATE GROUP MODE ....................................................................................... 2
FILL DATA ACROSS WORKSHEETS ................................................................................ 2
3-D FORMULAE ...................................................................................................... 3
REFERENCES TO OTHER SHEETS ................................................................................... 3
MULTIPLE WORKSHEETS ................................................................................................ 5
MOVING BETWEEN THE WORKBOOK SHEETS ................................................................ 5
WORKSHEET NAMES ................................................................................................ 5
MOVE AND COPY WORKSHEETS ................................................................................. 6
INSERT AND DELETE WORKSHEETS .............................................................................. 6
MOVING WORKSHEETS TO DIFFERENT WORKBOOKS ...................................................... 7
CONSOLIDATING DATA .................................................................................................. 9
CONSOLIDATE DATA FROM MULTIPLE WORKSHEETS IN A SINGLE WORKSHEET ....................... 9
OTHER WAYS TO CONSOLIDATE DATA ......................................................................... 12
CONDITIONAL FORMATTING ....................................................................................... 14
OTHER CONDITIONAL FORMATTING OPTIONS ............................................................. 15
MORE RULES ........................................................................................................ 15
CELL STYLES .................................................................................................................. 21
HOW TO SELECT CELLS, RANGES, ROWS, OR COLUMNS................................................... 21
GROUP AND UNGROUP ROWS AND COLUMNS .......................................................... 25
GROUPING ROWS AND COLUMNS............................................................................. 25
- i -
UNGROUPING ROWS AND/OR COLUMNS ................................................................... 26
PROTECT WORKSHEET DATA ....................................................................................... 27
UNLOCK CELLS ...................................................................................................... 27
WORKSHEET PROTECTION ....................................................................................... 28
UNPROTECT SHEETS ............................................................................................... 29
MAKING YOUR FILE READ ONLY ............................................................................... 30
DATA VALIDATION RULES ............................................................................................ 31
SETTING VALIDATION RULES .................................................................................... 32
TRACK CHANGES .......................................................................................................... 33
REVIEW AND INCORPORATE CHANGES ........................................................................ 35
WHAT-IF ANALYSIS ....................................................................................................... 36
GOAL SEEK ...................................................................................................................... 36
SOLVER ......................................................................................................................... 38
CONSTRAINTS ....................................................................................................... 39
ADVANCED SOLVER FEATURES ............................................................................................. 42
SOLVING METHODS USED BY SOLVER ....................................................................... 42
SOLVER OPTIONS .................................................................................................. 43
SOLVER AND SCENARIO MANAGER ........................................................................... 43
SAVING SOLVER SOLUTIONS .................................................................................... 43
SOLVER REPORTS .................................................................................................. 44
SCENARIO MANAGER ......................................................................................................... 45
CREATE A SCENARIO MANUALLY .............................................................................. 45
OPEN THE SCENARIO MANAGER ............................................................................... 46
SHOWING A SCENARIO ........................................................................................... 47
EDITING A SCENARIO .............................................................................................. 48
DELETING A SCENARIO............................................................................................ 49
SCENARIO SUMMARY ............................................................................................. 50
LINKING DATA .............................................................................................................. 51
LINKING DATA BETWEEN WORKSHEETS ................................................................................. 51
LINKING DATA BETWEEN WORKBOOKS ................................................................................. 52
INTRODUCTION TO CHARTING .................................................................................... 53
TERMINOLOGY ...................................................................................................... 53
- ii -
CREATING CHARTS ................................................................................................. 54
SEPARATE CHART PAGES ......................................................................................... 55
MOVING AND RESIZING EMBEDDED CHARTS............................................................... 56
DATA LAYOUT ...................................................................................................... 57
SHORTCUT MENU (RIGHT CLICK) .............................................................................. 59
CHART TYPES........................................................................................................ 59
AVAILABLE TYPES OF CHART .................................................................................... 59
CHANGING THE CHART TYPE .................................................................................... 60
DEFAULT CHART TYPE ............................................................................................ 62
FORMATTING CHARTS ............................................................................................ 62
DESIGN RIBBON .................................................................................................... 62
DATA SOURCE ...................................................................................................... 63
SWITCH ROWS AND COLUMNS ................................................................................. 64
ADD A SERIES MANUALLY ....................................................................................... 65
THE SERIES FUNCTION ............................................................................................ 65
CHARTING WITH BLOCKS OF DATA ........................................................................... 65
CHANGING THE CHART LAYOUT ................................................................................ 66
CHART STYLES ...................................................................................................... 66
MOVING CHART LOCATION ..................................................................................... 67
LAYOUT RIBBON .................................................................................................... 68
FORMATTING CHART ELEMENTS ............................................................................... 68
RESETTING CUSTOM FORMATS ................................................................................ 69
ADDING, REMOVING AND FORMATTING LABELS .......................................................... 69
AXES ................................................................................................................... 70
GRIDLINES ........................................................................................................... 72
UNATTACHED TEXT ................................................................................................ 72
FORMAT DIALOG ................................................................................................... 72
SPARKLINES .................................................................................................................. 76
CREATE SPARKLINES ............................................................................................... 77
AXIS OPTIONS ....................................................................................................... 78
ABSOLUTE AND RELATIVE REFERENCES ...................................................................... 80
RELATIVE REFERENCES........................................................................................................ 80
ABSOLUTE REFERENCES ...................................................................................................... 80
- iii -
NAMING CELLS AND RANGES ...................................................................................... 82
RULES WHEN NAMING CELLS .................................................................................. 82
NAME BOX .......................................................................................................... 82
DEFINING A NAME ................................................................................................ 83
SELECTING NAMES (NAVIGATION) ............................................................................ 84
MANAGE NAMES BY USING THE NAME MANAGER ..................................................... 85
BODMAS WITH FORMULAE ......................................................................................... 92
FUNCTIONS .................................................................................................................. 94
BASIC SUM FUNCTION ............................................................................................ 94
AUTOSUM ........................................................................................................... 94
OTHER COMMON FUNCTIONS.................................................................................. 95
FUNCTION LIBRARY ................................................................................................ 95
INSERT FUNCTION.................................................................................................. 95
FUNCTION BOX ..................................................................................................... 97
TYPE FUNCTIONS ................................................................................................... 97
FUNCTION ARGUMENT TOOL TIPS ............................................................................ 98
CELL REFERENCES .................................................................................................. 98
CONDITIONAL & LOGICAL FUNCTIONS ........................................................................ 99
IF STATEMENTS ..................................................................................................... 99
LOGICAL TEST ..................................................................................................... 100
VALUE IF TRUE / FALSE ........................................................................................ 100
NESTED IF .......................................................................................................... 101
COUNTING AND TOTALLING CELLS CONDITIONALLY .................................................... 102
STATISTICAL IF STATEMENTS .................................................................................. 103
SUMIF ............................................................................................................... 104
COUNTIF............................................................................................................ 105
AVERAGEIF......................................................................................................... 106
AVERAGEIFS ....................................................................................................... 107
SUMIFS ............................................................................................................. 108
COUNTIFS .......................................................................................................... 110
AND, OR, NOT ............................................................................................................. 111
AND FUNCTION .................................................................................................. 111
OR FUNCTION .................................................................................................... 112
- iv -
NOT FUNCTION ................................................................................................... 113
ISERROR FUNCTION ........................................................................................... 113
IFERROR FUNCTION ........................................................................................... 114
LOOKUP FUNCTIONS ........................................................................................................ 117
LOOKUP ............................................................................................................ 117
VECTOR LOOKUP ................................................................................................. 117
HLOOKUP .......................................................................................................... 119
VLOOKUP .......................................................................................................... 121
NESTED LOOKUPS ................................................................................................ 122
MATCH() ......................................................................................................... 123
TWO-WAY LOOKUP ............................................................................................. 123
INDEX FUNCTION ............................................................................................... 124
COMBINING MATCH AND INDEX ......................................................................... 125
CHOOSE ............................................................................................................ 126
OFFSET ............................................................................................................. 127
FINANCIAL FUNCTIONS .............................................................................................. 130
NPV() .............................................................................................................. 130
COMPARING INVESTMENT SCHEDULES ..................................................................... 131
IRR() ................................................................................................................ 132
PMT() .............................................................................................................. 133
CALCULATING LOAN REPAYMENTS .......................................................................... 134
FV() ................................................................................................................. 134
DATE & TIME CALCULATIONS .................................................................................... 136
DATES ............................................................................................................... 136
NETWORKDAYS() ............................................................................................ 136
ANALYSIS TOOLPAK ............................................................................................. 136
TODAY() .......................................................................................................... 137
NOW ............................................................................................................... 138
DAY(), MONTH() AND YEAR() .......................................................................... 138
TIME CALCULATIONS ........................................................................................................ 139
SUBTRACTING TIMES ............................................................................................ 139
TOTALLING TIMES ............................................................................................... 139
TEXT FUNCTIONS........................................................................................................ 142
- v -
PROPER, UPPER, LOWER, REPT ...................................................................... 142
CONCATENATE ............................................................................................... 142
STRING FUNCTIONS (LEFT, MID, RIGHT) .............................................................................. 143
LEFT FUNCTION ................................................................................................. 143
MID FUNCTION .................................................................................................. 143
RIGHT FUNCTION ............................................................................................... 143
CREATING A CUSTOM LIST ......................................................................................... 144
USING YOUR CUSTOM LIST ................................................................................... 145
EDITING THE LIST ................................................................................................. 145
EXCEL LISTS................................................................................................................. 146
LIST TERMINOLOGY.......................................................................................................... 146
ROW AND COLUMN CONTENT ............................................................................... 146
COLUMN LABELS ................................................................................................. 146
LIST SIZE AND LOCATION ....................................................................................... 146
MISCELLANEOUS ................................................................................................. 147
SORTING DATA ............................................................................................................... 147
QUICK SORT ....................................................................................................... 147
MULTI LEVEL SORT .............................................................................................. 148
CUSTOM SORTING OPTIONS .................................................................................. 149
CREATING A CUSTOM SORT ORDER ........................................................................ 150
SUBTOTALS .................................................................................................................... 151
ORGANISING THE LIST FOR SUBTOTALS .................................................................... 151
CREATE SUBTOTALS ............................................................................................. 151
SUMMARISING A SUBTOTALLED LIST........................................................................ 153
SHOW AND HIDE BY LEVEL .................................................................................... 153
REMOVE SUBTOTALS ............................................................................................ 155
FILTERING A LIST ............................................................................................................. 155
AUTOFILTERS ...................................................................................................... 156
SEARCH CRITERIA ................................................................................................ 157
CUSTOM CRITERIA AND / OR................................................................................. 158
TURNING OFF AUTOFILTER .................................................................................... 161
ADVANCED FILTERING .......................................................................................... 161
CRITERIA TIPS ..................................................................................................... 163
- vi -
CALCULATED CRITERIA .......................................................................................... 165
DATABASE FUNCTIONS ......................................................................................... 168
PIVOT TABLES ............................................................................................................. 171
CREATE A PIVOTTABLE ..................................................................................................... 173
SELECT A DATA SOURCE ........................................................................................ 174
SET A LOCATION .................................................................................................. 175
CREATE A PIVOTCHART FROM THE PIVOTTABLE ........................................................ 175
MAKE PIVOTCHART STATIC ................................................................................... 176
CREATE A STATIC CHART FROM THE DATA IN A PIVOTTABLE REPORT ............................ 176
DELETE A PIVOTTABLE OR PIVOTCHART REPORT....................................................... 177
TO DELETE A PIVOTCHART REPORT ......................................................................... 177
TO CREATE A LAYOUT ........................................................................................... 178
MODIFYING A PIVOT TABLE ................................................................................... 179
SORT A PIVOTTABLE ............................................................................................ 180
FILTER A PIVOTTABLE .......................................................................................... 181
MANAGING PIVOT TABLES ................................................................................................ 182
REFRESH A PIVOTTABLE WITH INTERNAL DATA .......................................................... 182
EXTERNAL DATA REFRESH ................................................................................................. 183
AUTOMATICALLY REFRESH DATA WHEN A WORKBOOK IS OPENED ............................... 183
AUTOMATICALLY REFRESH DATA AT REGULAR TIME INTERVALS .................................... 183
REQUIRE A PASSWORD TO REFRESH AN EXTERNAL DATA RANGE .................................. 183
GROUPING PIVOTTABLE ITEMS .............................................................................. 184
FORMATTING A PIVOT TABLE ................................................................................. 187
PIVOT TABLE STYLES ............................................................................................ 187
BANDING ........................................................................................................... 188
SLICERS ......................................................................................................................... 190
MAKE A SLICER AVAILABLE FOR USE IN ANOTHER PIVOTTABLE .................................... 191
STANDALONE SLICER ............................................................................................ 194
FORMULAS IN PIVOT TABLES ................................................................................. 194
ADD A PIVOT TABLE CALCULATED FIELD ................................................................... 195
CREATING A CALCULATED FIELD ............................................................................. 195
REMOVE A PIVOT TABLE CALCULATED FIELD ............................................................. 196
CREATE LIST OF PIVOT TABLE FORMULAS ................................................................. 197
- vii -
SUMMARISE DATA IN PIVOT TABLE ......................................................................... 198
SHOWING DATA AS % OF COLUMN TOTAL ............................................................... 198
SHOWING DATA AS % OF ROW .............................................................................. 199
SHOWING DATA AS % OF TOTAL ............................................................................ 200
INTRODUCTION TO MACROS .............................................................................................. 201
WHAT IS A MACRO? ............................................................................................. 201
MACRO SECURITY LEVELS ..................................................................................... 201
HOW TO CHANGE MACRO SECURITY LEVEL .............................................................. 202
TYPES OF MACRO ................................................................................................ 202
PERSONAL WORKBOOK OR WORKSHEET ................................................................... 202
RECORDED MACRO LIMITATIONS............................................................................ 203
BASIC MACRO RECORDING ............................................................................................... 204
TO RECORD AN ABSOLUTE MACRO ...................................................................................... 204
TO RECORD A RELATIVE MACRO .......................................................................................... 205
RUNNING AND ASSIGNING MACROS TO OBJECTS .................................................................... 206
TO RUN A MACRO ............................................................................................... 206
ASSIGN A MACRO TO A BUTTON ON A SHEET .............................................................. 206
ASSIGN A MACRO TO AN IMAGE OR OBJECT ............................................................... 206
ASSIGNING A MACRO ON THE USER ACCESS TOOLBAR ................................................. 207
IDENTIFY AN EXCEL TASK TO AUTOMATE .............................................................................. 208
GET READY TO RECORD ........................................................................................ 208
START RECORDING .............................................................................................. 208
PERFORM THE MACRO STEPS ................................................................................ 209
STOP THE RECORDING .......................................................................................... 210
GET READY TO TEST THE MACRO ........................................................................... 210
MACRO SECURITY SETTINGS .................................................................................. 211
TEST THE MACRO ................................................................................................ 211
RUN THE RECORDED MACRO ................................................................................. 211
SAMPLE DATA FOR ABOVE MACRO ........................................................................ 212
IDENTIFY AN EXCEL TASK TO AUTOMATE .............................................................................. 214
GET READY TO RECORD ........................................................................................ 214
START RECORDING .............................................................................................. 214
PERFORM THE MACRO STEPS ................................................................................ 215
STOP THE RECORDING .......................................................................................... 216
- viii -