Power Excel for Analysts & Managers
Services
Power Excel makes Beginners become productive and awesome in Excel. It has an optional module on Dashboards, which can teach you how to design awesome Excel Dashboards. It is full of Real World Examples.
Power Excel makes Beginners become productive and awesome in Excel. It has an optional module on Dashboards, which can teach you how to design awesome Excel Dashboards. It is full of Real World Examples.
Most of the downloads can be adapted to my job and suddenly I have, in the eyes of my boss, become an excel expert. - Terry Price
Course Package Free Demo Enroll & Pay
Course Highlights
About the Course
Who is This For? ||
- Students / IT newbies aspiring to become a BA
- IT Folks who want to transition into a BA
- Working Data / Business Analysts seeking formal training to stand out from their competition
- Project / Product Managers managing a team of BAs
- Anyone who uses Excel on a daily basis.
Participation Options || On-Demand 24/7
Duration & Schedule ||24 hours of Video Classes. Typically participants complete in 3-6 weeks. You get online access for 24 weeks.
Course Fees || $97 USD (On-Demand Access) / $147 USD (On-Demand Access + Download) / $247 USD (On-Demand Access + Download + Dashboards): Online Payment, Credit Card, Check or Bank Deposit
Quick Snapshot
| Key Course Topics | |||
| Formulas | Formatting | Conditional Formatting | Basic Charting |
| Advanced Charting | Excel Tables | Pivot Tables | Data Validation, Filters |
| Advanced. Formulas | Importing External Data | Shortcuts, Productivity | Basic Form Controls, Macros |
| Optional: Excel Dashboards – 8 hours video instruction |
|||
- 1,350 minutes excel lessons
Each of the 12 topics in Power Excel come with several videos (for eg. we have 15 videos on Formulas) discussing various topics in-detail. There is a total of ~24 hours of video in Power Excel at this moment. All these lessons are neatly structured in to a lesson plan with links to view, download and discuss. You can follow the lesson plan and boost your excel skills in a very short period of time. - 8 Hour Lessons on Excel DashboardsNEW
In this all new module (optional), you will learn how to build world-class Excel Dashboards from scratch. We will discuss dashboard design principles, best practices, see several examples, build 4 different dashboards. Also, you can download unlocked dashboard excel files and reuse them as you see fit. - 45+ example excel workbooks
Almost all the topics in Power Excel include downloadable content – either excel files or ppts or pdf files. Using these files, you can easily improve your understanding and play with the concepts right-away. - 6 Month Access to Online Classroom
Online classroom is a blog-like area with secure userid and password for students. This is where you can ask questions related to lesson topics, discuss the lesson content or share your tips / ideas with other students. You can access the classroom for 180 days from date of joining. - Free Bonus – Formula Cheat-sheet
If you use Excel Formulas alot, then this cheat sheet can help you. It contains the syntax of most important formulas along with example uses and tricks to handle errors. - Free Bonus – List of Keyboard Shortcuts PDF
This one page PDF includes some really powerful and useful Excel Keyboard Shortcuts. - Free bonus – Chart Design E-book
In this short 24 page e-book, I explain the process you should follow to format your charts to wow your audience. It also includes guidance on colors to use. - 30 Day money back guarantee
Each Power Excel membership comes with a 30 day money back guarantee. If you don’t like what you see in Power Excel, just drop us an email and we will refund your money. No questions asked.
Training Methodology
- Login to On-Demand Recordings will be provided. No need to call-in at a specified time.
- You can review the recordings as many times as you want for 6 months.
- Instructor support will be provided on the course website in forum format.
Why Attend Power Excel?
Why Train with Requirements Inc? Learn from Excel MVP
Training Program created & instructed by Experienced, Practicing Excel MVPs [Microsoft Most Valuable Professional].
We have a Proven Track Record: Over 100s trained since 2007.
Comprehensive, All-inclusive Beginner to Intermediate Excel Training Program. No need to enroll in multiple, short courses.
Take a No Risk, Free Demo Info Session on how you can learn a thing or two in Excel. Find out if we are a good fit!
Attend any time, 24 by 7. The Class is always open. Learn at your own time and review as many times as you’d like.
We’re Beginner-Friendly and start from the basics. No IT pre-requisites to attend the program.
Access to Online Course Website. One stop shop to download all course materials and resources.
Downloadable Videos (selected options only) – Get a course you can use any time in the future!
Downloadable templates and practice Excel files allow you to get productive right away.
Course completion certificate provided at the end of the training will demonstrate all skills acquired.
Course Overview
Course Package
Course Details
Lessons on Basic Formulas
- Relative & Absolute Cell References in Formulas
- Difference between relative and absolute references
- using $ symbol while writing formulas
- Switching between relative & absolute refs. With F4 key
- Using IF Formula, Nesting IF Formulas
- IF Formula
- Nesting IF Formulas
- CHOOSE Formula
- Examples of Nested IFs and Discussion
- COUNTIF, SUMIF Formulas
- What is it and Syntax
- SUMIFS examples
- COUNTIFS examples
- Excel Text Formulas
Text formulas are what you use to process, manipulate or find information about a give piece of text in some cell(s). In this lesson, you can learn about the most important text formulas in Excel. The following formulas are discussed,LEFT, TRIM, FIND, LEN, RIGHT, LOWER, SEARCH, TEXT, MID, UPPER, VALUE, PROPER, SUBSTITUTE, REPLACE, CONCATENATE and REPT - VLOOKUP formula, INDEX, MATCH Formulas
- What is it and Syntax
- Vlookup examples
- Vlookup hacks
- Vlookup Errors & Fixing them
- INDEX Formula
- MATCH formula
- SUMIFS Formula, COUNTIFS Formula
- What is it and Syntax
- SUMIFS examples
- COUNTIFS examples
- ROWS, COLUMNS Formulas
- What is it and Syntax
- Using Rows /columns to generate running numbers
- Formula Editing Tips And Tricks
- Using F2 key
- Using F4 Key
- Copying Formulas
- Moving Formulas
- Auto-filling formulas
- Using Mouse to Edit Formula Ranges
- More
- More
- Formula Debugging Using F9 Key
- How to debug formulas using F9 key
Lessons on Advanced Formulas
- Advanced VLOOKUP Tricks
- Advanced Vlookups
- Looking up on left
- How to Get Unique Values from a List using Formulas
- How to get unique values from a list using formulas alone
- OFFSET Formula
- What is it and Syntax
- Offset examples
- Uses of Offset formula
- SUMPRODUCT Formula
- What is it and Syntax
- Sumproduct – Simple examples
- Conditional Sum using Sumproduct
- Sumproduct hacks
- Alternatives to Sumproduct
- SMALL, LARGE Formulas
- What is it and Syntax
- Examples of SMALL and LARGE
- Using Small / large to fetch nth item of an un-sorted list
- Advanced Formulas
- Named Ranges
- Using Formula Audit Tools
- 3D References (example: consolidate sales data)
- Array Formulas – What are they? (example: MAXIF, MINIF)
- Circular References – What are they? (example: log)
- Various Calculation Modes and How to use them
- Formula Errors and how to handle them
Lessons on Formatting
- Formatting Paste Special
- Make phone numbers readable
- Make joining date readable
- Currency format sales and profits
- Understand how formats work, examples
- Conditional format codes – examples
- Hide a value without really removing it
- Make the entire table readable
- Copy and paste formats
- Column widths and row heights
- Format Painter, Paste Special
- Freezing the top row of table
Lessons on Conditional Formatting
- Conditional Formatting – 1
- What is Conditional Formatting?
- Basic CF with values
- Using Formulas in CF
- Examples of CF
- Conditional Formatting – 2
- Advanced CF Options
- Using Icons
- Using Data Bars
- Using Heat-maps
- Highlighting Top 5, above average etc.
- Conditional Formatting – 3
- Conditional Formatting & Dates
- Conditional Formatting and Duplicate Data
- Complex CF Examples
Lessons on Basic Charting
- How to Select Correct Chart
- Basics of Chart Selection – Rationale
- Common Messages for charts
- Which Charts to use
- Further help & Tips on Chart selection
- Introduction to Excel Charts
- Understanding Excel Charting Options
- Bar Charts / Column Charts
- Line Charts
- Scatter Plots
- Pie Charts
- Other Charts in Excel
- Chart Formatting – 10 Tips
- Making titles dynamic
- Reversing categories
- Adjusting axis – (non-date)
- Adjusting axis – (date)
- Removing axis, grid lines, legend
- Combining 2 chart types
- Adding secondary axis
- Error bars, adding and formatting them
- Choosing right colors
- Chart Templates
- Bar & Column Chart Formatting
- Overlapped Bar Charts, Understanding Series Gaps
- Negative bars
- Highlighting the max or minimum item automatically
- Adding average line to the chart
Lessons on Advanced Charting
- 7 Advanced Charting Tutorials in Excel
- Bullet chart – what is it and how to make
- Incell charts – using REPT to make charts
- Min-Max chart – showing range along with average
- Waterfall charts
- Time line charts
- Thermo-meter charts
- Gauge Chart
- Dynamic Charts in Excel
- Need for Dynamic Charts
- Dynamic charts using Filters
- Dynamic charts using camera tool
- More info. & ideas on dynamic charts
- Interactive Charts In Excel
- Need for interactive charts
- Introduction to form controls
- Using Scrollbar control
- using checkboxes
- Example interactive charts (2)
Lessons on Class Projects
- Excel School Class Project # 1
- Class project objective
- building an interactive chart to compare our performance with competition
- Excel School Class Project # 2
- Class project objective
- Building a dynamic dashboard using camera tool and macros
Lessons on Using Tables
- Excel Tables
- What Is A Table?
- Format As Table
- Understand Table Formatting Options
- Filters, Can Get Multiple Filters In The Same Sheet
- Total Row, Ability To Select Type Of Summary
- Structured References – What Are They?
- Write A Formula For Profit %
- Write A Formula For Profitability Per Customer
- Advantages Of Tables
- Convert Table To Named Range
- Compatibility Of Tables With Excel 2003 And Earlier
Lessons on Pivot Tables
- Excel Pivot Tables
- What Is A Pivot, Why You Should Use It
- Creating Your First Pivot Report
- Formatting Pivots
- Summarizing Data, Playing With Pivots
- Grouping Values
- Calculated Fields
- Calculated Items
- Making A Pivot Chart
- Copying Pivot Tables
- Example Uses Of Pivots
- Pivot Table Tricks
- Guest lecture by Debra Dalgleish
- Preparing your data
- Pivot and chart
- Group dates and data
- Calculate
- Focus with filters, sort
Lessons on Data Filters
- Data Filters & Sorting
- Uses of Filters
- Basic Filtering
- Short-cuts for filtering
- Visual Filters – Using Conditional Formats
- Sorting
- Sorting left to right
Lessons on Validation
- Data Validation
- Uses of Data Validation
- Simple Data Validation Examples
- Using drop-down box to validate
- Using Formulas
- Showing Error Messages
- Advanced DV Tricks
Lessons on Working with Data
- Importing External Data
- What is the purpose of Excel Import?
- Understanding various import options
- Importing Text Files – Example: Dynamic Data Validation
- Importing Web Pages – Example: Movie Tracker
- Importing Access DBs – Example: Sales Pivot Report
- Using Text Import Utility – Example: Converting Dates
- Excel Access Integration
- Excel to Access
- Access to Excel
- Integrating Excel & Access thru Automation
- Working With Complex Worksheets
- What is a complex sheet?
- Tips for handling complexity
- Designing a complex workbook
Lessons on Using Excel
- Export Excel to Other Formats
- How to Export Excel Content to PPT or Word Documents?
- How to Export Excel Content to Web Pages
- How to Export Excel Content to PDF
- Excel 2010 – A Short Introduction
- New features in Excel 2010
- Sparklines
- Conditional Formatting Enhancements
- Backstage view / File menu
- etc.
Lessons on Macros & VBA
- Introduction To Macros
- What is a Macro
- Using Macro Recorder and Your First Macro
- Editing Macros & VBE
- Macros vs. UDFs
- How to get more help on Macros & Advanced Excel
Lessons on Excel Dashboards
- What is a Dashboard?
- 5 Dashboard Examples
- Deriving our own dashboard definition
- Looking at Dashboard definitions around web
- Conclusions
- Dashboard Creation Process
- Why have a process – Learning from a Failed Dashboard
- Defining the Dashboard Creation Process
- Example Dashboard made with such process
- Conclusions
- Making Excel Dashboards – Part 1
- Defining the purpose of the dashboard
- Finding appropriate metrics for the dashboard
- Making a mock up of the dashboard
- Making Excel Dashboards – Part 2
- Understanding the data
- Writing Formulas
- Making Charts
- Adding Conditional Formats
- Putting Everything Together
- Conclusions & Homework
- Dashboard Design Tips & Tricks
- Making Website Dashboard
- Making a Sales Dashboard
- Making a KPI Dashboard
