Excel - Level 2 • BSCM 512V (18 hours)
Realistic examples will be used to demonstrate ways to build on your basic knowledge of Excel to increase your productivity. Use powerful logic functions such as "IF" to identify conditions met or not met for specific criteria. Determine if you can afford a loan with financial payment functions. Use conditional formatting tools to easily identify key indicators for your business such as top 10, or highest average, based on your unique criteria. Link and group worksheets, use named ranges to quickly locate data, and use powerful database techniques that include data validation, sorting and filtering and many more Excel topics. You must be familiar with AutoSum, AutoFill, how to create a basic chart and understand the difference between relative and absolute referencing. An electronic course manual (pdf) is included.
Course Learning Outcomes
Upon completion of this course, learners will be better able to:
- Create and modify charts (includes format titles, axis, scale, areas and data labels)
- Use functions including conditional formatting with:
- Financial functions for future value, payment, interest and principle payments (FV, PMT, IPMT, PPMT)
- Review of Statistical functions (AVG, COUNT, MIN, MAX, SUM) (taught in-depth in Excel 1)
- Median and Logical functions (IF, COUNTIF, SUMIF, AND, NOT, OR)
- Date and time functions (NOW, TODAY)
- Text functions (TRIM, UPPER, LOWER, CONCATENATE) and vertical lookup (VLookup)
- Create and edit hyperlinks to text and graphics within a workbook and to other files
- Link and group worksheets and workbooks
- Use Excel tables as a database:
- Create tables and data forms
- Sort with single and multiple fields
- Filter and subtotal within databases
- Create pivot tables and pivot charts (overview only)
- Apply data validation:
- Restrict cell entries to the data from a list
- Use a formula to validate data
- Find cells with data restrictions
- Work effectively with large worksheets (includes freeze panes, adjust page order, print titles, display row and column headings, insert page breaks and define print areas)
- Create and edit templates
- Protect and hide worksheets and workbooks
- Use advanced options (includes custom lists)
- Name ranges and use paste special
- Work with themes
- Understand when, why and how to create and run a macro
- Apply and create web queries
Prerequisites: BSCM 511V