Absolute & Relative Reference in Excel 2016

During a math class, Elijah is learning about different types of cell references in spreadsheets. What type of cell reference changes when copied to another cell?

• Absolute reference

• Mixed reference

• Relative reference

• Fixed reference

Answer explanation

A relative reference changes when copied to another cell because it adjusts based on the position of the new cell. In contrast, absolute references remain constant, while mixed references have both fixed and variable components.

Avery is trying to create a budget spreadsheet. How is an absolute reference designated in a formula?

• With a dollar sign ($)

• With an ampersand sign (&)

• With a percentage sign (%)

• With an exclamation mark (!)

Answer explanation

An absolute reference in a formula is designated with a dollar sign ($). This indicates that the row and/or column referenced will not change when the formula is copied to another cell.

During a math class, Benjamin is trying to understand what happens to relative references when copying a formula in his spreadsheet.

• They remain constant

• They are removed

• They change according to the relative position

• They become absolute references

Answer explanation

Relative references in a formula adjust based on their position when copied to another cell. This means they change according to the relative position, allowing for dynamic calculations across different cells.

During a class project, Hannah is working on a spreadsheet. Which key can she press to toggle between relative and absolute references?

• F6

• F4

• F2

• F5

Answer explanation

Pressing F4 toggles between relative and absolute references in spreadsheet applications like Excel. This allows users to quickly switch how cell references behave in formulas.

Evelyn is learning about different types of references in her programming class. What type of reference is used least frequently?

• Mixed reference

• Absolute reference

• Dynamic reference

• Relative reference

Answer explanation

Mixed reference is used least frequently because it combines both absolute and relative references, making it more complex and less intuitive for users compared to the more straightforward absolute and relative references.

Anika is trying to reference a cell from another sheet in her Excel workbook. How does she do it?

• With an equal sign (=)

• With a number sign (#)

• With an exclamation mark (!)

• With a plus sign (+)

Answer explanation

To reference a cell from another sheet in Excel, you use an exclamation mark (!). For example, 'Sheet2!A1' refers to cell A1 in Sheet2. The other symbols do not serve this purpose.

What error appears if Samuel enters the name of a sheet incorrectly?

• #VALUE!

• #REF!

• #NAME?

• #DIV/0!

Answer explanation

The error #NAME? appears when a formula refers to a name that Excel does not recognize, such as an incorrectly entered sheet name. This indicates that the name is not valid or does not exist.

Evelyn is calculating the total cost of her shopping. What formula would she use to multiply the unit price by the quantity and the tax rate?

• =B4+C4*$E$2

• =(B4*C4)*$E$2

• =B4*C4+$E$2

• =B4*C4*$E2

Answer explanation

The correct formula to calculate the total including tax is =(B4*C4)*$E$2. This multiplies the unit price (B4) by the quantity (C4) and then applies the tax rate ($E$2) to the result.

Avery is working on a spreadsheet for her math class. What should she do to ensure that a reference to a cell remains constant when copying it?

• Use a mixed reference

• Do nothing

• Use an absolute reference

• Use a relative reference

Answer explanation

To keep a cell reference constant when copying it, use an absolute reference (e.g., $A$1). This ensures that the reference does not change, unlike relative references which adjust based on the new location.

What should be included if Daniel wants to reference a sheet named 'Sales Data' in his report?

• Single quotes

• Brackets

• Double quotes

• Parentheses

Answer explanation

When a sheet name contains a space, it must be enclosed in single quotes to ensure proper recognition in formulas. This prevents errors and allows the sheet to be referenced correctly.