7 Crazy Excel Formulas that Do Amazing Things

If you are a regular user of Excel, then you know the value of Formulas in the spreadsheet. Formulas can help you to solve different issues in a second, so there are crazy excel formulas that do amazing things and improve your performance. Many people say that you have to join Excel training & certification courses to sharpen your skills on these formulas, but we recommend reading this article to get a high level of information to improve your skills easily.

Crazy Excel Formulas That Do Amazing Things

Now, we will give you brief information about the best formulas available for Excel so that you can implement them in your work to improve it completely.

1. Change Case

If you want to change the case (uppercase or lowercase) of every single cell of your spreadsheet, then you can use the formula given below:

proper() (you can also use lower() and upper() for changing the values)

For example: proper("excel spreadsheet") to get Excel Spreadsheet.

2. SUM

If you want to add two or more cells of different cells, you can do it using a simple SUM formula. So, here is the basic syntax for SUM

SUM(value 1, value 2, etc).

For example:

You want to add two values B1 and B6, then you can use this formula as

=SUM(B1,B6) (Press enter to apply formula)

You want to add a list of cells that begins from B1 and ends with B6, then you can use this formula as

=SUM(B1:B6) (Press enter to apply formula)

3. Subtraction

If you want to subtract two cells of different cells, you can do it using a simple Subtraction formula. So, here is the basic syntax for Subtraction

SUM(value 1, -value 2, etc).

For example:

You want to subtract two values B1 and B6, then you can use this formula as

*=SUM(B1,-B6) *(Press enter to apply formula. Remember that value behind the minus sign should be smaller).

4. Multiplication

If you want to multiply the values of two cells in Excel, you can easily use the Multiplication formula. So, here is the basic syntax for Multiplication

=PRODUCT(A1*B1 etc.)

For example:

You want to multiply two values B1 and B6, then you can use this formula as

=PRODUCT(B1*B6) (Press enter to apply formula).

If you want to multiply two or more values like B1, B2, and B3, then you need to use:

=PRODUCT(B1*B2*B3) (Press enter to apply formula).

5. Division

If you want to divide the values of two cells in Excel, you can easily use the Division formula. So, here is the basic syntax for Division

= (A1/B1 etc.)

For example:

You want to divide two values B1 and B6, then you can use this formula as

=(B1/B6) (Press enter to apply formula).

6. Average

If you want to find the average of specific cells in Excel, you can easily use the Average formula. So, here is the basic syntax for Average

** =AVERAGE(number1, number2, etc.) *or *=AVERAGE(Start Value:End Value) **

For example:

You want to find the average of B1, B2, B4, and B6, then you can use this formula as

= AVERAGE(B1, B2, B4, B6) (Press enter to apply formula).

7. DATE

If you want to use the DATE formula, then you can use =DATE(year, month, day). This Excel formula returns the data that corresponds to values in that particular cell or other cells. There is a huge variety of options available that can help you to use this crazy Excel formula to do amazing things.

You can create dates from a series of cell values, so highlight any empty cell and enter "=DATE, "at parentheses. After that, enter those specific details of the date in this "=DATE(year, month, day)"

If you want to set today's date automatically, then again highlight any empty cell then enter =DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY())) and press enter to apply changes.

Array

You can add, subtract, and multiply individual cells easily by entering an easy formula and press enter. However, it is hard to deal with multiple ranges, so Array is used for these conditions. Numerical arrays are useful for performing more than a single formula simultaneously in a single cell.

• To use the Array formula, type "=SUM," in parentheses, then enter the first of two or more ranges of cells you want to multiply. After performing this procedure, you will get "=SUM (C2:C5)" this type of formula.
• Now, add the asterisk after the last cell of your first range, which you have included in the formula. After that, enter the second range after this asterisk. As you are multiplying the second range of cells with the first, then the formula will look like this: =SUM(C2:C5*D2:D5)
• Remember that Array requires different commands, so after closing the parentheses on the array formula and press Ctrl+Shift+Enter. It will recognize the formula as the Array and complete the process.

Conclusion

In this article, we have provided complete information about crazy Excel formulas that do amazing things. We have tried to add up all of those basic functions, which you will get in Excel training, Excel tutorials, and Excel certification courses. Hence, these excel functions will help you to grow your knowledge and performance on Excel spreadsheets.