Top Excel Tips and Tricks (HACKS) to Make You a PRO (Free PDF) [PART-3]

Last Updated: February 28, 2024

- Written by Puneet

(Excel Tip-1) Sentence Case

In Excel, we have three different functions (LOWER, UPPER, and PROPER) to convert text into different cases. But there is no option to convert a text into a sentence case. Here is the formula which you can use:

excel tips and tricks to convert to sentence case

This formula converts the first letter of a sentence into capital and the rest all in small (learn how this formula works).

(Excel Tip-2) Random Numbers

In Excel, there are two specific functions that you can use to generate random numbers. First is RAND which generates random numbers between 0 and 1.

excel tips tricks rand

And second is RANDBETWEEN which generates random numbers within the range of two specific numbers.

excel tips tricks rand between

ALERT: These functions are volatile so whenever you re-calculate your worksheet or hit enter, they update their values so make sure to use them with caution. You can also use RANDBETWEEN to generate random letters and random dates.

(Excel Tip-3) Count Words

In Excel, there is no specific function to count words. You can count characters with LEN but not words. But, you can use the following formula which can help you to count words from a cell.

=LEN(A1)-LEN(SUBSTITUTE(A1,” “,”))+1
excel tips and tricks to count words

This formula counts the number of spaces from a cell and adds 1 to it after that which equals the total number of words in a cell.

(Excel Tip-4) Calculate the Age

The best way to calculate a person’s age is by using the DATEDIF. This mysterious function is specifically made to get the difference between a date range.

excel tips and tricks to calculate age

And the formula will be:

=”Your age is “& DATEDIF(Date-of-Birth,Today(),”y”) &” Year(s), “& DATEDIF(Date-of-Birth,TODAY(),”ym”)& ” MONTH(s) & “& DATEDIF(Date-of-Birth,TODAY(),”md”)& ” Day(s).”

(Excel Tip-5) Calculate the Ratio

I have figured out that there are four different ways to calculate the ratio in Excel but using a simple divide method is the easiest one. All you need to do is divide the larger number into the smaller ones and concatenate it with a colon and one and here’s the formula you need to use:

excel tips tricks to use simple divide to calculate ratio in excel

This formula divides the larger number by the smaller one so that you can take the smaller number as a base (1).

(Excel Tip-6) Root of Number

To calculate the square root, cube root, or any root of a number the best way is to use the exponent formula. In the exponent formula, you can specify the Nth number for which you want to calculate the root.


For example, if you want to calculate a square root of 625 then the formula will be:

excel tips tricks using an exponent operator formula to get square root

(Excel Tip-7) Days in a Month

To get a total number of days in a month dynamically you can use the following formula:

excel tips tricks get total number of days in a month using formula

(Excel Tip-8) Month’s Last Date

To simply get the last date of a month you can use the following dynamic formula.

excel tips and tricks to end of the month date

(Excel Tip-9) Reverse VLOOKUP

As we all know there is no way to look up to left for a value using VLOOKUP. But if you switch to INDEX MATCH you can look up in any direction.

index match sample formula enter

(Excel Tip-10) SUMPRODUCT IF

You can use the below formula to create a conditional SUMPRODUCT and product values using a condition.