(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:
=UPPER(LEFT(A1,1))&LOWER(RIGHT(A1,LEN(A1)-1))
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.
And second is RANDBETWEEN which generates random numbers within the range of two specific numbers.
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
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.
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:
=Larger-Number/Smaller-Number&”:”&”1″
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.
=number^(1/n)For example, if you want to calculate a square root of 625 then the formula will be:
=625^(1/2)
(Excel Tip-7) Days in a Month
To get a total number of days in a month dynamically you can use the following formula:
=DAY(EOMONTH(TODAY(),0))
(Excel Tip-8) Month’s Last Date
To simply get the last date of a month you can use the following dynamic formula.
=DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)
(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.
(Excel Tip-10) SUMPRODUCT IF
You can use the below formula to create a conditional SUMPRODUCT and product values using a condition.
=SUMPRODUCT(–(C7:C19=C2),E7:E19,F7:F19)