Ending In:
$945
97% off
Courses
6
Lessons
447
Enrolled
2,071
Courses
6
Lessons
447
Enrolled
2,071
What's Included
Microsoft Excel: Advanced Excel Formulas & Functions
$195 Value
Microsoft Excel: Data Visualization, Excel Charts & Graphs
$175 Value
Microsoft Excel: Intro to Power Query, Power Pivot & DAX
$175 Value
Microsoft Excel: Data Analysis with Excel Pivot Tables
$175 Value
Master Microsoft Excel Macros and Excel VBA
$50 Value
Microsoft Excel from Beginner to Advanced
$175 Value
Product Details
1 of 6
Access
Lifetime
Content
6 hours
Lessons
93
Microsoft Excel: Advanced Excel Formulas & Functions
Master 75+ Excel Formula & Functions with Hands-On Demos
By Excel Maven | in Online Courses
It's time to show Excel who's boss! Whether you're starting from square one or aspiring to become an absolute Excel wizard, this is the right place for you. This course will give you a deep understanding of the advanced formulas and functions that transform Excel from a basic spreadsheet program into a dynamic and powerful analytics tool. Through hands-on, contextual examples, you'll learn why these formulas are awesome and how they can be applied in a number of ways.
- Access 93 lectures & 6 hours of content 24/7
- Easily build dynamic tools & Excel dashboards to filter, display, & analyze your data
- Go rogue & design your own formula-based formatting rules
- Join datasets from multiple sources in seconds w/ lookup, index, & match functions
- Pull real-time data from APIs directly into Excel
- Manipulate dates, times, text, & arrays
- Automate tedious & time-consuming analytics tasks using cell formulas and functions in Excel
Chris Dutton is a certified Microsoft Excel Expert, analytics consultant, and best-selling instructor with more than a decade of experience specializing in business intelligence, marketing analytics and data visualization.
He founded Excel Maven in 2014 to provide high-quality, applied analytics training and consulting to clients around the world, and now mentors 25,000+ students in more than 150 countries. He has developed award-winning data analytics and visualization tools, featured by Microsoft, the New York Times, and the Society of American Baseball Research.
He founded Excel Maven in 2014 to provide high-quality, applied analytics training and consulting to clients around the world, and now mentors 25,000+ students in more than 150 countries. He has developed award-winning data analytics and visualization tools, featured by Microsoft, the New York Times, and the Society of American Baseball Research.
Details & Requirements
- Length of time users can access this course: lifetime
- Access options: web streaming, mobile streaming
- Certification of completion not included
- Redemption deadline: redeem your code within 30 days of purchase
- Experience level required: all levels
- Microsoft Excel required
Compatibility
- Internet required
Course Outline
- Getting Started
- Course Structure & Outline (4:12)
- Downloading the Homework Exercises (1:42)
- Excel Formulas 101
- Introduction (1:06)
- The Formula Library & Auditing Tools (4:37)
- DEMO: Evaluate Formula (1:25)
- Basic Formula Syntax (3:31)
- Fixed & Relative Cell References (4:57)
- Common Errors & IFERROR (7:25)
- Function, CTRL & ALT Shortcuts (6:36)
- BONUS: Mac Shortcuts (2:08)
- Data Validation Rules (3:28)
- Fixed vs. Volatile Functions (3:21)
- QUIZ: Formulas 101
- HOMEWORK: Formulas 101 (0:46)
- Conditional Statements & Logical Operators
- Introduction (1:23)
- Anatomy of the IF Statement (4:01)
- Nested IF Statements (4:53)
- AND/OR Operators (8:47)
- NOT () Operators (3:17)
- Addressing Errors with IFERROR (4:03)
- Common IS Statements (4:00)
- QUIZ: Conditional Statements & Logical Operators
- HOMEWORK: Conditional Statements & Logical Operators (0:44)
- Statistical Functions
- Introduction (1:12)
- Basic Stats Functions (5:17)
- SMALL/LARGE & RANK/PERCENTRANK (6:19)
- RAND() & RANDBETWEEN (2:12)
- The SUMPRODUCT Function (3:55)
- COUNTIFS/SUMIFS/AVERAGEIFS (4:49)
- DEMO: Basic Dashboards with Stats Functions (8:09)
- QUIZ: Statistical Functions
- HOMEWORK: Statistical Functions (0:35)
- Lookup & Reference Functions
- Introduction (1:23)
- Named Arrays (3:30)
- ROW/ROWS & COLUMN/COLUMNS (3:18)
- VLOOKUP & HLOOKUP (5:54)
- DEMO: Joining Data with VLOOKUP (6:23)
- Combining IFERROR & VLOOKUP (4:30)
- VLOOKUP Range Options (6:18)
- The INDEX Function (1:59)
- The MATCH Function (2:32)
- Combining INDEX & MATCH (6:08)
- Combining MATCH & VLOOKUP (4:47)
- UPDATE: VLOOKUP Correction (5:05)
- The OFFSET Function (2:05)
- Combining OFFSET & COUNTA (2:58)
- DEMO: Building a Scrolling Chart with OFFSET (9:51)
- QUIZ: Lookup & Reference Functions
- HOMEWORK: Lookup & Reference Functions (0:54)
- Text Functions
- Introduction (0:57)
- UPPER, LOWER, PROPER & TRIM (3:32)
- CONCATENATE (&) (3:26)
- LEFT, MID, RIGHT & LEN (3:31)
- TEXT & VALUE (4:20)
- SEARCH & FIND (5:04)
- Categorizing Data with IF(ISNUMBER(SEARCH)) (5:39)
- Combining RIGHT, LEN & SEARCH (5:43)
- The SUBSTITUTE Function (2:21)
- QUIZ: Text Functions
- HOMEWORK: Text Functions (0:59)
- Date & Time Functions
- Introduction (2:13)
- Understanding DATEVALUE (5:05)
- Date Formatting & Fill Series (3:39)
- TODAY() & NOW() (2:16)
- YEAR/MONTH/DAY & HOUR/MINUTE/SECOND (3:04)
- The EOMONTH Function (6:17)
- The YEARFRAC Function (2:27)
- WEEKDAY, WORKDAY & NETWORKDAYS (7:24)
- The DATEDIF Function (4:23)
- DEMO: Budget Pacing Tool (11:07)
- QUIZ: Date & Time Functions
- HOMEWORK: Date & Time Functions (0:35)
- Formula-Based Formatting
- Introduction (1:30)
- Creating and Managing Formula-Based Rules (4:39)
- DEMO: Highlighting Rows with MOD (3:50)
- DEMO: Formatting Cells Based on Values (3:29)
- DEMO: Formatting Cells with Stats Functions (5:28)
- DEMO: Formatting Cells with Text & Conditional Functions (5:08)
- QUIZ: Formula-Based Formatting
- HOMEWORK: Formula-Based Formatting (0:34)
- Basic Array Formulas
- Introduction (1:26)
- Rules of Array Formulas (3:24)
- Pros & Cons of Array Formulas (2:15)
- Vertical, Horizontal & 2-D Array Constants (6:26)
- Using Array Constants in Formulas (3:38)
- Named Array Constants (4:31)
- The TRANSPOSE Function (4:21)
- Linking Data: Array vs. Non-Array Comparison (2:36)
- DEMO: Returning the "X" Largest Values (3:09)
- DEMO: Counting Characters Across Cells (1:52)
- DEMO: Creating a "MAX IF" Function (Part 1) (3:09)
- DEMO: Creating a "MAX IF" Function (Part 2) (6:54)
- The Double Unary Operator ("--") (3:49)
- QUIZ: Array Formulas
- HOMEWORK: Array Formulas (2:09)
- Badass Bonus Functions
- Introduction (1:41)
- The INDIRECT Function (10:55)
- The HYPERLINK Function (6:19)
- Real-Time Data with WEBSERVICE & FILTERXML (7:16)
- QUIZ: Badass Bonus Functions
- Wrapping Up
- Resources & Next Steps (0:41)
Access
Lifetime
Content
4.5 hours
Lessons
49
Microsoft Excel: Data Visualization, Excel Charts & Graphs
Master 20+ Charts & Graphs in Excel 2016 with Hands-On Demos
By Excel Maven | in Online Courses
Ask people what comes to mind when they think of Excel and odds are they'll say, "spreadsheets". However, Excel is an incredibly powerful, robust, and dynamic data visualization platform. This course will give you a deep, comprehensive understanding of Excel's latest data visualization tools and techniques. You'll learn when, why, and how to use each chart type, discover key data visualization best practices, and learn through interactive, hands-on demos and exercises every step of the way.
- Access 49 lectures & 4.5 hours of content 24/7
- Explore each of the 20+ chart types that Excel 2016 has to offer
- Dive into a series of 12+ advanced Excel demos guaranteed to turn you into a data visualization pro
Chris Dutton is a certified Microsoft Excel Expert, analytics consultant, and best-selling instructor with more than a decade of experience specializing in business intelligence, marketing analytics and data visualization.
He founded Excel Maven in 2014 to provide high-quality, applied analytics training and consulting to clients around the world, and now mentors 25,000+ students in more than 150 countries. He has developed award-winning data analytics and visualization tools, featured by Microsoft, the New York Times, and the Society of American Baseball Research.
He founded Excel Maven in 2014 to provide high-quality, applied analytics training and consulting to clients around the world, and now mentors 25,000+ students in more than 150 countries. He has developed award-winning data analytics and visualization tools, featured by Microsoft, the New York Times, and the Society of American Baseball Research.
Details & Requirements
- Length of time users can access this course: lifetime
- Access options: web streaming, mobile streaming
- Certification of completion not included
- Redemption deadline: redeem your code within 30 days of purchase
- Experience level required: all levels
- Microsoft Excel 2016 required
Compatibility
- Internet required
Course Outline
- Getting Started
- Course Structure & Outline (2:03)
- Bonus Homework Exercises
- Data Visualization Best Practices
- Key Principles & The 10-Second Rule (2:45)
- The Good, The Bad & The Ugly (3:44)
- Three Key Questions (1:49)
- Chart Customization
- Setting Expectations (1:02)
- Chart Elements, Layouts & Styles (6:20)
- Chart Formatting Options (5:26)
- Changing Chart Types & Adding a Secondary Axis (3:03)
- Creating, Modifying & Applying Custom Templates (4:06)
- QUIZ: Chart Customization
- Mastering Basic Charts & Graphs
- Bar & Column Charts (8:49)
- HOMEWORK: Bar & Column Charts
- Histogram & Pareto Charts (5:58)
- HOMEWORK: Histogram & Pareto Charts
- Line Charts & Trendlines (5:16)
- HOMEWORK: Line Charts & Trendlines
- Area Charts (4:41)
- HOMEWORK: Area Charts
- Pies, Donuts & Race Tracks (12:04)
- HOMEWORK: Pies, Donuts & Race Tracks
- Scatter Plots (7:30)
- Bubble Charts (6:36)
- HOMEWORK: Scatter Plots & Bubble Charts
- Box & Whisker Charts (6:05)
- HOMEWORK: Box & Whisker Charts
- Tree Maps & Sunbursts (6:36)
- HOMEWORK: Tree Maps & Sunbursts
- Waterfall Charts (3:17)
- Funnel Charts (3:42)
- HOMEWORK: Waterfall & Funnel Charts
- Radar Charts (7:56)
- HOMEWORK: Radar Charts
- Stock Charts (7:18)
- HOMEWORK: Stock Charts
- Heat Maps (4:41)
- HOMEWORK: Heat Maps
- Surface & Contour Charts (7:35)
- HOMEWORK: Surface & Contour Charts
- Geospatial Maps with Power Map (5:28)
- HOMEWORK: Power Map
- Basic Combo Charts (7:57)
- HOMEWORK: Combo Charts
- Sparklines (2:07)
- HOMEWORK: Sparklines
- QUIZ: Basic Charts & Graphs
- Next-Level Data Viz Demos
- Setting Expectations (1:40)
- DEMO: Custom Image Overlay Charts (6:01)
- DEMO: Adding Binary Values to Highlight Ranges (5:02)
- DEMO: Automation with OFFSET & COUNTA (7:08)
- DEMO: Adding Interactive Elements with Form Controls (12:30)
- DEMO: Animating Changes Over Time (14:20)
- DEMO: Building a Dynamic Dashboard (Part 1) (16:10)
- DEMO: Building a Dynamic Dashboard (Part 2) (12:08)
- DEMO: Dynamic Value-Based Formatting (10:09)
- DEMO: Dynamically Highlighting a Data Series (10:26)
- DEMO: Building a Custom Pacing Chart (10:22)
- DEMO: Building a Custom Gauge Chart (9:58)
- DEMO: Visualizing Percentages with Arrays (7:16)
- QUIZ: Next-Level Data Viz
- Wrapping Up
- Resources & Next Steps (2:38)
Access
Lifetime
Content
7 hours
Lessons
86
Microsoft Excel: Intro to Power Query, Power Pivot & DAX
Get Up & Running with Excel's Data Modeling & Business Intelligence Tools
By Excel Maven | in Online Courses
This course will introduce you to Microsoft Excel's powerful data modeling and business tools Power Query, Power Pivot, and Data Analysis Expressions (DAX). If you're looking to become a power Excel user and supercharge your analytics, then this is the A-Z guide that you're looking for.
- Access 86 lectures & 7 hours of content 24/7
- Use sample data from a fictional supermarket chain to get hands-on w/ Power Query
- Practice shaping, blending, & exploring project files
- Cover the fundamentals of database design & normalization
- Explore & analyze your data model using Power Pivot & DAX
- Cover basic DAX syntax & learn some of the most powerful and commonly used functions
Chris Dutton is a certified Microsoft Excel Expert, analytics consultant, and best-selling instructor with more than a decade of experience specializing in business intelligence, marketing analytics and data visualization.
He founded Excel Maven in 2014 to provide high-quality, applied analytics training and consulting to clients around the world, and now mentors 25,000+ students in more than 150 countries. He has developed award-winning data analytics and visualization tools, featured by Microsoft, the New York Times, and the Society of American Baseball Research.
He founded Excel Maven in 2014 to provide high-quality, applied analytics training and consulting to clients around the world, and now mentors 25,000+ students in more than 150 countries. He has developed award-winning data analytics and visualization tools, featured by Microsoft, the New York Times, and the Society of American Baseball Research.
Details & Requirements
- Length of time users can access this course: lifetime
- Access options: web streaming, mobile streaming
- Certification of completion not included
- Redemption deadline: redeem your code within 30 days of purchase
- Experience level required: all levels
- You need a version of Excel that is compatible with Power Pivot (Excel 2013/2016 Standalone, Office 365 Pro Plus, Enterprise E3/E5, Office Professional 2016, etc.)
Compatibility
- Internet required
Course Outline
- Getting Started
- Course Structure & Outline (1:47)
- IMPORTANT: Versions & Compatibility (3:15)
- Exploring the Course Project Files (1:16)
- Setting Expectations (1:53)
- Intro to "Power Excel"
- The "Power Excel" Workflow (3:50)
- "Best Thing to Happen to Excel in 20 Years" (8:18)
- When to use Power Query & Power Pivot (0:44)
- QUIZ: Intro to "Power Excel"
- HOMEWORK: Intro to "Power Excel"
- Power Query
- Introduction (0:27)
- Meet Power Query (aka "Get & Transform") (2:40)
- The Query Editor (3:17)
- Data Loading Options (1:40)
- Basic Table Transformations (8:06)
- Text-Specific Tools (10:17)
- Number-Specific Tools (7:41)
- Date-Specific Tools (7:39)
- PRO TIP: Creating a Rolling Calendar (5:02)
- Adding Index & Conditional Columns (7:44)
- Grouping & Aggregating Data (7:10)
- Pivoting & Unpivoting (8:52)
- Modifying Workbook Queries (4:18)
- Merging Queries (7:43)
- Appending Queries (5:49)
- Connecting to a Folder of Files (9:32)
- Power Query Best Practices (2:44)
- QUIZ: Power Query
- HOMEWORK: Power Query
- Data Modeling 101
- Introduction (0:28)
- Meet Excel's "Data Model" (3:38)
- Data vs. Diagram View (2:33)
- Database Normalization (4:14)
- Data Tables vs. Lookup Tables (5:40)
- Relationships vs. Merged Tables (2:07)
- Creating Table Relationships (6:54)
- Modifying Table Relationships (2:06)
- Active vs. Inactive Relationships (3:55)
- Relationship Cardinality (5:46)
- Connecting Multiple Data Tables (5:59)
- Filter Direction (5:08)
- Hiding Fields from Client Tools (4:59)
- Defining Hierarchies (3:53)
- Data Model Best Practices (1:38)
- QUIZ: Data Modeling 101
- HOMEWORK: Data Modeling 101
- Power Pivot & DAX 101
- Introduction (0:29)
- Creating a "Power" PivotTable (5:27)
- "Power" Pivots vs. "Normal" Pivots (2:48)
- Intro to Data Analysis Expressions (DAX) (1:12)
- Calculated Columns (8:39)
- DAX Measures = Supercharged Calculated Fields (2:24)
- Creating Implicit Measures (1:59)
- Creating Explicit Measures (AutoSum) (7:08)
- Creating Explicit Measures (Power Pivot) (7:42)
- Understanding Filter Context (9:18)
- Step-by-Step Measure Calculation (12:06)
- RECAP: Calculated Columns vs. Measures (2:29)
- Power Pivot Best Practices (1:50)
- QUIZ: Power Pivot & DAX 101
- HOMEWORK: Power Pivot & DAX 101
- Common DAX Functions
- Introduction (0:27)
- DAX Formula Syntax & Operators (4:05)
- Common DAX Function Categories (3:54)
- Basic Math & Stats Functions (12:45)
- COUNT, COUNTA, DISTINCTCOUNT & COUNTROWS (10:35)
- HOMEWORK: Math & Stats Functions
- Logical Functions (IF/AND/OR) (16:49)
- SWITCH & SWITCH(TRUE) (10:33)
- Text Functions (10:53)
- HOMEWORK: Logical & Text Functions
- The CALCULATE Function (14:39)
- Adding Filter Context with FILTER (Part 1) (9:20)
- Adding Filter Context with FILTER (Part 2) (14:23)
- Removing Filter Context with ALL (9:06)
- HOMEWORK: CALCULATE, FILTER & ALL
- Joining Data with RELATED (3:39)
- Iterator ("X") Functions: SUMX (12:20)
- Iterator ("X") Functions: RANKX (5:33)
- HOMEWORK: Iterator Functions
- Basic Date & Time Functions (4:58)
- Time Intelligence Formulas (17:02)
- HOMEWORK: Time Intelligence
- Speed & Performance Considerations (3:02)
- DAX Best Practices (2:03)
- QUIZ: Common DAX Functions
- Wrapping Up
- Introduction (0:53)
- Data Visualization Options (4:35)
- SNEAK PEEK: PowerBI (5:55)
- Resources & Next Steps (2:25)
Access
Lifetime
Content
6 hours
Lessons
82
Microsoft Excel: Data Analysis with Excel Pivot Tables
Master Excel Pivot Tables & Pivot Charts with Hands-On Demos
By Excel Maven | in Online Courses
Pivot Tables are an essential tool for anyone who works with data in Excel. Pivots allow you to explore and analyze raw data quickly, revealing powerful insights and trends otherwise buried in the noise. In other words, they give you answers. Whether you're exploring product sales, analyzing marketing tactics, or wonder how Boston condo prices have trended over the past 15 years, Excel Pivot Tables provide fast, accurate, and intuitive solutions to even the most complicated questions. This course will give you a deep, 100% comprehensive understanding of Pivot Tables and Pivot Charts.
- Access 82 lectures & 6 hours of content 24/7
- Cover raw data structure, table layouts & styles
- Explore design & formatting options
- Understand sorting, filtering, & grouping tools
- Anaylze data from a number of real-world case studies including San Diego burrito ratings, shark attack records, MLB team statistics, & more
Chris Dutton is a certified Microsoft Excel Expert, analytics consultant, and best-selling instructor with more than a decade of experience specializing in business intelligence, marketing analytics and data visualization.
He founded Excel Maven in 2014 to provide high-quality, applied analytics training and consulting to clients around the world, and now mentors 25,000+ students in more than 150 countries. He has developed award-winning data analytics and visualization tools, featured by Microsoft, the New York Times, and the Society of American Baseball Research.
He founded Excel Maven in 2014 to provide high-quality, applied analytics training and consulting to clients around the world, and now mentors 25,000+ students in more than 150 countries. He has developed award-winning data analytics and visualization tools, featured by Microsoft, the New York Times, and the Society of American Baseball Research.
Details & Requirements
- Length of time users can access this course: lifetime
- Access options: web streaming, mobile streaming
- Certification of completion not included
- Redemption deadline: redeem your code within 30 days of purchase
- Experience level required: all levels
- You need a version of Excel that is compatible with Power Pivot (Excel 2013/2016 Standalone, Office 365 Pro Plus, Enterprise E3/E5, Office Professional 2016, etc.)
Compatibility
- Internet required
Course Outline
- Getting Started
- Course Structure & Outline (3:26)
- Getting to Know the IMDb Movie Database (2:21)
- Setting Expectations (1:14)
- PivotTable 101
- Why PivotTables? (4:10)
- Structuring the Source Data (2:00)
- Inserting Your First PivotTable (3:13)
- Navigating the Field List (7:43)
- Analyze & Design Options (1:33)
- Selecting, Clearing, Moving & Copying Pivots (5:14)
- Refreshing & Updating Pivots (7:30)
- PRO TIP: Dealing with Growing Source Data (6:49)
- How PivotTables ACTUALLY Work (3:39)
- QUIZ: PivotTable 101
- HOMEWORK: PivotTable 101
- PivotTable Formatting
- Number Formatting (2:12)
- PRO TIP: Formatting Empty Cells (1:41)
- Table Layouts & Styles (5:30)
- PRO TIP: Using Tabular Layouts to Create New Tables (2:20)
- Customizing Headers & Labels (1:16)
- Adding Conditional Formats (6:51)
- PRO TIP: Data Bars with Invisible Text (2:13)
- QUIZ: PivotTable Formatting
- HOMEWORK: PivotTable Formatting
- Sorting, Filtering & Grouping
- Basic Sorting Options (5:01)
- PRO TIP: Incorrect Alphabetical Sorting (2:01)
- Label Filters & Manual Selections (8:15)
- PRO TIP: Label Filters with Wildcards (3:15)
- Value Filters (3:10)
- PRO TIP: Enabling Multiple Filters (2:42)
- Grouping Data (3:23)
- Automatic Date Grouping (4:25)
- Filtering with Slicers & Timelines (4:51)
- Breaking Out Report Filter Pages (4:13)
- QUIZ: Sorting, Filtering & Grouping
- HOMEWORK: Sorting, Filtering & Grouping
- Calculated Values & Fields
- Summarizing Values (4:45)
- PRO TIP: Avoiding the "Count Of" Trap (3:49)
- "Show Values As" Calculations (3:35)
- Show Values As: % of Column/Row (3:47)
- Show Values As: % of Parent (3:02)
- Show Values As: Difference From (4:41)
- Show Values As: Running Total (2:44)
- Show Values As: Rank (2:36)
- Show Values As: Index (8:06)
- Inserting Calculated Fields (6:30)
- Calculations in Pivots vs. Raw Data (4:32)
- Calculating Using Counts (Part 1) (7:20)
- Calculating Using Counts (Part 2) (4:19)
- Calculated Items (NOT RECOMMENDED) (6:11)
- Solve Order & List Formula Tools (4:54)
- QUIZ: Calculated Values & Fields
- HOMEWORK: Calculated Values & Fields
- PivotCharts
- Intro to PivotCharts (2:26)
- DEMO: Column Chart (4:13)
- DEMO: Pie & Donut Charts (5:46)
- DEMO: Clustered Bar Chart (3:25)
- PRO TIP: Prevent Charts from Resizing with Cells (4:45)
- Changing Chart Types on the Fly (3:18)
- DEMO: Stacked Area Chart (5:46)
- PivotChart Layouts & Styles (4:03)
- Moving PivotCharts to New Sheets (2:09)
- Applying Slicers & Timelines to Multiple Charts (5:01)
- DEMO: Building a Dynamic Dashboard (13:22)
- QUIZ: PivotCharts
- HOMEWORK: PivotCharts
- PivotTable Case Studies
- Setting Expectations (1:45)
- U.S. Voters (2012) (10:33)
- HOMEWORK: U.S. Voters (2012)
- San Francisco Salaries (13:06)
- HOMEWORK: San Francisco Salaries
- Shark Attack Records (10:31)
- HOMEWORK: Shark Attack Records
- Stock Market Data (12:25)
- HOMEWORK: Stock Market Data
- Baseball Team Statistics (14:16)
- HOMEWORK: Baseball Team Statistics
- San Diego Burrito Ratings (16:40)
- HOMEWORK: San Diego Burrito Ratings
- Daily Weather Conditions (12:41)
- HOMEWORK: Daily Weather Conditions
- Spartan Race Facebook Posts (14:37)
- HOMEWORK: Spartan Race Facebook Posts
- Wrapping Up
- Resources & Next Steps (2:27)
Access
Lifetime
Content
3.5 hours
Lessons
57
Master Microsoft Excel Macros and Excel VBA
Use 6 Simple Projects to Become a Master Working with Excel Macros & VBA
By Kyle Pew | in Online Courses
During this course, you will build a foundation of working with Excel Macros and VBA. You'll engage in several projects that will build upon one another as you progress through the basic building blocks of automating tasks within Excel. Soon you'll be ready to enter the world of Excel VBA, where you will experience a more dynamic, robust experience for yourself and your peers.
- Access 57 lectures & 3.5 hours of content 24/7
- Automate placing & formatting a title on a worksheet
- Interact w/ the users of your Macros through input & message boxes
- Clean up multiple Excel worksheets & prepare the data to be reported on
- Create a report based on multiple sets of data found within multiple Excel worksheets
- Automate the creation of Excel Formulas to SUM data up in your report
Kyle Pew is a Microsoft Certified Trainer (MCT) and a certified Microsoft Office Master Instructor and has been teaching and consulting for the past 10+ years on various computer applications, including:
- Microsoft Office Suite 1997, 2000, XP, 2003, 2007, 2010, 2013
- Excel, Word, PowerPoint, Outlook, Access and Visio
- SharePoint End-User 2007, 2010, 2013
- VBA (Excel and Access)
- Adobe Suite
- Photoshop, Illustrator, InDesign
- Maya (Modeling and Animation)
- Unity3d (Game Design)
- HTML, CSS and JavaScript
- Crystal Reports
Kyle is a graduate of the San Francisco Art Institute in the Media Arts and Animation Program. He has worked as a Game Designer for Electronic Arts, designing on games such as Nerf N-Strike, Nerf N-Strike Elite, both for the Wii, and a Sims 3 Expansion Pack for the PC.
Kyle has facilitated courses that range from 1-on-1 interactions to large scale groups of 100+ participants, including; live in person classes, webinar style classes online and live online full courses. He is consistently ranked top in reviews for each of the courses he teaches.
Details & Requirements
- Length of time users can access this course: lifetime
- Access options: web streaming, mobile streaming
- Certification of completion not included
- Redemption deadline: redeem your code within 30 days of purchase
- Experience level required: all levels
- Microsoft Excel 2010, 2013, or 2016
Compatibility
- Internet required
Course Outline
- Course Introduction and Overview
- Welcome to the Course (4:08)
- Something to THINK About and SHARE (2:01)
- Understanding the Why and How Behind Excel Macros (3:18)
- Project #1: Using Excel's Macro Recorder Tool
- Introduction to Project #1 - Inserting and Formatting Text (1:39)
- Project #1 - Start Recording! (7:14)
- Project #1 - Running a Macro with a Button (2:12)
- Project #1 - Opps! I Need to Edit My Macro (4:04)
- Practical Uses of Excel Macros (2:17)
- Project #1 - Quiz - Test Your Knowledge on Using the Macro Recorder
- Project #1 - Exercise - Try it Yourself and Practice
- Excel VBA Concepts
- Excel VBA Concepts (0:30)
- The Visual Basic Editor (2:06)
- Excel VBA Modules (1:14)
- Creating Excel VBA Procedures (2:05)
- Understanding Excel VBA Variables (5:16)
- Working Excel VBA Loops to repeat blocks of code (4:11)
- Building Logic with an IF Statement (2:39)
- VBA Concepts - Quiz - Test Your Knowledge on VBA Programming Concepts
- Project #2: Moving Beyond the Basics and Into VBA
- Introduction to Project #2 - Interacting with the User (2:42)
- Project #2 - Breaking Down the VBA Code (3:05)
- Project #2 - Prompting the User for Information (5:35)
- Project #2: Building Logic in Your Macros (4:57)
- Project #2 - Alerting the User of Errors (7:05)
- Other Practical Uses of Message Boxes and Input Boxes (3:18)
- Project #2 - Quiz - Test Your Knowledge on Editing Your Macros
- Project #2 - Exercise - Try it Yourself and Practice
- Project #3: Preparing and cleaning up Data with a little VBA
- Introduction to Project #3 - Cleaning Up and Formatting Multiple Worksheets (2:59)
- Project #3 - Using the Macro Recorder to Lay the Foundation (1:53)
- Project #3: Preparing to use the Macro Recorder (2:28)
- Project #3 - Inserting the Headers (2:40)
- Project #3 - Formatting the Headers (5:07)
- Project #3 - Putting it all Together with alittle VBA Magic (9:54)
- Project #3 - Quiz - Test Your Knowledge on VBA
- Project #3 - Exercise - Try it Yourself and Practice
- Project #4: Using VBA to automate Excel Formulas
- Introduction to Project #4 - Automate Microsoft Excel Formulas with VBA (2:23)
- Project #4: Automate the SUM Function through Code (9:30)
- Project #4: Loop the SUM Function over Multiple Worksheets (5:57)
- Project #4 - Quiz - Test Your Knowledge Automating a Function with VBA
- Project #4 - Exercise - Try it Yourself and Practice
- Project #5: Bringing it all togther and create a weekly report
- Introduction to Project #5 - Creating a Final Report (2:56)
- Project 5: Putting it all together to create a Final Report (9:57)
- Project #5 - Quiz - Test Your Knowledge of Creating the Final Report
- Project #5 - Exercise - Try it Yourself and Practice
- Bonus Project #6: Working with VBA Forms
- Introduction to Bonus Project #6 - Working with Excel VBA Forms (3:14)
- Bonus Project #6: Creating an Excel VBA Form (2:48)
- Bonus Project #6: Adding Controls to the Form (2:42)
- Bonus Project #6: Changing Control and Form Properties (4:56)
- Bonus Project #6: Adding VBA Code to the Initialize Event of the Form (7:26)
- Project 6: Adding VBA Code to the ComboBox Change Event (4:20)
- Project #6: Adding VBA Code to the Add Worksheet Button Click Event (6:15)
- Project #6: Adding VBA Code to the Create Report Button Click Event (2:03)
- Bonus Project #6: Showing the Form (3:27)
- Bonus Project #6 - Quiz - Test Your Knowledge on Creating Excel VBA Forms
- Bonus Project #6 - Exercise - Try it Yourself and Practice
- Course Wrap Up
- Congratulations! You're an Excel Macro/VBA Master (1:02)
- BONUS! - Special Offer! Any of my courses for only $10
Access
Lifetime
Content
6.5 hours
Lessons
80
Microsoft Excel from Beginner to Advanced
The Complete "How-To" Excel Crash Course
By Warrick Kilmaytys | in Online Courses
More than 750 million people have Excel installed on their computer but most do not know how to use it. Whatever version of Excel you have, this course will show you how to build and prepare a workbook good enough for a Fortune 500 CEO, to a household budget, and everything in between.
- Access 80 lectures & 6.5 hours of content 24/7
- Get downloadable Excel workbooks which you can use to follow along
- Learn how to install Microsoft Excel & work w/ formulas and functions
- Calculate w/ the IF Function & apply conditional formatting
- Use the PMT Function to explore monthly & yearly compounding
- Use Google Drive to collect data
Warrick Klimaytys has spent many years with all kinds of computers in many different situations around the world and is eager to share his knowledge and skills with the StackCommerce community.
Around a decade ago Warrick started his professional life in the military where experienced all kinds of computers and machines. Today, he could be referred to as just a Microsoft "Windows Warrior". As a result of his past experience, he finds it best to deliver lessons in bite sized chunks.
Around a decade ago Warrick started his professional life in the military where experienced all kinds of computers and machines. Today, he could be referred to as just a Microsoft "Windows Warrior". As a result of his past experience, he finds it best to deliver lessons in bite sized chunks.
Details & Requirements
- Length of time users can access this course: lifetime
- Access options: web streaming, mobile streaming
- Certification of completion not included
- Redemption deadline: redeem your code within 30 days of purchase
- Experience level required: all levels
- Microsoft Excel required
Compatibility
- Internet required
Course Outline
- First Section
- Introduction (6:14)
- How to install Microsoft Excel 2016 (5:47)
- ColumnsRows Formula bar (8:43)
- New Spreadsheets and Autofill (4:32)
- Main Workbook Material
- Formulas and Funtions (7:57)
- Formulas Part 1 (4:31)
- Formulas and Funtions Part 2 (4:39)
- "Goals" Exercise (2:30)
- Finding the Totals (7:40)
- Formatting into a Table Part 1 (3:02)
- Formatting into a table Part 2 (7:29)
- Inserting the First Chart (6:58)
- Inserting the Second Chart (5:17)
- Inserting the Third Chart (3:13)
- Formatting the Title (3:55)
- Printing the First Spreadsheet (8:54)
- Printing the Second Spreadsheet (5:42)
- Printing the Third Spreadsheet (6:24)
- Different types of references
- Workbook for exercise
- Mixed, Relative and Absolute References (7:19)
- The Mixed Reference (6:03)
- Keeping score with the 'IF' and 'Vlookup' Function
- Workbook for exercise
- Calculating with the IF Function (8:41)
- Applying Conditional Formatting (3:28)
- Assigning grades with the Vlookup Function (8:47)
- Splitting names with the 'Find', 'Left', 'Right' and 'Len' Function
- Workbook for exercise
- Splitting names (1) (6:30)
- Splitting names (2) (5:21)
- Using the PMT Function to explore monthly and yearly compounding
- Workbook for exercise
- Pension/Savings payout with the PMT Function (8:18)
- How to use Google Drive to collect data
- Collecting data with Google Drive (3:01)
- Creating a survey form (6:25)
- Completing the form (5:26)
- Filling in a Google Drive survey (4:12)
- Data Analysis (4:12)
- Using PMT and IPMT Funtions to work out a mortgage
- Workbook for exercise
- PMT for a Mortgage (5:07)
- Mortgages Part 2 (5:15)
- Mortgages Part 3 (2:01)
- Mortgages Part 4 (6:34)
- Mortgages Complete! (5:06)
- Introduction to Charts in Excel
- Workbook for exercise (PIE)
- Introduction to Charts in Excel (6:01)
- Advanced Pie Chart Formatting (1) (4:10)
- Advanced Pie Chart Formatting (2) (5:18)
- Introduction to Column Charts (6:22)
- Workbook for exercise (COLUMN)
- Advanced Column Chart Formatting (1) (6:49)
- Advanced Column Chart Formatting (2) (7:24)
- Workbook for exercise (LINE)
- Introduction to Line Charts (4:28)
- Advanced Line Chart Formatting (1) (5:17)
- Advanced Line Chart Formatting (2) (8:17)
- Advanced Line Chart Formatting (3)
- Workbook for exercise (Scatter/Combo)
- Introduction to Scatter and Combo Charts (1:19)
- Advanced Scatter and Combo Chart Formatting (1) (4:05)
- Advanced Scatter and Combo Chart Formatting (2) (5:45)
- How to insert a Combo Chart (3:29)
- Using the 'What if Analysis' function to calculate changes in data
- Workbook for exercise
- "What if" Analysis (5:32)
- Workbook for exercise
- "What if" Analysis with a Data Table (4:02)
- Deleting duplicate data, Concatenating and more!
- Workbook for exercise
- Locating and Deleting Duplicate Data (2:42)
- Deleting Duplicates (2:17)
- Creating Email Addresses with Formulas (4:36)
- Creating Email Addresses with Concatenate (4:33)
- Deleting Duplicates Inactive/Active (4:12)
- Working out true cost. PMT & IPMT for Finance
- Workbook for exercise
- Calculating payment with PMT and IPMT (4:38)
- Exploring cost (2:26)
- Calculating total costs (5:24)
- Filling in the table with Autofill (5:48)
- Completing the table (6:36)
- Inserting a chart for quick reference (6:17)
- Managing Large Amounts of Data
- Workbook for exercise
- Freezing the Panes (3:58)
- Deleting Blank Rows and sorting Data (6:02)
- Filtering and Analysing (4:58)
- Sub-totalling data
- Workbook for exercise
- Sub-totalling data (1) (3:11)
- Sub-totalling data (2) (3:53)
- Sub-totalling data (3) (5:13)
- Welcome to Pivot Charts and Tables in Excel
- Workbook for exercise
- Welcome to Pivot Charts and tables. The Pie Chart (4:23)
- Inserting the first pivot table (8:02)
- Inserting the second pivot table (4:53)
- Inserting the third pivot table (6:34)
- Creating the last pivot table (5:12)
- Building a Dashboard (3:31)
- Inserting a Slicer (5:37)
Terms
- Instant digital redemption
