Excel Advanced Formulas: Master Complex Calculations
You know the basics. Now let's learn advanced formulas that'll make you an Excel power user. These formulas can do complex calculations, look up data, and automate decision-making.
This course assumes you know basic Excel. If you don't, check out our Excel Basics course first.
IF Statements
IF statements make decisions in your formulas:
=IF(condition, value_if_true, value_if_false)
Example:
=IF(A1>50, "Pass", "Fail")
If A1 is greater than 50, it returns "Pass", otherwise "Fail".
Nested IF:
=IF(A1>=90, "A", IF(A1>=80, "B", IF(A1>=70, "C", "F")))
This assigns grades based on score. But there's a better way - use IFS (see below).
IFS - Multiple Conditions
IFS is cleaner than nested IF statements:
=IFS(A1>=90, "A", A1>=80, "B", A1>=70, "C", TRUE, "F")
Checks conditions in order. The first true condition wins. TRUE at the end is the default (like "else").
VLOOKUP - Look Up Values
VLOOKUP finds a value in a table and returns a corresponding value:
=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
Example: You have a table with employee names and salaries. Find someone's salary:
=VLOOKUP("John", A2:B10, 2, FALSE)
Looks for "John" in column A, returns the value from column 2 (B). FALSE means exact match.
Important: VLOOKUP searches the first column of your table. The lookup value must be in the leftmost column.
INDEX and MATCH - Better Than VLOOKUP
INDEX/MATCH is more flexible than VLOOKUP:
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
Example:
=INDEX(B2:B10, MATCH("John", A2:A10, 0))
MATCH finds "John" in A2:A10, returns its position. INDEX uses that position to get the value from B2:B10.
Why it's better: Works left-to-right or right-to-left. More flexible than VLOOKUP.
SUMIF and SUMIFS
SUMIF adds numbers that meet a condition:
=SUMIF(range, criteria, sum_range)
Example: Sum all sales where region is "North":
=SUMIF(A2:A10, "North", B2:B10)
SUMIFS works with multiple conditions:
=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2)
Example: Sum sales where region is "North" AND month is "January":
=SUMIFS(C2:C10, A2:A10, "North", B2:B10, "January")
COUNTIF and COUNTIFS
COUNTIF counts cells that meet a condition:
=COUNTIF(range, criteria)
Example: Count how many scores are above 80:
=COUNTIF(A2:A10, ">80")
COUNTIFS works with multiple conditions, just like SUMIFS.
CONCATENATE and CONCAT
Combine text from multiple cells:
=CONCATENATE(A1, " ", B1) or =A1 & " " & B1
Both combine A1 and B1 with a space between. The & operator is simpler.
CONCAT (newer) works similarly but is simpler:
=CONCAT(A1, " ", B1)
Text Functions
LEFT, RIGHT, MID: Extract parts of text
=LEFT(A1, 3) - First 3 characters
=RIGHT(A1, 3) - Last 3 characters
=MID(A1, 2, 3) - 3 characters starting at position 2
UPPER, LOWER, PROPER: Change text case
=UPPER(A1) - All uppercase
=LOWER(A1) - All lowercase
=PROPER(A1) - Title case
Date Functions
TODAY, NOW: Get current date/time
=TODAY() - Current date
=NOW() - Current date and time
DATEDIF: Calculate difference between dates
=DATEDIF(A1, B1, "d") - Days between dates
=DATEDIF(A1, B1, "m") - Months
=DATEDIF(A1, B1, "y") - Years
Combining Formulas
You can nest formulas inside each other:
=IF(SUM(A1:A10)>100, "High", "Low")
This sums A1:A10, then uses IF to return "High" if the sum is over 100, "Low" otherwise.
Another example:
=VLOOKUP(MAX(A1:A10), A1:B10, 2, FALSE)
Finds the maximum value in A1:A10, then looks it up in the table to return the corresponding value from column B.
Array Formulas (Modern Excel)
Modern Excel (365/2021) has dynamic arrays. Formulas automatically spill:
=SORT(A1:A10) - Sorts the range
=UNIQUE(A1:A10) - Returns unique values
=FILTER(A1:B10, B1:B10>50) - Filters rows where B is greater than 50
These are powerful and much easier than old array formulas.
Common Mistakes
- Missing dollar signs: Use $ to lock cell references when copying formulas (absolute vs relative)
- Wrong range: Make sure your ranges include all the data you need
- Exact match: VLOOKUP needs FALSE for exact matches, or it might return wrong results
- Parentheses: Complex formulas need careful parentheses - Excel will warn you if they're wrong
- Text vs numbers: Make sure data types match - "123" (text) is different from 123 (number)
Pro Tip: Use the Formula Builder (fx button) to help build complex formulas. It shows you what each argument does and helps prevent errors. Also, break complex formulas into smaller parts to test them.
Practice Examples
Example 1: Grade Calculator
Score in A1, grade in B1:
=IFS(A1>=90, "A", A1>=80, "B", A1>=70, "C", A1>=60, "D", TRUE, "F")
Example 2: Sales Commission
Sales in A1, commission in B1:
=IF(A1>10000, A1*0.1, A1*0.05)
If sales over $10,000, 10% commission, otherwise 5%
Example 3: Lookup Employee Info
Employee name in A1, find their department:
=VLOOKUP(A1, Employees!A2:C100, 3, FALSE)
Looks in another sheet called "Employees"
Common Questions
When should I use VLOOKUP vs INDEX/MATCH?
VLOOKUP is simpler and works fine for most cases. Use INDEX/MATCH when you need to look left (VLOOKUP only looks right), or when you need more flexibility. Many people prefer INDEX/MATCH once they learn it.
Why does my VLOOKUP return #N/A?
Usually means it can't find the value. Check: Is the lookup value spelled exactly right? Is it in the first column of your table? Did you use FALSE for exact match? Is there extra spaces in your data?
How do I learn more formulas?
Practice with real data. When you need to do something, Google "Excel how to [what you want]". Excel has hundreds of formulas - you don't need to memorize them all, just know they exist and how to find them.
Start Using Advanced Formulas
Open Excel and try these formulas with your own data. Start with IF statements and VLOOKUP - they're the most useful. Practice combining formulas. The more you use them, the more natural they become. Advanced formulas can automate a lot of work once you master them.