Microsoft Excel: Advanced - 3 Sessions
with John Dahlgren
$290
Class has begun
Dates: 4/26, 5/3, 5/10 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
-
Apr 26 - May 10th, 2024
Fri for 3 weeks from 9:00 am - 12:00 pm