Syllabus Detail
- Spreadsheet terms; cell, formula, function (sum, average, max, min, count, countif) , label, worksheet, lookup tables vlookup/hlookup 11 ATAR, 12 GEN
Quiz below.
vLookup - excellent help here
vLookup help 2 here
| Term | Meaning | Example |
| Cell | A cell is a box created by the crossing of a column and a row. The cell value is referenced with coordinates made up from the row and columns. | A1 is composed of a vertical column, A, and a horizontal row, 1. |
| Formula | A formula is an expression that is used to calculate the value of a cell. | =(A1 + Z10) |
| A function is a pre-defined formula that are readily available in a spreadsheet software. | ||
|
- Sum |
Returns the sum of the given range of cells. | =SUM(A1:Z10) |
|
- Average |
Returns the average of the given range of cells. | =AVERAGE(A1:Z10) |
|
- Max |
Returns the value of the cell with the largest value. | =MAX(A1:Z10) |
|
- Min |
Returns the value of the cell with the smallest value. | =MIN(A1:Z10) |
|
- Count |
Returns the counted number of cells with a value in the given range. | =COUNT(A1:A10) |
|
- Countif |
Returns the counted number of cells with specified criterion. |
=COUNTIF(A1:Z10, "Hello") [Shows the count of cells with the word'Hello'] =COUNTIF(A1:Z10, 3) [Shows the count of cells with a value of '3'] |
|
Label |
Commonly referred to as a heading used to identify a column in a spreadsheet. | "Date" - e.g. a column consisting of date values. |
|
Worksheet |
A worksheet is a single spreadsheet made up of rows and columns that meet to create cells. | "Worksheet 1" |
|
Lookup tables |
These functions are used to reference values in cells, either in the same worksheet (locally) or external worksheets. | LOOKUP / VLOOKUP / HLOOKUP |
|
- VLookup |
VLOOKUP is a function used when you need to find values from a table, or a specific range by row. For example, a spreadsheet could utilize VLOOKUP to find an employee's email address by looking up their last name. Note: the key to VLOOKUP is to organize data in such a way that the value we know (e.g. their last name) is to the left of the value we want to find (e.g. their email address). |
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup]) =VLOOKUP("Bloggs",B2:D4,2) In this example, we're looking for the 2nd column's value from the ranges B2:D4, but only where "Bloggs" is found. |
|
- HLookup |
HLOOKUP works in the same way that VLOOKUP works, except with one difference: this looks up information specified by a row as opposed to a column. This means that it returns a value from the same column as a row is in. |
HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) =HLOOKUP("Chocolates",A1:C4,2) In this example, we're looking up "Chocolate" in row 1 and returns the value from row 2 in the same column of the given range (A1:C4). |
Practice by yourself or with a friend
A cell is the smallest part of a spreadsheet
A row is the line along a spreadsheet from left to right.
A column is the line down a spreadsheet from top to bottom.
A spreadsheet can have multiple worksheets - these are the tabs at bottom of the spreadsheet.
Answer these.
What is the difference between a label and a text or number in a cell?
List the 6 most common functions.
This quiz is for registered users only.
Further Research
- Learn everything there is to know about Excel from Microsoft's Office Support website
Found an error or enhancement? Please fill out this contact us form.