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)**

- Note that numbers 65 to 90 will return capital letters
- 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**

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

Related : Learn **Transpose Function**

**20. Creating hyperlinks using HYPERLINK**

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

Related: Learn **Hyperlink Function**

**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

- Quotient Function
- 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

**100 Excel Formulas Explained [Video Series]****Make your VLOOKUP formula Robust****10 Tips to write better Excel Formulas****Formula for finding unique values****Use MIN and MAX to replace IF****IF Nested IF and Boolean Logic in Excel****Seven Date formulas to make life easy**

What your favorite formula hacks in Excel?