Power Tools, Tables and Arrays

Please provide your name

1. What is the shortcut to format a range of data as a table?

Ctrl P Ctrl T Ctrl R Ctrl F

2. Which one of the following statements is true with regards to using timelines in Pivot Tables?

Timelines can be added to Tables or Pivot Tables Timelines are used to sort data from the oldest to the newest date Timelines allow connections to be reported to more than one Pivot Table Timelines can work with text columns, date columns or numerical columns

3. What is the shortcut for opening the Name Manager dialogue box?

Ctrl F3 Ctrl N Ctrl M Ctrl F4

4. Which one of the following statements is NOT true regarding naming ranges?

Names cannot have spaces or other special characters An underscore can be used to put a space between words The range names are not case sensitive and can contain letters or numbers Cell references, such as B5 or D102, can be used as range names

5. Which one of the following formulas will return the contents of a cell at the intersection of a specific row and a specific column?

INDEX MATCH COLUMNS VLOOKUP

6. Which one of the following methods can be used to address a #SPILL error?

Delete any nested functions and rewrite the formula Select obstructing cells, and delete all obstructing cells Function is unable to spill, so should be dragged down Ranges should be named before included in functions

7. What does the UNIQUE function do?

Sorts a range in terms of a specified column Filters data based on a specified criteria Generates an array of random numbers Extracts unique items from a range of cells

8. Which one of the following formulas will split text into columns and rows by using delimiters?

TEXTJOIN VSTACK TEXTSPLIT CHOOSECOLS

9. Which one of the following formulas will combine an array vertically into a single array?

VSTACK BYCOL TAKE HSTACK

10. What is the name of the functionality in Excel that can import data from various sources?

Power Maps Power Pivot Power Query Solver Add-in