MICROSOFT® EXCEL (INTERMEDIATE-ADVANCED)
1 session per week
4 hours per session
48 total class hours
Any individual interested in increasing their knowledge and proficiency with Excel 2016. Students transitioning from Excel 2007/2010/2013. Anyone wishing to move their Excel skills to a next level.
Prospective students should be familiar with basic computer terminology, be proficient with the use of the mouse and keyboard, saving files to folders other than their C drive, downloading and uploading files from websites which will be used extensively in the class, have access to the internet and are able to use Excel either at their personal or work computer and have at least 6 months experience using Microsoft Excel 2007 and beyond. Participants should understand basic mathematical principles.
(This outline is intended to be a guide for the course and is subject to change without notice at the discretion of the instructor)
2007 /2010 to 2013 to 2016 Skills Migration
- Learning the new 2016 interface. How to adapt what we know about 2007/2010/2013 into 2016.
- Shortcut keys for navigating the new ribbon interface will be discussed and practiced.
Range Names, Lists, and String Functions
- Use of range names for navigation, formula simplification, and using range name constants.
- 2016 range name scopes will be discussed.
- Use of the new Names Manager. MID, LEFT, RIGHT, CONCATENATE, TRANSPOSE.
- SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, AVERAGEIFS.
- Nesting functions.
- Various LOOKUP functions will be discussed.
- Combined with the previous weeks named range concept and a new “list” tool, participants obtain a useful and commonly used skill set.
PivotTables and PivotCharts
- PivotTables and PivotCharts Filtering, Slicers, Timeline
- Formatting options, including the use of the conditional formatting features.
- Creating calculated fields PivotTables
- Refreshing Pivotable data
- Proper data design principles.
- Adding, deleting records, sorting, filtering data.
- Advanced querying, Outlining, Data Tables.
- Using multi-sheet and multi-book formulas. 3D formulas and linking
- Linking charts to PowerPoint presentations.
Managing Multi-Sheet Workbook
- Using multi-sheet and multi-book formulas. 3D formulas and linking. Array functions.
- Learn the process of recording “keystroke” macros and analyzing the results.
- Learn to modify and enhance the recorded macros.
- Create macros from scratch.
- Lock Cells and Protect worksheet/workbook
- Apply your skills.
- Extend your skills.