Course Description
Just about everyone knows how to construct a simple spreadsheet. Most business users can add IF statements and SUM function to do heavy-duty number crunching. But if you really want to set your Excel skills apart from the rest of the pack, you have to move on to the advanced functions. This usually involves needing Excel training. Knowing how to create sophisticated spreadsheets capable of not only displaying data but of producing refined analysis of the data.
The Excel Power Skills class was designed with your real-world problem solving needs in mind. In each of its subject modules it first provides you with a quick overview of the concept followed by a step-by-step introduction as to how Excel implements the concept. Once these essentials have been mastered, you will move on to more sophisticated models that provide real-value to solving real-world situations.
You will leave this Excel training course proficient in power functions and techniques that others do not even know exist. You will be set to immediately begin implementing what you have learned to the benefit of your job and your career.
Substitution & Cancellation Policy:
You may cancel or reschedule up to 21 days prior to the start date of the class at no penalty. For any cancellation or reschedule requests within 21 days, the full course tuition is still due and not eligible for refund. Any paid tuition will be credited towards a future class and must be used within 12 months.
*Partner delivered courses may be subject to different cancellation terms
Agenda
1. The "Power" Functions — the basics for building power spreadsheets
Practice Session: Applying the functions
2. Linear Programming with Excel — optimizing everyday business solutions
- Problem Types
- Setting up the Solution
Practice Session: Maximum/Minimum Solutions with Solver
3. Odds and Ends — a helpful collection of various tips, tricks, and commands for everyday use
- IFERROR
- Displaying cells with FORMAT
- VLOOKUP/ HLOOKUP
- Resolving Circular References
- Trace Dependencies
- Goal Seek
Practice Session: Practice Worksheet
4. Using Text Functions — formatting text to get it just the way you want it
- LEN
- LEFT
- RIGHT
- MID
- TRIM
- And many others
Practice Session: Cleaning up database entries
5. Multiple Regression with Excel — determining relationships between variables
- Regressions
- Multiple Regressions
- Interpreting Results
- STEYX, LINEST
- Qualitative Variables
Practice Session: Trend analysis of business data
6. Using Pivot Tables — Pivot tables for displaying and analyzing data
- Creating the Pivot Table
- Creating the Pivot Chart
- Viewing data from multiple perspectives
Practice Session: Evaluating Sales Data
7. Utilizing Correlations — using the Data Analysis Tool
- The meaning of correlations
- Calculating and interpreting correlations
Practice Session: Calculate correlations for a predictive model
8. Array Functions
- One dimensional arryas
- Two dimensional arrays
- Working with arrays
- Counting characters
- Summing random subsets of variables
Practice Session: Building one and two dimensional arrays
9. Using Functions for Random Variables — statistics with Excel
- Random Variables
- Probability Distributions
- Common Business Examples
Practice Session: Statistical Analysis using Excel
10. Monte Carlo Analysis
- The basis for Monte Carlo
- RAND
- Building a simple model
- Building an advanced Monte Carlo model
Practice Session: Calculating Risk for uncertain events