Free Excel Basic and Advance Questions and Answers

Question 1

What key combination on the keyboard locks cell references in a formula?

Accepted Answer

F4

Answer

The F4 key is a powerful shortcut in Excel used to cycle through different types of cell references (relative, absolute, mixed) when editing a formula. Pressing F4 repeatedly on a cell reference (e.g., A1) will add or remove dollar signs ($) to lock either the row, column, or both, making it an absolute reference ($A$1) that does not change when copied.

Question 2

What are the AutoSum shortcut keys?

Accepted Answer

ALT and =

Answer

The keyboard shortcut ALT + = is the quickest way to activate Excel's AutoSum feature. When you select an empty cell adjacent to a range of numbers and press this combination, Excel automatically detects the contiguous numbers and inserts the `SUM` function with the correct range, significantly speeding up data aggregation.

Question 3

Which of the following represents the weighted average score calculation algorithm for cell C8 as shown below?

Accepted Answer

=SUMPRODUCT(C2:C4,B2:B4)

Answer

The `SUMPRODUCT` function is perfectly suited for calculating the sum of products, which is the core component of a weighted average. It multiplies corresponding values in two or more arrays (e.g., scores in C2:C4 by weights in B2:B4) and then sums those products. This efficiently performs the necessary multiplications and additions in one step.

Question 4

As indicated in column E2, Company A is considering four possible projects and will approve them if the IRR is 10% or above. What is the formula in cell C2 that produces the outcomes displayed below and can be duplicated down to cells C3 through C5?

Accepted Answer

=IF(B2>=$E$2,"Accept","Reject")

Answer

This formula uses an `IF` statement to check if the IRR in cell B2 is greater than or equal to the threshold in E2. The key to duplicating the formula down is using absolute referencing for the threshold cell `$E$2`. This ensures that as the formula is copied, the reference to the project's IRR (B2) changes relatively (B3, B4, etc.), but the comparison threshold in E2 remains fixed.

Question 5

What are the short cuts in an Excel spreadsheet to add a new row?

Accepted Answer

ALT + H + I + R

Answer

The shortcut sequence ALT + H + I + R is used to insert a new row in Excel. ALT activates the ribbon, H navigates to the Home tab, I opens the Insert options, and R specifically selects 'Insert Sheet Rows'. This allows for quick row insertion without needing to use the mouse.

Excel Practice Test

Excel Practice Test

Free Excel Basic and Advance Questions and Answers