Basic and Advance Flashcards

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

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.

What are the AutoSum shortcut keys?

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.

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

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.

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?

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.

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

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.

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

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.

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

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.

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

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.

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

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.

What distinguishes the keyboard shortcuts for pasting?

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.

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?

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.

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

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.

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

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.

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

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.

What excel financial modeling technique is recommended?

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.

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

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'.