Excel for Business Level II - Intermediate

Excel for Business Level II - Intermediate is the second of a three part series that aims to tackle the most common and practical uses of Excel in business. This course is perfect for those who have either taken Level I or already have a basic working knowledge of Excel and would like to improve efficiency and expand into more complex functions and data management features. By the end of this course, you will know how to dynamically extract data from large databases, quickly organize and summarize tables with Pivot Tables, and manage large amounts of data. You'll also learn some handy tricks and shortcuts to cut down hours of unnecessary work each day. 

Worksheet Management 
1) Navigation - Keyboard shortcuts that facilitate quick and easy navigation around the worksheet.
2) Paste Special - lUse Paste Special to apply formats, as well as perform calculations on selected cells.

Text
3) Splitting TextUse Text to Columns to split text into multiple cells.
4) Joining TextJoin text from separate cells.

Database Management
5) Named Ranges - Assign a name to a range of cells to make it easier to reference those ranges in calculations.
6) Data Validation - Create a dropdown menu to make data entry quicker and more efficient.
7) Sort & Filter - Use Sort & Filter to find and organize data in large databases.
8) Remove Duplicates - Use Remove Duplicates to eliminate duplicate data.

Database Functions 
9) Vlookup - Use Vlookup to find information in one column and return information from another column.
10) Hlookup - Use Hlookup to find information in one row and return information from a second row.
11) Vlookup-Closet Match - If an "Exact Match" cannot be found, Vlookup can still be used to find the closest match.

Logical Functions
12) True, False, Not - Tests to see whether criteria is true/false, or not true/false.
13) And, Or - Tests to see whether multiple conditions are true.
14) IF statements - Use IF statements to return output based on the contents of another cell.

Statistical Functions
15) Rank.eq function - Use Rank.eq function to determine a cell's position within a larger array.
16) Countifs function - Use Countifs function to count cells based on one or more conditions.
17) Sumifs function - Use Sumifs function to sum cells based on one or more conditions.

Pivot Tables 
18) Table review - Review benefits of formatting data as a table.
19) Pivot Table - Create Pivot Tables to quickly summarize large databases.
20) Pivot Tables & Grouping - Group within Pivot Tables.
21) Multiple Pivot Tables - Create multiple Pivot Tables on a single worksheet.

End of Class Project 
22) Project - End of class project to review key concepts from the class.