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 None 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) None 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 None 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 None 5. Which mouse technique lets us edit existing content in a cell? Single-click Double-click Right-click Drag None 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 None 7. Which item comes LAST in the order of operations in the "Sales with Tax" formula? Multiplication Addition Exponents Parentheses None 8. Which symbol indicates a range of cells in a function formula? Comma Colon Semi-colon Forward slash None 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 None 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 None 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 None 12. What kind of words can the Spell Check correct? Proper names Common Names Verbs Words in its dictionary None 13. Under which Ribbon tab do we find the Formatting controls? File Home Page Layout View None 14. Which number format uses a dollar sign? Accounting Currency A & B None of the Above None 15. Which feature allow us to combine cells, e.g. for titles? Merge and Center Text Wrap Increase/Decrease Indent Rotate Text Up/Down None 16. Which theme-related item can we NOT easily create or edit? Colors Fonts Effects Themes None 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 None 18. Which is the file type/extension for an Excel template? .xls xltx .xlsx .xlsm None 19. In the Page Setup dialog, which tab allows us to set up the Print Titles? Sheet Header/Footer Margins Page None 20. Under which Ribbon tab is conditional formatting located? Home Layout Data View None 21. How many worksheets can a saved Excel workbook have? 64 255 1024 Limited only by available space in memory None 22. Which feature lets us keep the top and left sides of the sheet visible for labeling purposes? Arrange Windows Split Freeze Panes Workspaces None 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 None 24. Which of these can a range name NOT begin with? Letter Number Underscore Backslash None 25. What needs to enclose most criteria in a function such as COUNTIF? Double-quotes Single-quotes Asterisks Underscores None 26. How many levels of functions can be nested within one first-level function? 7 9 64 Unlimited None 27. Which of these functions does NOT require user input (i.e. arguments in parentheses)? NETWORKDAYS TODAY DATE WEEKDAY None 28. Which of these functions requires three arguments? LEFT RIGHT CONCATENATE MID None 29. How many levels of sorting can be done with a block of data? One Three Equal to the number of columns Unlimited None 30. How few filters could accidentally filter out all data, i.e. return no results? One Two Three Four None 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 None 32. In the Subtotal dialog box, which of these functions is NOT available to aggregate data with? Sum DistinctCount Average Count None 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 None 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 None 36. Under which tab can we change the style of a chart? Layout Design Format None of the above None 37. What type of chart can display more than one data series at the same time? Treemap Box & Whisker Waterfall Combo None 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 None 39. What needs to go around the file name and sheet name in an external reference? Double-quotes Single-quotes Square brackets Nothing None 40. Do grouped worksheets have to be adjacent to each other for a 3-D reference? Yes No None 41. Which of these is NOT available in the Consolidate dialog box dropdown? Count Average Dsum Var None 42. Which of these requires the most arguments (optional items aside)? VLOOKUP LOOKUP MATCH INDEX None 43. Which function allows us to return more than one piece of information from a table array? TABLELOOKUP XLOOKUP ARRAYLOOKUP LOOKUP None 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 None 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 None 46. For any level of workbook or worksheet protection, the password should include: Letters Numbers Special characters All of the above None 47. When setting data validation, which level(s) of error alert will NOT allow invalid data entry? Stop Warning Information All of these None 48. How many major error types will produce a written warning in a cell? Three Five Seven Nine None 49. When creating a macro, does Excel time the pause between steps? Yes No None 50. Which type of sparkline works best with negative numbers? Line Column Win/Loss None of these None 51. What kind of information must be present to use the Map feature? Time Geographic Sales/Currency All of these None 52. How many possible solutions are permitted in a one- or two-variable data table? One Five Ten As many as needed None 53. When creating scenarios, which button can we use to directly compare them when complete? Add Edit Summary Merge None 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 None 55. Where is the Forecast feature in the Ribbon? The Home tab The Insert tab The Formulas tab The Data tab None Time's up
