CONTENTS
www.fmworldcup.com
OF
Follow Financial Modeling World Cup on LinkedIn
TABLE

1. Functions and Formulas
2. 5 Excel Features You Should Know
3. Excel Shortcuts
4. 12 Excel Mistakes to Avoid
5. 10 Common Excel Errors
6. 10 Reasons Why Excel is the Best Spreadsheets Tools
7. Excel Alphabet
8. Mastering Large Data Sets
9. Getting Started with Python in Excel
10. How ChatGPT Can Simplify Excel Workflow
11. History of Microsoft Excel

1. FUNCTION & FORMULAS

www.fmworldcup.com
FUNCTIONS 10EXCEL YOU SHOULD KNOW
2.
SUMIFS function adds all of its arguments that meet
multiple criteria. For example, you would use
SUMIFS in your financial model to sum up the sales
of (1) a specific employee (2) for a specific product.
=SUMIFS() 3.
Use IFERROR function to format your financial
models. The function checks for errors and
returns the value specified by the user if
found. The function checks for the following
errors: #N/A, #VALUE!, #REF!, #DIV/0!,

NUM!, #NAME? or #NULL!.

=IFERROR()
Internal rate of return metric is needed to find
out the annual growth rate of an investment.
The higher the IRR, the better the investment
(keeping all other factors the same, of course).
IRR is good for comparing different investment
opportunities.
=XIRR()
Know your IFs, COUNTIFs, AVERAGEIFs and all other IFs too –
after all, financial modeling is just a series of IFs that could
happen in this world.
1.

4. =XNPV() 5. =PMT() 6. =SLOPE() 7. =XLOOKUP
   If you’re into investment banking,
   at some point you’ll have to
   calculate the Beta of a stock, which
   means volatility. By using the
   SLOPE function in Excel, you’ll find
   it easily by using the returns of the
   stock and the comparative bench-
   mark index.
   Lookup functions are a must to
   know for any modeler. They are
   used to quickly and easily find data
   in a table, for example, to find the
   amount sold by an employee, ID
   number, and thousands of other
   things.
   PMT function calculates the payment
   for a loan based on constant
   payments and a constant interest
   rate. You have to know the present
   loan value, number of periods and
   the interest rate. PMT, PPMT and
   IPMT functions are needed to figure
   out annuity loan repayments (e.g.
   mortgage)
   Unlike IRR and NPV, XIRR and XNPV functions
   allow for payments at irregular intervals
   Finance is money and we all
   know that money today is
   worth more than tomorrow.
   Financial analysts oftentimes
   have to calculate the value of
   an investment/company/
   project in today’s terms.
5. =INDEX() & MATCH() 9. =EOMONTH() 10. =SEQUENCE
   Sometimes, XLOOKUP won’t do the job, as it
   can only compare one array with another one.
   Index and Match function combination can look
   up values in the whole table – it’s 2 Dimensional.
   EOMONTH function finds the last day of the
   month after you add a specific number of
   months to a date. It’s useful for calculating
   maturity dates or due dates that fall on the
   last day of the month. It also aids in setting up
   your financial model.
   The SEQUENCE function allows you to
   generate a list of sequential numbers in
   an array. SEQUENCE function works
   great if you need to generate a list of
   10,000 numbers in a column.

100 FUNCTIONS
EXCEL
ABS Returns the absolute value of a number.
Arcos Returns the arccosine of a number.
COS Returns the cosine of an angle given in radians.
EVEN Rounds a number up to the nearest even integer.
EXP Returns e raised to the power of a given number.
FACT Returns the factorial of a number.
FLOOR Rounds a number down to the nearest multiple of signifcance.
GAMMADIST Returns the gamma distribution.
GCD Returns the greatest common divisor of two or more integers.
HARMEAN Calculates the harmonic mean of a dataset.
INT Rounds a number down to the nearest integer.
LN Returns the natural logarithm of a number.
LOG Returns the logarithm of a number to a specifed base.
LOG10 Returns the base-10 logarithm of a number.
LET Defnes variables and calculates a result based on given expressions.
MOD Returns the remainder of a division operation.
ODD Rounds a number up to the nearest odd integer.
PI Returns the mathematical constant π.
POWER Raises a number to a specifed power.
RADIANS Converts degrees to radians.
RAND Returns a random number between 0 and 1.
SIGN Returns the sign of a number.
SIN Returns the sine of an angle given in radians.
SQRT Returns the square root of a number.
TAN Returns the tangent of an angle given in radians.
TRUNC Truncates a number to a specifed number of digits.
AGGREGATE Performs various aggregation functions on a range of data.
AVERAGE Calculates the average of a range of numbers.
CHIDIST Returns the one-tailed probability of the chi-squared distribution.
COUNT Counts the number of cells containing numbers in a range.
COUNTBLANK Counts the number of blank cells within a range.
COUNTIFS Counts the number of cells that meet multiple criteria.
DAVERAGE Returns the average of selected database entries.
FREQUENCY Calculates the frequency distribution of data.
FDIST Returns the one-tailed F probability distribution.
LINEST Calculates statistics for a line by using the least squares method.
MAX Returns the largest value in a range of cells.
MEDIAN Calculates the median of a dataset.
MIN Returns the smallest value in a range of cells.
MDETERM Returns the matrix determinant of an array.
NPV Returns the net present value of an investment based on a series of cash fows.
NPER Returns the number of periods for an investment based on periodic, constant payments and a constant
interest rate.
QUOTIENT Returns the integer portion of a division operation.
RANK Returns the rank of a number in a list of numbers.
SLOPE Calculates the slope of the linear regression line through a given set of data points.
STDEV Estimates standard deviation based on a sample.
VAR Calculates the variance of a dataset.
ZTEST Calculates the one-tailed probability-value of a Z-test.
ADDRESS Returns the cell address as text.
CONCAT Concatenates multiple text strings into one.
CONCATENATEX Concatenates the result of an expression evaluated for each row in a table.
LEFT Extracts a specifed number of characters from the left side of a text string.
LEN Returns the number of characters in a text string.
REPLACE Replaces part of a text string with another text string.
RIGHT Extracts a specifed number of characters from the right side of a text string.
SEARCH Finds one text string within another and returns the starting position.
SUBSTITUTE Replaces existing text with new text in a text string.
TEXT Formats a number as text using a specifed format.
UPPER Converts text to uppercase.
VALUE Converts a text string that represents a number to a number.
AND Returns TRUE if all its arguments are TRUE.
IF Returns one value if a condition is true and another value if false.
IFERROR Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula.
ISERR Checks if a value is an error other than #N/A.
ISFORMULA Checks if a cell contains a formula.
ISNONTEXT Checks if a value is not text.
ISNUMBER Checks if a value is a number.
NOT Reverses the logical value of a statement.
OR Returns TRUE if any argument is TRUE.
XOR Returns TRUE if one argument is TRUE and the other is FALSE.
DATE Returns the serial number of a date.
DAY Extracts the day portion of a date.
DAYS Calculates the number of days between two dates.
HOUR Extracts the hour portion of a time.
MINUTE Extracts the minute portion of a time.
MONTH Extracts the month portion of a date.
NOW Returns the current date and time.
SECOND Extracts the second portion of a time.
TIME Returns the serial number of a specifc time.
TODAY Returns the current date.
WEEKDAY Returns the day of the week as a number.
YEAR Extracts the year portion of a date.
YEARFRAC Returns the fraction of the year represented by the number of whole days between
two dates.
INDEX Returns the value of a cell in a specifed row and column.
MATCH Searches for a value in a range and returns its relative position.
OFFSET Returns a reference ofset from a starting cell.
VLOOKUP Searches for a value and returns a corresponding result from a specifed column.
HLOOKUP Searches for a value and returns a value in the same column from a specifed row.
XLOOKUP Searches for a value and returns a result from another range or array.
PMT Calculates the payment for a loan based on constant payments and a constant interest rate.
XNPV
NPV Returns the net present value of an investment based on a series of cash fows.
RATE Calculates the interest rate per period of an annuity.
IRR Calculates the internal rate of return for a series of cash fows that occur at regular intervals.
MATH & TRIGONOMETRY
Returns the net present value for cash fows at irregular intervals.
STATISTICAL
LOGICAL
TEXT
DATE AND TIME
LOOKUP AND REFERENCE
FINANCIAL
SEQUENCE Generates a sequence of numbers in an array.
UNIQUE Returns unique values from a range or array.
ARRAY
ERROR.TYPE Returns a number corresponding to the type of error value.
IFERROR Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula.
ERROR HANDLING
LAMBDA Defnes a custom function in Excel.
LET Defnes variables and calculates a result based on given expressions.
CUSTOM FUNCTIONS

EXCEL FUNCTIONS PYRAMID
SUM, AVERAGE, COUNT, IF, ROUND, MIN, MAX
VLOOKUP, HLOOKUP,
CONCATENATE, COUNTIF, SUMIF, EOMONTH
IFERROR, INDIRECT/ADDRESS,
SUMIFS, COUNTIFS, INDEX/MATCH,
XLOOKUP, SUMPRODUCT
OFFSET, TRANSPOSE,
SEQUENCE, UNIQUE, TEXTSPLIT,
XIRR, XNPV
ï Users demonstrate a
mastery of Excel that
extends beyond formulas
ï Users at this level have
a strong command over
data manipulation
and analysis.
ï Users can perform
data analysis with
greater precision ï Users at this and flexibility. level are
comfortable
working with
data from
different
angles.
ï Fundamental for any Excel user, forms the basis
for more complex calculations.
Expert
Power User
Advanced
Basics
Intermediate
1.LET,
2.LAMBDA,
3.Array Formulas,
4.FILTER, SORT,
5.Power Query, VBA
FM
WCFMWC
FMWC

www.fmworldcup.com
a) lookup_value: The value you want to find in the lookup_array.
b) lookup_array: The range of cells containing possible lookup values.
c) return_array: The range of cells containing the values to be returned.
d) [if_not_found]: Optional. Specifies the value to return if the
lookup_value is not found. If omitted, #N/A is returned.
e) [match_mode]: Optional. Specifies the type of match: 0 for an exact
match, -1 for less than, and 1 for greater than.
f) [search_mode]: Optional. Specifies the search mode: 1 for first to
last, 2 for last to first, and 3 for a binary search.
XLOOKUP GUIDE
The XLOOKUP function in Excel is a powerful and versatile tool for searching a
range, returning corresponding values, and handling various lookup scenarios.

• Arguments
• Examples
  Searches for lookup_value in the range A2:A10 and returns
  the corresponding value from B2:B10.

1. Basic XLOOKUP

• XLOOKUP Function Syntax

XLOOKUP GUIDE
Returns “Not Found” if the lookup_value is not found in the
specified range.

2. Handling not found
   Performs an exact match lookup. Use 1 for greater than
   or -1 for less than.
3. Approximate Match

• Examples
  Searches for lookup_value in the range B2:B10 and returns
  the corresponding value from A2:A10.

2. Reverse Lookup
   Uses IFERROR to handle errors and return a custom message
   for not found values.
3. Handling Errors

• Examples
• Use wildcard characters like * and ? in lookup_value
  for partial matches.

1. Wildcard Characters:

• Tips
• XLOOKUP supports array mode, allowing you to
  handle multiple lookup values at once.

1. Array Mode:

• XLOOKUP automatically spills results into adjacent
  cells for dynamic array functionality.

1. Dynamic Arrays:

• Use IFERROR or other error-handling functions to
  manage situations where values are not found.

1. Error Handling:

VLOOKUP VS XLOOKUP
www.fmworldcup.com
FMWC
Supports only approximate match (TRUE)
or exact match (FALSE).
Requires specifying the column index number
where the return value is located.
Automatically returns values based on their
position in the lookup and return arrays, eliminating
the need for a separate column index number.
Supports four search modes: exact match, exact
match with wildcard characters, approximate match,
and the ability to return the last matching item.
Known to be slower, especially with large
datasets, due to its limitations and the need
for sorted data.
Suitable for simple vertical lookups where data
is sorted and the lookup value is in the leftmost
column.
Ideal for more complex lookup scenarios, unsorted
data, and when flexibility and efficiency are
paramount.
Generally faster and more efficient, especially for
unsorted data and complex lookup scenarios.
May return errors if the lookup value is not found
or if the table is not sorted (for approximate
matches).
Allows for customizable error handling with the
[if_not_found] parameter, providing more control over
error messages.
Available in older versions of Excel and widely
used in legacy spreadsheets.
Available in Excel 365 and Excel 2021, so compatibility
may be limited to newer versions unless using the
XLOOKUP function backport.
Definition
Syntax
Functionality
Lookup
Mode
Column
Indexing
Handling
Errors
Compatibility
Performance
Use Cases
Stands for Vertical Lookup. It’s a function used
to search for a value in the first column of a
range and return a value in the same row from
another column.
A newer function introduced in Excel that stands for
Extended Lookup. It’s designed to overcome some
limitations of VLOOKUP and offers more flexibility
and capabilities.
VLOOKUP(lookup_value, table_array,
col_index_num, [range_lookup])
XLOOKUP(lookup_value, lookup_array, return_array,
[if_not_found], [match_mode], [search_mode])
Searches for a value in the leftmost column of
a table and returns a value in the same row
from a column you specify.
More versatile and can perform lookups in any direction
(horizontal or vertical). It also allows for approximate
and exact matches without sorting data.
This formula searches for the value
“1003” in column B, returns the corre-
sponding value from column C (the
second column of the range B2:C5), and
uses an exact match (FALSE).
This formula searches for the value
“1003” in the range B2:B5 (lookup_array),
and returns the corresponding value from
the range C2:C5 (return_array). XLOOKUP
automatically performs an exact match.
Example
Retrieve the name of the employee with ID “1003”

XLOOKUP VS FILTER
www.fmworldcup.com
=XLOOKUP(“Apple”, A2:A10,
B2:B10, “Not Found”)
This searches for “Apple” in the range A2 and
returns the corresponding value from B2.
If “Apple” is not found, it returns “Not Found”.
=FILTER(A2:C10, B2:B10=”Yes”,
“No Data”)
This filters the range A2 and returns rows where the
value in column B is “Yes”.
If no rows meet the criteria, it returns “No Data”.
Ideal for situations where you
need to find a specific piece of
data in a table, such as looking up
a price of a product or an
employee’s department based on
their ID.
Perfect for extracting subsets of
data that meet certain conditions,
like filtering sales data to show
only transactions from a particular
region or date range.
Purpose
Syntax
Key
Features
Use Cases
XLOOKUP is used to search a
range or an array and return an
item corresponding to the first
match it finds. It can replace
VLOOKUP, HLOOKUP, and
INDEX/MATCH functions.
FILTER is used to return a filtered
version of the data array, where the
criteria match. It helps in
extracting data that meets
specified conditions.
XLOOKUP(lookup_value,
lookup_array, return_array,
[if_not_found],
[match_mode],
[search_mode])
FILTER(array, include,
[if_empty])
ï Flexible Lookup Direction: Can search from
top-to-bottom or bottom-to-top.
ï Exact Match by Default: Searches for an exact
match unless specified otherwise.
ï Error Handling: Allows specifying a custom
message if the lookup value is not found.
ï Multiple Lookup Values: Can search for
multiple values at once.
ï Dynamic Arrays: Returns dynamic arrays that
automatically spill over to adjacent cells.
ï Multiple Conditions: Supports filtering based on
multiple criteria.
ï Error Handling: Allows specifying a custom
message if no data meets the criteria.
ï Versatile: Can be used to filter data both
horizontally and vertically.
Example

XMATCH
www.fmworldcup.com

1. Function Name
   lookup_value
   CHEATSHEET
   XMATCH
2. Purpose The XMATCH function searches for a specified value in a range or array
   and returns the relative position of the item found.
3. Function Name
4. Arguments
   The value you want to search for.
   lookup_array
   The range or array where the lookup value will be searched.
   match_mode
   Specifies the type of match. Default is 0 (exact match).

• 0 or omitted: Exact match.
• -1: Exact match or next smallest item.
• 1: Exact match or next largest item.
  search_mode
  Specifies the direction of the search. Default is 1 (search from top
  to bottom or left to right).
• 1 or omitted: Search from top to bottom or left to right.
• -1: Search from bottom to top or right to left.

1. Return Value The position of the matched item relative to the lookup array.
   Returns #N/A if no match is found.
2. Example Searches for the value 42 in cells A1:A10 using an exact match (match_-
   mode = 0) and searches from top to bottom (search_mode = 1).
   The value returned is 5 indicating the row in which the number is located.
3. Notes • XMATCH is a newer version of the MATCH function in Excel, introduced in Excel 2019
   and Excel for Microsoft 365.

• It offers more flexibility than the MATCH function by allowing for approximate matching
  and specifying the search direction.
• XMATCH can handle both vertical and horizontal lookup arrays.
  Tips • Use XMATCH for advanced lookup scenarios where you need more control over the
  matching criteria and search direction.
• Experiment with different match modes and search modes to tailor the function to your
  specific requirements.
• Combine XMATCH with other functions like INDEX and IFERROR for more powerful
  lookup formulas.
  8.