Often times when you work with excel you can use these hacks (simple formulas) in your larger formulas to ease your work. I could think of 25 of them but I am sure there are many. Why don’t you go through these and add to list the ones that you use most often..

1. Count Blank Cells in a Range using COUNTBLANK

Use this formula for counting only blank cells in a range

2. Count Text in a Range using COUNTA

Note that COUNTA will count everything (text and numbers) and COUNT will only count numbers. The difference of these two will give you the number of text items in a range

3. Use Wild cards with SUMIF/COUNTIF for approximation

Notice the asterisk (*) sign before and after the word “Mobile” that means there could be anything written before or after the word Mobile. Similar logic can be applied in COUNTIF as well

Related : Learn Countif and Sumif

4. Sum by Clubbing worksheets in a Formula

This is will take a total from Sheet 4 to Sheet 2 of the cell A1 on each sheet

5. Rounding off to the next multiple of 100 using CEILING Function

Related : Learn Ceiling Function

6. Rounding off to the previous multiple of 50

Related : Learn Floor Function

7. Stripping off decimals from a Number using INT Function

Related : Learn Int Function

8. Keeping only Decimals using MOD and INT

Related : Learn Int and Mod Function

9. Getting the name of the month from a Date using TEXT

10. Converting values into other

11. Generating Random Numbers

Related : Learn Randbetween Function

12. Generating Random Alphabets (Caps and Non Caps)

1. Note that numbers 65 to 90 will return capital letters
2. For lower case letters use the numbers 97 (for a) and 122 (for z) in the same formula

13. Retrieving the File Name Using the CELL, MID and SEARCH

14. Calculating EMIs

15. Matching data EXACTLY

Note that the EXACT Function not only matches the values but even the case (upper or lower) of the text. If the 2 values do not match, it returns a FALSE

16. Removing Extra Spaces

The Trim Function leaves one space by default between 2 words. Read this when your TRIM function fails to remove spaces

17. Changing the case of the TEXT – UPPER LOWER or PROPER

18. Get the Column or Row number of any cell

If you do not write anything in the ROW or COLUMN function, it will return the row or the columns number of the cell in which the formula is written

Related : Learn Row and Column function

19. Transpose your data – Horizontal to vertical or vice versa

1. First Select the range where you want to write the transpose the date
2. Then write the Transpose formula
3. The Press CTRL SHIFT and ENTER to execute the formula

Related : Learn Transpose Function

A hyperlink named Blog will be generated will which lead you to www.goodly.co.in

21. Extracting Formulas from the Cells

22. Finding the Value of PI

23. Find the power of any number

This means 2 to the power of 5. Which can also be written as =2^5

24. Finding the Sq Root of any number

25. Finding the Quotient

Related : Learn

1. Quotient Function
2. Use quotient for finding Quarter and Indian Financial year Quarter

The Last one is a Bonus and you’ll love it!

26. Converting a number of Roman Numbers

Can you guess the result.. sure enough it is XL

## Other Formula Resources

What your favorite formula hacks in Excel?