Spreadsheets

Question 1

What is a spreadsheet?

Answer 1

An application often used to process and display financial or statistical information.

Question 2

What is a cell?

Answer 2

Given a name called a cell reference
E.g B1, C5

Question 3

What is a row?

Answer 3

Horizontal group of cells
E.g 1,2,3

Question 4

What is a column?

Answer 4

Vertical group of cells
E.g A,B,C

Question 5

What is a label (text)

Answer 5

A piece of text used to identify the contents of rows/columns
E.g Name,Age

Question 6

What are values (number)

Answer 6

These must be entered in to allow for calculations to be carried out.
E.g 5, 23

Question 7

What is a Formula? Examples and how it is laid out

Answer 7

An instruction to carry out a calculation, using mathematical operators and references to other cells. Always starts with an equals sign.

= A1 + B2 - addition

= A1 - B2 - subtraction

= A1 * B2 - Multiplication

= A1 / B2 - Division

Question 8

What is a function and examples?

Answer 8

A function is used to do a very specific job within a spreadsheet.

AUTOSUM
AVERAGE
MAX
MIN
IF VLOOKUP

Question 9

What does the function do? - AUTOSUM

Answer 9

=SUM(A1:A10)
The autosum formula will add a range of cells. A1 is the first cell in the range and A10 is the last cell in the range. This formula will add A1 through to A10 together

Question 10

What does the function do? - AVERAGE

Answer 10

=AVERAGE(A1:A10)
The average function will find the average of a range of cells

Question 11

What does the function do? - MAX

Answer 11

MAX
=MAX(A1:A10)
The max function will find the highest value in this range of cells

Question 12

What does the function do? - MIN

Answer 12

=MIN(A1:A10)
The min function will find the lowest value in this range of cells.

Question 13

What does the function do? - If

Answer 13

IF
=IF(A1>60,”PASS”,”FAIL”)
If the contents of cell A1 is greater than 60 then the word PASS will be entered, if it lower than 60, the word FAIL will be entered.

Question 14

What does the function do? - VLOOKUP

Answer 14

A B
1 BLUE 20.0
2 PINK 1.0
The formula looks like this =VLOOKUP(“BLUE”, A1:B2, 2) and the answer it returns is 20.0

Short for Vertical Lookup. This allows you to look up a value in a range of cells in a column and display that value. Use the value in a selected cell to “lookup” a match in a column of a lookup table

Question 15

What is relative cell reference?

Answer 15

A relative cell reference is cell reference in a formula that changes depending on its location on a worksheet. For example, if a formula is copied from one cell to another the cell reference in the formula will change.

Question 16

What is absolute cell reference

Answer 16

This is when you want a cell reference in a formula to always stay the same. A dollar sign ($) is used to tell the spreadsheet software to always to use that particular cell. In the example above, cell B2 is the cell to be used in all formula. Therefore a dollar sign has entered in front of the letter B and the row number 2 to ensure that cell is always used in the formula even when replicated.

Question 17

What is data validation?

Answer 17

Data validation is used to ensure that data entered by the user is reasonable and sensible. Data validation can be used in a number of ways such as ensuring that data entered is within a particular range, that data is in a particular format or that only certain items in a list can be selected.

Question 18

Examples of data validation

Answer 18

Examples of Data Validation:
• Age must be a whole number which is less than 18
• Exam Result must be between 0 and 100
• Gender must be from a list which consists of Male and Female

Question 19

What is conditional formatting?

Answer 19

Conditional formatting is used to change the appearance or the formatting of a cell when certain conditions are met. Imagine that a teacher used a spreadsheet to record students exam results and they want to identify everyone who has got over the pass rate of 60 in green and everyone who got under 60 in red.

Question 20

What is merge?

Answer 20

Making two or more cells form into one larger cell

Question 21

What is data modelling?

Answer 21

A model is created to replicate a real-life situation. In a spreadsheet application, a model can be created using different functions and formula to see what impact a change in the data entered by the user may have on other data held in the spreadsheet.

Question 22

Examples of data modelling?

Answer 22

For example, business users could assess the impact a change in unit price may have on their overall profit (what-if),

or teachers could use a spreadsheet model to work out how many marks a student needs to achieve a particular grade, based on what they have achieved so far.

If sales of a product continue to grow at the same rate, what will sale be in 18 months’ time?

Question 23

What is a macro?

Answer 23

A macro is a small program created to perform a repetitive task. It allows the user to perform a task by using the macro rather than having to enter the individual instructions. If the task is likely to be used many times it is much easier and more user friendly to use a macro.

It is a set of commands/instructions recorded together that can be activated by a mouse click or pressing a key. In simple terms it is a short cut to a task.

A macro is created by recording the tasks to be carried out. It is then named and saved.

Question 24

Why are charts used Spreadsheets?

Answer 24

Spreadsheets display data in tabular form. Charts and graphs can be used to display data in a more visual way making it easy to read and to spot trends. There are different types of charts you can make. For example:
• Bar Chart
• Line Chart
• Pie Chart
• Scatter Graph

Question 25

State the term that describes a preformatted layout into which data can be added.

Answer 25

Template