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

Dates: 5/8, 5/15, 5/22  9 am - 12 pm

Laptops provided

In MS Excel BASIC topic areas including getting started with terms and navigating a spreadsheet, entry and editing data a beginning modification to spreadsheets are covered. Upon completion, attendees will be able to: 

• Define a spreadsheet and identify spreadsheet components
• Identify the main components of the excel window
• Open and navigate workbooks
• Create a workbook from a template
• Enter and edit text and values in a worksheet
• Enter and edit formulas in a worksheet
• Save and update a workbook, and save a workbook in a different file format
• Move and copy data in a worksheet
• Move, copy, and view formulas in a worksheet
• Use absolute references in formulas
• Insert and delete ranges, rose, and columns in a worksheet

• Apply the SUM function to calculate the sum of values
• use the AutoSum to enter some functions
• Use the AVERAGE, MIN, MAX, COUNT, and COUNTA functions to find average, minimum, and maximum values, and the count of cells in a range
• Apply text formatting
• Customize column widths, row heights, and alignment, and apply color and border formatting
• Format values as currency, percentages, and ordinary numbers
• Apply conditional formatting based on specific criteria
• Copy formatting, apply cell styles, and use Find and Replace to update the formatting for specific content

• Create charts based on worksheet data, and move charts within a workbook
• Customize charts and format chart elements
• Insert a picture into a worksheet
• use graphics as conditional formatting to represent cell data
• insert and modify SmartArt graphics
• Preview how a worksheet will look when printed, use the spelling checker, and use find and replace to update data
• set page orientation, scaling, and margins, and create headers and footers
• print a worksheet and a selected range

Dates : 6/3, 6/4, 6/5  9 am - 12 pm

In MS Excel INTERMEDIATE I (3-hour session), topic areas including managing workbooks and worksheets, advanced methods of spreadsheet formatting and the use of outlining and subtotals are covered. Upon completion, attendees will be able to: 

• Freeze panes, split a worksheet, and hide and display data in window elements.
• Set print titles and page breaks
• Navigate, manage, and print multiple worksheets
• Create 3-D formulas to link worksheets, and add a watch window
• Switch between workbooks, and copy a sheet from one workbook to another
• Create and manage linked workbooks
• Apply built in and custom number formats to display data in specific formats
• Apply and modify themes
• Merge and change orientation of cells to display text in special ways; Transpose data; and add backgrounds and watermarks
• Create outlines to group and organize data, create custom views, and consolidate data from different worksheets
• Summarize the data in a worksheet by creating automatic subtotals

In MS Excel INTERMEDIATE II (3-hour session), topic areas in the application of creating names for cells and cell ranges, understanding data structure and tables and web sharing of workbooks and worksheets will be covered. Upon completion, attendees will be able to: 

• Use names to make your formulas easier to understand
• Use the name manager and define 3-D names, which span multiple worksheets
• Organize data logically, sort it by the contents of its columns, and filter it to show only those rows that meet certain criteria
• Create and format tables, and use structured references to include table column names in formulas
• Save a workbook as a web page, and use the auto republish feature to keep the web version of a workbook updated
• Add and remove hyperlinks in a worksheet
• Send workbooks via email

In MS Excel INTERMEDIATE III (3-hour session), topic areas including documenting spreadsheets with notes, tags and comments and the creation of workbook/worksheet templates including an extensive review of settings will be covered. Upon completion, attendees will be able to: 

• Use auditing features to trace precedent and dependent cells, and trace errors
• Add and edit comments for a cell and a worksheet
• Protect a workbook or part of a worksheet from unauthorized access or unintentional changes
• Share workbooks, merge versions of a workbook, track changes made by various users, remove personal data from a workbook, and mark a workbook as final
• Change Excel's default application settings
• Create and modify custom templates

Dates: 8/5, 8/7, 8/9  9 am - 12 pm

Laptops provided

In MS Excel BASIC topic areas including getting started with terms and navigating a spreadsheet, entry and editing data a beginning modification to spreadsheets are covered. Upon completion, attendees will be able to: 

• Define a spreadsheet and identify spreadsheet components
• Identify the main components of the excel window
• Open and navigate workbooks
• Create a workbook from a template
• Enter and edit text and values in a worksheet
• Enter and edit formulas in a worksheet
• Save and update a workbook, and save a workbook in a different file format
• Move and copy data in a worksheet
• Move, copy, and view formulas in a worksheet
• Use absolute references in formulas
• Insert and delete ranges, rose, and columns in a worksheet

• Apply the SUM function to calculate the sum of values
• use the AutoSum to enter some functions
• Use the AVERAGE, MIN, MAX, COUNT, and COUNTA functions to find average, minimum, and maximum values, and the count of cells in a range
• Apply text formatting
• Customize column widths, row heights, and alignment, and apply color and border formatting
• Format values as currency, percentages, and ordinary numbers
• Apply conditional formatting based on specific criteria
• Copy formatting, apply cell styles, and use Find and Replace to update the formatting for specific content

• Create charts based on worksheet data, and move charts within a workbook
• Customize charts and format chart elements
• Insert a picture into a worksheet
• use graphics as conditional formatting to represent cell data
• insert and modify SmartArt graphics
• Preview how a worksheet will look when printed, use the spelling checker, and use find and replace to update data
• set page orientation, scaling, and margins, and create headers and footers
• print a worksheet and a selected range

Dates : 9/11, 9/18, 9/25  9 am - 12 pm

In MS Excel INTERMEDIATE I (3-hour session), topic areas including managing workbooks and worksheets, advanced methods of spreadsheet formatting and the use of outlining and subtotals are covered. Upon completion, attendees will be able to: 

• Freeze panes, split a worksheet, and hide and display data in window elements.
• Set print titles and page breaks
• Navigate, manage, and print multiple worksheets
• Create 3-D formulas to link worksheets, and add a watch window
• Switch between workbooks, and copy a sheet from one workbook to another
• Create and manage linked workbooks
• Apply built in and custom number formats to display data in specific formats
• Apply and modify themes
• Merge and change orientation of cells to display text in special ways; Transpose data; and add backgrounds and watermarks
• Create outlines to group and organize data, create custom views, and consolidate data from different worksheets
• Summarize the data in a worksheet by creating automatic subtotals

In MS Excel INTERMEDIATE II (3-hour session), topic areas in the application of creating names for cells and cell ranges, understanding data structure and tables and web sharing of workbooks and worksheets will be covered. Upon completion, attendees will be able to: 

• Use names to make your formulas easier to understand
• Use the name manager and define 3-D names, which span multiple worksheets
• Organize data logically, sort it by the contents of its columns, and filter it to show only those rows that meet certain criteria
• Create and format tables, and use structured references to include table column names in formulas
• Save a workbook as a web page, and use the auto republish feature to keep the web version of a workbook updated
• Add and remove hyperlinks in a worksheet
• Send workbooks via email

In MS Excel INTERMEDIATE III (3-hour session), topic areas including documenting spreadsheets with notes, tags and comments and the creation of workbook/worksheet templates including an extensive review of settings will be covered. Upon completion, attendees will be able to: 

• Use auditing features to trace precedent and dependent cells, and trace errors
• Add and edit comments for a cell and a worksheet
• Protect a workbook or part of a worksheet from unauthorized access or unintentional changes
• Share workbooks, merge versions of a workbook, track changes made by various users, remove personal data from a workbook, and mark a workbook as final
• Change Excel's default application settings
• Create and modify custom templates

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

Dates: 11/6, 11/13, 11/20  9 am - 12 pm

Laptops provided

In MS Excel BASIC topic areas including getting started with terms and navigating a spreadsheet, entry and editing data a beginning modification to spreadsheets are covered. Upon completion, attendees will be able to: 

• Define a spreadsheet and identify spreadsheet components
• Identify the main components of the excel window
• Open and navigate workbooks
• Create a workbook from a template
• Enter and edit text and values in a worksheet
• Enter and edit formulas in a worksheet
• Save and update a workbook, and save a workbook in a different file format
• Move and copy data in a worksheet
• Move, copy, and view formulas in a worksheet
• Use absolute references in formulas
• Insert and delete ranges, rose, and columns in a worksheet

• Apply the SUM function to calculate the sum of values
• use the AutoSum to enter some functions
• Use the AVERAGE, MIN, MAX, COUNT, and COUNTA functions to find average, minimum, and maximum values, and the count of cells in a range
• Apply text formatting
• Customize column widths, row heights, and alignment, and apply color and border formatting
• Format values as currency, percentages, and ordinary numbers
• Apply conditional formatting based on specific criteria
• Copy formatting, apply cell styles, and use Find and Replace to update the formatting for specific content

• Create charts based on worksheet data, and move charts within a workbook
• Customize charts and format chart elements
• Insert a picture into a worksheet
• use graphics as conditional formatting to represent cell data
• insert and modify SmartArt graphics
• Preview how a worksheet will look when printed, use the spelling checker, and use find and replace to update data
• set page orientation, scaling, and margins, and create headers and footers
• print a worksheet and a selected range

Dates : 12/4, 12/11, 12/18  9 am - 12 pm

In MS Excel INTERMEDIATE I (3-hour session), topic areas including managing workbooks and worksheets, advanced methods of spreadsheet formatting and the use of outlining and subtotals are covered. Upon completion, attendees will be able to: 

• Freeze panes, split a worksheet, and hide and display data in window elements.
• Set print titles and page breaks
• Navigate, manage, and print multiple worksheets
• Create 3-D formulas to link worksheets, and add a watch window
• Switch between workbooks, and copy a sheet from one workbook to another
• Create and manage linked workbooks
• Apply built in and custom number formats to display data in specific formats
• Apply and modify themes
• Merge and change orientation of cells to display text in special ways; Transpose data; and add backgrounds and watermarks
• Create outlines to group and organize data, create custom views, and consolidate data from different worksheets
• Summarize the data in a worksheet by creating automatic subtotals

In MS Excel INTERMEDIATE II (3-hour session), topic areas in the application of creating names for cells and cell ranges, understanding data structure and tables and web sharing of workbooks and worksheets will be covered. Upon completion, attendees will be able to: 

• Use names to make your formulas easier to understand
• Use the name manager and define 3-D names, which span multiple worksheets
• Organize data logically, sort it by the contents of its columns, and filter it to show only those rows that meet certain criteria
• Create and format tables, and use structured references to include table column names in formulas
• Save a workbook as a web page, and use the auto republish feature to keep the web version of a workbook updated
• Add and remove hyperlinks in a worksheet
• Send workbooks via email

In MS Excel INTERMEDIATE III (3-hour session), topic areas including documenting spreadsheets with notes, tags and comments and the creation of workbook/worksheet templates including an extensive review of settings will be covered. Upon completion, attendees will be able to: 

• Use auditing features to trace precedent and dependent cells, and trace errors
• Add and edit comments for a cell and a worksheet
• Protect a workbook or part of a worksheet from unauthorized access or unintentional changes
• Share workbooks, merge versions of a workbook, track changes made by various users, remove personal data from a workbook, and mark a workbook as final
• Change Excel's default application settings
• Create and modify custom templates