Excel Formulas List

If you are pursuing a career in finance or accounting, here is a tip: mastering Microsoft Excel can keep you a step ahead in the job hunt. 

In these fields, Excel is no longer just a tool but a highly sought-after skill. From financial modeling and budgeting to data analysis and forecasting, Excel allows professionals to manage large datasets, automate calculations, and generate insightful reports. Its ability to streamline complex tasks makes it a critical skill for anyone looking to succeed in finance or accounting.

Before you think it is all about memorising Excel functions, let’s clear up a common misconception. While Excel functions and formulas might sound the same, they aren’t. Functions are ready-made operations, like adding up a column or finding an average. Formulas, however, are user-defined equations that combine functions, values, and operators to solve complex tasks.

A strong understanding of Excel can significantly improve your job prospects and career growth. Here is a complete Excel formulas list that is widely acknowledged by hiring companies and can help you stand out in your career.

Types of Excel formulas: List of the most common ones

From logical tests to financial projections, Excel formulas do the heavy lifting. To get you started, we have compiled an Excel formulas list here. 

Excel formulas list for logical functions

1. IF: Think of the IF function as Excel’s decision-maker. It tests a condition and returns one value if true and another if false. For example, =IF(A2>50, “Pass”, “Fail”). If the value in A2 is more than 50, you get a “Pass.” If not, you get “Fail.” Simple, right?

1. OR: OR gives you flexibility, which means it returns TRUE if any condition is true. For example, =OR(A2>50, B2>60). If either A2 exceeds 50 or B2 exceeds 60, you are in the clear with a TRUE result.
2. COUNTIF and COUNTIFS: Counts the number of cells that meet a specific condition. Like COUNTIF, but with more conditions, COUNTIFS makes it useful for multi-criteria counting.  For example, =COUNTIFS(A1:A10, “>50”, B1:B10, “Yes”). It counts cells where A1 is over 50 and B1 equals “Yes.”

Excel formulas list for text functions

1. CONCATENATE: Excel has a way of glueing strings together. It is how you merge text without breaking a sweat. For example, =CONCATENATE(“Hello”, ” “, “World”). This turns “Hello” and “World” into one string: “Hello World.”
2. LEN: Want to know the length of a text string? LEN’s your go-to formula. It counts the number of characters. As an example, =LEN(“Excel is awesome”). This formula counts each letter and space, giving you 16 characters.

1. TRIM: This function tidies up your text by removing unnecessary spaces. For example, =TRIM(” Hello “) removes those pesky extra spaces and leaves you with a clean “Hello.”

Excel formulas list for financial functions

1. PMT: It calculates the periodic payment on a loan. It is a lifesaver when you are figuring out those monthly payments. For example: =PMT(5%/12, 36, 2,00,000). With a 5% interest rate, this formula tells you the monthly payment for a Rs. 2,00,000 loan over 36 months.
2. PV: Want to know the current value of an investment? The PV function tells you what that future cash is worth today. For example, =PV(5%/12, 36, -1,00,000, 0). This formula calculates the present value of 36 monthly payments of Rs. 1,00,000.
3. RATE: Need to figure out the interest rate for an annuity? The RATE function calculates your periodic interest rate. You can write =RATE(36, -1000, 2,50,000, 0) and calculate the interest rate for a loan of Rs 2,50,000 paid over 36 periods with Rs. 1000 per month.

Excel formulas list for lookup and reference functions

1. VLOOKUP: It is a classic Excel formula that looks up a value in the first column of a range and returns something from another column in the same row. For example, =VLOOKUP(A3, B2:D10, 3, TRUE). It finds the value in A3, then looks across columns B through D to return a result from the 3rd column of the range.
2. HLOOKUP: Like VLOOKUP, but horizontal. HLOOKUP looks across the top row and returns data from a specified row. For example, =HLOOKUP(A3, B2:D5, 2, TRUE) looks for the value in A3 across the top row and returns a result from the 2nd row in the B2:D5 range.
3. INDEX: When retrieving data from a specific position, INDEX is the go-to function. For example, =INDEX(B2:D10, 2, 3). This fetches the value from the 2nd row and 3rd column of the range B2.
4. XLOOKUP: This Excel formula searches a range or array for a specified value and returns a corresponding value from another range or array. This allows flexible searches in any direction with exact matches by default. For example, =XLOOKUP(“P100”, A2:A10, B2:B10, “Not Found”). This searches for the product ID “P100” in the A2:A10 range. If found, it returns the corresponding value from B2:B10. If not found, it returns “Not Found”.

Excel formulas list for statistical functions

1. STDEV: If you need to calculate how spread out your data is, STDEV gives you the standard deviation of a sample. For example, =STDEV(A1:A10) measures the spread of data in the range A1.
2. VAR: It calculates the variance of a sample, which tells you how much your data varies from the average. For example, =VAR(A1:A10). This formula gives you the variance for the data in A1.
3. CORREL: Need to know if two data sets move together? CORREL gives you the correlation coefficient. For example, =CORREL(A1:A10, B1:B10) tells you how strongly the data in A1 correlates with B1.

Wrapping up

It is about time you consider Excel on the path to becoming a pro accountant who can handle anything thrown your way. And if you want to take your expertise to the next level, a comprehensive online or offline Excel course is the perfect place to do that. These Excel courses go beyond just teaching formulas; they prepare you for the real-world challenges of finance and accounting. 

So, if you’re ready to sharpen your skills and stand out professionally, it’s time to dive in and excel!

FAQs

• How often should I practise Excel to stay sharp?

If you have an Excel formulas list handy, try practising daily or weekly to ensure proficiency, especially when adapting new techniques.

• Are Excel certifications valuable for job seekers?

Absolutely! Excel certifications highlight your skills and give you a competitive edge in finance and accounting roles.

• Are Excel formulas case-sensitive?

Excel formulas are generally not case-sensitive, but some functions like FIND are case-sensitive in their operations.