PC PASSPORT: Spreadsheets - SQA

PC Passport [pic] Spreadsheets Student Workbook
[pic] Published date: August 2008 Publication code: CB4122 Published by the Scottish Qualifications Authority
The Optima Building, 58 Robertson Street, Glasgow G2 8DQ
Ironmills Road, Dalkeith, Midlothian EH22 1LE www.sqa.org.uk The information in this publication may be reproduced to support the
delivery of PC Passport or its component Units. If it is to be used for any
other purpose, then written permission must be obtained from the Assessment
Materials and Publishing Team at SQA. It must not be reproduced for trade
or commercial purposes. © Scottish Qualifications Authority 2008 Introduction This student workbook is one of a range of eight titles designed to cover
topics for the refreshed PC Passport. Each title in the range covers the
required subject material and exercises for candidates studying PC
Passport. This workbook covers all three levels of PC Passport - Beginner,
Intermediate and Advanced - with each level clearly identified. There are a number of exercises associated with each subject and it is
recommended that centres download and use the sample exercise files
provided. Each workbook will help prepare candidates for the assessments for the
refreshed PC Passport. It is recommended that centres use the most up-to-
date Assessment Support Packs appropriate for their type of centre, eg
either school, FE or work-based.
Contents
Beginner 1
Spreadsheet Software 1
Excel Overview 1
Creating Workbooks 10
Exercise 1: Using Copy and Cut 16
Exercise 2: Using Copy and Paste 19
Exercise 3: Cell Formatting and Calculations 26
Exercise 4: Using Formulas 27
Exercise 5: Using AutoSum and Other Formatting Options 29
Cell Referencing 31
Exercise 6: Absolute/Relative Cell Referencing 34
Exercise 7: Absolute/Relative Cell Addressing and Statistical Functions 34
Formatting Text 35
Exercise 8: Formatting Cells 37
Exercise 9: Using AutoFormat 39
Previewing and Printing a Workbook 40
Exercise 10: Printing Your Workbook 45
Exercise 11: Working with Print Options 46
Using Functions 48 Intermediate 51
Mathematical Functions 52
Statistical Functions 53
Financial Functions 55
Logical Functions 57
Graphics 62
Charts 72
Exercise 12: Creating Charts 84
Exercise 13: Amending Charts 85 Advanced 87
File Protection 88
Exercise 14: Applying File Protection 95
Creating Spreadsheet Templates 96
Data Validation 98
Using Macros 104
Exercise 15: Creating a Macro 107
PivotTable Reports 108
Exercise 16: Using PivotTables 116
Using Goal Seek 116
Exercise 17: Using Scenarios 118
Sorting Data 119
Exercise 18: Sorting 121
Using Lists 121
Exercise 19: Using Lists 123
Using Filters 125
Exercise 20: Using Filters 129
Using Functions with Lists and Filters 132
Exercise 21: Using Lists, Data Forms and Filters 137
Finally 138 Spreadsheet Software To enable you to perform calculations on numbers, hold lists of things,
perform analyses on data and create charts, you need to use spreadsheet
software. Spreadsheet software allows you to do all of these things and is
the most widely used software in any office, especially when you need to
keep financial information. There are two popular applications: Lotus 123
and Microsoft Excel. You can also get specialist financial and accounting
software like SAGE.
Opening and Closing Spreadsheet Software You can open your spreadsheet software in a number of ways. The most
popular method is using the Start menu, which is shown at the bottom left
of the Windows screen on the taskbar: o Click the Start button, choose All Programs (or Programs if you don't use
Windows XP) and then Microsoft Excel or Lotus 123. Note: If you use Windows XP, and you use Microsoft Excel frequently, you
may also find it on the menu shown when you first click the Start button.
This is to make it quicker for you to launch the program. When you have finished using your spreadsheet software, you should close
it. You do this by clicking File on the menu bar and then choosing Exit
from the menu of options.
Excel Overview Microsoft Excel is one of the most popular spreadsheet applications, but
most have similar features to Excel. Files created in Excel are called
workbooks. A workbook may contain one or more worksheets. Each worksheet in a workbook stores information in a grid of rows and
columns of cells. You can enter text, numbers, dates and calculations
(formulas) in the cells to present and analyse the information you need.
For example, you could use a workbook to store budget forecasts, employee
timesheets, profit and loss accounts, calculation of depreciation, cash
flow analysis and monthly expense reports. Some advantages of using a computer-based workbook are: o You can format the information in a workbook using a variety of fonts,
lines and shading, making the information easier to read.
o Changes can be made to values in a workbook at any time, and all
calculations making reference to those values will be updated
automatically.
o The information in a workbook can be presented graphically using charts
of various types.
[pic] Each cell in the workbook has its own unique cell address (also known as
the cell reference). This is made up of its column letter and then its row
number, so the cell at the top left of the worksheet in column A on row 1
is cell A1. The cell that reads 'Expenditure' in the above illustration is
cell A12, while the cell that reads 'May' is cell F5, and the active cell
pointer is on cell A4. Note: When working with Excel, you might notice that it sometimes inserts
the name of the worksheet the cell address comes from. In that case, in the
above example, cell H23 on sheet 'Cash Flow Projections' would be referred
to as:
'Cash Flow Projections'!H23 Notice the cell address is made up from text and numbers. When you want to
replicate a formula across a number of cells you can use the fill facility.
How to Fill Data within a Row or Column 1 Select the cells you want to copy.
2 Drag the fill handle (the small black square in the lower-right
corner of the selection - when you point to the fill handle, the pointer
changes to a black cross) across the cells you want to fill, and then
release the mouse button. Spreadsheet Screen Elements When your spreadsheet application is loaded, the application window will be
displayed. The screen elements described below are for Microsoft Excel, however most
spreadsheet applications have similar options. In fact most applications
use a menu system, either along the top or the side of the application
screen, for you to access various options to enter, manipulate and use the
spreadsheet software to create your spreadsheet.
Title Bar The application window has a title bar displaying Microsoft Excel - Book 1,
although Book 1 will change to the name you give the workbook when you save
it. The window has standard window elements such as the Minimise button and
Maximise buttons that you'll see on all program windows, and like any other
window, you can resize and position the Excel window anywhere on the
screen.
Menu Bar Below the title bar is the menu bar. By clicking a menu name or by pressing
[Alt] then the underlined letter, you can display the menu options. For
example, [Alt] F will access the File menu. Once the menu is open, to
choose an option simply click it or press its underlined letter.
Some commands can be accessed using keyboard shortcuts. When a keyboard
shortcut is available, you will see it described to the right of the
command name. For example, if you click the Edit menu, you will see the
Copy command can also be actioned by pressing [Ctrl] C. This means pressing
C while you hold down one of the [Ctrl] keys. Options that appear dimmed
are not available for selection at this time. If the command can also be
accessed through a toolbar button, eg Cut, the picture that appears on the
button is shown to the left of the menu option. Initially, only some of the options will be shown on each menu, however,
you can extend the menu to show all the available options if necessary. As
you work with Excel, those options that you use will be added to the
shortened list initially displayed.
Toolbars To begin with, the Standard and Formatting toolbars are displayed - they
are the ones you work with most. You can choose to display or hide toolbars
using the View, Toolbars command. Alternatively, right-click any toolbar
currently displayed to see the shortcut menu and then select a toolbar name
to display or hide it.
Worksheet Tabs Each workbook in Excel is made up of one or more worksheets. Each worksheet
(or simply sheet) in the workbook is represented by a tab at the bottom of
the window. Each time you create a new workbook, it will have the default
number of worksheets for your system. You can rename the sheets from their
original Sheet1, Sheet2... format by double-clicking the tabs and typing
the new names. This can make it easier for you to know what data is on each
sheet.
Scroll Bars The scroll bars shown along the bottom and right edges of the window allow
you to navigate up, down and across your spreadsheet and can be used to see
different parts of your workbook if it's too large to be seen all at once
on the screen. When you click the arrow buttons at the end of a scroll bar, the cells on-
screen will scroll in the direction shown on the arrow. If you want to
scroll further, you can click and drag the scroll box along the scroll bar
to the required position.
How to Open a Workbook If the workbook you want to work with has already been typed and saved, you
have to open it first. 1 Open the file using one of th