Part 1

Question 1

Vlookup Function

Answer 1

=Vlookup(lookup value, lookup table, index number, range lookup)

Question 2

Match Function

Answer 2

=MATCH(lookup_value, lookup_array, [match_type])

Question 3

Index Function

Answer 3

=INDEX(array,row num,column num)

Question 4

Index definition

Answer 4

Index returns a value in the specified array that is at the intersection of row_num and column_num (Retrieve)

Question 5

Match definition

Answer 5

Match returns a position number or an error (Find) **Finds index point

Question 6

Excel Table References

Answer 6

• Tables self-expand with additional data
• Tables use structured references
• Range Name defined by structured reference

Question 7

How to put 2 cells together

Answer 7

• &
• =N4 & J4
• If want space between =N4& “ “ &J4

Question 8

PivotTable Definition

Answer 8

displays unique values from a dataset in a cross-tabular format

Question 9

Why would you want to use a PiviotTable?

Answer 9

• Data reduction through filtering
• User interaction
• Different data sources are possible

Question 10

Refreshing Report Data for PiviotTable

Answer 10

-Drilling down the data

Question 11

CSV

Answer 11

Comma Separated Value .csv- Data may be in a file with commas delimiting / separating data values

Question 12

Files without the csv file extension when importing into Excel

Answer 12

• Delimited

- Fixed width

Question 13

Pivotable Fields in Value section

Answer 13

numeric fields are summed and alphanumeric fields are counted.

Question 14

Primary components of PiviotTables

Answer 14

1) PivotTable Report Areas (Where data appears)
2) Field List Dialog Box (List & fields)
3) PivotTable Tab

Question 15

Values Field

Answer 15

• displayed if there are two or more fields being summarized in the values area
• Column or Rows

Question 16

External Data

Answer 16

• To access external data, you create a CONNECTION to it.
• *Single file format: CSV, Excel, Web, MS Access, proprietary format, etc.
• *Database format: MS Access, SQL Server, Oracle Server, etc.

Question 17

Why create a connection to External Data Source?

Answer 17

• Data source many be too large to fit in Excel.
• Changes made to the data source can be easily updated in Excel without recreating a connection
• In Excel, you only need a few columns or rows from the data source

Question 18

Import Wizard

Answer 18

• Data Format and Skipping Columns

- Break Lines in Fixed Length Text Files

Question 19

Microsoft Access Tables

Answer 19

organize data in a two-dimensional view. Each column has a column heading (called attribute or field).

Question 20

Microsoft Access Views

Answer 20

Views are queries that pull data from multiple tables.

Question 21

How to pull MS Access Data into Excel

Answer 21

There are generally two ways to pull MS Access data (e.g., tables and views) into Excel.

• *1: Data | From Access
• This approach is limited to retrieving data from one table or one view.
• Can’t use parameters or execute queries
• *2: Use Microsoft Query
• This is the better approach to pull data from MS Access.

Question 22

Excel Table

Answer 22

• a series of rows and columns that are managed independently from the data in other rows and columns
• By default, every column has filtering in the header row.
  
  • *total row can be added to provide aggregate functions for columns.

Question 23

What can you do with Excel tables?

Answer 23

• Sorting and filtering
• Formatting table datawith custom table styles
• Inserting and deleting table rows and columns
• Using a calculated column
• Displaying and calculating table data totals
• Using structured references
• Ensuring data integrity
• Exporting to a SharePoint list