50 multiple-choice questions with answers and detailed explanations. Untimed — choose an answer to check it instantly.
0/50 answered0 correct
Question 1 of 50
Which Excel function calculates the number of periods required to pay off a loan with $250 monthly payments and a total loan of $10,000?
Correct answer: C) =NPER()
Explanation: Explanation: The =NPER() function calculates the number of periods required to pay off a loan based on periodic payments, interest rate, and loan amount.
Question 2 of 50
Which feature in Excel will allow you to freeze the first row of a worksheet while scrolling through the rest of the data?
Correct answer: A) Freeze Panes
Explanation: Explanation: Freeze Panes allows you to keep a row or column visible while scrolling through a worksheet, making it easier to reference important data.
Question 3 of 50
Which formula will return the current date and time in Excel?
Correct answer: B) =NOW()
Explanation: Explanation: The =NOW() function returns both the current date and time, which updates each time the worksheet is recalculated.
Question 4 of 50
Which Excel function calculates the future value of regular monthly payments of $150 over 10 years with a 4% interest rate?
Correct answer: B) =FV()
Explanation: Explanation: The =FV() function calculates the future value of regular periodic payments, such as monthly savings, based on a constant interest rate.
Question 5 of 50
A manager needs to determine how much sales have increased between two periods. Which formula will calculate the difference between sales in cells A1 and B1?
Correct answer: B) =B1-A1
Explanation: Explanation: To find the increase in sales, subtract the earlier value (A1) from the later value (B1). The other options do not reflect the correct operation.
Question 6 of 50
Which function calculates the correlation coefficient between two data arrays in Excel?
Correct answer: A) =CORREL()
Explanation: Explanation: The =CORREL() function returns the correlation coefficient between two data arrays, indicating the strength and direction of their relationship.
Question 7 of 50
A doctor needs to display a filtered list of patients diagnosed with "Flu" from a dataset. What Excel feature should they use?
Correct answer: C) Filter
Explanation: Explanation: The Filter feature allows users to display only the rows that meet specific criteria, such as patients diagnosed with "Flu."
Question 8 of 50
Which formula would return TRUE if sales in cell B2 are greater than or equal to $1,000?
Correct answer: B) =IF(B2>=1000,TRUE,FALSE)
Explanation: Explanation: This formula checks if the value in cell B2 is greater than or equal to 1,000 and returns TRUE if the condition is met.
Question 9 of 50
Which Excel feature would you use to track changes made to a shared workbook by multiple users?
Correct answer: B) Track Changes
Explanation: Explanation: The Track Changes feature helps users monitor and review changes made by multiple collaborators in a shared workbook.
Question 10 of 50
Which formula will correctly calculate the payment for a loan, where the rate is in C13, the number of months is in C12, and the loan amount is in C11?
Correct answer: B) =PMT(C13/12,C12,C11)
Explanation: Explanation: To calculate the monthly payment for a loan, divide the annual interest rate (C13) by 12 and use the =PMT() function.
Question 11 of 50
Which formula will calculate the markup for an item in cell D4 if the cost is in cell C4 and the markup percentage is in A1?
Correct answer: C) =C4+$A1
Explanation: Explanation: The correct formula adds the cost (C4) to the markup percentage (A1), where $A1 keeps the reference to the markup constant when copying the formula.
Question 12 of 50
An accountant wants to restrict user input in cell B3 to whole numbers between 1 and 100. Which Excel feature should be used?
Correct answer: A) Data Validation
Explanation: Explanation: Data Validation restricts the type of data that can be entered into a cell, such as whole numbers within a specified range.
Question 13 of 50
A manager tracks the correlation between employee hours and productivity. The correlation coefficient is calculated as 0.65. What does this suggest?
Correct answer: B) Moderate correlation
Explanation: Explanation: A correlation coefficient of 0.65 suggests a strong positive relationship between the two variables.
Question 14 of 50
Which Excel function counts the number of sales transactions that exceed $500 in a dataset?
Correct answer: A) =COUNTIF()
Explanation: Explanation: The =COUNTIF() function counts the number of cells that meet a specific condition, such as sales greater than $500.
Question 15 of 50
Which of the following is a valid use of a circular reference in an Excel spreadsheet model?
Correct answer: C) To calculate interdependent values simultaneously
Explanation: Explanation: Circular references occur when two formulas depend on each other’s results, and they are sometimes used in advanced models that require simultaneous calculations.
Question 16 of 50
A student wants to track the correlation between hours studied and exam grades in Excel. How should they set this up?
Correct answer: B) Use =CORREL() to calculate the correlation
Explanation: Explanation: The =CORREL() function calculates the correlation coefficient between two datasets, such as study hours and exam grades.
Question 17 of 50
Which formula will calculate the total amount paid for a loan by multiplying the monthly payment in cell C15 by the loan term in cell C12?
Correct answer: C) =C15*C12
Explanation: Explanation: This formula multiplies the monthly payment by the total number of months to calculate the total amount paid over the loan’s term.
Question 18 of 50
Which formula will calculate the interest for period 1 if the loan balance is in F19 and the interest rate is in C13?
Correct answer: A) =F19*C13/12
Explanation: Explanation: This formula calculates the monthly interest by multiplying the loan balance by the annual interest rate divided by 12.
Question 19 of 50
An owner wants to know how many orders were placed for a product that sold at least 200 units. Which formula should they use?
Correct answer: A) =COUNTIF(B1:B10,">=200")
Explanation: Explanation: The =COUNTIF() function counts the number of cells in the specified range (B1:B10) that contain values greater than or equal to 200.
Question 20 of 50
Which feature in Excel helps visualize differences in values between categories on a chart?
Correct answer: B) Legend
Explanation: Explanation: A Legend helps identify what different colors or symbols represent in a chart, making it easier to compare values between categories.
Question 21 of 50
A client wants to summarize sales data across multiple worksheets named by month abbreviations. Which formula will display the highest sales value for the year?
Correct answer: A) =MAX(JAN:DEC)
Explanation: Explanation: This formula finds the maximum value across the specified range of worksheets named by month.
Question 22 of 50
Which function will calculate the principal payment for a loan by subtracting the interest from the total payment?
Correct answer: B) =C20-D20
Explanation: Explanation: The principal payment is calculated by subtracting the interest from the total loan payment.
Question 23 of 50
A user needs to extract the first 5 characters from a text string in cell A1. Which formula should be used?
Correct answer: B) =LEFT(A1,5)
Explanation: Explanation: The =LEFT() function extracts the specified number of characters from the beginning of a text string, which in this case is the first 5 characters.
Question 24 of 50
Which Excel function allows users to sum values based on a condition, such as summing all sales greater than $500?
Correct answer: B) =SUMIF()
Explanation: Explanation: The =SUMIF() function adds up values that meet a specific condition, such as sales greater than $500.
Question 25 of 50
Which Excel feature allows users to filter data by adding a button to each column header that can be used to hide or show rows based on certain criteria?
Correct answer: C) Filter
Explanation: Explanation: The Filter feature allows users to display only rows that meet specific criteria by adding dropdown buttons to each column header.
Question 26 of 50
An employee wants to display all the data related to "Manufacturing" in a table with different industry types. What steps should they take?
Correct answer: B) Click Filter and select "Manufacturing"
Explanation: Explanation: By using the Filter feature, the employee can display only the rows that relate to "Manufacturing" from a dataset with different industry types.
Question 27 of 50
Which of the following features allows users to add additional filtering to a data field in a PivotTable?
Correct answer: A) Slicer
Explanation: Explanation: Slicers provide a visual interface for applying filters to data fields in PivotTables.
Question 28 of 50
Which of the following chart types can be used to show the percentage breakdown of total sales for different products?
Correct answer: A) Pie Chart
Explanation: Explanation: A Pie Chart is used to show the proportion of total sales contributed by each product, making it ideal for percentage breakdowns.
Question 29 of 50
A manager wants to know the total number of employees who have worked more than 40 hours in a week. Which formula should be used?
Correct answer: A) =COUNTIF(B1:B10,">40")
Explanation: Explanation: The =COUNTIF() function counts the number of cells in a range that meet a specific condition, such as employees who worked more than 40 hours.
Question 30 of 50
A PivotTable is used to calculate the total sales for each department. Which feature allows users to filter the data in the PivotTable based on department names?
Correct answer: B) Slicer
Explanation: Explanation: Slicers allow users to filter data in a PivotTable by specific categories, such as department names.
Question 31 of 50
Which Excel formula will return "Passed" if a student scored more than 60 in cell B2 and "Failed" otherwise?
Correct answer: A) =IF(B2>60,"Passed","Failed")
Explanation: Explanation: The =IF() function checks if the value in cell B2 is greater than 60 and returns "Passed" if true and "Failed" if false.
Question 32 of 50
An analyst wants to calculate the total revenue from products that sold more than 100 units. Which formula should they use?
Correct answer: B) =SUMIF()
Explanation: Explanation: The =SUMIF() function sums the values that meet a specified condition, such as products that sold more than 100 units.
Question 33 of 50
A business owner tracks monthly sales in columns A through L. Which formula will return the total sales for the year?
Correct answer: A) =SUM(A1:L1)
Explanation: Explanation: The =SUM() function adds all the values in the specified range, such as sales data from columns A through L.
Question 34 of 50
Which Excel feature helps create a concise report showing the total sales by product in a summarized format?
Correct answer: A) PivotTable
Explanation: Explanation: PivotTables allow users to summarize large datasets by categories, such as total sales by product, in a concise format.
Question 35 of 50
A client wants to calculate the present value of future payments of $500 each month for 2 years, at a 5% interest rate. Which function should they use?
Correct answer: D) =PV()
Explanation: Explanation: The =PV() function calculates the present value of future payments based on the payment amount, interest rate, and number of periods.
Question 36 of 50
Which formula will return the number of unique inventory items in a list that have a quantity greater than zero?
Correct answer: B) =COUNTIF(B1:B10,">0")
Explanation: Explanation: The =COUNTIF() function counts the number of cells in a specified range that meet a condition, such as a quantity greater than zero.
Question 37 of 50
A bank offers several different loan interest rates. Which tool should be used to create a scenario report showing the payments for each option?
Correct answer: B) Scenario Manager
Explanation: Explanation: Scenario Manager allows users to compare different sets of input variables, such as interest rates, to see how they affect outcomes like loan payments.
Question 38 of 50
A customer wants to calculate the maximum amount they can borrow with a $500 monthly payment for 2 years. Which Excel function should be used?
Correct answer: D) =PV()
Explanation: Explanation: The =PV() function calculates the present value of future payments, such as the maximum loan amount based on monthly payments.
Question 39 of 50
Which formula will count the number of cells in a range that contain a text string such as "Yes"?
Correct answer: A) =COUNTIF(A1:A10,"Yes")
Explanation: Explanation: The =COUNTIF() function counts the number of cells in a specified range that contain a specific text string, such as "Yes."
Question 40 of 50
A manager wants to calculate the total number of touch-ups performed on jobs with a difficulty rating of 3 or higher. Which function should be used?
Correct answer: A) =COUNTIF()
Explanation: Explanation: The =COUNTIF() function counts the number of cells in a range that meet a specified condition, such as jobs with a difficulty rating of 3 or higher.
Question 41 of 50
Which function calculates the number of payments required to pay off a $15,000 loan with monthly payments of $300 and an interest rate of 4%?
Correct answer: B) =NPER()
Explanation: Explanation: The =NPER() function calculates the number of periods required to pay off a loan based on the payment amount, interest rate, and loan amount.
Question 42 of 50
Which Excel function returns the rightmost 10 digits from a text string in cell A1?
Correct answer: B) =RIGHT(A1,10)
Explanation: Explanation: The =RIGHT() function extracts the specified number of characters from the right side of a text string.
Question 43 of 50
A sales manager wants to identify which sales representatives have met their monthly sales targets. Which formula would return "Met Target" if the value in cell B2 is greater than or equal to 10,000 and "Missed Target" otherwise?
Correct answer: A) =IF(B2>10000,"Met Target","Missed Target")
Explanation: Explanation: This formula checks if the value in B2 is greater than or equal to 10,000 and returns "Met Target" if true, and "Missed Target" if false.
Question 44 of 50
A business owner tracks sales for multiple products and wants to calculate the total for products that sold more than 50 units. Which formula should be used?
Correct answer: B) =SUMIF(B1:B10,">50")
Explanation: Explanation: The =SUMIF() function adds up the values in the specified range (B1:B10) that are greater than 50.
Question 45 of 50
Which function will calculate the percentage of total sales for each product in a table, based on the product's sales value divided by the total sales?
Correct answer: D) =A1/SUM(A1:A5)
Explanation: Explanation: This formula divides the sales value for a specific product by the total sales to calculate the percentage of total sales.
Question 46 of 50
Which feature allows you to protect a worksheet from being modified by other users?
Correct answer: C) Protect Sheet
Explanation: Explanation: The Protect Sheet feature allows users to prevent others from editing specific parts of the worksheet.
Question 47 of 50
An administrator needs to track changes made to an Excel workbook shared with multiple users. Which tool should they use to monitor updates?
Correct answer: C) Track Changes
Explanation: Explanation: The Track Changes feature allows administrators to track and review any changes made by multiple users in a shared workbook.
Question 48 of 50
A financial analyst needs to predict future sales based on past performance. Which function should be used to forecast the sales of a product?
Correct answer: A) =FORECAST()
Explanation: Explanation: The =FORECAST() function predicts future values based on past data, making it ideal for estimating future sales.
Question 49 of 50
Which Excel function will return the day portion of a date stored in cell B1?
Correct answer: D) =DAY()
Explanation: Explanation: The =DAY() function extracts the day portion from a given date.
Question 50 of 50
A user needs to extract the month from a date value stored in cell B1. Which function should they use?
Correct answer: A) =MONTH()
Explanation: Explanation: The =MONTH() function returns the month portion of a date.
Set 3 complete
You scored 0%
0 correct out of 0 answered (50 questions in this set).