Mastering Excel for Finance: Your Ultimate Cheat Sheet from Basics to Pro: Professional Level Excel Finance Cheatsheet

Formulas & Questions

Introduction

Welcome to your ultimate Excel Finance Cheat Sheet! In the world of finance, Excel is an indispensable tool that empowers professionals to analyze data, create forecasts, and make informed decisions efficiently. This blog post is designed to guide you through four levels of Excel formulas: Basic, Intermediate, Expert, and Professional (Stealth Mode). Each section includes essential formulas, practical examples, and challenging questions to help reinforce your understanding.

Whether you are just starting or looking to refine your advanced skills, this cheat sheet will provide you with a structured approach to mastering Excel in the finance domain. Let’s dive in and enhance your Excel proficiency!

What to Expect

In this blog cheat sheet episodes, you’ll find:

• 20 Cheat Sheets for Each Level: A carefully selected collection of key Excel formulas that cater to your current skill level.
• 10 Questions per Level: Thought-provoking questions that test your knowledge and application of the formulas, complete with answers and examples.
• Progressive Learning: The format allows you to build on your skills progressively, ensuring a comprehensive understanding of Excel’s capabilities.
• Real-World Examples: Practical applications of each formula, helping you connect theory with real-life financial tasks.Intermediate Level Excel Finance Cheatsheet

Formula’s : -

1. XLOOKUP with Multiple Criteria:

• Formula: =XLOOKUP(1, (criteria1_range=criteria1)*(criteria2_range=criteria2), return_array, "Not Found")
• Example: =XLOOKUP(1, (A1:A10="Product A")*(B1:B10="Region 1"), C1:C10, "Not Found") - Finds the value in C1where both criteria are met.

2. SUMIFS:

• Formula: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
• Example: =SUMIFS(D1:D10, A1:A10, "Product A", B1:B10, ">100") - Sums values in D1where criteria in A1is "Product A" and values in B1are greater than 100.

3. INDEX MATCH with Multiple Criteria:

• Formula: =INDEX(return_range, MATCH(1, (criteria1_range=criteria1)*(criteria2_range=criteria2), 0))
• Example: =INDEX(C1:C10, MATCH(1, (A1:A10="Product A")*(B1:B10="Region 1"), 0)) - Similar to XLOOKUP, but uses INDEX and MATCH.

4. SUMPRODUCT:

• Formula: =SUMPRODUCT(array1, [array2], [array3], ...)
• Example: =SUMPRODUCT(A1:A10, B1:B10) - Multiplies corresponding components in the given arrays and returns the sum.

5. DBSLOPE:

• Formula: =DBSLOPE(database, field, criteria)
• Example: =DBSLOPE(A1:C10, "Sales", E1:E2) - Returns the slope of the linear regression line for the specified field, filtered by the criteria.

6. DCOUNT:

• Formula: =DCOUNT(database, field, criteria)
• Example: =DCOUNT(A1:C10, "Sales", E1:E2) - Counts the number of cells in a field that contain numbers, filtered by the criteria.

7. NETWORKDAYS:

• Formula: =NETWORKDAYS(start_date, end_date, [holidays])
• Example: =NETWORKDAYS(A1, B1) - Calculates the number of workdays between two dates.

8. OFFSET:

• Formula: =OFFSET(reference, rows, cols, [height], [width])
• Example: =SUM(OFFSET(A1, 1, 0, 10, 1)) - Sums the next 10 rows in column A starting from A2.

9. INDIRECT:

• Formula: =INDIRECT(ref_text, [a1])
• Example: =SUM(INDIRECT("A1:A10")) - Returns the sum of the range specified as text.

10. AGGREGATE:

• Formula: =AGGREGATE(function_num, options, array, [k])
• Example: =AGGREGATE(1, 6, A1:A10) - Calculates the average of A1while ignoring errors.

11. FVSCHEDULE:

• Formula: =FVSCHEDULE(principal, schedule)
• Example: =FVSCHEDULE(1000, B1:B5) - Calculates the future value of an investment based on a series of compound interest rates.

12. RANDBETWEEN:

• Formula: =RANDBETWEEN(bottom, top)
• Example: =RANDBETWEEN(1, 100) - Returns a random integer between the specified numbers.

13. PERCENTILE.INC:

• Formula: =PERCENTILE.INC(array, k)
• Example: =PERCENTILE.INC(A1:A10, 0.9) - Returns the k-th percentile of values in a range.

14. PERCENTRANK.INC:

• Formula: =PERCENTRANK.INC(array, x, [significance])
• Example: =PERCENTRANK.INC(A1:A10, 50) - Returns the rank of a value in a data set as a percentage.

15. VAR.P:

• Formula: =VAR.P(number1, [number2], ...)
• Example: =VAR.P(A1:A10) - Calculates the variance based on the entire population.

16. VAR.S:

• Formula: =VAR.S(number1, [number2], ...)
• Example: =VAR.S(A1:A10) - Calculates the variance based on a sample.

17. STDEV.P:

• Formula: =STDEV.P(number1, [number2], ...)
• Example: =STDEV.P(A1:A10) - Calculates the standard deviation based on the entire population.

18. STDEV.S:

• Formula: =STDEV.S(number1, [number2], ...)
• Example: =STDEV.S(A1:A10) - Calculates the standard deviation based on a sample.

19. CUMIPMT:

• Formula: =CUMIPMT(rate, nper, pv, start_period, end_period, type)
• Example: =CUMIPMT(5%/12, 60, 10000, 1, 12, 0) - Calculates the cumulative interest paid on a loan.

20. CUMPRINC:

• Formula: =CUMPRINC(rate, nper, pv, start_period, end_period, type)
• Example: =CUMPRINC(5%/12, 60, 10000, 1, 12, 0) - Calculates the cumulative principal paid on a loan.

Questions and Answers

1. Question: How can you perform a lookup with multiple criteria using XLOOKUP?

• Answer: Use =XLOOKUP(1, (A1:A10="Product A")*(B1:B10="Region 1"), C1:C10, "Not Found").

2. Question: How do you sum values in a range based on multiple conditions?

• Answer: Use =SUMIFS(D1:D10, A1:A10, "Product A", B1:B10, ">100").

3. Question: How can you find the number of workdays between two dates?

• Answer: Use =NETWORKDAYS(A1, B1).

4. Question: What formula would you use to count cells in a field based on criteria?

• Answer: Use =DCOUNT(A1:C10, "Sales", E1:E2).

5. Question: How do you calculate the future value of an investment based on a series of rates?

• Answer: Use =FVSCHEDULE(1000, B1:B5).

6. Question: How can you use SUMPRODUCT to calculate weighted averages?

• Answer: Use =SUMPRODUCT(A1:A10, B1:B10)/SUM(B1:B10).

7. Question: How do you calculate the variance for a sample set of data?

• Answer: Use =VAR.S(A1:A10).

8. Question: What formula would you use to find the cumulative interest paid on a loan?

• Answer: Use =CUMIPMT(rate, nper, pv, start_period, end_period, type).

9. Question: How can you calculate the percentile rank of a value in a dataset?

• Answer: Use =PERCENTRANK.INC(A1:A10, 50).

10. Question: How can you ignore errors when calculating the average of a range?

• Answer: Use =AGGREGATE(1, 6, A1:A10).

I hope now you have better understanding starting with excel formula’s and what question you expect from an interview stand point.

Cheers! You have completed all the levels :)