MS-EXCEL FOR CHARTERED ACCOUNTANTS
1 Basics & Beyond..
1.1 Introduction
Microsoft Excel is a spreadsheet program that is designed to record and analyze numbers and data. Excel is very widely used for accounting and financial purposes.
The files created in Excel are known as workbooks. In turn, each workbook can contain one or more worksheets. An Excel worksheet is laid out like a grid with horizontal rows
and vertical columns. Columns are labeled with alphabets (A, B, C, etc.) while rows are given numbers (1, 2, 3, etc.). The intersection of a row and a column is called a cell. A
cell is referred by a combination of column alphabet and row number (A1, A2, etc.). A cell is a primary unit of measure in Excel and all the information is stored in cells. . A
range is a collection of contiguous cells (which form a rectangular block) on which the user wants to perform similar type of calculations. A range is referred to by a combination of the cell addresses of the diagonally opposite cells separated by a colon.
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
Download attached file: You must be Loged in to download file