Excel Test(Done)Δ Contact Step 1 of 2 - 50% First NameLast NameEmail idPhone/MobileNext1. The process of arranging the items of a column in some sequence or order is known as None of the above Sorting Filtering Autofill2. The ____ feature of MS Excel quickly completes a series of data- Auto Fill Sorting Fill Handle Auto complete3. What type of chart is useful for comparing parts of a whole ? Column Chart Line Chart Dot Graph Pie Chart 4. Which of the following is the correct formula to calculate the weighted average score in cell C8 as shown in the above image? =AVERAGE(C2:C4) =SUMPRODUCT(C2:C5,B2:B5) =AVERAGE(B2:B4) =SUMPRODUCT(C2:C4,B2:B4)5. It is a collection of data that is stored electronically as a series of records in a table MS Word Presentation Database Spreadsheet6. Which of the following keyboard shortcut can be used for creating a 'Chart' from the selected cells ? F10 F2 F3 F117. Which key is used to 'Go to' tab in MS Excel ? F5 F7 F6 F98. To minimize workbook in MS Excel, we use- Ctrl+F9 Ctrl+F8 Ctrl+F6 Ctrl+F7 9. What formula can be used in cell G2 to create a dynamic date which shows the last day of each month ? =MONTH($G$2) =EOMONTH($B$2,B1) =EOMONTH($B$2,C1) =EOMONTH($B$2,G1)10. Assuming cell A1 is displaying the number "12000.7789". What formula should be used to round this number to the closest integer ? =ROUND(A1,0) =ROUND(A1,1) =MROUND(A1,10) =MROUND(A1,100)11. The shortcut keys to increase the number of decimal places are- Alt+H+9 Alt+H+D Alt+H+0 Alt+H+P 12. Suppose a company expected its revenue to be $180,000 for 2014-2018. What formula should be entered in cell E6 so it displays revenue in 2016 if it was above budget, otherwise it'll show 0 ? =IF(E2<$B$6,E2,0) =IF(E2<$B$6,0,E2) =IF(E2>$B$6,0,E2) =IF(E2>$B$6,E2,0)13. Which language is used to create macros in excel ? Visual C++ C Java Visual basic14. To record a sequence of keystrokes and mouse actions to play back later we use- Macro recorder None of the above Media Player Sound Recorder15. We can save and protect the workbook by- Write Reservation Password Protection Password Read-only Recommended Any of the above16. Which of the descriptions listed below best describe what a macro in Excel does ? It is a series of instructions contained in one command that you can use to automate complex and/or repetitive tasks. It is a type of formatting you can apply to enlarge the worksheet area. It is a file type that you save a workbook as. It is a recording function that allows you to save worksheets, audio recordings and videos that are displayed on the screen. 17. Study the screenshot given above. Where will you find the option to launch the Macros dialog box ? Under the Formulas tab on the Ribbon, in the Functions group. Under the Developer tab on the Ribbon, in the Add-Ins group. Under the Developer tab on the Ribbon, in the Code group. Under the Insert tab on the Ribbon, in the Macros group.18. How can you view and delete macros that have been created in a worksheet? Press Alt+F8, select the macro to be deleted and click delete. Under the Developer tab, click on the Macros button in the Code group. Select the macro to delete and click delete. Press Alt+F11. Click on Tools and select Macros. Select the macro to delete and click delete. All of the options listed above are correct. 19. Which of the formula is correct to fetch State from Table B only when the rate in Table A is greater than 10 otherwise it should display "Not Applicable"? =IF($B$2:$B$6>10,VLOOKUP(A3,$D$3:$E$6,2,0),”Not Applicable”) =IF($B$2:$B$6>10,VLOOKUP(A3:B6,$D$3:$E$6,2,0),”Not Applicable”) None of the above =IF($B$2:$B$6>10,”Not Applicable”,VLOOKUP(A3,$D$3:$E$6,2,0))20. Is VLOOKUP function by default case sensitive ? Yes No21. The sum function is applied to values in a pivot table by default. How can I change this so that values are automatically counted & not summed ? Insert the COUNT formula (=Count()) into the PivotTable. None of the above In the Calculations group, change the Summarize Values By to Count. Change the format of the values in the Pivot Table to General Numbers.22. Which dialog box allows you to change a field name in a Pivot Table ? Field Option Field Structure Field Pane Field Settings23. What type of result does the MATCH function return, when used on its own ? It returns the cell reference of the lookup value. It returns the relative position of a lookup value, either as a row or column number, within the selected array. It returns the lookup value located in a specific location. It returns a value that is the same as the lookup value.24. Study the screenshot given below. Using the INDEX-MATCH function, which of the formulas below, when entered into the highlighted cell (E3), will yield the result Group (32A) ? =INDEX(B2:B9,MATCH(A2:A9,0)) =MATCH(B2:B9,INDEX(D3,A2:A9,0)) =INDEX(MATCH(D3,A2:A9,0)) =INDEX(B2:B9,MATCH(D3,A2:A9,0)) 25. Study the screenshot given above. Using the VLOOKUP function, which of the formulas below, when entered into the highlighted cell (E3), will yield the result Group (32A) ? =VLOOKUP(D3,A2:B9,2,FALSE) =VLOOKUP(D3,A2:B9,FALSE) =VLOOKUP(A2:B9,2,FALSE) =VLOOKUP(D3,A2,2,FALSE)26. In a VLOOKUP formula, what does the argument ‘column index number’ mean ? This is a numerical representation of the letters at the top of a worksheet For eg. A=1, B=2 This is numbers you assign to columns in your worksheet This is secret INDEX number that each column in an excel worksheet has. This is the number of the column within the selected table array where the lookup result is located.27. Using the following string of characters, DLM343456B82, which formula will extract 343456? The data is located in cell B2. =left(B2,4,6) =Mid(B2,3,6) =left(4,6) =Mid(B2,4,6)28. What does COUNTA () function do ? Counts non empty cells Counts empty cells Counts selected cells Counts cells having alphabets29. When all the numbers between 0 & 100 in a range should be displayed in Red color, apply- Select the cells that contain number between 0 & 100 then click Red color on Text color tool Apply conditional formatting command on home tab None of the above Use=if() function to format the required numbers red30. What function displays row data in a column or column data in a row ? Rows Index Transpose Hyperlink Previous Submit Answer
