Microsoft Excel: Advanced - 3 Sessions

Dates: 10/16, 10/23, 10/30 9 am - 12 pm

Session I - Data Analysis Advanced functions and formulas Use logical functions to calculate values based on specified criteria Use conditional functions to summarize, count and average data Use text functions to extract specific text strings from cells Use date functions to calculate duration, expressed in a number of days or weeks Use array formulas to perform multiple calculations on multiple sets of values Lookups and data tables Use the VLOOKUP and HLOOKUP and XLOOKUP functions to find values in a worksheet list Use the MATCH function to find the relative position of a value in a range, and use the INDEX function to find the value of a cell at a given position within a range Use data tables to see the effects of changing the values in a formula

Session 2 - PivotTables and PivotCharts PivotTables and PivotCharts Use the pivot table command to create a pivot table for analyzing and comparing large amounts of data Change pivot table views by grouping data, moving fields, and using calculated fields Improving the appearance of a pivot table by applying a style and changing its field settings Create a pivot chart to graphically display data from a pivot table Exporting and importing data Export data from excel to a text file and import data from a text file into an excel workbook Use Microsoft query and the web query feature to import data from external databases

Session 3 - Power Query and Power Pivot Use of Power Query to connect to data sources, transform data, build relationships in tables and bring back to Excel Use of Power Pivot to build comprehensive reports from transformed data Introduce Power BI with transformed data and build a multi-element dashboard