Mastering Excel for Finance: Your Ultimate Cheat Sheet from Basics to Pro: Intermediate 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. SUMIF:

• Formula: =SUMIF(range, criteria, [sum_range])
• Example: =SUMIF(A1:A10, ">500", B1:B10) - Sums the values in B1 where the corresponding value in A1 is greater than 500.

2. AVERAGEIF:

• Formula: =AVERAGEIF(range, criteria, [average_range])
• Example: =AVERAGEIF(A1:A10, "<100", B1:B10) - Averages the values in B1 where the corresponding value in A1 is less than 100.

3. COUNTIF:

• Formula: =COUNTIF(range, criteria)
• Example: =COUNTIF(C1:C10, "Yes") - Counts the number of cells in C1that equal "Yes".

4. INDEX:

• Formula: =INDEX(array, row_num, [column_num])
• Example: =INDEX(A1:B10, 3, 2) - Returns the value from the 3rd row and 2nd column of the range A1.

5. MATCH:

• Formula: =MATCH(lookup_value, lookup_array, [match_type])
• Example: =MATCH("Product A", A1:A10, 0) - Finds the position of "Product A" in the range A1.

6. SUMPRODUCT:

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

7. IFERROR:

• Formula: =IFERROR(value, value_if_error)
• Example: =IFERROR(A1/B1, "Error") - Returns "Error" if the division results in an error.

8. YEAR:

• Formula: =YEAR(serial_number)
• Example: =YEAR(D1) - Returns the year of the date in D1.

9. MONTH:

• Formula: =MONTH(serial_number)
• Example: =MONTH(D1) - Returns the month of the date in D1.

10. DAY:

• Formula: =DAY(serial_number)
• Example: =DAY(D1) - Returns the day of the date in D1.

11. DATEDIF:

• Formula: =DATEDIF(start_date, end_date, unit)
• Example: =DATEDIF(D1, E1, "Y") - Calculates the number of complete years between the dates in D1 and E1.

12. NETWORKDAYS:

• Formula: =NETWORKDAYS(start_date, end_date, [holidays])
• Example: =NETWORKDAYS(D1, E1) - Returns the number of working days between two dates.

13. RANDBETWEEN:

• Formula: =RANDBETWEEN(bottom, top)
• Example: =RANDBETWEEN(1, 100) - Generates a random integer between 1 and 100.

14. CONCAT:

• Formula: =CONCAT(text1, [text2], ...)
• Example: =CONCAT(A1, " ", B1) - Combines the text in A1 and B1 with a space in between.

15. TEXTJOIN:

• Formula: =TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)
• Example: =TEXTJOIN(", ", TRUE, A1:A10) - Joins the text in A1 to A10 with a comma and space, ignoring empty cells.

16. TRUNC:

• Formula: =TRUNC(number, [num_digits])
• Example: =TRUNC(A1, 2) - Truncates the number in A1 to 2 decimal places without rounding.

17. SUBTOTAL:

• Formula: =SUBTOTAL(function_num, ref1, [ref2], ...)
• Example: =SUBTOTAL(1, A1:A10) - Calculates the average of the range A1.

18. XIRR:

• Formula: =XIRR(values, dates, [guess])
• Example: =XIRR(A1:A10, B1:B10) - Calculates the internal rate of return for a series of cash flows.

19. XNPV:

• Formula: =XNPV(rate, values, dates)
• Example: =XNPV(10%, A1:A10, B1:B10) - Calculates the net present value for a series of cash flows.

20. FLOOR:

• Formula: =FLOOR(number, significance)
• Example: =FLOOR(A1, 0.05) - Rounds the number in A1 down to the nearest multiple of 0.05.

Questions and Answers

1. Question: How can you sum the sales greater than $500 in a range?

• Answer: Use =SUMIF(A1:A10, ">500", B1:B10) to sum sales in B1 where corresponding values in A1 are greater than 500.

2. Question: What formula would you use to calculate the average of values in column B where corresponding values in column A are less than 100?

• Answer: Use =AVERAGEIF(A1:A10, "<100", B1:B10).

3. Question: How do you count the number of “Yes” responses in a range?

• Answer: Use =COUNTIF(C1:C10, "Yes").

4. Question: If you have a list of products and you want to find the price of “Product B”, which formulas would you use?

• Answer: First, use =MATCH("Product B", A1:A10, 0) to find the position, then use =INDEX(B1:B10, MATCH("Product B", A1:A10, 0)) to get the price.

5. Question: How can you multiply two ranges and sum the result?

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

6. Question: If you want to avoid division errors in a formula, how can you handle that?

• Answer: Use =IFERROR(A1/B1, "Error") to return "Error" if the division results in an error.

7. Question: How do you extract the year from a date in cell D1?

• Answer: Use =YEAR(D1).

8. Question: If you want to find the number of working days between two dates, which formula do you use?

• Answer: Use =NETWORKDAYS(D1, E1).

9. Question: What function would you use to generate a random integer between 1 and 100?

• Answer: Use =RANDBETWEEN(1, 100).

10. Question: How can you join multiple cells with a comma as a delimiter?

• Answer: Use =TEXTJOIN(", ", TRUE, A1:A10) to join non-empty cells in A1 to A10.

I hope now you have better understanding starting with excel formula’s and what question you expect from an interview stand point. Let’s see you in next level :)

Cheers!