15 Excel Formulas That Will Make You Feel Like An Expert

Excel is a spreadsheet software program created by Microsoft, Excel is a powerful data visualization and analysis tool. It uses spreadsheet to organize numbers and data with formulas and functions. Excel is used by many organizations and businesses around the world to carry out tasks such as financial analysis.

What has been your struggle in manipulating your way around Excel?

Are you struggling to create charts, or completing a VLOOKUP, or having difficulty understanding what really pivot tables are? Do not fret because this Simple, direct and precise guide provided for you in this article will lead you through the path of understanding and confidence. It might seem as though Excel is an impossible skill to acquire, but I assure you now that all you need to do is follow gradually the steps listed here watching carefully all the videos provided with quality practice time you will be an expert in no time.

Just ensure you stick around to the end.

Let's get straight into business!

Below are the 15 Excel formulas that will make you feel like an expert:

1.a. XLOOKUP instead of INDEX & MATCH

Both functions give us Chase's grade.

VLOOKUP takes 3 mandatory arguments:

- Value

- Lookup Array (where to look for the value)

- Return Array (what values to return)

VLOOKUP does the work of INDEX/MATCH with less effort.

1.b. XLOOKUP is upgraded version of VLOOKUP

We want to find John's income in the month of February.

This is a vertical lookup in column A and column C.

The formula: =XLOOKUP(lookup value, lookup array, return array).

1.c. XLOOKUP can also do HLOOKUP

We want to find Harper's income in the month of June.

This is a horizontal lookup in raw 4 and raw 8

The formula: =XLOOKUP(lookup value, lookup array, return array).

2. IF

The Excel "IF" function allows you to see if a condition you set is either true or false for a particular given value. If the condition is true, you get one result, If the condition is false, you get another result.

Use the IF function to make logical comparisons.

In our case, the logical comparison is to return the word True if the income is higher than 50,000.

If it is not, it returns False.

The formula: =IF(A2>50000,"True","False").

3. COUNTIFS

It applies criteria to cells across multiple ranges and counts the number of times all criteria are met.

Here, it counts the number of times of a given store and a product. i.e, with the COUNTIF function, Excel can count the number of times a word or number appears in any range of cells.

The formula: =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

4. SUMIFS

SUMIFS is used to sum the values in a range that meets multiple criteria that you specify.

In the example, we want the sum of our two criteria, which are stores and product.

The formula =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

5. CONCATENATE

Use this function to join two or more text strings into one string.

I'm trying to combine the first name and last name in one cell.

Don't forget the “ ” and the space in the middle of it to separate the two texts.

The formula: =CONCATENATE (A2,“ ”,B2).

6. PMT

It calculates the payment for a loan based on constant payments and a constant interest rate.

This can be used to calculate a car loan or mortgage payment.

Put a negative in the function so that the result is positive.

The formula: PMT(rate, nper, pv, [fv], [type]).

7. MATCH & ISNUMBER

MATCH searches and returns the position items in the range.

ISNA checks whether a value is a number, and returns True or False.

Only services, books, and dairy returned numbers. That is why they are true. 

This means that they are on both lists.

8. SEQUENCE & COUNTA

SEQUENCE returns a sequence of numbers in an array.

COUNTA counts the number of cells in a range that are not empty.

These two formulas combined produce a list where a sequence will continue populating when you add information to the list.

9. CONVERT

It converts a number from one measurement system to another.

There are multiple conversion calculations that you can do with this function. 

We had a set of data and converted it from Celsius to Fahrenheit.

10. UPPER, LOWER AND PROPER

These three functions convert the case of letters in strings.

-UPPER: converts a text string to all uppercase

- LOWER: converts a text string to all lowercase

- PROPER: converts a text string to the proper case.

11. FLASH FILL

Flash Fill automatically fills your data when it senses a pattern.

We typed the first name, the last name, and the initials and excel recognized the pattern and reproduced the desired result.

12. TRIM

The TRIM function removes extra spaces from a text string, whether at the beginning, middle, or end.

In the example, we had text strings that had a lot of spaces. When we used the function, the spaces were removed.

13.THE QUICK ANALYSIS TOOL

It is a collection of some selected options that you can use to analyze the data quickly.

It includes conditional formatting, charts, tables, pivot table, and sparklines.

14. PIVOT TABLES

A PivotTable is a powerful tool to calculate, summarize, and analyze data that lets you see comparisons, patterns, and trends in your data.

15. REPT

The function repeats text a given number of times.

The function was used to better look at how these two employees performed with their sales.

With some formatting, the REPT allowed us to have a bar chart.

And it's a wrap!

These are 15 Excel Formulas That Will Make You Feel Like An Expert.

Practice! Practice! Practice! is the secret ingredient in learning how to apply and manipulate these Excel formulas.

If you find this article to be helpful to you, do well also to share with friend and family.

Drop your reviews in the comment section! ✌