Free Excel Basic and Advance Questions and Answers — Questions and Answers

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

CTRL
ALT
F2
F4 (Correct answer)

Correct answer: F4

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?

ALT and S
CTRL and S
CTRL and =
ALT and = (Correct answer)

Correct answer: ALT and =

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?

=SUMPRODUCT(C2:C4,B2:B4) (Correct answer)
=SUMPRODUCT(C2:C5,B2:B5)
=AVERAGE(C2:C4)
=AVERAGE(B2:B4)

Correct answer: =SUMPRODUCT(C2:C4,B2:B4)

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?

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

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

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?

ALT + H + I + C
ALT + H + I + S
ALT + H + I + R (Correct answer)
ALT + H + I + I

Correct answer: ALT + H + I + R

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.

Question 6: What shortcut keys can you use to quickly aggregate rows so you may enlarge or reduce an area of data?

ALT + A + W + G
ALT + A + G + G (Correct answer)
ALT + A + U + U
ALT + A + B

Correct answer: ALT + A + G + G

The shortcut keys ALT + A + G + G are used to group rows or columns in Excel. This feature allows users to collapse or expand sections of data, making large spreadsheets more manageable and easier to navigate. It's a powerful tool for organizing and presenting data efficiently.

Question 7: Which one of the aforementioned Excel capabilities enables you to select/highlight every cell that contains a formula?

Replace
Find
Go To Special (Correct answer)
Go To

Correct answer: Go To Special

The 'Go To Special' feature in Excel (accessible via F5 or Ctrl+G, then 'Special...') provides advanced selection options, including the ability to highlight every cell that contains a formula. This is invaluable for auditing spreadsheets, understanding data dependencies, and quickly identifying all calculated values within a worksheet.

Question 8: What equation needs to be typed into cell A3 in order to reflect the outcomes as displayed below?

="Income Statement"&A1
="Income Statement "&A1 (Correct answer)
="Income Statement "&"A1"
="Income Statement "+A1

Correct answer: ="Income Statement "&A1

To combine text with the content of a cell in Excel, the ampersand (&) operator is used for concatenation. The text 'Income Statement ' is enclosed in double quotes, and a space is intentionally included after 'Statement' to ensure proper spacing before the value from cell A1 is appended. This creates a dynamic label that updates if A1's content changes.

Question 9: How can a dynamic date that displays the final day of each month be created in cell G2?

=EOMONTH($B$2,G1) (Correct answer)
=EOMONTH($B$2,B1)
=MONTH($G$2)
=EOMONTH($B$2,C1)

Correct answer: =EOMONTH($B$2,G1)

The `EOMONTH` function is designed to return the last day of the month, a specified number of months before or after a given start date. By using `$B$2` as the fixed start date and `G1` (which likely contains the number of months to add or subtract) as the month offset, this formula dynamically calculates the end of the month for a series of periods.

Question 10: What distinguishes the keyboard shortcuts for pasting?

ALT + H + V + P
ALT + H + V + F
ALT + H + V + S (Correct answer)
ALT + H + V + O

Correct answer: ALT + H + V + S

The shortcut sequence ALT + H + V + S is used to access the 'Paste Special' dialog box in Excel. ALT activates the ribbon, H goes to the Home tab, V opens the Paste options, and S specifically selects 'Paste Special'. This feature offers granular control over what attributes of the copied data are pasted, such as values, formats, or formulas.

Question 11: Let's say that cell A1 shows the value "12000.7789". How should this number be rounded to the nearest integer using the following formula?

=MROUND(A1,10)
=ROUND(A1,0) (Correct answer)
=MROUND(A1,100)
=ROUND(A1,1)

Correct answer: =ROUND(A1,0)

The `ROUND` function in Excel is used to round a number to a specified number of decimal places. To round a number to the nearest integer, you specify `0` as the number of decimal places. This instructs Excel to round the value in cell A1 to the closest whole number.

Question 12: What are the shortcut keys on the keyboard for editing a cell's formula?

F4
CTRL + F
F2 (Correct answer)
CTRL + 1

Correct answer: F2

The F2 key is the standard keyboard shortcut in Excel for entering 'Edit mode' for a selected cell. When pressed, it places the cursor at the end of the cell's content or formula in the formula bar, allowing you to easily modify its contents without needing to double-click the cell.

Question 13: What are the shortcut keys on the keyboard for inserting a table?

ALT + N + C
ALT + N + T (Correct answer)
ALT + N + R
ALT + N + V

Correct answer: ALT + N + T

The shortcut keys ALT + N + T are used to insert a table in Excel. ALT activates the ribbon, N goes to the Insert tab, and T specifically selects 'Table'. Excel tables provide enhanced functionality for data management, including automatic filtering, structured references, and easy formatting.

Question 14: How do you switch Workbook Views to Page Break Preview on the ribbon?

Review
Page Layout
View (Correct answer)
Data

Correct answer: View

The 'Page Break Preview' option, along with other workbook views like Normal and Page Layout, is located under the 'View' tab on the Excel ribbon. This view is essential for preparing worksheets for printing, as it visually displays where page breaks will occur and allows for easy adjustment.

Question 15: What excel financial modeling technique is recommended?

Use blue font for all hard-coded numbers and formulas and black font for only the totals
Use black font for all hard-coded numbers and formulas and blue font for only the totals
Use blue font for hard-coded numbers and black font for formulas
Use blue font for hard-coded numbers and black font for formulas (Correct answer)

Correct answer: Use blue font for hard-coded numbers and black font for formulas

A recommended best practice in financial modeling is to use blue font for hard-coded numbers (inputs) and black font for formulas (calculations). This visual distinction helps users quickly identify which cells contain assumptions that can be changed and which cells are derived values, improving model transparency and auditability.

Question 16: Which of the following characteristics is not available in the Data ribbon?

PivotTable (Correct answer)
What-If Analysis
Text to Columns

Correct answer: PivotTable

While PivotTables are a crucial Excel feature for data analysis, the option to insert a PivotTable is found under the 'Insert' ribbon tab, not the 'Data' ribbon tab. The Data tab primarily focuses on tools for data management, such as sorting, filtering, data validation, and 'What-If Analysis'.

Free Excel Basic and Advance Questions and Answers

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