Step 1: Policies And Procedures
1. Which of the following formulas would return the value in Column E that corresponds to the first occurrence of “Got It” in Column C ?
Answer: =VLOOKUP("Got It",$C$5:$F$20,3)
2. In Cell C10 is the formula =SUM(A10,B10). The user copies Cell C10 and pastes it into Cell C11. Which of the following statements is TRUE?
Answer: The result in Cell C11 will be the sum of the values in A11 and B11.
3. A user wants to remove duplicates within their data. Which of the following options would be the quickest, most efficient way to do this?
Answer: Select cells → Data → Remove Duplicates
4. Given the commission percentages listed in the table below, which formula should be used in Cell F2 to calculate the commission percentages for each sales representative?
Answer: =IF(E2<100>=100,E2<150>=150,E2<=200),$B$4,$B$5)))
5. Sheet1, cell C2 contains the formula "=Sheet1!A2+Sheet1!B2". When you copied this formula to C3, the range did not change. What would you do?
Answer: Use the Formula tab to change calculation option from manual to automatic
6. Which of the following statements are true regarding the MATCH function in Excel? (Select all that apply.)?
Answer: The MATCH function returns the relative position of a lookup value in a selected array.
7. A user has a large worksheet consisting of sales data for their company’s salespeople. This user wants to add the daily sales totals for one salesperson. What formula would allow them to quickly add up the totals given a specific name criteria?
Answer: =SUMIF(range,criteria,[sum_range])
8. Given the revenue per client, per month in this revenue table, which of the following formulas would find the highest revenue in Month 3?
Answer: =MAX(INDEX(A2:M7,0,4))
9. A user is entering weekly paycheck information into a range of cells. In Column G the user is entering a dollar amount and wants this information to be shown as a monetary value (e.g., 321.5 needs to show as $321.50). What formatting change is necessary?
Answer: The cell format can be changed to CURRENCY by highlighting the column and changing the format
10. Which of the following statements are true? (Select all that apply.)?
Answer: The VLOOKUP function searches for a value in the first column of a table and returns the value in the same row based on the index number.
11. The data used to create this pivot chart consisted of the following information: Customer Age, Customer ID number, Customer Type (Normal or Discount), and Location. How would you help a User who only wants to show the 'Normal' type customer?
Answer: Select the chart, go to Design tab, select Add chart Elements, select Legends to show Legend and use Legend to select Normal type customer
12. A User submits a question regarding a PivotTable they have created. They want to be able to quickly filter their data within the table to show only the data meeting certain User-selected criteria. Which of the following answer choices would be the most efficient way to accomplish this?
Answer: Insert a Slicer into their PivotTable
13. Given the chart below, how would you add data labels to the columns?
Answer: Right click on any column, select add data label option
14. Which of the following options correctly explains the logic for a possible set of values in the following formula?
Answer: If the value in G9 is 182 and the value in H9 is 5 the final output will be Yes.
15. Given the following data table, which of the following functions can be used to calculate the discount based on method of payment and customer type?
Answer: Add a new column using & in the formula to generate a new lookup range and VLOOKUP
Step 2: Subject Assessment 2025
1. A user wants to combine first name, middle name, and last name in columns A, B, and C. Which formula should they use?
Answer: =CONCATENATE(A1,B1,C1)
2. A user wants to remove duplicates within their data. Which of the following options would be the quickest, most efficient way to do this?
Answer: Select cells → Data → Remove Duplicates
3. A user submits a spreadsheet of data showing Quarter 1 Sales Figures by Region and Salesperson. They are seeking your help to display this data in a chart or graph. Which of the following options would be a correct response to the user?
Answer: All of the above answers are correct
4. In Cell G10 is the string “Silicon Valley CALIFORNIA”. The following formula is entered into Cell G11: =FIND(“n”,G10,2). Which of the following answer choices is correct?
Answer: Cell G11 will return the value of 6 because “n” is the sixth character in the string when counting from the second character.
5. Given the chart below, the User wishes to insert another row of data and update the chart. Which of the following options would accomplish this for the User? (Select all of the options that are correct.)
Answer: Inserting a row within the table will automatically update the chart. Select the Chart Design tab, Select Data. Right-click on the chart area, select "Select Data" option, set new range.
6. What will the following formula return? =INDEX(A5:H448,5,6)
Answer: The value of the cell in row 5, column 6 across the given array.
7. Given the commission percentages listed in the table below, which formula should be used in Cell F2 to calculate the commission percentages for each sales representative?
Answer: =IF(E2<100,$B$2,IF(AND(E2>=100,E2<150),$B$3,IF(AND(E2>=150,E2<=200),$B$4,$B$5)))
8. Sheet1, cell C2 contains the formula "=Sheet1!A2+Sheet1!B2". When you copied this formula to C3, the range did not change. What would you do?
Answer: Use the Formula tab to change calculation option from manual to automatic
9. Which of the following options correctly explains the logic for a possible set of values in the following formula? =IF((AND(G9>=175,H9<=10)), “Yes”, “No”)
Answer: If the value in G9 is 182 and the value in H9 is 5 the final output will be Yes.
10. Which of the following functions or key combinations allows for data from different sheets to be linked?
Answer: Sheet2!A1+Sheet1!A1
11. In Cell C10 is the formula =SUM(A10,B10). The user copies Cell C10 and pastes it into Cell C11. Which of the following statements is TRUE?
Answer: The result in Cell C11 will be the sum of the values in A11 and B11.
12. In a large data set, a user wants to find how many occurrences of a given number exist. Which of the following functions would allow the user to find this information?
Answer: COUNTIF
13. When copied from one cell to another, which of the following formulas will return the same result?
Answer: All of the cases above
14. Which of the following formulas would round the value of Cell G9 to the thousands?
Answer: =ROUND(G9,-3)
15. The value in cells C3, C4, and D3 are 10, 15, and 20, respectively. The following formula is inserted into cell D4: =(SUM(AVERAGE(C3,C4),D3)). What value will this formula produce in cell D4?
Answer: 32.5
16. Given the chart below, how would you add data labels to the columns?
Answer: Right click on any column, select add data label option
17. The data used to create this pivot chart consisted of the following information: Customer Age, Customer ID number, Customer Type (Normal or Discount), and Location. How would you help a User who only wants to show the 'Normal' type customer?
Answer: Select the chart, go to Design tab, select Add chart Elements, select Legends to show Legend and use Legend to select Normal type customer
18. The current date is January 29, 2018. Cell G9 has the formula =TODAY(). Cell G10 has the formula =DATE(YEAR(G9)-1,MONTH(G9)+5, DAY(G9)-10). What value will be returned in cell G10?
Answer: 6/19/17
19. Cell P12 has the following combination of characters in this order: 123_456_ABC. What value would Cell P13 return if in Cell P13 was =LEN(P12)?
Answer: 11, because LEN returns the total number of characters in a given cell
20. A user is entering weekly paycheck information into a range of cells. In Column G the user is entering a dollar amount and wants this information to be shown as a monetary value (e.g., 321.5 needs to show as $321.50). What formatting change is necessary?
Answer: The cell format can be changed to CURRENCY by highlighting the column and changing the format.
21. Which of the following functions can be used to get the full name from first and last name? (e.g., Cell A1 is David, Cell B1 is Smith and in Cell C1 we want David Smith.)
Answer: CONCATENATE
22. Which of the following statements are true regarding the MATCH function in Excel? (Select all that apply.)
Answer: The MATCH function returns the relative position of a lookup value in a selected array.
23. A User submits a question regarding a PivotTable they have created. They want to be able to quickly filter their data within the table to show only the data meeting certain User-selected criteria. Which of the following answer choices would be the most efficient way to accomplish this?
Answer: Insert a Slicer into their PivotTable.
24. Given the following table, what function can be used to calculate the number of New York employees in salary grade 20?
Answer: COUNTIFS.
25. Which of the following is the correct syntax for using the IF function?
Answer: =IF(Logical_Test,Value_if_True,Value_if_False).
26. A user wants to create a chart that shows the distribution of sales by region. Which chart type would be best for this purpose?
Answer: A pie chart could be used to show the percentage of sales that correspond to each region.
27. How can a user quickly apply the same formatting, such as bold or italic, to multiple cells in a worksheet?
Answer: Use the Format Painter tool to copy and apply the formatting to multiple cells.
28. Which of the following statements is true about the VLOOKUP function?
Answer: VLOOKUP searches for a value in the first column of a table and returns a value in the same row from a specified column.
29. What is the purpose of the CONCATENATE function in Excel?
Answer: CONCATENATE combines text from different cells into one cell.
30. How can a user quickly create a table from a range of data in Excel?
Answer: Highlight the range of data, then go to the Insert tab and select Table.
31. Which of the following functions can be used to count the number of cells that meet a certain condition?
Answer: COUNTIF.
32. How can a user insert a new row in an Excel worksheet?
Answer: Right-click on the row number where you want to insert the new row, then select Insert.
33. Which of the following formulas can be used to calculate the average of a range of numbers in Excel?
Answer: =AVERAGE(range).
34. What is the purpose of the IF function in Excel?
Answer: The IF function checks whether a condition is met, and returns one value if true and another value if false.
35. How can a user quickly remove all formatting from a range of cells in Excel?
Answer: Select the range of cells, then go to the Home tab and select Clear Formats from the Clear dropdown menu.
36. Which of the following statements is true about the SUM function in Excel?
Answer: The SUM function adds all the numbers in a range of cells.
37. How can a user quickly apply a predefined style to a range of cells in Excel?
Answer: Use the Cell Styles gallery on the Home tab to apply a predefined style.
38. Which of the following statements are true regarding the MATCH function in Excel? (Select all that apply.)
Answer: The MATCH function returns the relative position of a lookup value in a selected array.
39. Assuming no holidays, which of the following formulas would allow someone to calculate the total number of workdays from 1/1/18-2/28/18?
Answer: =NETWORKDAYS("January 1, 2018","February 28, 2018")
40. Given the following table, what function can be used to calculate the number of New York employees in salary grade 20?
Answer: COUNTIFS
41. A user has a large worksheet consisting of sales data for their company’s salespeople. This user wants to add the daily sales totals for one salesperson. What formula would allow them to quickly add up the totals given a specific name criteria?
Answer: =SUMIF(range,criteria,[sum_range])
42. The value in Cells G7 and G8 are -5 and 16, respectively. The following formula is inserted into Cell G9: =PRODUCT(G7,G8). What value will this formula produce in Cell G9?
Answer: -80
43. In Cell C10 is the formula =SUM(A10,B10). The user copies Cell C10 and pastes it into Cell C11. Which of the following statements is TRUE?
Answer: The result in Cell C11 will be the sum of the values in A11 and B11.
44. Which of the following formulas would round the value of Cell G9 to the thousands?
Answer: =ROUND(G9,-3)
45. Given the commission percentages listed in the table below, which formula should be used in Cell F2 to calculate the commission percentages for each sales representative?
Answer: =IF(E2<100>=100,E2<150>=150,E2<=200),$B$4,$B$5)))
46. When copied from one cell to another, which of the following formulas will return the same result?
Answer: All of the cases above
47. Sheet1, cell C2 contains the formula "=Sheet1!A2+Sheet1!B2". When you copied this formula to C3, the range did not change. What would you do?
Answer: Use the Formula tab to change calculation option from manual to automatic
48. Which of the following is the correct syntax for using the IF function?
Answer: =IF(Logical_Test,Value_if_True,Value_if_False)
49. A user is entering weekly paycheck information into a range of cells. In Column G the user is entering a dollar amount and wants this information to be shown as a monetary value (e.g., 321.5 needs to show as $321.50). What formatting change is necessary?
Answer: The cell format can be changed to CURRENCY by highlighting the column and changing the format.
50. In Cell G10 is the string “Silicon Valley CALIFORNIA”. The following formula is entered into Cell G11: =FIND(“n”,G10,2). Which of the following answer choices is correct?
Answer: Cell G11 will return the value of 7 because “n” is the seventh character in the string.
51. What will the following formula return? =INDEX(A5:H448,5,6)
Answer: The value of the cell in row 5, column 6 across the given array.
52. Given the chart below, the User wishes to insert another row of data and update the chart. Which of the following options would accomplish this for the User? (Select all of the options that are correct.)
Answer: Inserting a row within the table will automatically update the chart.
Answer: Select the Chart Design tab, Select Data.
Answer: Right click on the chart area, select "Select Data" option, set new range.
53. Which of the following formulas would calculate the number of weeks from 01/01/2016 to 03/15/2016?
Answer: DATEDIF("01/01/2016","03/15/2016","W")
54. A User submits a question regarding a PivotTable they have created. They want to be able to quickly filter their data within the table to show only the data meeting certain User-selected criteria. Which of the following answer choices would be the most efficient way to accomplish this?
Answer: Insert a Slicer into their PivotTable
55. Given the revenue per client, per month in this revenue table, which of the following formulas would find the highest revenue in Month 3?
Answer: =MAX(INDEX(A2:M7,0,4))
56. Given the 2 tables below, which of the following formulas should be entered in cell D2 to calculate the total cost of the group subscription based on the monthly cost table?
Answer: =C2*INDEX($H$2:$H$4,MATCH(B2,$G$2:$G$4,0))
.png)
Comments
Post a Comment