PivotTables and Lookup functions are the most powerful yet under utilized features in Microsoft Excel. With these, one can slice and dice information in multiple dimensions and create reports in minutes that would normally take many hours using traditional spreadsheet methods. This course will enable you to learn Pivot Table, Array and Lookup functions in details to produce more professional business information.
PivotTable/ PivotChart : Creating a Pivot Table, Rearranging Fields in a Pivot Table, Explaining the Report Layout Options, Handling Blank Cells, Sorting a Pivot Table, Pivot Cache, Data from Another Workbook, Refreshing Data, Grouping Hierarchical Data, Summarize By Options, Advanced Calculated Fields, Calculated Items, Pivot Table Formatting, Conditional Formatting, GetPivotTableData, Pivot Table Cell References, Pivot Table Functions, Creating Pivot Chart from a Pivot Table
VLOOKUP/HLOOKUP: For looking up and comparing numeric or text data from same or different worksheet
INDEX: Returns a value or the reference to a value from within a table or range.
OFFSET: For getting a reference to a range that is a specified number of rows and columns from a cell or range of cells.
Search: SEARCH returns the number of the character at which a specific character or text string is first found, beginning with start_num. Use SEARCH to determine the location of a character or text string within another text string.
Find: FIND finds one text string (find_text) within another text string (within_text), and returns the number of the starting position of find_text, from the first character of within_text.
Match: Returns the relative position of an item in an array that matches a specified value in a specified order. Use MATCH instead of one of the LOOKUP functions when you need the position of an item in a range instead of the item itself.
Indirect: Returns the reference specified by a text string. References are immediately evaluated to display their contents. Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself.
Array Formula: An array formula can perform multiple calculations and then return either a single result or multiple results. Array constants can be used in place of references when you don't want to enter each constant value in a separate cell on the worksheet.
![](Templates/"../images/bodyedge_tr.gif"width="2"){kind=small}
![](Templates/"../images/bodyedge_tl.gif"width="2"){kind=small}
