25 Formula Hacks for everyday use main

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

25 Formula Hacks for everyday use

Use this formula for counting only blank cells in a range

 

2. Count Text in a Range using COUNTA

25 Formula Hacks for everyday use 2

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

25 Formula Hacks for everyday use 3

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

25 Formula Hacks for everyday use 4

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

25 Formula Hacks for everyday use 5

Related : Learn Ceiling Function

 

6. Rounding off to the previous multiple of 50

25 Formula Hacks for everyday use 6

Related : Learn Floor Function

 

7. Stripping off decimals from a Number using INT Function

25 Formula Hacks for everyday use 7

Related : Learn Int Function

 

8. Keeping only Decimals using MOD and INT

25 Formula Hacks for everyday use 8

Related : Learn Int and Mod Function

 

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

25 Formula Hacks for everyday use 9

 

10. Converting values into other

25 Formula Hacks for everyday use 10

 

11. Generating Random Numbers

25 Formula Hacks for everyday use 11

Related : Learn Randbetween Function

 

12. Generating Random Alphabets (Caps and Non Caps)

25 Formula Hacks for everyday use 12

  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

25 Formula Hacks for everyday use 13

 

14. Calculating EMIs

25 Formula Hacks for everyday use 14

 

15. Matching data EXACTLY

25 Formula Hacks for everyday use 15

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

25 Formula Hacks for everyday use 16

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

25 Formula Hacks for everyday use 17

 

18. Get the Column or Row number of any cell

25 Formula Hacks for everyday use 18

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

25 Formula Hacks for everyday use 19

  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

 

20. Creating hyperlinks using HYPERLINK

25 Formula Hacks for everyday use 20

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

25 Formula Hacks for everyday use 21

 

22. Finding the Value of PI

25 Formula Hacks for everyday use 22

 

23. Find the power of any number

25 Formula Hacks for everyday use 23

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 Formula Hacks for everyday use 24

 

25. Finding the Quotient

25 Formula Hacks for everyday use 25

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

25 Formula Hacks for everyday use 26

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

 

Other Formula Resources

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

 

What your favorite formula hacks in Excel?

 



Topics that I write about...






Download Smart Ebooks on
Excel and Power BI