Excel Formula Blueprint

Name: Excel Formula Blueprint
Rating: 4.6 (45 reviews)

40+ Excel Formulas From Beginner To Advanced - Learn With Short & Step-By-Step Lectures And Over 15 Spreadsheets

Created byDragos Stefanescu, Richard Korbut

Last updated 5/2016

English

English [Auto],

What you'll learn

Master Every Excel-Formula Related Aspect Even If They're A Complete Beginner At Excel
Learn By Focusing On The End-Result Of Formulas And Understand That These Lectures Represent The 20% Of Excel They'll Use 80% Of The Time
Learn By Doing & Following Along - The Spreadsheets That Are Shown In Each Lecture Are Available For Download At The Beginning Of Each Section
Test Your Excel Knowledge - Specific Class Test Scenarios Are Present At The End Of Each Section Along With The Solutions (Both In Video And XLS Formats)
Master The Formula Syntax And Understand Basic Formulas
Learn How To Copy & Paste Formulas, Use Auto-Fill Or How To Apply Absolute & Relative Referencing
Discover Basic Formatting Principles And Use Simple Formulas Such As SUM, AVERAGE, COUNT, MIN, MAX And Their Variations
Make Use Of IF Statements, Understand The AND, OR & NOT Operators And Combine Them Into SUMIF, COUNTIF Or AVERAGEIF
Tidily Write Formulas And Apply This Method When Writing Nested IFs
Manipulate Excel Text Strings - Ensure Proper Formatting, Stick Them Together, Extract Specific Text Strings Or Ensure Text Accuracy
Master VLookups & HLookups Whilst Being Aware Of The Most Common Mistakes That You Can Make Whilst Using Them
Approach Nested Lookups, Multi-Conditional Lookups And The MATCH Function
Take Advantage Of Excel Advanced Features Such As Narrowing Down Variables By 2 Positions Using Formula Combos
Check Against 2 Criteria For Unique Values

Course content

7 sections • 45 lectures • 2h 31m total length

Welcome To This Course!6:50
Master excel formulas through a hands-on course from basics to advanced topics like 40 formulas, lookups, and text manipulation. Practice on spreadsheets with class tests and solutions to reinforce learning.
Quick Win: The Excel Shortcuts You Need To Know1:20
Master essential Excel keyboard shortcuts to speed your workflow. Print the 12 essential shortcuts slide deck and the one-page cheat sheet before you start to navigate the course more efficiently.

Introduction (Spreadsheet Attached)1:42
A quick lecture to show what we'll be covering in this fundamental first content-based section.
Beginner's Guide: Writing Simple Formulas In Excel2:41
A quick guide to writing the simplest formulas in Excel.
Master These 2 Essential Excel Functionalities4:02
In this lecture you will learn how to use Auto-Fill and Copy-Paste Special. You will use these functionalities consistently with Excel, so it's imperative that you master them from the get-go.
Ultimate Guide To Absolute & Relative Referencing4:36
This key concept will help you throughout your entire formula writing in Excel. By learning this, you will be able to write formulas that are more flexible and accurate.
How To Make Sense Of Unfamiliar Formulas2:15
In this lecture you will understand the importance of the helping cues Excel offers you when writing formulas. This will help you approach unfamiliar instances and know better how the syntax should be formulated.
ACTION: Section 2 Class Test (Spreadsheet Attached)1:04
A quick introduction to the class test for this module & its requirements. The spreadsheet is attached as a resource to this lecture.
XLS: Section 2 Solution (Spreadsheet Attached)2:21
The video solution to the class test for this section. The final spreadsheet is attached as a resource.

Introduction (Spreadsheet Attached)1:00
Master section three fundamental Excel formulas, starting with basic formatting principles for clear tables, then explore count and average formulas, including counta and count blank, min and max.
Fundamental Excel Formatting Principles You Need To Know4:22
Learn to format tables in Excel with borders and header emphasis. Quickly adjust decimals and apply thousand separators, currency, and negative-number formatting for clarity.
How To Count & Perform Averages In Excel2:31
Count counts how many cells in a range contain numbers, while average returns the arithmetic mean of those numbers; both are fundamental Excel formulas for data checks.
Why You Should Use These 2 Variations Of Count2:06
Explore how to use counta and count blank to count non empty and empty cells in a range, assess numbers vs blanks, and apply these variations to real data analysis.
Understanding Minimum & Maximum Values2:12
Explore max and min formulas in Excel to identify the highest price, highest cost, and lowest weight within a range by selecting the relevant columns.
ACTION: Section 3 Class Test (Spreadsheet Attached)1:46
Apply seven Excel formula variations to a football match matrix, completing cells for played and unplayed games while contrasting count and counta to handle numbers and dashes.
XLS: Section 3 Solution (Spreadsheet Attached)2:52
Master essential Excel Formula Blueprint techniques for sports data, using count, count blank, sum, average, max, and min to compute games played, games left, and total games.

Introduction (Spreadsheet Attached)1:27
Master the IF function, including nested ifs and logical operators, to build complex spreadsheets, and apply countif and average with practical scenarios.
The IF Function Blueprint4:37
Master the IF function in Excel by building a logical test to flag heavy items over 50 kilos, copy across with absolute/relative references, and handle text comparisons like Andy.
Use These 3 Operators To Add Variety To Your IF Clauses8:14
Hack: Write & Understand Formulas Better By Using This Nifty Trick2:16
Master a quick trick to tidy excel formulas by using spaces within the parentheses and alt-enter line breaks, enabling clearer nested ifs and safer, more readable logic.
Ultimate Guide To Using Nested IFs6:05
Learn how to implement nested ifs in Excel to route items into warehouses by weight ranges, using multiple conditional branches within a single if statement.
Formula Combo: The 3 Formulas That Use IF Clauses3:55
ACTION: Section 4 Class Test (Spreadsheet attached)1:15
XLS: Section 4 Solution (Spreadsheet attached)2:50
Explore mapping class Hage to OK and dash otherwise with a nested if. Learn to handle zero to two children, Pacific origin, and male checks, then average children for bachelors.

Introduction (Spreadsheet Attached)1:20
Ensure Proper Text Formatting3:00
Learn to format text in Excel using lower, upper, and proper formulas to standardize names. Use a helper column to apply and copy formulas for consistent capitalization and export-ready lists.
The 2 Approaches To Joining Text Strings Together2:56
Master two methods to join text strings in Excel: the concatenate function and an operator, including inserting a space between first name and surname.
Use These 4 Formulas To Extract Any Piece Of Text9:42
Here's How To Ensure Text Accuracy3:56
Learn to ensure text accuracy in Excel by using len to check character counts and trim to remove extra spaces, preventing lookup mismatches caused by irregular names.
ACTION: Section 5 Class Test (Spreadsheet Attached)0:47
Complete section 5 class test by formatting full names with proper capitalization and splitting the product name and model into two columns using the given formulas. Check the video solution.
XLS: Section 5 Solution (Spreadsheet Attached)1:55
Combine two columns into a properly formatted third column using concatenation or a combined formula, then extract product name with left and dash, and model with right.

Introduction (Spreadsheet Attached)1:12
Explore look up formulas in Excel, including vlookup and the match function, while identifying common mistakes and mastering nested lookups, with a class test and solution.
The 2 Formulas To Search Horizontally OR Vertically In A Spreadsheet6:19
Learn to use look up formulas to search vertically or horizontally in a spreadsheet, retrieving data from a source table via a unique identifier in leftmost column, with exact-match settings.
Avoid These Most Common Lookup Mistakes2:36
Identify and fix the four common lookup mistakes in Excel, such as placing the lookup value in the first column, wrong column index, missing absolute references, and neglecting exact matches.
Here's How To Approach Nested Lookups3:25
Learn how to perform nested lookups in Excel by correlating customer name with the price paid across two tables, using exact-match lookups and returning the unit price.
Formula Combo: Using VLookup & Choose5:09
Master multi-criteria lookups in Excel by combining VLOOKUP with CHOOSE, matching name and product, and using an array formula (Ctrl+Shift+Enter) to return the unit price.
The MATCH Function & Why I Use It All The Time3:52
ACTION: Section 6 Class Test (Spreadsheet Attached)1:08
Apply module formulas to populate a table with customer names, order numbers, and costs (birthday parlor, tax amount, freight) from source table, and explore combining two formulas for optimized lookups.
XLS: Section 6 Solution (Spreadsheet Attached)5:25
Master vlookup with a unique identifier in the first column, using match to select the correct column, then copy the formula across large tables for fast, accurate Excel lookups.

Introduction (Spreadsheet Attached)1:16
Match + Index: The Formula Combo That Changed The Way I Worked In Excel4:54
Master the match and index formula combo to fill tables by determining row and column coordinates from a lookup array, using exact matches to extract customer names and order data.
Using Indirect + Address For Accurate Data3:59
Explore how indirect and address dynamically pull values from different sheets. Compare with match plus index and learn when static references are preferable for accurate data retrieval.
The Ultimate Guide To 2 Way Lookups5:08
Master two way lookups in Excel by combining index and match with multiple conditions, using array formulas to return the correct freight value.
A Guide To The SumProduct Formula4:04
Master the sumproduct formula to multiply corresponding arrays, add results, and customize operators; apply conditions to perform conditional sums, such as filtering by Australia.
Dealing With Errors In Excel4:55
Learn to handle Excel errors in large updating databases by using match and index with iferror and is blank, displaying clean blanks or dashes and tracing root causes.

Requirements

Microsoft Excel - Preferably 2010 Or Newer (Although 07 Works As Well)
For Mac Users - Office 2013 or 2016 Is Preferable (Older Versions Will Be Significantly Different)
No Prior Excel Knowledge Needed!
If You Consider Yourself An Experienced Or Intermediate User, Watch The Intro Lecture To Understand Which Section You Should Start With

Description

13,000+ Students have enrolled in my 13 Courses!
The 4.9 Rating from student reviews shows the value packed in this course!
Practicality: This course has 15 Downloadable Spreadsheets spread across including Section Scenarios, Class Test Scenarios & Solutions

What if you mastered Excel formulas to the point where you could create Investment Banking quality spreadsheets that are efficient, automated and easy to
maintain?

Here's the blunt truth: I can't give you a magic pill to learn Excel over night. What
I can do is show you the fastest way to master formulas if you are willing to
take action.

Instead of cramming useless Excel theory during 8 boring and confusing hours, why not apply the formulas that you will use on a daily basis whilst also having the
spreadsheets in front of you?

This course will allow you to hack over 40 Excel Formulas including:

The Formula Bootcamp: Excel beginners will learn the fundamental formulas and IF clauses to have a firm grip over Excel
Excel Text Manipulator: Know exactly how to manipulate any Excel text string
Excel Pro Formulas: Master the more complex excel functionalities which will completely transform your efficiency in Excel

As a bonus you will also receive class tests at the end of every section (including
the solutions in both text and spreadsheet format), a PDF with the most widely
used Excel keyboard shortcuts and an entire section comprising complementary formulas.

I guarantee this course will be the easiest way for you to master Excel formulas.
If you have taken action and not seen results, I will personally pay to enroll
you in another Excel course.

The path has been laid out for you to become an Excel Formula Ninja. Take this course now and join me on this exciting journey.

Who this course is for:

Financial Analysts Who Want To Improve Their Excel Skills By Using The Appropriate Tools
Entrepreneurs Who Need To Manage Both Basic & Advanced Spreadsheets And Save Time Whilst Doing So
Anybody Who Wants To Become Proficient With Excel Formulas Fast

Excel Formula Blueprint

What you'll learn

Explore related topics

Course content

Introduction To This Course2 lectures • 8min

Introduction To Formulas - Syntax Basics7 lectures • 19min

Beginners' Guide to Excel Formulas7 lectures • 17min

IF Functions & Variations8 lectures • 31min

Excel Text String Manipulator7 lectures • 24min

The Ultimate Guide To LOOKUP Formulas8 lectures • 29min

Advanced Excel Formulas6 lectures • 24min

Requirements

Description

Who this course is for: