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

Formula’s : -

1. SUM:

• Formula: =SUM(range)
• Example: =SUM(A1:A10) - Adds the values in cells A1 to A10.

2. AVERAGE:

• Formula: =AVERAGE(range)
• Example: =AVERAGE(B1:B10) - Calculates the average of the values in cells B1 to B10.

3. COUNT:

• Formula: =COUNT(range)
• Example: =COUNT(C1:C10) - Counts the number of cells with numeric entries in C1 to C10.

4. MIN:

• Formula: =MIN(range)
• Example: =MIN(D1:D10) - Finds the minimum value in cells D1 to D10.

5. MAX:

• Formula: =MAX(E1:E10)
• Example: =MAX(E1:E10) - Finds the maximum value in cells E1 to E10.

6. IF:

• Formula: =IF(condition, value_if_true, value_if_false)
• Example: =IF(F1 > 500, "High", "Low") - Returns "High" if F1 is greater than 500, otherwise returns "Low".

7. PMT:

• Formula: =PMT(rate, nper, pv)
• Example: =PMT(5%/12, 60, -10000) - Calculates the monthly payment for a loan of $10,000 at an annual interest rate of 5% over 5 years.

8. ROUND:

• Formula: =ROUND(number, num_digits)
• Example: =ROUND(G1, 2) - Rounds the value in G1 to 2 decimal places.

9. CONCATENATE:

• Formula: =CONCATENATE(text1, text2, ...)
• Example: =CONCATENATE(H1, " ", I1) - Joins the text in H1 and I1 with a space in between.

10. VLOOKUP:

• Formula: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
• Example: =VLOOKUP(J1, A1:C10, 2, FALSE) - Looks for the value in J1 in the first column of A1and returns the corresponding value from the second column.

11. TODAY:

• Formula: =TODAY()
• Example: =TODAY() - Returns the current date.

12. NOW:

• Formula: =NOW()
• Example: =NOW() - Returns the current date and time.

13. TEXT:

• Formula: =TEXT(value, format_text)
• Example: =TEXT(K1, "$#,##0.00") - Formats the number in K1 as currency.

14. LEN:

• Formula: =LEN(text)
• Example: =LEN(M1) - Returns the number of characters in the text in M1.

15. LEFT:

• Formula: =LEFT(text, [num_chars])
• Example: =LEFT(N1, 3) - Returns the first three characters of the text in N1.

16. RIGHT:

• Formula: =RIGHT(text, [num_chars])
• Example: =RIGHT(O1, 2) - Returns the last two characters of the text in O1.

17. MID:

• Formula: =MID(text, start_num, num_chars)
• Example: =MID(P1, 2, 4) - Returns four characters starting from the second character of the text in P1.

18. ISNUMBER:

• Formula: =ISNUMBER(value)
• Example: =ISNUMBER(Q1) - Returns TRUE if Q1 contains a number.

19. ISBLANK:

• Formula: =ISBLANK(value)
• Example: =ISBLANK(R1) - Returns TRUE if R1 is blank.

20. TRIM:

• Formula: =TRIM(text)
• Example: =TRIM(S1) - Removes extra spaces from the text in S1.

Questions and Answers

1. Question: How do you calculate the total sales using the SUM formula?

• Answer: Use =SUM(A1:A10) to add the values in cells A1 through A10.

2. Question: What is the average expense if your expenses are listed in cells A1 to A10?

• Answer: Use =AVERAGE(A1:A10) to find the average of those expenses.

3. Question: How many sales transactions occurred if the sales data is in column B?

• Answer: Use =COUNT(B:B) to count the number of numeric entries in column B.

4. Question: What is the maximum revenue recorded in your sales data?

• Answer: Use =MAX(B1:B10) to find the highest revenue in the range B1 to B10.

5. Question: What is the minimum cost of goods sold in your inventory list?

• Answer: Use =MIN(C1:C10) to get the lowest cost in the range C1 to C10.

6. Question: How would you check if sales in cell A1 are greater than $500 and return “Yes” or “No”?

• Answer: Use =IF(A1 > 500, "Yes", "No") to check the condition.

7. Question: If you take a loan of $10,000 at a 5% interest rate for 3 years, what is your monthly payment?

• Answer: Use =PMT(5%/12, 36, -10000) which returns the monthly payment amount.

8. Question: How do you round the profit in cell A1 to two decimal places?

• Answer: Use =ROUND(A1, 2) to round the profit to two decimal places.

9. Question: How can you join the first name in cell A1 with the last name in cell B1?

• Answer: Use =CONCATENATE(A1, " ", B1) to combine both names with a space in between.

10. Question: How do you retrieve the price of a product listed in column A based on the product name in cell C1?

• Answer: Use =VLOOKUP(C1, A1:B10, 2, FALSE) to find the price in column B corresponding to the product name in column A.

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!