Using Ctrl + Page Up and Ctrl + Page Down is an efficient way to navigate between worksheets quickly without interrupting the workflow. While using the mouse (option a) or the Go To dialog box (option c) can be effective, they are slower and less efficient for frequent navigation. Copying all data into one worksheet (option d) is not practical for large datasets and can make the workbook difficult to manage and analyze.

Ctrl + Shift + $ applies the currency format to the selected cell, which is a quick and efficient way to format financial data. Ctrl + Shift + F does not apply a specific format (it brings up the Font dialog box), Ctrl + 1 opens the Format Cells dialog box (which is useful but not as fast as a direct shortcut), and Ctrl + Shift + % applies the percentage format.

A mixed reference with a fixed row (e.g., A$1) will keep the row reference constant while allowing the column reference to change when the formula is copied horizontally. This is ideal for John’s needs. A relative reference (option a) changes both the row and column, an absolute reference (option b) keeps both row and column constant, and a mixed reference with a fixed column (option d) keeps the column constant but allows the row to change.

An absolute reference (e.g., $A$1) ensures that a specific cell reference remains constant, regardless of where the formula is copied. This is ideal for referencing a tax rate or any constant value used throughout the model. Other options would benefit from relative or mixed references depending on the specific needs of the formula.

The TEXT function is used to convert a number to text in a specific number format, ensuring that numbers are displayed correctly when combined with text. CONCATENATE (or the & operator) can combine text and numbers but doesn’t format numbers. VALUE converts text to numbers, and DATEVALUE converts a date in text format to a serial number, neither of which address formatting text and numbers together.

DATEVALUE converts a date in text format to a serial number that Excel recognizes as a date. This allows for proper date-based calculations. TEXT converts numbers to text, VALUE converts text to numbers, and DATE creates a date from year, month, and day components, but doesn’t convert text dates directly.

Cell Styles allow Tom to apply consistent formatting (including font, color, and borders) across multiple cells with a single click. Format Painter copies formatting from one cell to another but doesn’t ensure consistency across many cells. Conditional Formatting applies formatting based on cell values and isn’t used for manual formatting. Data Validation controls the type of data entered in a cell but doesn’t apply formatting.

The custom number format 0.00;[Red]-0.00 formats positive numbers as black (default) and negative numbers in red. The semicolon separates the positive and negative formats. [Red]0.00;-0.00 incorrectly places the color before the positive format. 0.00;-0.00[Red] and 0.00;0.00[Red] are incorrectly formatted.

Conditional Formatting with custom rules allows Liam to set specific criteria for formatting cells based on their values. He can create rules to format cells with sales figures above the target in green and below the target in red. Data Bars, Icon Sets, and Color Scales provide visual representations but are not as precise for this specific need.

The formula =A1=”Urgent” checks if the cell in column A contains the text “Urgent” and applies the conditional formatting to the corresponding cell in column B. This formula is relative and will adjust for each cell in column B. =B1=”Urgent” checks the wrong column, =INDIRECT(“A1″)=”Urgent” unnecessarily complicates the reference, and =MATCH(“Urgent”, A1:A100, 0) is used for finding the position of “Urgent” in a range, not for conditional formatting.

Ms. Davis should go to Excel Options > Advanced > Edit Custom Lists to create a custom list. This method allows her to define a sequence that can be used with the Fill Handle for quick filling. The other options either do not provide a way to create a reusable custom list or misinterpret the Data Validation feature.

Mr. Johnson should use the Data > Get External Data > From Text option, which opens the Text Import Wizard. This tool provides step-by-step guidance on defining delimiters and data formats, ensuring that the imported data is correctly formatted and integrated. The other options either do not provide the necessary formatting options or embed the file rather than importing the data.

Ms. Chen should select the entire dataset, go to Data > Sort, and add two levels: first by Department (A-Z) and then by Salary (largest to smallest). This ensures a proper multi-level sort. The other options either do not perform a multi-level sort or use filters, which are not suitable for this task.

Flash Fill can be activated by entering the desired data pattern and then pressing Ctrl+E. This feature recognizes patterns and fills the remaining cells accordingly. The other options either do not correctly utilize Flash Fill or suggest different features like Text to Columns.

Data Validation is used to restrict the type of data or values that users can enter into a cell, ensuring data integrity. While it can create drop-down lists (option c), its primary purpose is broader, covering various data entry restrictions. Options b and d describe different functionalities.

Mr. Lee should go to Data > Get Data > From Database > From SQL Server Database, enter the server and database details, and configure the connection settings. This method establishes a direct connection to the SQL database, allowing dynamic data retrieval. The other options do not provide a direct database connection or misinterpret the task.

Ms. Patel should create a criteria range specifying the conditions (e.g., sales > 10,000 and region = North or West) and use Data > Advanced Filter to apply the filter. This allows for complex criteria combinations. The other options do not support complex criteria or only visually highlight the data.

Mr. Thompson should set up a criteria range specifying the conditions for Date (current month) and Status (Approved), then use Data > Advanced Filter. This method allows for filtering based on complex criteria. The other options either do not handle complex criteria or involve manual, error-prone steps.

To use the AutoSum tool, select the range of cells, click the AutoSum button, and choose Sum or Average from the dropdown menu. This method quickly inserts the appropriate function and calculates the total or average. The other options either do not utilize AutoSum correctly or involve more steps.

To sort data based on a non-standard order, create a custom list in Excel with the desired order (e.g., Monday, Tuesday, etc.), then use the Sort option with this custom list. This ensures the data is sorted according to the specified sequence. The other options do not support non-standard orders or provide a visual sort only.

The SUMIF function allows Mr. Adams to sum a range based on a specified criterion. In this case, the function =SUMIF(A:A, “East”, B:B) will sum all values in column B where the corresponding cell in column A matches “East.”

The SUMIFS function (option c) is also valid for multiple criteria but is unnecessary here since there’s only one condition. Option b does not apply filtering, while option d sums the entire column A, which is irrelevant in this context.

The LEFT function is specifically designed to extract a specified number of characters from the start of a text string. In this scenario, =LEFT(C2, 3) correctly returns “AB1”.

Option b, MID, extracts characters starting from a specified position, which is not needed here. Option c uses RIGHT, which retrieves characters from the end, and option d attempts to concatenate, which is unnecessary and incorrect.

The TODAY() function returns the current date, and wrapping it with the YEAR function will extract just the year. Thus, =YEAR(TODAY()) will provide the current year.

Option b returns the current date and time, while option c retrieves the year from a specific date in cell D2, which is not the requirement. Option d retrieves the current month instead of the year, making it irrelevant.

The VLOOKUP function searches for a value in the first column of a table and returns a value in the same row from a specified column. Here, =VLOOKUP(“Widget A”, F:G, 2, FALSE) looks for “Widget A” in column F and returns the corresponding price from column G.

Option b is incorrect because HLOOKUP is used for horizontal lookups, while option c uses INDEX and MATCH, which is a valid alternative but not necessary for this simple lookup. Option d is vague and does not specify the array correctly.

The IF function combined with AND allows for checking multiple conditions. In this case, =IF(AND(H2 > 500, I2 < 0.05), "Approved", "Review") accurately reflects the logic needed for Mr. Chen’s criteria. Option b uses OR, which would return "Approved" even if one condition fails. Option c negates the condition, which is incorrect, and option d incorrectly sets the conditions.

While CONCATENATE and TEXTJOIN can also achieve the desired result, the simplest method is using the & operator. Thus, =J2 & ” ” & K2 concatenates the first and last names effectively.

Option b is valid but longer than necessary. Option c uses TEXTJOIN, which is also valid but may be unnecessary for two cells. Option d uses JOIN, which is not a recognized Excel function.

The Evaluate Formula Tool allows Mr. Smith to see how Excel calculates each part of a formula step-by-step. This tool is particularly useful for understanding complex formulas and auditing them.

The Formula Bar is where he can view and edit the formula but does not provide step-by-step evaluation. The Watch Window is for monitoring changes in values, while Cell Comments are for additional notes, not for evaluating formulas.

The MAX function returns the largest value in a set of values, so using =MAX(M:M) will return the most recent date from the list. This is critical for tracking the latest entry.

Option b returns the current date but does not consider the values in column M. Option c returns both the current date and time but lacks context to the dataset. Option d would provide the oldest date instead.

The IF function is ideal for simple condition checks. The formula =IF(N2 > O2, “Q1 better”, “Q2 better”) correctly assesses the comparison between the two cells.

Options b, c, and d either invert the logic or incorrectly use AND/OR where they are unnecessary.

The DATEDIF function with the “D” argument calculates the difference in days between two dates. Thus, =DATEDIF(P2, Q2, “D”) correctly returns the number of days between the dates in P2 and Q2.

Option b switches the order of dates, potentially causing negative results. Option c uses “MD” to return the difference in days, ignoring months and years, while option d, DAYS, requires the end date first, making it less intuitive.