DPA202T/103/2/2010
DEPARTMENT OF
MANAGEMENT ACCOUNTING
PRACTICAL ACCOUNTING DATA PROCESSING
DPA202T
Tutorial letter 103/2010
ADDITIONAL GUIDANCE FOR
STUDY UNIT 4 (MICROSOFT OFFICE EXCEL)
SECOND SEMESTER
Contents
1 Basic principles to revise before working on Microsoft Office Excel
2 How to solve a problem in Microsoft Office Excel
3 Background
4 Workbook basics
5 Operating a workbook
6 Workbook formatting
7 Printing & Page layout
8 Working with formulas
9 Working with functions
10 Working with data
11 Working with charts
12 Frequent mistakes in Microsoft Office Excel questions (and how not
to lose hard earned marks)
, 2
DPA202T/103/2/2010
Dear Student
This tutorial letter is the manual for achieving the aim of study unit four. It is only available in English. The
examination will include questions based on the competencies learned in this tutorial letter.
As stipulated in the yearbook under the heading “General Information” we expect you to have access to the
Microsoft Office Excel spreadsheet software or Open Office Calc (which can be downloaded free of charge from
the Internet). It is not our intention to give you a comprehensive knowledge of the Microsoft Office Excel or
Open Office package, but rather to equip you with some basic principles so that you will be able to operate a
spreadsheet package with ease. Please note this tutorial letter is based on Microsoft Office Excel 2007 and you
will need to make some adjustments if you are using Microsoft Office Excel 2003 or Open Office.
You will find, when using the help key (F1), that there is more than one way of executing a command (e.g.
mouse, keyboard and menu). We will only show you some of the options, but in the exam, you are welcome to
write down any command option you know. However, for your answer to be correct, the command should be
written down correctly and in the right order.
A Microsoft Office Excel file, DPA202T_2010_TL103_1_E_activities data.xlsx, containing the data used in the
activities, is available for download from myUnisa under Additional resources. Please note this file only
contains data and not any formulas or functions. The Microsoft Office Excel file containing both the data and
the completed formulas and functions are available from the lecturers. The completed file will however only be
made available to you if we receive your Microsoft Office Excel file with your attempts to complete all the
activities.
The best way to learn Microsoft Office Excel is to use it in your day-to-day work. Always ask, “How can I do this
task quicker and easier by using Microsoft Office Excel?” There will be a Microsoft Office Excel discussion
forum on myUnisa. Please post your questions on this forum and please help your fellow students with answers
to their questions. Please also post Microsoft Office Excel functions and examples you personally found very
useful in your day-to-day work. Note this discussion forum is not limited to the functions covered in this tutorial
letter. We will check the discussion forum at least once a week and make sure you are still on the right track.
Finally, we sincerely hope you enjoy the course, and that you will continue to benefit from it in the future. All the
best and every success with your studies - remember the only place where success is before work, is in the
dictionary.
Microsoft product screen shots used in this tutorial letter is reprinted with permission from Microsoft Corporation.
Kind regards,
Lecturer
Miss. GM Viviers (Gerda) Miss. C Leonard (Christi)
AJH van der Walt room 1-69 AJH van der Walt room 1-70
Tel nr: (012) 429 3914 Tel nr: (012) 429 4025
Cel nr: 072 683 6482 Cel nr: 072 683 6482
Fax nr: 086 532 5869 Fax nr: 086 541 4855
E-mail:
First semester:
Second semester:
Fax:
Fax number of Departement of Management Accounting: (012) 429-4894
, 3 DPA202T/103/2/2010
The detail table of content for this tutorial letter is:
1. Basic principles to revise before working on Microsoft Office Excel
1.1. Signs/operators
1.2. Order of operation
1.3. Changing the sign of an amount
1.4. Percentages %
1.5. Working with VAT and Gross profit%
2. How to solve a problem in Microsoft Office Excel
3. Background
3.1. What is a spreadsheet
3.2. Components/layout of a spreadsheet
3.2.1. Active cell
3.2.2. Ribbon
3.2.3. Dialogue Box Launcher
3.2.4. Microsoft Office Button
3.2.5. Quick Access Toolbar
4. Workbook basics
4.1. Starting a workbook
4.2. Saving a file
4.3. Close a file and exit Microsoft Office Excel
4.3.1. Close a file
4.3.2. Close a file and exit Microsoft Office Excel
4.4. Naming/renaming worksheets
4.5. Inserting a worksheet
4.6. Inserting a column or row
4.7. Deleting a worksheet
4.8. Deleting a column or row
4.9. Moving around in a spreadsheet
4.10. Selecting a worksheet range
5. Operating a workbook
5.1. Entering data
5.1.1. Entering numbers
5.1.2. Entering text
5.2. Editing data
5.2.1. Correcting errors as data is being entered
5.2.2. Correcting errors after data was entered
5.3. Removing data
5.4. Copying data
5.5. Moving of text
6. Workbook formatting
6.1. Cell formatting
6.1.1. Number formatting
6.1.2. Alignment of data
6.1.3. Change font and attributes
6.1.4. Borders and Frames
6.1.5. Patterns
6.1.6. Protection
6.2. Column width
6.3. Row height
, 4
7. Printing & Page layout
7.1. Print area
7.2. Page setup
7.2.1. Page
7.2.2. Margins
7.2.3. Headers/footers
7.2.4. Sheet (Gridlines and Row & Column headings)
7.3. Print & Print preview
8. Working with formulas
8.1. Common errors when using formulas and functions
8.2. Entering formulas
8.2.1. Adding, subtracting, dividing and multiplying values
8.2.2. Adding values in two separate worksheets
8.2.3. Combining text and values (&)
8.3. Copying formulas
8.3.1. Relative reference
8.3.1.1. Copy down
8.3.1.2. Copy across
8.3.1.3. Copy across & down
8.3.2. Absolute reference
8.3.2.1. Absolute row reference
8.3.2.2. Absolute column reference
8.3.2.3. Absolute column and row reference
9. Working with functions
9.1. Structure of a function
9.2. Reference operators
9.2.1. Colon (:)
9.2.2. Comma (,)
9.2.3. Combining a colon (:) and comma (,)
9.3. Insert a function
9.4. Commonly used functions
9.4.1. Mathematical functions
9.4.1.1. Sum
9.4.1.2. Round
9.4.2. Statistical functions
9.4.2.1. Average
9.4.2.2. Max
9.4.2.3. Min
9.4.3. Financial functions
9.4.3.1. Payments
9.4.3.2. Future value
9.4.3.3. Present value
9.4.4. Logical functions (IF)
9.4.5. Text functions
9.4.5.1. Left
9.4.5.2. Mid
9.4.5.3. Right
9.4.6. Lookup functions (VLOOKUP)
10. Working with data
10.1. Sorting data
10.2. Filter data
DEPARTMENT OF
MANAGEMENT ACCOUNTING
PRACTICAL ACCOUNTING DATA PROCESSING
DPA202T
Tutorial letter 103/2010
ADDITIONAL GUIDANCE FOR
STUDY UNIT 4 (MICROSOFT OFFICE EXCEL)
SECOND SEMESTER
Contents
1 Basic principles to revise before working on Microsoft Office Excel
2 How to solve a problem in Microsoft Office Excel
3 Background
4 Workbook basics
5 Operating a workbook
6 Workbook formatting
7 Printing & Page layout
8 Working with formulas
9 Working with functions
10 Working with data
11 Working with charts
12 Frequent mistakes in Microsoft Office Excel questions (and how not
to lose hard earned marks)
, 2
DPA202T/103/2/2010
Dear Student
This tutorial letter is the manual for achieving the aim of study unit four. It is only available in English. The
examination will include questions based on the competencies learned in this tutorial letter.
As stipulated in the yearbook under the heading “General Information” we expect you to have access to the
Microsoft Office Excel spreadsheet software or Open Office Calc (which can be downloaded free of charge from
the Internet). It is not our intention to give you a comprehensive knowledge of the Microsoft Office Excel or
Open Office package, but rather to equip you with some basic principles so that you will be able to operate a
spreadsheet package with ease. Please note this tutorial letter is based on Microsoft Office Excel 2007 and you
will need to make some adjustments if you are using Microsoft Office Excel 2003 or Open Office.
You will find, when using the help key (F1), that there is more than one way of executing a command (e.g.
mouse, keyboard and menu). We will only show you some of the options, but in the exam, you are welcome to
write down any command option you know. However, for your answer to be correct, the command should be
written down correctly and in the right order.
A Microsoft Office Excel file, DPA202T_2010_TL103_1_E_activities data.xlsx, containing the data used in the
activities, is available for download from myUnisa under Additional resources. Please note this file only
contains data and not any formulas or functions. The Microsoft Office Excel file containing both the data and
the completed formulas and functions are available from the lecturers. The completed file will however only be
made available to you if we receive your Microsoft Office Excel file with your attempts to complete all the
activities.
The best way to learn Microsoft Office Excel is to use it in your day-to-day work. Always ask, “How can I do this
task quicker and easier by using Microsoft Office Excel?” There will be a Microsoft Office Excel discussion
forum on myUnisa. Please post your questions on this forum and please help your fellow students with answers
to their questions. Please also post Microsoft Office Excel functions and examples you personally found very
useful in your day-to-day work. Note this discussion forum is not limited to the functions covered in this tutorial
letter. We will check the discussion forum at least once a week and make sure you are still on the right track.
Finally, we sincerely hope you enjoy the course, and that you will continue to benefit from it in the future. All the
best and every success with your studies - remember the only place where success is before work, is in the
dictionary.
Microsoft product screen shots used in this tutorial letter is reprinted with permission from Microsoft Corporation.
Kind regards,
Lecturer
Miss. GM Viviers (Gerda) Miss. C Leonard (Christi)
AJH van der Walt room 1-69 AJH van der Walt room 1-70
Tel nr: (012) 429 3914 Tel nr: (012) 429 4025
Cel nr: 072 683 6482 Cel nr: 072 683 6482
Fax nr: 086 532 5869 Fax nr: 086 541 4855
E-mail:
First semester:
Second semester:
Fax:
Fax number of Departement of Management Accounting: (012) 429-4894
, 3 DPA202T/103/2/2010
The detail table of content for this tutorial letter is:
1. Basic principles to revise before working on Microsoft Office Excel
1.1. Signs/operators
1.2. Order of operation
1.3. Changing the sign of an amount
1.4. Percentages %
1.5. Working with VAT and Gross profit%
2. How to solve a problem in Microsoft Office Excel
3. Background
3.1. What is a spreadsheet
3.2. Components/layout of a spreadsheet
3.2.1. Active cell
3.2.2. Ribbon
3.2.3. Dialogue Box Launcher
3.2.4. Microsoft Office Button
3.2.5. Quick Access Toolbar
4. Workbook basics
4.1. Starting a workbook
4.2. Saving a file
4.3. Close a file and exit Microsoft Office Excel
4.3.1. Close a file
4.3.2. Close a file and exit Microsoft Office Excel
4.4. Naming/renaming worksheets
4.5. Inserting a worksheet
4.6. Inserting a column or row
4.7. Deleting a worksheet
4.8. Deleting a column or row
4.9. Moving around in a spreadsheet
4.10. Selecting a worksheet range
5. Operating a workbook
5.1. Entering data
5.1.1. Entering numbers
5.1.2. Entering text
5.2. Editing data
5.2.1. Correcting errors as data is being entered
5.2.2. Correcting errors after data was entered
5.3. Removing data
5.4. Copying data
5.5. Moving of text
6. Workbook formatting
6.1. Cell formatting
6.1.1. Number formatting
6.1.2. Alignment of data
6.1.3. Change font and attributes
6.1.4. Borders and Frames
6.1.5. Patterns
6.1.6. Protection
6.2. Column width
6.3. Row height
, 4
7. Printing & Page layout
7.1. Print area
7.2. Page setup
7.2.1. Page
7.2.2. Margins
7.2.3. Headers/footers
7.2.4. Sheet (Gridlines and Row & Column headings)
7.3. Print & Print preview
8. Working with formulas
8.1. Common errors when using formulas and functions
8.2. Entering formulas
8.2.1. Adding, subtracting, dividing and multiplying values
8.2.2. Adding values in two separate worksheets
8.2.3. Combining text and values (&)
8.3. Copying formulas
8.3.1. Relative reference
8.3.1.1. Copy down
8.3.1.2. Copy across
8.3.1.3. Copy across & down
8.3.2. Absolute reference
8.3.2.1. Absolute row reference
8.3.2.2. Absolute column reference
8.3.2.3. Absolute column and row reference
9. Working with functions
9.1. Structure of a function
9.2. Reference operators
9.2.1. Colon (:)
9.2.2. Comma (,)
9.2.3. Combining a colon (:) and comma (,)
9.3. Insert a function
9.4. Commonly used functions
9.4.1. Mathematical functions
9.4.1.1. Sum
9.4.1.2. Round
9.4.2. Statistical functions
9.4.2.1. Average
9.4.2.2. Max
9.4.2.3. Min
9.4.3. Financial functions
9.4.3.1. Payments
9.4.3.2. Future value
9.4.3.3. Present value
9.4.4. Logical functions (IF)
9.4.5. Text functions
9.4.5.1. Left
9.4.5.2. Mid
9.4.5.3. Right
9.4.6. Lookup functions (VLOOKUP)
10. Working with data
10.1. Sorting data
10.2. Filter data
