Intermediate Excel for Business

Intermediate Excel for Business covers the essential applications of Excel used frequently by business professionals. This course is perfect for those with familiarity with basic formulas and functions, charts, and workbook management techniques. In this intermediate-level course, you will learn Pivot Tables, database functions including VLOOKUP and SUMIFS, and logical and statistical functions. We will show you crucial database management tools including Named Ranges, Sort & Filter, and Data Validation. Throughout the class we'll show you tricks and techniques to work more efficiently, including navigational and keyboard shortcuts. In this hands-on course you will work on real-world examples, reinforcing the concepts with practice throughout the class, as well as a cumulative project at the end. Each attendee will receive access to our revolutionary Excel Fundamentals video suite, with lifetime access to several hours of premium content.

Additional instructions on how to set up Mac will be sent upon registration.

Syllabus:

Worksheet Management
1) Navigation	Keyboard shortcuts that facilitate quick and easy navigation around the worksheet
2) Paste Special	Use Paste Special to apply formats, as well as perform calculations on selected cells
Working with Text
3) Splitting Text	Use Text to Columns to split text into multiple cells
4) Joining Text	Join text from separate cells
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) AND, OR	Tests to see whether multiple conditions are true
13) IF statements	Use IF statements to return output based on the contents of another cell
Pivot Tables
14) Pivot Tables	Create Pivot Tables to quickly summarize large databases
15) Pivot Tables & Grouping	Group within Pivot Tables
16) Multiple Pivot Tables	Create multiple Pivot Tables on a single worksheet
Statistical Functions
17) Ranking	Use Rank.eq and Rank.avg functions to determine a cell's position within a larger array
18) COUNTIFS	Use COUNTIFS function to count cells based on one or more conditions
19) SUMIFS	Use SUMIFS function to sum cells based on one or more conditions
Advanced Charts
20) Combo Charts	Combine two or more charts into a single chart, with the option of adding a secondary axis
End of Class Project
22) Project	End of class project to review key concepts from the class