MS-EXCEL FOR ALL

TABLE OF CONTENTS
1 Basics & Beyond... ..........................................................................................................4
1.1 Introduction... ..........................................................................................................4
1.2 The EXCEL Screen... ........................................................................................... ...4
1.3 Moving Around... ....................................................................................................5
2 Data Entry ... ................................................................................................................... .8
2.1 Text ... ......................................................................................................................8
2.2 Number (including date, time, percent) ... ............................................................ ..8
2.3 Formulae ... ............................................................................................................. .8
2.4 Functions ... ............................................................................................................ .9
2.5 AutoComplete ... ................................................................................................... ..9
2.6 AutoCorrect... ........................................................................................................10
2.7 AutoFill ... ..............................................................................................................10
2.8 Data Validation ... ..................................................................................................11
3 Totals & More.. . ......................................................................................................... ..13
3.1 + + + ,…. why not? ... ...........................................................................................13
3.2 SUM() Function ... ............................................................................................ ...13
3.3 QuickSum ... ...................................................................................................... ...13
3.4 SUBTOTAL() Function ... ................................................................................ ...14
3.5 SUMIF() Function ... .......................................................................................... ..14
3.6 Sorting Data ... ................................................................................................... ...14
3.7 Sub-Totals ... ...................................................................................................... ...15
3.8 Conditional Sum Add-In ... ................................................................................ ..16
4 Queries in Lists ... ...................................................................................................... ...17
4.1 AutoFilter ... ...........................................................................................................17
4.2 Advanced Filter... ..................................................................................................17
5 Functions ... .................................................................................................................. .19
5.1 Lookup Functions ... .......................................................................................... ...19
5.2 Date Functions ... ................................................................................................ ..19
5.3 Numeric Functions ... ......................................................................................... ..21
5.4 Text Functions ... ................................................................................................ ..21
5.5 Financial Functions ... ...........................................................................................21
5.6 Some more functions ... .........................................................................................22
6 The Look & Feel of Output ... .................................................................................... ..24
6.1 Formatting ... ..................................................................................................... ...24
6.2 Styles... ................................................................................................................ ..25
6.3 Conditional Formatting ... .....................................................................................26
6.4 Custom Views.. . ............................................................................................... ...26
6.5 Printing... ............................................................................................................ ..27
6.6 Saying it with Charts... ....................................................................................... ..27
7 Copying & Moving ... ................................................................................................ ...28
7.1 Paste Special ... ................................................................................................... ..29
8 Saving Work & Protecting It ... ................................................................................. ...30
8.1 File-Level Protection ... .........................................................................................30
9 Analysing Data... ...........................................................................................................32
9.1 Data Tables ... ........................................................................................................32
9.2 Scenarios ... ......................................................................................................... ..33
9.3 Goal Seek ... ...........................................................................................................33
9.4 Solver ... .................................................................................................................33
10 PivotTables ... ......................................................................................................... ..35
10.1 Creating a Pivot Table ... .................................................................................... ..35
10.2 Layout of the PivotTable ... ...................................................................................37
10.3 Some Examples:... ............................................................................................. ...37
11 Auditing Tools ... ................................................................................................... ...41
11.1 Auditing Toolbar... ............................................................................................. ..41
11.2 Documenting a Sheet ... ........................................................................................41
11.3 Migrating Data from Other Software... ................................................................43
11.4 Common Audit Techniques ... .......................................................................... ...43
12 Automating MS-EXCEL ... .................................................................................... ..44
12.1 Open EXCEL each time computer starts ... ..........................................................44
12.2 Open a particular file each time EXCEL starts... ................................................ .44
12.3 Create a new file based on a template each time computer starts... ................. ...44
12.4 Specifying the Defaults in EXCEL ... ...................................................................44
12.5 Customizing Menus & Toolbars.. . .................................................................... ..45
12.6 Customization Options... .................................................................................... ..46
12.7 Templates ..............................................................................................................46
12.8 Workspaces.. . ........................................................................................................47
12.9 Talking with Other Software ................................................................................47
13 Introduction to Macros... ....................................................................................... ...48
13.1 Global Macros vs. Individual Macros... ........................................................... ...48
13.2 Use of Macro Recorder ... .....................................................................................49
13.3 Running Macros ... ............................................................................................ ...49
13.4 Basics of VB Programming ... .......................................................................... ...51
1 ANNEXURE A: KEYBOARD SHORTCUTS ... .................................................... ..53
2 ANNEXURE “B” IMPORTANT EXCEL FUNCTIONS... ........................................55
3 ANNEXURE “C”: COMMON ERROR CODES .......................................................59
4 ANNEXURE “D” LIST OF ADD-INS PROVIDED WITH MS-EXCEL ... .......... ..61