Part 1 Flashcards
Vlookup Function
=Vlookup(lookup value, lookup table, index number, range lookup)
Match Function
=MATCH(lookup_value, lookup_array, [match_type])
Index Function
=INDEX(array,row num,column num)
Index definition
Index returns a value in the specified array that is at the intersection of row_num and column_num (Retrieve)
Match definition
Match returns a position number or an error (Find) **Finds index point
Excel Table References
- Tables self-expand with additional data
- Tables use structured references
- Range Name defined by structured reference
How to put 2 cells together
- &
- =N4 & J4
- If want space between =N4& “ “ &J4
PivotTable Definition
displays unique values from a dataset in a cross-tabular format
Why would you want to use a PiviotTable?
- Data reduction through filtering
- User interaction
- Different data sources are possible
Refreshing Report Data for PiviotTable
-Drilling down the data
CSV
Comma Separated Value .csv- Data may be in a file with commas delimiting / separating data values
Files without the csv file extension when importing into Excel
- Delimited
- Fixed width
Pivotable Fields in Value section
numeric fields are summed and alphanumeric fields are counted.
Primary components of PiviotTables
1) PivotTable Report Areas (Where data appears)
2) Field List Dialog Box (List & fields)
3) PivotTable Tab
Values Field
- displayed if there are two or more fields being summarized in the values area
- Column or Rows
External Data
- 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.
Why create a connection to External Data Source?
- 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
Import Wizard
- Data Format and Skipping Columns
- Break Lines in Fixed Length Text Files
Microsoft Access Tables
organize data in a two-dimensional view. Each column has a column heading (called attribute or field).
Microsoft Access Views
Views are queries that pull data from multiple tables.
How to pull MS Access Data into Excel
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.
Excel Table
- 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.
What can you do with Excel tables?
- 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
