The Training Place

ETP members contact: etp@butte.edu

9 am - 12 pm

4/16, 4/23, 4/30

Part 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 

Part II - 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 

Part III - 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