IC3 - Digital Literacy Spreadsheet Applications 2 — Questions and Answers
Question 1: What is a pivot table and what is it used for?
- A table that can be physically rotated on screen
- A data summarization tool that automatically groups, sorts, counts, and aggregates large datasets (Correct answer)
- A table with only two columns
- A method for merging two spreadsheets
Correct answer: A data summarization tool that automatically groups, sorts, counts, and aggregates large datasets
A pivot table can automatically summarize, group, sort, count, and calculate totals and averages from large datasets.
Pivot tables allow you to drag data fields into four areas: Rows, Columns, Values, and Filters. They automatically handle sorting, grouping, and calculations, update dynamically when source data changes, and can be formatted with conditional rules.
Question 2: What does the IF function do in a spreadsheet?
- It formats cells with different colors
- It performs a logical test and returns one value if true and another if false (Correct answer)
- It inserts an image into a cell
- It imports data from another file
Correct answer: It performs a logical test and returns one value if true and another if false
The IF function evaluates a logical condition and returns one value when true and a different value when false.
Syntax: =IF(logical_test, value_if_true, value_if_false). IF functions can be nested for multiple conditions. Related functions include IFS, IFERROR, AND/OR, and SWITCH.
Question 3: What is data validation in a spreadsheet?
- A process of backing up spreadsheet data
- Rules that restrict the type or range of data that can be entered into specific cells (Correct answer)
- A method for validating internet connections
- A formula for checking password strength
Correct answer: Rules that restrict the type or range of data that can be entered into specific cells
Data validation sets rules on cells that restrict what data can be entered, such as numbers within a range, dates, or items from a dropdown list.
Validation types include whole numbers or decimals within a range, dates, text of specific length, values from a predefined list (creating dropdown menus), and custom formulas. Data validation is essential for maintaining data integrity in shared spreadsheets.
Question 4: How do you create and use named ranges in a spreadsheet?
- Named ranges automatically sort data
- Named ranges assign a descriptive name to a cell or range of cells for use in formulas and navigation (Correct answer)
- Named ranges are only available in premium versions
- Named ranges delete data in the specified range
Correct answer: Named ranges assign a descriptive name to a cell or range of cells for use in formulas and navigation
Named ranges assign a meaningful name to cells, making formulas more readable and easier to maintain.
Named ranges transform formulas like =SUM(B2:B100) into =SUM(SalesTotal). Benefits include self-documenting formulas, easier maintenance, reduced errors, and quick navigation. They can also be used in data validation, conditional formatting, and charts.
Question 5: What is the difference between a relative, absolute, and mixed cell reference?
- There is no functional difference between them
- Relative references adjust when copied, absolute references stay fixed, and mixed references lock either the row or column (Correct answer)
- Relative references are for text, absolute for numbers
- Absolute references are faster to calculate
Correct answer: Relative references adjust when copied, absolute references stay fixed, and mixed references lock either the row or column
Relative references (A1) adjust when copied, absolute references ($A$1) remain fixed, and mixed references ($A1 or A$1) lock only the column or row.
Relative references: both column and row adjust when copied. Absolute references ($A$1): both remain fixed. Mixed references: $A1 locks the column, A$1 locks the row. Mixed references are essential for multiplication tables and cross-referencing matrices.
Question 6: What is the purpose of the CONCATENATE or CONCAT function?
- It calculates the total of a range of cells
- It joins two or more text strings together into one combined string (Correct answer)
- It splits text in a cell into separate cells
- It counts the number of characters in a cell
Correct answer: It joins two or more text strings together into one combined string
CONCAT joins multiple text strings into a single string, useful for combining names or building descriptions from separate data fields.
Syntax: =CONCAT(text1, text2, ...). The ampersand (&) operator provides a shorthand. Related functions include TEXTJOIN, LEFT/RIGHT/MID, TRIM, and UPPER/LOWER/PROPER. Common uses include combining names, building addresses, and generating email addresses.
IC3 - Digital Literacy Spreadsheet Applications 2
What is a pivot table and what is it used for?