Microsoft Excel Assessment Welcome to your Microsoft Excel Assessment First Name Last Name Company Email 1. What are the sections of a Ribbon tab called? Commands Groups Dialog Boxes Dropdowns 2. Select the correct formula to enter in cell B6 that would total sales for all quarters for the year 2011. =B2:B3:B4:B5 =B2:B5 =SUM(B2:B5) =TOTAL(B2:B5) 3. Which key(s) CANNOT be used to navigate in an Excel Worksheet? The arrow keys Tab and Enter Insert and Delete Page Up and Page Down 4. How can you save a file while working on it? Ctrl-S Click the File Tab, then Save Click the Save button in the Quick Access Toolbar All of the above 5. Which mouse technique lets us edit existing content in a cell? Single-click Double-click Right-click Drag 6. Which is the easiest way to call up Help related to the operation you are currently doing? The F1 key The help button in the Help tab on the Ribbon Typing in the Search space None of the above 7. Which item comes LAST in the order of operations in the "Sales with Tax" formula? Multiplication Addition Exponents Parentheses 8. Which symbol indicates a range of cells in a function formula? Comma Colon Semi-colon Forward slash 9. Where do the dollar signs go in an absolute reference? Before both the column and row reference Before the column reference Before the row reference None of the above 10. How can we auto-fit the column width or row height? Drag the column or row divider Double-click the column or row divider Single-click the column or row divider None of the above 11. If we want to use Find/Replace on an entire worksheet, how many cells should we select? The first row The first column All Any one cell 12. What kind of words can the Spell Check correct? Proper names Common Names Verbs Words in its dictionary 13. Under which Ribbon tab do we find the Formatting controls? File Home Page Layout View 14. Which number format uses a dollar sign? Accounting Currency A & B None of the Above 15. Which feature allow us to combine cells, e.g. for titles? Merge and Center Text Wrap Increase/Decrease Indent Rotate Text Up/Down 16. Which theme-related item can we NOT easily create or edit? Colors Fonts Effects Themes 17. What does conditional formatting allow you to do? Format values when exporting data Format formulas so they are more easily readable Format cells based on conditions Format pages for printing conditionally 18. Which is the file type/extension for an Excel template? .xls xltx .xlsx .xlsm 19. In the Page Setup dialog, which tab allows us to set up the Print Titles? Sheet Header/Footer Margins Page 20. Under which Ribbon tab is conditional formatting located? Home Layout Data View 21. How many worksheets can a saved Excel workbook have? 64 255 1024 Limited only by available space in memory 22. Which feature lets us keep the top and left sides of the sheet visible for labeling purposes? Arrange Windows Split Freeze Panes Workspaces 23. What are they keyboard shortcuts to move from worksheet to worksheet? PageUp and PageDn CTRL+END and CTRL+HOME ALT+END and ALT+HOME CTRL+PageUp and CTRL+PageDn 24. Which of these can a range name NOT begin with? Letter Number Underscore Backslash 25. What needs to enclose most criteria in a function such as COUNTIF? Double-quotes Single-quotes Asterisks Underscores 26. How many levels of functions can be nested within one first-level function? 7 9 64 Unlimited 27. Which of these functions does NOT require user input (i.e. arguments in parentheses)? NETWORKDAYS TODAY DATE WEEKDAY 28. Which of these functions requires three arguments? LEFT RIGHT CONCATENATE MID 29. How many levels of sorting can be done with a block of data? One Three Equal to the number of columns Unlimited 30. How few filters could accidentally filter out all data, i.e. return no results? One Two Three Four 31. What do ALL Excel database functions have in common? Their names all start with “D” They all require a properly labeled data block They all require the same three arguments All of the above 32. In the Subtotal dialog box, which of these functions is NOT available to aggregate data with? Sum DistinctCount Average Count 33. When we create a table, which one of these is NOT required? Headers/Labels Blank space on all sides All data contiguous Clicking in table to use features 34. Which feature allows us to see what we're filtering for in a PivotTable or PivotChart? (choose more than one) Pivot Filter Data Filter Slicer Timeline 35. Which type of cell reference is frequently used in a conditional formatting formula? Absolute Relative Mixed All of the above 36. Under which tab can we change the style of a chart? Layout Design Format None of the above 37. What type of chart can display more than one data series at the same time? Treemap Box & Whisker Waterfall Combo 38. Where can we put fields in the Field List to label a PivotTable? In the Columns area In the Rows area A or B None of the above 39. What needs to go around the file name and sheet name in an external reference? Double-quotes Single-quotes Square brackets Nothing 40. Do grouped worksheets have to be adjacent to each other for a 3-D reference? Yes No 41. Which of these is NOT available in the Consolidate dialog box dropdown? Count Average Dsum Var 42. Which of these requires the most arguments (optional items aside)? VLOOKUP LOOKUP MATCH INDEX 43. Which function allows us to return more than one piece of information from a table array? TABLELOOKUP XLOOKUP ARRAYLOOKUP LOOKUP 44. If an item is added to the Watch window, and the file is saved and given to someone else, can the other person see that item listed in their Watch window? Yes No 45. If Sharing mode is activated for an Excel workbook, how many people can edit its contents at once? Two Five Ten As many as needed 46. For any level of workbook or worksheet protection, the password should include: Letters Numbers Special characters All of the above 47. When setting data validation, which level(s) of error alert will NOT allow invalid data entry? Stop Warning Information All of these 48. How many major error types will produce a written warning in a cell? Three Five Seven Nine 49. When creating a macro, does Excel time the pause between steps? Yes No 50. Which type of sparkline works best with negative numbers? Line Column Win/Loss None of these 51. What kind of information must be present to use the Map feature? Time Geographic Sales/Currency All of these 52. How many possible solutions are permitted in a one- or two-variable data table? One Five Ten As many as needed 53. When creating scenarios, which button can we use to directly compare them when complete? Add Edit Summary Merge 54. What is the one thing required in the cell using Goal Seek? A formula with a number A formula with a cell reference An equals sign None of these 55. Where is the Forecast feature in the Ribbon? The Home tab The Insert tab The Formulas tab The Data tab Time's up
