The advanced function workshop:

• Combine functions by precisely inserting them into one another, in order to obtain more complex calculations;

• Accurately use the following functions:

  • "Logic" functions: IF, IFERROR;

  • "Text" functions: Clean, Code, Concatenate, Exact, Find, Lower, Proper, T, Trim, Value, Upper;

  • "Data" functions: Day, Days, Edate, Eomonth, Isoweeknum;

  • "Look-up" functions: Getpivotdata, Vlookup, Hlookup, Lookup, Match, Transpose

  • "Statistical" functions: Large; Countifs, Sumif, Sumifs; Averageif; Averageifs; Min; Max;

  • Using IFERROR to eliminate Excel Errors.

  • Focus on Vlook-up and Hlook-up – common mistakes and ways to prevent them;

Pivot Tables

• Construct and work with a Pivot Table to analysis your data.

• Drilldown on the Data Area

• Use the Layout Options

• Use Group and Outline

• Change the Subtotal Function

• Use different functions in the Data Area

• Use the “Show Data As” Option

• Use the "slicer" and "timeline" functions

• Create Calculated Items

• Create Calculated Fields

• Create a PivotChart report

Concatenate

• In a single cell, accurately string together cell values, text and formulas into a single value.

Data from web-sites;

• How to import data from a web-site;