Learner Guide:
Excel Spreadsheets
SAQA US 116937:
Use a Graphical User Interface (GUI)-based spreadsheet application to create and edit spreadsheets
Specific Outcome 1:
Demonstrate an understanding of the principles of spreadsheets
https://www.obami.com/portals/Silulo_Ulutho_Technologies/ExcelSpreadsheets/UnitStandard1/QR_SO1_1
SPECIFIC OUTCOME 1
Demonstrate an understanding of the principles of spreadsheets.
At the end of this unit standard you should be able to understand what a spreadsheet is.
ASSESSMENT CRITERION 1
Spreadsheet is defined in terms of its purpose and use.
Note: A spreadsheet is a computer application (software program) that looks like a paper
accounting worksheet.
Excel is a spreadsheet program .The desktop displays many cells in rectangular grid that consists
of rows and columns.
The word "spreadsheet" came from "spread" in its sense of a newspaper or magazine item (text
and/or graphics) that covers two facing pages, extending across the center fold and treating the
two pages as one large one.
The compound word "spread-sheet" came to mean the format used to present book-keeping
ledgers:
✓ with columns for categories of expenditures across the top,
✓ invoices listed down the left margin,
✓ and the amount of each payment in the cell where its row and column intersect, which were,
traditionally, a "spread" across facing pages of a bound ledger (book for keeping accounting
records) or on oversized sheets of paper ruled into rows and columns in that format and
approximately twice as wide as ordinary paper.
Tip: An electronic spreadsheet program can be used for storing, organising and
manipulating data.
A spreadsheet will add long columns or rows of figures for you. You can also do other
calculations, such as determining the average of a row of figures, calculating interest or finding
out the minimum or maximum values of a row or column of figures.
Once you have calculated the value of a row or column of figures, e.g. the average, and you
make changes to any of the numbers, the value will be changed to the new value automatically.
In a spreadsheet application, each value (the numbers you are working with) sits in a cell. when
you are working on the spreadsheet, you define the type of data in each cell and how the
different cells depend on each other.
Tip: The relationships between cells are called formulas (for example multiply or add the
values of two or more cells), and the names (invoice number or amount, etc.) of
the cells are called labels.
The first thing therefore is to define the cells and the formulas for linking them together. Now you
will enter the data you want to manipulate
A simple example of a useful spreadsheet application is one that calculates mortgage payments
for a house. You would define five cells:
1. Total cost of the house
2. Down payment
3. Mortgage rate (the interest rate)
4. Mortgage term (the number of years or months in which you will repay the house)
5. Monthly payment
You have now defined how the cells depend on each other and now you can manipulate the
data: you could change the interest rates to find out how that will affect your monthly payments,
or you could change the amount of the housing loan to find out how
Once you had defined how these cells depend on one another, you could enter numbers and
play with various possibilities. For example, keeping all the other values the same, you could see
how different interest rates would affect your monthly payments or how different monthly
payments will affect your repayment term.
You can therefore study various “what if” scenarios
In short, Excel will automate repetitive calculation tasks for you by for example, adding rows or columns of
figures.
ASSESSMENT CRITERION 2
Examples of spreadsheets that can be produced using a spreadsheet application are provided.
Spreadsheets are used to process and perform calculations of raw data. They are used frequently
in the fields of business and accounting.
A spreadsheet appears as a grid where the rows are numbered and the columns are marked with
letters from A to Z, then AA to ZZ and so on.
Since a spreadsheet allows for calculations on data, the parameters can be changed and
manipulated in order to produce forecasting and modelling, often used in areas such as
budgeting and sales. Graphs and charts can be produced from the results. The data can be
merged with email functions to produce mailing lists.
MORE USES OF SPREADSHEETS:
✓ You can use a spreadsheet to calculate your monthly budget
✓ You can work out the amount of interest your savings will earn for the next 2 or even 5 years
✓ You can work out average sales per person for a certain period
✓ You can calculate average school marks per subject for yourself or your children
✓ You can work out how much an increase of 10% will be on the prices of food or even your salary
✓ A computer spreadsheet will also act as a database: You can also put together a list of clients or
contacts with all their details: addresses, telephone numbers, postal addresses, etc.
✓ Companies use spreadsheets to calculate average income, cash flow and even fluctuations in
budgets
WE CAN THEREFORE SAY THAT A SPREADSHEET IS USED FOR:
✓ Performing basic mathematical operations such as summing columns and rows of figures.
✓ Finding values such as profit or loss.
✓ Calculating repayment plans for loans or mortgages.
✓ Finding the average, maximum, or minimum values in a specified range of data.
✓ Graphing or charting data to assist users in identifying data trends.
✓ Sorting and filtering data to find specific information.
OTHER EXAMPLES AND USES OF A SPREADSHEET
Although spreadsheets are typically used with anything containing numbers, the uses of a spreadsheet are
almost endless. Below are some other popular uses of spreadsheets.
Finance
Like our example shown above, spreadsheets are great at any financial data such as your checking
account information, budgets, transactions, billing, invoices, receipts, forecasts, and any payment system.
Forms
Form templates can be created to handle inventory, evaluations, performance reviews, quizzes, time
sheets, patient information, and surveys.
School and Grades
In a school, teachers can use a spreadsheet to track students, calculate overall grades, and identify
important data such as high and low scores, missing tests, and students who are struggling.
Lists
Managing a list in a spreadsheet is a great example of data that does not contain numbers, but still can be
used in a spreadsheet. Great examples of spreadsheet lists include telephone, to-do, and grocery lists.
Sports
Spreadsheets can keep track of your favourite player stats or stats on the whole team. With the collected
data, you can also find averages, high scores, and other statistical data. Spreadsheets can even be used to
create tournament brackets.
ASSESSMENT CRITERION 3
The benefits of using a spreadsheet application for producing and working with spreadsheets are
explained.
One look at the range of free templates available online for Microsoft Excel, Google Docs and
OpenOffice; Calc shows that spreadsheets are useful to home and business users of all skill levels.
An underlying benefit, no matter how you use them, is the relative ease with which you can turn
raw data into useful information
BENEFITS OF SPREADSHEETS:
1. Calculation and Error-Handling Capabilities
The built-in calculators, formulas and functions in spreadsheets save time and improve mathematical
accuracy. You can also use formulas and functions to manipulate text strings. For example, you can
separate first and last names from a full name or find and replace words or characters in a string with
another word or set of characters. Formulas can link and instantly update cells in the spreadsheet should
the value of a cell change. If you do make a mistake, built-in error-handling capabilities flag and provide
information about the incorrect cell value.
2. What-if Scenarios
Spreadsheets support basic and advanced data modelling, which can benefit both home and business
users. A home user might create a basic what-if scenario to see how changing a single variable, such as the
price of gasoline or food, will affect their budget. A business user might benefit more from a complex
scenario that shows how changing more than one variable affects a break-even or profit-volume-cost
analysis. Advanced data modelling techniques, such as pivot tables and charts, are useful for sorting and
summarizing data without changing the original data layout.
3.
Formatting and Styling
Spreadsheets come with a range of presentation and formatting options that not only improve readability,
but also make it possible to use the data in external reports and presentations. Styling options allow you to
change fonts, font sizes, background and text colours and cell borders to create a neat look. Print options
allow you to define a specific print area and print all or a specified portion of a spreadsheet. Graph and
charting options are useful for displaying data in a report or a slideshow presentation.
4. Connections and Program Compatibility
Spreadsheets have features that permit you to get and use data, text and image files from other sources, as
well as share data with word processing, email, database and presentation programs. You can use a
contact information spreadsheet to create form letters in a mail merge or export spreadsheet data to a
database program. A variety of save options means you can use spreadsheets with more than one
company’s software. For example, save options mean you can use an Excel spreadsheet in an Open Office
program and use an Open Office Calc program in a Microsoft Office program.
ASSESSMENT CRITERION 4
Examples of spreadsheet programs are named.
A short history of spreadsheets
Visicalc is mostly considered the first electronic spreadsheet (although this has been challenged),
and it helped turn the Apple II computer into a success.
When DOS was the dominant operating system, Lotus 1-2-3 became the leading spreadsheet.
MS Excel now has the largest market sheet on the Windows and Macintosh platforms.
VisiCalc
The spreadsheet concept became widely known in the late 1970s and early 1980s. It was the first
spreadsheet that combined all essential features of modern spreadsheet applications, such as
✓ WYSIWYG interactive user interface,
✓ automatic recalculation,
✓ status and formula lines,
✓ range copying with relative and absolute references,
✓ formula building by selecting referenced cells
Lotus 1-2-3 and other MS-DOS spreadsheets
Lotus 1-2-3 was released on January 26, 1983, started outselling then-most-popular Visicalc the
very same year, and for a number of years was the leading spreadsheet for DOS.
Examples of spreadsheet applications
Microsoft Excel: Starting in the mid-1990s continuing through the present, Microsoft Excel has
dominated the commercial electronic spreadsheet market.
Apple Numbers: Numbers is Apple Inc’s spreadsheet software and is part of iWork.
OpenOffice.org Calc: a free, open-source program modelled after MS Excel. Calc can both open
and save in the Excel (XLS) file format Calc can be acquired as both an installation file and a
portable program, capable of being run from a device such as a USB memory drive. It can be
downloaded from the OpenOffice.org website.
Gnumeric: a free spreadsheet program that is part of the GNOME Free Software Desktop Project. A
Windows version is available. It is intended to be a free replacement for proprietary spreadsheet
programs such as MS Excel.
Web based spreadsheets: With the coming of advanced web technologies such as Ajax circa 2005,
a new generation of online spreadsheets have been developed. Equipped with a rich Internet
application user experience, the best web based online spreadsheets have many of the features
seen in desktop spreadsheet applications, such as strong multi-user collaboration features and
real time updated from remote sources such as stock prices
Other spreadsheets
✓ IBM Lotus Symphony (2007)
✓ KSpread
✓ ZCubes-Calci
✓ Resolver One
Examples of spreadsheet program
Today, Microsoft Excel is the most popular and widely used spreadsheet program, but there are also many
alternatives. Below is a list of spreadsheet programs that can be used to create a spreadsheet.
• Google Docs - Online and collaborative Spreadsheet
• iWork Numbers - Apple Office Suite
• Lotus 1-2-3
• OpenOffice - Calc
• Lotus Symphony - Spreadsheets
• Microsoft Excel
• VisiCalc
ASSESSMENT CRITERION 5
Properties of a spreadsheet are identified and described in terms of its purpose and use.
The Excel Window
Ms Excel 2010 has a results-oriented user interface, making it easier for you to perform tasks in
Excel. The features of the Excel window include:
1. Quick Access Toolbar
This customizable toolbar allows you to add frequently used commands. Click on the down arrow
at the end of the toolbar to display the toolbar's options
2. Title Bar
The Title bar is where we find the name of the program as well as the document we are working in
– the Quick Access Toolbar is situated on the Title Bar
3. Ribbon
The Ribbon is the strip of buttons and icons located above the work area. The Ribbon is organized
into a series of tabs - such as File, Home, and Formulas. Each tab contains a number of related
features and options. First introduced in Excel 2007, the Ribbon replaced the menus and toolbars
found in Excel 2003 and earlier versions.
4. File Tab
The File tab is new to Excel 2010 - Sort of. It replaces the Office Button that was found in Excel 2007
and also the File menu from older Excel versions. Like the old file menu, the File tab options have
to do with file management, e.g. Opening new or existing worksheet files, saving, printing
5. Formula bar
It is situated directly above the worksheet. This area displays the contents of the active cell. It is
also used for entering or editing data and formulas into the active cell. This is situated right next to
the formula bar and displays the cell reference or the name of the active cell.
6. Scroll bars
Scroll Bars: are used to view parts of the workbook that are not visible.
7. Active cell
The active cell is recognized by its black outline (selection box). Data is always entered into the
active cell. Different cells can be made active by clicking on them with the mouse or by using the
arrow keys on the keyboard.
8. Column headings or letters
Columns run vertically on a worksheet and each one is identified by a letter in the column header.
9. Row headings or numbers
Rows run horizontally in an Excel 2010 worksheet and are identified by a number in the row
header.
10. Sheet tabs
Switching between worksheets in an Excel 2010 file is done by clicking on the sheet tab at the
bottom of the screen.
11. Windows Taskbar
This is a part of Windows and displays the buttons of the programs, which have been opened, as
well as the time and the Start button.
12. Status Bar
A horizontal bar at the bottom of the screen that displays information about the current condition
of the program, such as the status of items in the window, the progress of the current task, or
information about the selected item.
TO CUSTOMIZE THE RIBBON:
You can customize the Ribbon by creating
your own tabs that house your desired
commands. Commands are always housed
within a group, and you can create as many
groups as you need to keep your tabs
organized. You can also add commands to
any of the default tabs as long as you create
a custom group within the tab.
1. Right-click the Ribbon, then select Customize the Ribbon. A dialog box will appear.
2. Click New Tab. A new tab will be created with a new group inside it.
3. Make sure the new group is selected.
4. Select a command from the list on the left, then click Add. You can also drag
commands directly into a group.
5. When you are done adding commands, click OK.
If you do not see the command you want, click the Choose commands drop-down box and
select All Commands.
To minimize and maximize the Ribbon:
The Ribbon is designed to be easy to use and
responsive to your current tasks; however, if you find
that it's taking up too much of your screen space,
you can minimize it.
1. Click the arrow in the upper-right corner of
the Ribbon to minimize it.
2. To maximize the Ribbon, click the arrow
again.
When the Ribbon is minimized, you can make it
reappear by clicking a tab. However, the Ribbon will
disappear again when you're not using it.
